Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit2e7a688

Browse files
committed
Add aggsortop column to pg_aggregate, so that MIN/MAX optimization can
be supported for all datatypes. Add CREATE AGGREGATE and pg_dump supporttoo. Add specialized min/max aggregates for bpchar, instead of dependingon text's min/max, because otherwise the possible use of bpchar indexescannot be recognized.initdb forced because of catalog changes.
1 parent3803f24 commit2e7a688

File tree

15 files changed

+356
-123
lines changed

15 files changed

+356
-123
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 9 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
<!--
22
Documentation of the system catalogs, directed toward PostgreSQL developers
3-
$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.99 2005/03/29 19:44:22 tgl Exp $
3+
$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.100 2005/04/12 04:26:13 tgl Exp $
44
-->
55

66
<chapter id="catalogs">
@@ -250,11 +250,17 @@
250250
<entry><literal><link linkend="catalog-pg-proc"><structname>pg_proc</structname></link>.oid</literal></entry>
251251
<entry>Final function (zero if none)</entry>
252252
</row>
253+
<row>
254+
<entry><structfield>aggsortop</structfield></entry>
255+
<entry><type>oid</type></entry>
256+
<entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</literal></entry>
257+
<entry>Associated sort operator (zero if none)</entry>
258+
</row>
253259
<row>
254260
<entry><structfield>aggtranstype</structfield></entry>
255261
<entry><type>oid</type></entry>
256262
<entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
257-
<entry>The type of the aggregate function's internal transition (state) data</entry>
263+
<entry>Data type of the aggregate function's internal transition (state) data</entry>
258264
</row>
259265
<row>
260266
<entry><structfield>agginitval</structfield></entry>
@@ -263,7 +269,7 @@
263269
<entry>
264270
The initial value of the transition state. This is a text
265271
field containing the initial value in its external string
266-
representation. Ifthe value is null, the transition state
272+
representation. Ifthis field is null, the transition state
267273
value starts out null.
268274
</entry>
269275
</row>

‎doc/src/sgml/ref/create_aggregate.sgml

