Skip to content

Commit 11c8669

Browse files
robertmhaasrobertmhaas
committed
Add parallel query support functions for assorted aggregates.
This lets us use parallel aggregate for a variety of useful cases that didn't work before, like sum(int8), sum(numeric), several versions of avg(), and various other functions. Add some regression tests, as well, testing the general sanity of these and future catalog entries. David Rowley, reviewed by Tomas Vondra, with a few further changes by me.
1 parent 7117685 commit 11c8669

File tree

11 files changed

+1317
-79
lines changed

11 files changed

+1317
-79
lines changed

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

Lines changed: 860 additions & 0 deletions
Large diffs are not rendered by default.

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

Lines changed: 49 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3524,6 +3524,55 @@ interval_accum(PG_FUNCTION_ARGS)
35243524
PG_RETURN_ARRAYTYPE_P(result);
35253525
}
35263526

3527+
Datum
3528+
interval_combine(PG_FUNCTION_ARGS)
3529+
{
3530+
ArrayType *transarray1 = PG_GETARG_ARRAYTYPE_P(0);
3531+
ArrayType *transarray2 = PG_GETARG_ARRAYTYPE_P(1);
3532+
Datum *transdatums1;
3533+
Datum *transdatums2;
3534+
int ndatums1;
3535+
int ndatums2;
3536+
Interval sum1,
3537+
N1;
3538+
Interval sum2,
3539+
N2;
3540+
3541+
Interval *newsum;
3542+
ArrayType *result;
3543+
3544+
deconstruct_array(transarray1,
3545+
INTERVALOID, sizeof(Interval), false, 'd',
3546+
&transdatums1, NULL, &ndatums1);
3547+
if (ndatums1 != 2)
3548+
elog(ERROR, "expected 2-element interval array");
3549+
3550+
sum1 = *(DatumGetIntervalP(transdatums1[0]));
3551+
N1 = *(DatumGetIntervalP(transdatums1[1]));
3552+
3553+
deconstruct_array(transarray2,
3554+
INTERVALOID, sizeof(Interval), false, 'd',
3555+
&transdatums2, NULL, &ndatums2);
3556+
if (ndatums2 != 2)
3557+
elog(ERROR, "expected 2-element interval array");
3558+
3559+
sum2 = *(DatumGetIntervalP(transdatums2[0]));
3560+
N2 = *(DatumGetIntervalP(transdatums2[1]));
3561+
3562+
newsum = DatumGetIntervalP(DirectFunctionCall2(interval_pl,
3563+
IntervalPGetDatum(&sum1),
3564+
IntervalPGetDatum(&sum2)));
3565+
N1.time += N2.time;
3566+
3567+
transdatums1[0] = IntervalPGetDatum(newsum);
3568+
transdatums1[1] = IntervalPGetDatum(&N1);
3569+
3570+
result = construct_array(transdatums1, 2,
3571+
INTERVALOID, sizeof(Interval), false, 'd');
3572+
3573+
PG_RETURN_ARRAYTYPE_P(result);
3574+
}
3575+
35273576
Datum
35283577
interval_accum_inv(PG_FUNCTION_ARGS)
35293578
{

‎src/include/catalog/catversion.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -53,6 +53,6 @@
5353
*/
5454

5555
/* yyyymmddN */
56-
#define CATALOG_VERSION_NO 201604051
56+
#define CATALOG_VERSION_NO 201604052
5757

5858
#endif

‎src/include/catalog/pg_aggregate.h

Lines changed: 54 additions & 54 deletions
Large diffs are not rendered by default.

‎src/include/catalog/pg_proc.h

Lines changed: 28 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2441,8 +2441,20 @@ DATA(insert OID = 1832 ( float8_stddev_samp PGNSP PGUID 12 1 0 0 0 f f f f t f
24412441
DESCR("aggregate final function");
24422442
DATA(insert OID = 1833 ( numeric_accum PGNSP PGUID 12 1 0 0 0 f f f f f f i s 2 0 2281 "2281 1700" _null_ _null_ _null_ _null_ _null_ numeric_accum _null_ _null_ _null_ ));
24432443
DESCR("aggregate transition function");
2444+
DATA(insert OID = 3341 ( numeric_combine PGNSP PGUID 12 1 0 0 0 f f f f f f i s 2 0 2281 "2281 2281" _null_ _null_ _null_ _null_ _null_ numeric_combine _null_ _null_ _null_ ));
2445+
DESCR("aggregate combine function");
24442446
DATA(insert OID = 2858 ( numeric_avg_accum PGNSP PGUID 12 1 0 0 0 f f f f f f i s 2 0 2281 "2281 1700" _null_ _null_ _null_ _null_ _null_ numeric_avg_accum _null_ _null_ _null_ ));
24452447
DESCR("aggregate transition function");
2448+
DATA(insert OID = 3337 ( numeric_avg_combine PGNSP PGUID 12 1 0 0 0 f f f f f f i s 2 0 2281 "2281 2281" _null_ _null_ _null_ _null_ _null_ numeric_avg_combine _null_ _null_ _null_ ));
2449+
DESCR("aggregate combine function");
2450+
DATA(insert OID = 2740 ( numeric_avg_serialize PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 17 "2281" _null_ _null_ _null_ _null_ _null_ numeric_avg_serialize _null_ _null_ _null_ ));
2451+
DESCR("aggregate serial function");
2452+
DATA(insert OID = 2741 ( numeric_avg_deserialize PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 2281 "17" _null_ _null_ _null_ _null_ _null_ numeric_avg_deserialize _null_ _null_ _null_ ));
2453+
DESCR("aggregate deserial function");
2454+
DATA(insert OID = 3335 ( numeric_serialize PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 17 "2281" _null_ _null_ _null_ _null_ _null_ numeric_serialize _null_ _null_ _null_ ));
2455+
DESCR("aggregate serial function");
2456+
DATA(insert OID = 3336 ( numeric_deserialize PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 2281 "17" _null_ _null_ _null_ _null_ _null_ numeric_deserialize _null_ _null_ _null_ ));
2457+
DESCR("aggregate deserial function");
24462458
DATA(insert OID = 3548 ( numeric_accum_inv PGNSP PGUID 12 1 0 0 0 f f f f f f i s 2 0 2281 "2281 1700" _null_ _null_ _null_ _null_ _null_ numeric_accum_inv _null_ _null_ _null_ ));
24472459
DESCR("aggregate transition function");
24482460
DATA(insert OID = 1834 ( int2_accum PGNSP PGUID 12 1 0 0 0 f f f f f f i s 2 0 2281 "2281 21" _null_ _null_ _null_ _null_ _null_ int2_accum _null_ _null_ _null_ ));
@@ -2451,6 +2463,12 @@ DATA(insert OID = 1835 ( int4_accum PGNSP PGUID 12 1 0 0 0 f f f f f f i s 2
24512463
DESCR("aggregate transition function");
24522464
DATA(insert OID = 1836 ( int8_accum PGNSP PGUID 12 1 0 0 0 f f f f f f i s 2 0 2281 "2281 20" _null_ _null_ _null_ _null_ _null_ int8_accum _null_ _null_ _null_ ));
24532465
DESCR("aggregate transition function");
2466+
DATA(insert OID = 3338 ( numeric_poly_combine PGNSP PGUID 12 1 0 0 0 f f f f f f i s 2 0 2281 "2281 2281" _null_ _null_ _null_ _null_ _null_ numeric_poly_combine _null_ _null_ _null_ ));
2467+
DESCR("aggregate combine function");
2468+
DATA(insert OID = 3339 ( numeric_poly_serialize PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 17 "2281" _null_ _null_ _null_ _null_ _null_ numeric_poly_serialize _null_ _null_ _null_ ));
2469+
DESCR("aggregate serial function");
2470+
DATA(insert OID = 3340 ( numeric_poly_deserialize PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 2281 "17" _null_ _null_ _null_ _null_ _null_ numeric_poly_deserialize _null_ _null_ _null_ ));
2471+
DESCR("aggregate deserial function");
24542472
DATA(insert OID = 2746 ( int8_avg_accum PGNSP PGUID 12 1 0 0 0 f f f f f f i s 2 0 2281 "2281 20" _null_ _null_ _null_ _null_ _null_ int8_avg_accum _null_ _null_ _null_ ));
24552473
DESCR("aggregate transition function");
24562474
DATA(insert OID = 3567 ( int2_accum_inv PGNSP PGUID 12 1 0 0 0 f f f f f f i s 2 0 2281 "2281 21" _null_ _null_ _null_ _null_ _null_ int2_accum_inv _null_ _null_ _null_ ));
@@ -2461,6 +2479,14 @@ DATA(insert OID = 3569 ( int8_accum_inv PGNSP PGUID 12 1 0 0 0 f f f f f f i
24612479
DESCR("aggregate transition function");
24622480
DATA(insert OID = 3387 ( int8_avg_accum_inv PGNSP PGUID 12 1 0 0 0 f f f f f f i s 2 0 2281 "2281 20" _null_ _null_ _null_ _null_ _null_ int8_avg_accum_inv _null_ _null_ _null_ ));
24632481
DESCR("aggregate transition function");
2482+
DATA(insert OID = 2785 ( int8_avg_combine PGNSP PGUID 12 1 0 0 0 f f f f f f i s 2 0 2281 "2281 2281" _null_ _null_ _null_ _null_ _null_ int8_avg_combine _null_ _null_ _null_ ));
2483+
DESCR("aggregate combine function");
2484+
DATA(insert OID = 2786 ( int8_avg_serialize PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 17 "2281" _null_ _null_ _null_ _null_ _null_ int8_avg_serialize _null_ _null_ _null_ ));
2485+
DESCR("aggregate serial function");
2486+
DATA(insert OID = 2787 ( int8_avg_deserialize PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 2281 "17" _null_ _null_ _null_ _null_ _null_ int8_avg_deserialize _null_ _null_ _null_ ));
2487+
DESCR("aggregate deserial function");
2488+
DATA(insert OID = 3324 ( int4_avg_combine PGNSP PGUID 12 1 0 0 0 f f f f f f i s 2 0 2281 "2281 2281" _null_ _null_ _null_ _null_ _null_ int4_avg_combine _null_ _null_ _null_ ));
2489+
DESCR("aggregate combine function");
24642490
DATA(insert OID = 3178 ( numeric_sum PGNSP PGUID 12 1 0 0 0 f f f f f f i s 1 0 1700 "2281" _null_ _null_ _null_ _null_ _null_ numeric_sum _null_ _null_ _null_ ));
24652491
DESCR("aggregate final function");
24662492
DATA(insert OID = 1837 ( numeric_avg PGNSP PGUID 12 1 0 0 0 f f f f f f i s 1 0 1700 "2281" _null_ _null_ _null_ _null_ _null_ numeric_avg _null_ _null_ _null_ ));
@@ -2494,6 +2520,8 @@ DESCR("aggregate final function");
24942520

24952521
DATA(insert OID = 1843 ( interval_accum PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 1187 "1187 1186" _null_ _null_ _null_ _null_ _null_ interval_accum _null_ _null_ _null_ ));
24962522
DESCR("aggregate transition function");
2523+
DATA(insert OID = 3325 ( interval_combine PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 1187 "1187 1187" _null_ _null_ _null_ _null_ _null_ interval_combine _null_ _null_ _null_ ));
2524+
DESCR("aggregate combine function");
24972525
DATA(insert OID = 3549 ( interval_accum_inv PGNSP PGUID 12 1 0 0 0 f f f f t f i s 2 0 1187 "1187 1186" _null_ _null_ _null_ _null_ _null_ interval_accum_inv _null_ _null_ _null_ ));
24982526
DESCR("aggregate transition function");
24992527
DATA(insert OID = 1844 ( interval_avg PGNSP PGUID 12 1 0 0 0 f f f f t f i s 1 0 1186 "1187" _null_ _null_ _null_ _null_ _null_ interval_avg _null_ _null_ _null_ ));

‎src/include/utils/builtins.h

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1064,15 +1064,27 @@ extern Datum numeric_float8_no_overflow(PG_FUNCTION_ARGS);
10641064
extern Datum float4_numeric(PG_FUNCTION_ARGS);
10651065
extern Datum numeric_float4(PG_FUNCTION_ARGS);
10661066
extern Datum numeric_accum(PG_FUNCTION_ARGS);
1067+
extern Datum numeric_combine(PG_FUNCTION_ARGS);
10671068
extern Datum numeric_avg_accum(PG_FUNCTION_ARGS);
1069+
extern Datum numeric_avg_combine(PG_FUNCTION_ARGS);
1070+
extern Datum numeric_avg_serialize(PG_FUNCTION_ARGS);
1071+
extern Datum numeric_avg_deserialize(PG_FUNCTION_ARGS);
1072+
extern Datum numeric_serialize(PG_FUNCTION_ARGS);
1073+
extern Datum numeric_deserialize(PG_FUNCTION_ARGS);
10681074
extern Datum numeric_accum_inv(PG_FUNCTION_ARGS);
10691075
extern Datum int2_accum(PG_FUNCTION_ARGS);
10701076
extern Datum int4_accum(PG_FUNCTION_ARGS);
10711077
extern Datum int8_accum(PG_FUNCTION_ARGS);
1078+
extern Datum numeric_poly_combine(PG_FUNCTION_ARGS);
1079+
extern Datum numeric_poly_serialize(PG_FUNCTION_ARGS);
1080+
extern Datum numeric_poly_deserialize(PG_FUNCTION_ARGS);
10721081
extern Datum int2_accum_inv(PG_FUNCTION_ARGS);
10731082
extern Datum int4_accum_inv(PG_FUNCTION_ARGS);
10741083
extern Datum int8_accum_inv(PG_FUNCTION_ARGS);
10751084
extern Datum int8_avg_accum(PG_FUNCTION_ARGS);
1085+
extern Datum int8_avg_combine(PG_FUNCTION_ARGS);
1086+
extern Datum int8_avg_serialize(PG_FUNCTION_ARGS);
1087+
extern Datum int8_avg_deserialize(PG_FUNCTION_ARGS);
10761088
extern Datum numeric_avg(PG_FUNCTION_ARGS);
10771089
extern Datum numeric_sum(PG_FUNCTION_ARGS);
10781090
extern Datum numeric_var_pop(PG_FUNCTION_ARGS);
@@ -1090,6 +1102,7 @@ extern Datum int4_sum(PG_FUNCTION_ARGS);
10901102
extern Datum int8_sum(PG_FUNCTION_ARGS);
10911103
extern Datum int2_avg_accum(PG_FUNCTION_ARGS);
10921104
extern Datum int4_avg_accum(PG_FUNCTION_ARGS);
1105+
extern Datum int4_avg_combine(PG_FUNCTION_ARGS);
10931106
extern Datum int2_avg_accum_inv(PG_FUNCTION_ARGS);
10941107
extern Datum int4_avg_accum_inv(PG_FUNCTION_ARGS);
10951108
extern Datum int8_avg_accum_inv(PG_FUNCTION_ARGS);

‎src/include/utils/timestamp.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -187,6 +187,7 @@ extern Datum interval_mul(PG_FUNCTION_ARGS);
187187
extern Datum mul_d_interval(PG_FUNCTION_ARGS);
188188
extern Datum interval_div(PG_FUNCTION_ARGS);
189189
extern Datum interval_accum(PG_FUNCTION_ARGS);
190+
extern Datum interval_combine(PG_FUNCTION_ARGS);
190191
extern Datum interval_accum_inv(PG_FUNCTION_ARGS);
191192
extern Datum interval_avg(PG_FUNCTION_ARGS);
192193

‎src/test/regress/expected/create_aggregate.out

Lines changed: 80 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -101,24 +101,93 @@ CREATE AGGREGATE sumdouble (float8)
101101
msfunc = float8pl,
102102
minvfunc = float8mi
103103
);
104-
-- Test aggregate combine function
104+
-- aggregate combine and serialization functions
105+
-- Ensure stype and serialtype can't be the same
106+
CREATE AGGREGATE myavg (numeric)
107+
(
108+
stype = internal,
109+
sfunc = numeric_avg_accum,
110+
serialtype = internal
111+
);
112+
ERROR: aggregate serialization type cannot be "internal"
113+
-- if serialtype is specified we need a serialfunc and deserialfunc
114+
CREATE AGGREGATE myavg (numeric)
115+
(
116+
stype = internal,
117+
sfunc = numeric_avg_accum,
118+
serialtype = bytea
119+
);
120+
ERROR: aggregate serialization function must be specified when serialization type is specified
121+
CREATE AGGREGATE myavg (numeric)
122+
(
123+
stype = internal,
124+
sfunc = numeric_avg_accum,
125+
serialtype = bytea,
126+
serialfunc = numeric_avg_serialize
127+
);
128+
ERROR: aggregate deserialization function must be specified when serialization type is specified
129+
-- serialfunc must have correct parameters
130+
CREATE AGGREGATE myavg (numeric)
131+
(
132+
stype = internal,
133+
sfunc = numeric_avg_accum,
134+
serialtype = bytea,
135+
serialfunc = numeric_avg_deserialize,
136+
deserialfunc = numeric_avg_deserialize
137+
);
138+
ERROR: function numeric_avg_deserialize(internal) does not exist
139+
-- deserialfunc must have correct parameters
140+
CREATE AGGREGATE myavg (numeric)
141+
(
142+
stype = internal,
143+
sfunc = numeric_avg_accum,
144+
serialtype = bytea,
145+
serialfunc = numeric_avg_serialize,
146+
deserialfunc = numeric_avg_serialize
147+
);
148+
ERROR: function numeric_avg_serialize(bytea) does not exist
149+
-- ensure return type of serialfunc is checked
150+
CREATE AGGREGATE myavg (numeric)
151+
(
152+
stype = internal,
153+
sfunc = numeric_avg_accum,
154+
serialtype = text,
155+
serialfunc = numeric_avg_serialize,
156+
deserialfunc = numeric_avg_deserialize
157+
);
158+
ERROR: return type of serialization function numeric_avg_serialize is not text
159+
-- ensure combine function parameters are checked
160+
CREATE AGGREGATE myavg (numeric)
161+
(
162+
stype = internal,
163+
sfunc = numeric_avg_accum,
164+
serialtype = bytea,
165+
serialfunc = numeric_avg_serialize,
166+
deserialfunc = numeric_avg_deserialize,
167+
combinefunc = int4larger
168+
);
169+
ERROR: function int4larger(internal, internal) does not exist
105170
-- ensure create aggregate works.
106-
CREATE AGGREGATE mysum (int)
171+
CREATE AGGREGATE myavg (numeric)
107172
(
108-
stype = int,
109-
sfunc = int4pl,
110-
combinefunc = int4pl
173+
stype = internal,
174+
sfunc = numeric_avg_accum,
175+
finalfunc = numeric_avg,
176+
serialtype = bytea,
177+
serialfunc = numeric_avg_serialize,
178+
deserialfunc = numeric_avg_deserialize,
179+
combinefunc = numeric_avg_combine
111180
);
112181
-- Ensure all these functions made it into the catalog
113-
SELECT aggfnoid,aggtransfn,aggcombinefn,aggtranstype
182+
SELECT aggfnoid,aggtransfn,aggcombinefn,aggtranstype,aggserialfn,aggdeserialfn,aggserialtype
114183
FROM pg_aggregate
115-
WHERE aggfnoid = 'mysum'::REGPROC;
116-
aggfnoid | aggtransfn | aggcombinefn | aggtranstype
117-
----------+------------+--------------+--------------
118-
mysum | int4pl | int4pl | 23
184+
WHERE aggfnoid = 'myavg'::REGPROC;
185+
aggfnoid | aggtransfn | aggcombinefn | aggtranstype | aggserialfn | aggdeserialfn | aggserialtype
186+
----------+-------------------+---------------------+--------------+-----------------------+-------------------------+---------------
187+
myavg | numeric_avg_accum | numeric_avg_combine | 2281 | numeric_avg_serialize | numeric_avg_deserialize | 17
119188
(1 row)
120189

121-
DROP AGGREGATE mysum (int);
190+
DROP AGGREGATE myavg (numeric);
122191
-- invalid: nonstrict inverse with strict forward function
123192
CREATE FUNCTION float8mi_n(float8, float8) RETURNS float8 AS
124193
$$ SELECT $1 - $2; $$

‎src/test/regress/expected/opr_sanity.out

Lines changed: 93 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -279,15 +279,21 @@ ORDER BY 1, 2;
279279
-- Look for functions that return type "internal" and do not have any
280280
-- "internal" argument. Such a function would be a security hole since
281281
-- it might be used to call an internal function from an SQL command.
282-
-- As of 7.3 this query should find only internal_in.
282+
-- As of 7.3 this query should find internal_in, and as of 9.6 aggregate
283+
-- deserialization will be found too. These should contain a runtime check to
284+
-- ensure they can only be called in an aggregate context.
283285
SELECT p1.oid, p1.proname
284286
FROM pg_proc as p1
285287
WHERE p1.prorettype = 'internal'::regtype AND NOT
286288
'internal'::regtype = ANY (p1.proargtypes);
287-
oid | proname
288-
------+-------------
289+
oid | proname
290+
------+--------------------------
291+
2741 | numeric_avg_deserialize
292+
3336 | numeric_deserialize
293+
3340 | numeric_poly_deserialize
294+
2787 | int8_avg_deserialize
289295
2304 | internal_in
290-
(1 row)
296+
(5 rows)
291297

292298
-- Look for functions that return a polymorphic type and do not have any
293299
-- polymorphic argument. Calls of such functions would be unresolvable
@@ -1528,6 +1534,89 @@ WHERE proisagg AND provariadic != 0 AND a.aggkind = 'n';
15281534
-----+---------
15291535
(0 rows)
15301536

1537+
-- Check that all serial functions have a return type the same as the serial
1538+
-- type.
1539+
SELECT a.aggserialfn,a.aggserialtype,p.prorettype
1540+
FROM pg_aggregate a
1541+
INNER JOIN pg_proc p ON a.aggserialfn = p.oid
1542+
WHERE a.aggserialtype <> p.prorettype;
1543+
aggserialfn | aggserialtype | prorettype
1544+
-------------+---------------+------------
1545+
(0 rows)
1546+
1547+
-- Check that all the deserial functions have the same input type as the
1548+
-- serialtype
1549+
SELECT a.aggserialfn,a.aggserialtype,p.proargtypes[0]
1550+
FROM pg_aggregate a
1551+
INNER JOIN pg_proc p ON a.aggdeserialfn = p.oid
1552+
WHERE p.proargtypes[0] <> a.aggserialtype;
1553+
aggserialfn | aggserialtype | proargtypes
1554+
-------------+---------------+-------------
1555+
(0 rows)
1556+
1557+
-- An aggregate should either have a complete set of serialtype, serial func
1558+
-- and deserial func, or none of them.
1559+
SELECT aggserialtype,aggserialfn,aggdeserialfn
1560+
FROM pg_aggregate
1561+
WHERE (aggserialtype <> 0 OR aggserialfn <> 0 OR aggdeserialfn <> 0)
1562+
AND (aggserialtype = 0 OR aggserialfn = 0 OR aggdeserialfn = 0);
1563+
aggserialtype | aggserialfn | aggdeserialfn
1564+
---------------+-------------+---------------
1565+
(0 rows)
1566+
1567+
-- Check that all aggregates with serialtypes have internal states.
1568+
-- (There's no point in serializing anything apart from internal)
1569+
SELECT aggfnoid,aggserialtype,aggtranstype
1570+
FROM pg_aggregate
1571+
WHERE aggserialtype <> 0 AND aggtranstype <> 'internal'::regtype;
1572+
aggfnoid | aggserialtype | aggtranstype
1573+
----------+---------------+--------------
1574+
(0 rows)
1575+
1576+
-- Check that all serial functions are strict. It's wasteful for these to be
1577+
-- called with NULL values.
1578+
SELECT aggfnoid,aggserialfn
1579+
FROM pg_aggregate a
1580+
INNER JOIN pg_proc p ON a.aggserialfn = p.oid
1581+
WHERE p.proisstrict = false;
1582+
aggfnoid | aggserialfn
1583+
----------+-------------
1584+
(0 rows)
1585+
1586+
-- Check that all deserial functions are strict. It's wasteful for these to be
1587+
-- called with NULL values.
1588+
SELECT aggfnoid,aggdeserialfn
1589+
FROM pg_aggregate a
1590+
INNER JOIN pg_proc p ON a.aggdeserialfn = p.oid
1591+
WHERE p.proisstrict = false;
1592+
aggfnoid | aggdeserialfn
1593+
----------+---------------
1594+
(0 rows)
1595+
1596+
-- Check that no combine functions with an INTERNAL return type are strict.
1597+
SELECT aggfnoid,aggcombinefn
1598+
FROM pg_aggregate a
1599+
INNER JOIN pg_proc p ON a.aggcombinefn = p.oid
1600+
INNER JOIN pg_type t ON a.aggtranstype = t.oid
1601+
WHERE t.typname = 'internal' AND p.proisstrict = true;
1602+
aggfnoid | aggcombinefn
1603+
----------+--------------
1604+
(0 rows)
1605+
1606+
-- Check that aggregates which have the same transition function also have
1607+
-- the same combine, serialization, and deserialization functions.
1608+
SELECT a.aggfnoid, a.aggcombinefn, a.aggserialfn, a.aggdeserialfn,
1609+
b.aggfnoid, b.aggcombinefn, b.aggserialfn, b.aggdeserialfn
1610+
FROM
1611+
pg_aggregate a, pg_aggregate b
1612+
WHERE
1613+
a.aggfnoid < b.aggfnoid AND a.aggtransfn = b.aggtransfn AND
1614+
(a.aggcombinefn != b.aggcombinefn OR a.aggserialfn != b.aggserialfn
1615+
OR a.aggdeserialfn != b.aggdeserialfn);
1616+
aggfnoid | aggcombinefn | aggserialfn | aggdeserialfn | aggfnoid | aggcombinefn | aggserialfn | aggdeserialfn
1617+
----------+--------------+-------------+---------------+----------+--------------+-------------+---------------
1618+
(0 rows)
1619+
15311620
-- **************** pg_opfamily ****************
15321621
-- Look for illegal values in pg_opfamily fields
15331622
SELECT p1.oid

0 commit comments

Comments
 (0)