Skip to content

Commit 2e7a688

Browse files
tglsfdctglsfdc
committed
Add aggsortop column to pg_aggregate, so that MIN/MAX optimization can
be supported for all datatypes. Add CREATE AGGREGATE and pg_dump support too. Add specialized min/max aggregates for bpchar, instead of depending on text's min/max, because otherwise the possible use of bpchar indexes cannot be recognized. initdb forced because of catalog changes.
1 parent 3803f24 commit 2e7a688

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. If the value is null, the transition state
272+
representation. If this 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(const char *aggName,
4445
Oid aggNamespace,
46+
Oid aggBaseType,
4547
List *aggtransfnName,
4648
List *aggfinalfnName,
47-
Oid aggBaseType,
49+
List *aggsortopName,
4850
Oid aggTransType,
4951
const char *agginitval)
5052
{
@@ -55,6 +57,7 @@ AggregateCreate(const char *aggName,
5557
Form_pg_proc proc;
5658
Oid transfn;
5759
Oid finalfn = InvalidOid; /* can be omitted */
60+
Oid sortop = InvalidOid; /* can be omitted */
5861
Oid rettype;
5962
Oid finaltype;
6063
Oid fnArgs[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
AclResult aclresult;
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
else if (pg_strcasecmp(defel->defname, "finalfunc") == 0)
8384
finalfuncName = defGetQualifiedName(defel);
85+
else if (pg_strcasecmp(defel->defname, "sortop") == 0)
86+
sortoperatorName = defGetQualifiedName(defel);
8487
else if (pg_strcasecmp(defel->defname, "basetype") == 0)
8588
baseType = defGetTypeName(defel);
8689
else if (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
static Oid
543543
fetch_agg_sort_op(Oid aggfnoid)
544544
{
545-
#ifdef NOT_YET
546545
HeapTuple aggTuple;
547546
Form_pg_aggregate aggform;
548547
Oid aggsortop;
@@ -558,18 +557,4 @@ fetch_agg_sort_op(Oid aggfnoid)
558557
ReleaseSysCache(aggTuple);
559558

560559
return aggsortop;
561-
#else
562-
/*
563-
* XXX stub implementation for testing: hardwire a few cases.
564-
*/
565-
if (aggfnoid == 2132) /* min(int4) -> int4lt */
566-
return 97;
567-
if (aggfnoid == 2116) /* max(int4) -> int4gt */
568-
return 521;
569-
if (aggfnoid == 2145) /* min(text) -> text_lt */
570-
return 664;
571-
if (aggfnoid == 2129) /* max(text) -> text_gt */
572-
return 666;
573-
return InvalidOid;
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+
int len1,
696+
len2;
697+
int cmp;
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+
int len1,
713+
len2;
714+
int cmp;
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
int ntups;
63266326
int i_aggtransfn;
63276327
int i_aggfinalfn;
6328+
int i_aggsortop;
63286329
int i_aggtranstype;
63296330
int i_agginitval;
63306331
int i_anybasetype;
63316332
int i_fmtbasetype;
63326333
int i_convertok;
63336334
const char *aggtransfn;
63346335
const char *aggfinalfn;
6336+
const char *aggsortop;
63356337
const char *aggtranstype;
63366338
const char *agginitval;
63376339
bool convertok;
@@ -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+
else if (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)