Lines changed: 38 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_aggregate.sgml,v 1.31 2005/01/04 00:39:53 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_aggregate.sgml,v 1.32 2005/04/12 04:26:15 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -26,6 +26,7 @@ CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> (
2626
STYPE = <replaceable class="PARAMETER">state_data_type</replaceable>
2727
[ , FINALFUNC = <replaceable class="PARAMETER">ffunc</replaceable> ]
2828
[ , INITCOND = <replaceable class="PARAMETER">initial_condition</replaceable> ]
29+
[ , SORTOP = <replaceable class="PARAMETER">sort_operator</replaceable> ]
2930
)
3031
</synopsis>
3132
</refsynopsisdiv>
@@ -125,6 +126,29 @@ CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> (
125126
<function>avg</function> returns null when it sees there were zero
126127
input rows.
127128
</para>
129+
130+
<para>
131+
Aggregates that behave like <function>MIN</> or <function>MAX</> can
132+
sometimes be optimized by looking into an index instead of scanning every
133+
input row. If this aggregate can be so optimized, indicate it by
134+
specifying a <firstterm>sort operator</>. The basic requirement is that
135+
the aggregate must yield the first element in the sort ordering induced by
136+
the operator; in other words
137+
<programlisting>
138+
SELECT agg(col) FROM tab;
139+
</programlisting>
140+
must be equivalent to
141+
<programlisting>
142+
SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
143+
</programlisting>
144+
Further assumptions are that the aggregate ignores null inputs, and that
145+
it delivers a null result if and only if there were no non-null inputs.
146+
Ordinarily, a datatype's <literal>&lt;</> operator is the proper sort
147+
operator for <function>MIN</>, and <literal>&gt;</> is the proper sort
148+
operator for <function>MAX</>. Note that the optimization will never
149+
actually take effect unless the specified operator is the LessThan or
150+
GreaterThan strategy member of a btree index opclass.
151+
</para>
128152
</refsect1>
129153

130154
<refsect1>
@@ -211,6 +235,19 @@ CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> (
211235
</para>
212236
</listitem>
213237
</varlistentry>
238+
239+
<varlistentry>
240+
<term><replaceable class="PARAMETER">sort_operator</replaceable></term>
241+
<listitem>
242+
<para>
243+
The associated sort operator for a <function>MIN</>- or
244+
<function>MAX</>-like aggregate.
245+
This is just an operator name (possibly schema-qualified).
246+
The operator is assumed to have the same input datatypes as
247+
the aggregate.
248+
</para>
249+
</listitem>
250+
</varlistentry>
214251
</variablelist>
215252

216253
<para>

‎src/backend/catalog/pg_aggregate.c

Lines changed: 21 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/pg_aggregate.c,v 1.72 2005/03/31 22:46:06 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/pg_aggregate.c,v 1.73 2005/04/12 04:26:17 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -26,6 +26,7 @@
2626
#include"optimizer/cost.h"
2727
#include"parser/parse_coerce.h"
2828
#include"parser/parse_func.h"
29+
#include"parser/parse_oper.h"
2930
#include"utils/acl.h"
3031
#include"utils/builtins.h"
3132
#include"utils/lsyscache.h"
@@ -42,9 +43,10 @@ static Oid lookup_agg_function(List *fnName, int nargs, Oid *input_types,
4243
void
4344
AggregateCreate(constchar*aggName,
4445
OidaggNamespace,
46+
OidaggBaseType,
4547
List*aggtransfnName,
4648
List*aggfinalfnName,
47-
OidaggBaseType,
49+
List*aggsortopName,
4850
OidaggTransType,
4951
constchar*agginitval)
5052
{
@@ -55,6 +57,7 @@ AggregateCreate(const char *aggName,
5557
Form_pg_procproc;
5658
Oidtransfn;
5759
Oidfinalfn=InvalidOid;/* can be omitted */
60+
Oidsortop=InvalidOid;/* can be omitted */
5861
Oidrettype;
5962
Oidfinaltype;
6063
OidfnArgs[2];/* we only deal with 1- and 2-arg fns */
@@ -167,6 +170,12 @@ AggregateCreate(const char *aggName,
167170
errdetail("An aggregate returning \"anyarray\" or \"anyelement\" "
168171
"must have one of them as its base type.")));
169172

173+
/* handle sortop, if supplied */
174+
if (aggsortopName)
175+
sortop=LookupOperName(aggsortopName,
176+
aggBaseType,aggBaseType,
177+
false);
178+
170179
/*
171180
* Everything looks okay. Try to create the pg_proc entry for the
172181
* aggregate. (This could fail if there's already a conflicting
@@ -207,6 +216,7 @@ AggregateCreate(const char *aggName,
207216
values[Anum_pg_aggregate_aggfnoid-1]=ObjectIdGetDatum(procOid);
208217
values[Anum_pg_aggregate_aggtransfn-1]=ObjectIdGetDatum(transfn);
209218
values[Anum_pg_aggregate_aggfinalfn-1]=ObjectIdGetDatum(finalfn);
219+
values[Anum_pg_aggregate_aggsortop-1]=ObjectIdGetDatum(sortop);
210220
values[Anum_pg_aggregate_aggtranstype-1]=ObjectIdGetDatum(aggTransType);
211221
if (agginitval)
212222
values[Anum_pg_aggregate_agginitval-1]=
@@ -248,6 +258,15 @@ AggregateCreate(const char *aggName,
248258
referenced.objectSubId=0;
249259
recordDependencyOn(&myself,&referenced,DEPENDENCY_NORMAL);
250260
}
261+
262+
/* Depends on sort operator, if any */
263+
if (OidIsValid(sortop))
264+
{
265+
referenced.classId=get_system_catalog_relid(OperatorRelationName);
266+
referenced.objectId=sortop;
267+
referenced.objectSubId=0;
268+
recordDependencyOn(&myself,&referenced,DEPENDENCY_NORMAL);
269+
}
251270
}
252271

253272
/*

‎src/backend/commands/aggregatecmds.c

Lines changed: 6 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -9,7 +9,7 @@
99
*
1010
*
1111
* IDENTIFICATION
12-
* $PostgreSQL: pgsql/src/backend/commands/aggregatecmds.c,v 1.23 2005/03/29 00:16:57 tgl Exp $
12+
* $PostgreSQL: pgsql/src/backend/commands/aggregatecmds.c,v 1.24 2005/04/12 04:26:20 tgl Exp $
1313
*
1414
* DESCRIPTION
1515
* The "DefineFoo" routines take the parse tree and pick out the
@@ -51,6 +51,7 @@ DefineAggregate(List *names, List *parameters)
5151
AclResultaclresult;
5252
List*transfuncName=NIL;
5353
List*finalfuncName=NIL;
54+
List*sortoperatorName=NIL;
5455
TypeName*baseType=NULL;
5556
TypeName*transType=NULL;
5657
char*initval=NULL;
@@ -81,6 +82,8 @@ DefineAggregate(List *names, List *parameters)
8182
transfuncName=defGetQualifiedName(defel);
8283
elseif (pg_strcasecmp(defel->defname,"finalfunc")==0)
8384
finalfuncName=defGetQualifiedName(defel);
85+
elseif (pg_strcasecmp(defel->defname,"sortop")==0)
86+
sortoperatorName=defGetQualifiedName(defel);
8487
elseif (pg_strcasecmp(defel->defname,"basetype")==0)
8588
baseType=defGetTypeName(defel);
8689
elseif (pg_strcasecmp(defel->defname,"stype")==0)
@@ -143,9 +146,10 @@ DefineAggregate(List *names, List *parameters)
143146
*/
144147
AggregateCreate(aggName,/* aggregate name */
145148
aggNamespace,/* namespace */
149+
baseTypeId,/* type of data being aggregated */
146150
transfuncName,/* step function name */
147151
finalfuncName,/* final function name */
148-
baseTypeId,/*type of data being aggregated */
152+
sortoperatorName,/*sort operator name */
149153
transTypeId,/* transition data type */
150154
initval);/* initial condition */
151155
}

‎src/backend/optimizer/plan/planagg.c

Lines changed: 1 addition & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planagg.c,v 1.1 2005/04/11 23:06:55 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/optimizer/plan/planagg.c,v 1.2 2005/04/12 04:26:24 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -542,7 +542,6 @@ replace_aggs_with_params_mutator(Node *node, List **context)
542542
staticOid
543543
fetch_agg_sort_op(Oidaggfnoid)
544544
{
545-
#ifdefNOT_YET
546545
HeapTupleaggTuple;
547546
Form_pg_aggregateaggform;
548547
Oidaggsortop;
@@ -558,18 +557,4 @@ fetch_agg_sort_op(Oid aggfnoid)
558557
ReleaseSysCache(aggTuple);
559558

560559
returnaggsortop;
561-
#else
562-
/*
563-
* XXX stub implementation for testing: hardwire a few cases.
564-
*/
565-
if (aggfnoid==2132)/* min(int4) -> int4lt */
566-
return97;
567-
if (aggfnoid==2116)/* max(int4) -> int4gt */
568-
return521;
569-
if (aggfnoid==2145)/* min(text) -> text_lt */
570-
return664;
571-
if (aggfnoid==2129)/* max(text) -> text_gt */
572-
return666;
573-
returnInvalidOid;
574-
#endif
575560
}

‎src/backend/utils/adt/varchar.c

Lines changed: 35 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/utils/adt/varchar.c,v 1.108 2004/12/31 22:01:22 pgsql Exp $
11+
* $PostgreSQL: pgsql/src/backend/utils/adt/varchar.c,v 1.109 2005/04/12 04:26:26 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -687,6 +687,40 @@ bpcharcmp(PG_FUNCTION_ARGS)
687687
PG_RETURN_INT32(cmp);
688688
}
689689

690+
Datum
691+
bpchar_larger(PG_FUNCTION_ARGS)
692+
{
693+
BpChar*arg1=PG_GETARG_BPCHAR_P(0);
694+
BpChar*arg2=PG_GETARG_BPCHAR_P(1);
695+
intlen1,
696+
len2;
697+
intcmp;
698+
699+
len1=bcTruelen(arg1);
700+
len2=bcTruelen(arg2);
701+
702+
cmp=varstr_cmp(VARDATA(arg1),len1,VARDATA(arg2),len2);
703+
704+
PG_RETURN_BPCHAR_P((cmp >=0) ?arg1 :arg2);
705+
}
706+
707+
Datum
708+
bpchar_smaller(PG_FUNCTION_ARGS)
709+
{
710+
BpChar*arg1=PG_GETARG_BPCHAR_P(0);
711+
BpChar*arg2=PG_GETARG_BPCHAR_P(1);
712+
intlen1,
713+
len2;
714+
intcmp;
715+
716+
len1=bcTruelen(arg1);
717+
len2=bcTruelen(arg2);
718+
719+
cmp=varstr_cmp(VARDATA(arg1),len1,VARDATA(arg2),len2);
720+
721+
PG_RETURN_BPCHAR_P((cmp <=0) ?arg1 :arg2);
722+
}
723+
690724

691725
/*
692726
* bpchar needs a specialized hash function because we want to ignore

‎src/bin/pg_dump/pg_dump.c

Lines changed: 30 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -12,7 +12,7 @@
1212
*by PostgreSQL
1313
*
1414
* IDENTIFICATION
15-
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.405 2005/04/01 18:35:41 tgl Exp $
15+
* $PostgreSQL: pgsql/src/bin/pg_dump/pg_dump.c,v 1.406 2005/04/12 04:26:27 tgl Exp $
1616
*
1717
*-------------------------------------------------------------------------
1818
*/
@@ -6325,13 +6325,15 @@ dumpAgg(Archive *fout, AggInfo *agginfo)
63256325
intntups;
63266326
inti_aggtransfn;
63276327
inti_aggfinalfn;
6328+
inti_aggsortop;
63286329
inti_aggtranstype;
63296330
inti_agginitval;
63306331
inti_anybasetype;
63316332
inti_fmtbasetype;
63326333
inti_convertok;
63336334
constchar*aggtransfn;
63346335
constchar*aggfinalfn;
6336+
constchar*aggsortop;
63356337
constchar*aggtranstype;
63366338
constchar*agginitval;
63376339
boolconvertok;
@@ -6349,10 +6351,25 @@ dumpAgg(Archive *fout, AggInfo *agginfo)
63496351
selectSourceSchema(agginfo->aggfn.dobj.namespace->dobj.name);
63506352

63516353
/* Get aggregate-specific details */
6352-
if (g_fout->remoteVersion >=70300)
6354+
if (g_fout->remoteVersion >=80100)
6355+
{
6356+
appendPQExpBuffer(query,"SELECT aggtransfn, "
6357+
"aggfinalfn, aggtranstype::pg_catalog.regtype, "
6358+
"aggsortop::pg_catalog.regoperator, "
6359+
"agginitval, "
6360+
"proargtypes[0] = 'pg_catalog.\"any\"'::pg_catalog.regtype as anybasetype, "
6361+
"proargtypes[0]::pg_catalog.regtype as fmtbasetype, "
6362+
"'t'::boolean as convertok "
6363+
"from pg_catalog.pg_aggregate a, pg_catalog.pg_proc p "
6364+
"where a.aggfnoid = p.oid "
6365+
"and p.oid = '%u'::pg_catalog.oid",
6366+
agginfo->aggfn.dobj.catId.oid);
6367+
}
6368+
elseif (g_fout->remoteVersion >=70300)
63536369
{
63546370
appendPQExpBuffer(query,"SELECT aggtransfn, "
63556371
"aggfinalfn, aggtranstype::pg_catalog.regtype, "
6372+
"0 as aggsortop, "
63566373
"agginitval, "
63576374
"proargtypes[0] = 'pg_catalog.\"any\"'::pg_catalog.regtype as anybasetype, "
63586375
"proargtypes[0]::pg_catalog.regtype as fmtbasetype, "
@@ -6366,6 +6383,7 @@ dumpAgg(Archive *fout, AggInfo *agginfo)
63666383
{
63676384
appendPQExpBuffer(query,"SELECT aggtransfn, aggfinalfn, "
63686385
"format_type(aggtranstype, NULL) as aggtranstype, "
6386+
"0 as aggsortop, "
63696387
"agginitval, "
63706388
"aggbasetype = 0 as anybasetype, "
63716389
"CASE WHEN aggbasetype = 0 THEN '-' "
@@ -6380,6 +6398,7 @@ dumpAgg(Archive *fout, AggInfo *agginfo)
63806398
appendPQExpBuffer(query,"SELECT aggtransfn1 as aggtransfn, "
63816399
"aggfinalfn, "
63826400
"(select typname from pg_type where oid = aggtranstype1) as aggtranstype, "
6401+
"0 as aggsortop, "
63836402
"agginitval1 as agginitval, "
63846403
"aggbasetype = 0 as anybasetype, "
63856404
"(select typname from pg_type where oid = aggbasetype) as fmtbasetype, "
@@ -6403,6 +6422,7 @@ dumpAgg(Archive *fout, AggInfo *agginfo)
64036422

64046423
i_aggtransfn=PQfnumber(res,"aggtransfn");
64056424
i_aggfinalfn=PQfnumber(res,"aggfinalfn");
6425+
i_aggsortop=PQfnumber(res,"aggsortop");
64066426
i_aggtranstype=PQfnumber(res,"aggtranstype");
64076427
i_agginitval=PQfnumber(res,"agginitval");
64086428
i_anybasetype=PQfnumber(res,"anybasetype");
@@ -6411,6 +6431,7 @@ dumpAgg(Archive *fout, AggInfo *agginfo)
64116431

64126432
aggtransfn=PQgetvalue(res,0,i_aggtransfn);
64136433
aggfinalfn=PQgetvalue(res,0,i_aggfinalfn);
6434+
aggsortop=PQgetvalue(res,0,i_aggsortop);
64146435
aggtranstype=PQgetvalue(res,0,i_aggtranstype);
64156436
agginitval=PQgetvalue(res,0,i_agginitval);
64166437
/* we save anybasetype for format_aggregate_signature */
@@ -6471,6 +6492,13 @@ dumpAgg(Archive *fout, AggInfo *agginfo)
64716492
aggfinalfn);
64726493
}
64736494

6495+
aggsortop=convertOperatorReference(aggsortop);
6496+
if (aggsortop)
6497+
{
6498+
appendPQExpBuffer(details,",\n SORTOP = %s",
6499+
aggsortop);
6500+
}
6501+
64746502
/*
64756503
* DROP must be fully qualified in case same name appears in
64766504
* pg_catalog

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp