Skip to content

Commit 01bde4f

Browse files
RhodiumToadRhodiumToad
committed
Implement OR REPLACE option for CREATE AGGREGATE.
Aggregates have acquired a dozen or so optional attributes in recent years for things like parallel query and moving-aggregate mode; the lack of an OR REPLACE option to add or change these for an existing agg makes extension upgrades gratuitously hard. Rectify.
1 parent f2004f1 commit 01bde4f

File tree

13 files changed

+235
-24
lines changed

13 files changed

+235
-24
lines changed

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

Lines changed: 18 additions & 9 deletions
Original file line numberDiff line numberDiff line change
@@ -21,7 +21,7 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
CREATE AGGREGATE <replaceable class="parameter">name</replaceable> ( [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">arg_data_type</replaceable> [ , ... ] ) (
24+
CREATE [ OR REPLACE ] AGGREGATE <replaceable class="parameter">name</replaceable> ( [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">arg_data_type</replaceable> [ , ... ] ) (
2525
SFUNC = <replaceable class="parameter">sfunc</replaceable>,
2626
STYPE = <replaceable class="parameter">state_data_type</replaceable>
2727
[ , SSPACE = <replaceable class="parameter">state_data_size</replaceable> ]
@@ -44,7 +44,7 @@ CREATE AGGREGATE <replaceable class="parameter">name</replaceable> ( [ <replacea
4444
[ , PARALLEL = { SAFE | RESTRICTED | UNSAFE } ]
4545
)
4646

47-
CREATE AGGREGATE <replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">arg_data_type</replaceable> [ , ... ] ]
47+
CREATE [ OR REPLACE ] AGGREGATE <replaceable class="parameter">name</replaceable> ( [ [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">arg_data_type</replaceable> [ , ... ] ]
4848
ORDER BY [ <replaceable class="parameter">argmode</replaceable> ] [ <replaceable class="parameter">argname</replaceable> ] <replaceable class="parameter">arg_data_type</replaceable> [ , ... ] ) (
4949
SFUNC = <replaceable class="parameter">sfunc</replaceable>,
5050
STYPE = <replaceable class="parameter">state_data_type</replaceable>
@@ -59,7 +59,7 @@ CREATE AGGREGATE <replaceable class="parameter">name</replaceable> ( [ [ <replac
5959

6060
<phrase>or the old syntax</phrase>
6161

62-
CREATE AGGREGATE <replaceable class="parameter">name</replaceable> (
62+
CREATE [ OR REPLACE ] AGGREGATE <replaceable class="parameter">name</replaceable> (
6363
BASETYPE = <replaceable class="parameter">base_type</replaceable>,
6464
SFUNC = <replaceable class="parameter">sfunc</replaceable>,
6565
STYPE = <replaceable class="parameter">state_data_type</replaceable>
@@ -88,12 +88,21 @@ CREATE AGGREGATE <replaceable class="parameter">name</replaceable> (
8888
<title>Description</title>
8989

9090
<para>
91-
<command>CREATE AGGREGATE</command> defines a new aggregate
92-
function. Some basic and commonly-used aggregate functions are
93-
included with the distribution; they are documented in <xref
94-
linkend="functions-aggregate"/>. If one defines new types or needs
95-
an aggregate function not already provided, then <command>CREATE
96-
AGGREGATE</command> can be used to provide the desired features.
91+
<command>CREATE AGGREGATE</command> defines a new aggregate function.
92+
<command>CREATE OR REPLACE AGGREGATE</command> will either define a new
93+
aggregate function or replace an existing definition. Some basic and
94+
commonly-used aggregate functions are included with the distribution; they
95+
are documented in <xref linkend="functions-aggregate"/>. If one defines new
96+
types or needs an aggregate function not already provided, then
97+
<command>CREATE AGGREGATE</command> can be used to provide the desired
98+
features.
99+
</para>
100+
101+
<para>
102+
When replacing an existing definition, the argument types, result type,
103+
and number of direct arguments may not be changed. Also, the new definition
104+
must be of the same kind (ordinary aggregate, ordered-set aggregate, or
105+
hypothetical-set aggregate) as the old one.
97106
</para>
98107

99108
<para>

‎src/backend/catalog/pg_aggregate.c

Lines changed: 54 additions & 3 deletions
Original file line numberDiff line numberDiff line change
@@ -45,6 +45,7 @@ static Oid lookup_agg_function(List *fnName, int nargs, Oid *input_types,
4545
ObjectAddress
4646
AggregateCreate(const char *aggName,
4747
Oid aggNamespace,
48+
bool replace,
4849
char aggKind,
4950
int numArgs,
5051
int numDirectArgs,
@@ -77,8 +78,10 @@ AggregateCreate(const char *aggName,
7778
{
7879
Relation aggdesc;
7980
HeapTuple tup;
81+
HeapTuple oldtup;
8082
bool nulls[Natts_pg_aggregate];
8183
Datum values[Natts_pg_aggregate];
84+
bool replaces[Natts_pg_aggregate];
8285
Form_pg_proc proc;
8386
Oid transfn;
8487
Oid finalfn = InvalidOid; /* can be omitted */
@@ -609,7 +612,7 @@ AggregateCreate(const char *aggName,
609612

610613
myself = ProcedureCreate(aggName,
611614
aggNamespace,
612-
false, /* no replacement */
615+
replace, /* maybe replacement */
613616
false, /* doesn't return a set */
614617
finaltype, /* returnType */
615618
GetUserId(), /* proowner */
@@ -648,6 +651,7 @@ AggregateCreate(const char *aggName,
648651
{
649652
nulls[i] = false;
650653
values[i] = (Datum) NULL;
654+
replaces[i] = true;
651655
}
652656
values[Anum_pg_aggregate_aggfnoid - 1] = ObjectIdGetDatum(procOid);
653657
values[Anum_pg_aggregate_aggkind - 1] = CharGetDatum(aggKind);
@@ -678,8 +682,51 @@ AggregateCreate(const char *aggName,
678682
else
679683
nulls[Anum_pg_aggregate_aggminitval - 1] = true;
680684

681-
tup = heap_form_tuple(tupDesc, values, nulls);
682-
CatalogTupleInsert(aggdesc, tup);
685+
if (replace)
686+
oldtup = SearchSysCache1(AGGFNOID, ObjectIdGetDatum(procOid));
687+
else
688+
oldtup = NULL;
689+
690+
if (HeapTupleIsValid(oldtup))
691+
{
692+
Form_pg_aggregate oldagg = (Form_pg_aggregate) GETSTRUCT(oldtup);
693+
694+
/*
695+
* If we're replacing an existing entry, we need to validate that
696+
* we're not changing anything that would break callers.
697+
* Specifically we must not change aggkind or aggnumdirectargs,
698+
* which affect how an aggregate call is treated in parse
699+
* analysis.
700+
*/
701+
if (aggKind != oldagg->aggkind)
702+
ereport(ERROR,
703+
(errcode(ERRCODE_WRONG_OBJECT_TYPE),
704+
errmsg("cannot change routine kind"),
705+
(oldagg->aggkind == AGGKIND_NORMAL ?
706+
errdetail("\"%s\" is an ordinary aggregate function.", aggName) :
707+
oldagg->aggkind == AGGKIND_ORDERED_SET ?
708+
errdetail("\"%s\" is an ordered-set aggregate.", aggName) :
709+
oldagg->aggkind == AGGKIND_HYPOTHETICAL ?
710+
errdetail("\"%s\" is a hypothetical-set aggregate.", aggName) :
711+
0)));
712+
if (numDirectArgs != oldagg->aggnumdirectargs)
713+
ereport(ERROR,
714+
(errcode(ERRCODE_INVALID_FUNCTION_DEFINITION),
715+
errmsg("cannot change number of direct args of an aggregate function")));
716+
717+
replaces[Anum_pg_aggregate_aggfnoid - 1] = false;
718+
replaces[Anum_pg_aggregate_aggkind - 1] = false;
719+
replaces[Anum_pg_aggregate_aggnumdirectargs - 1] = false;
720+
721+
tup = heap_modify_tuple(oldtup, tupDesc, values, nulls, replaces);
722+
CatalogTupleUpdate(aggdesc, &tup->t_self, tup);
723+
ReleaseSysCache(oldtup);
724+
}
725+
else
726+
{
727+
tup = heap_form_tuple(tupDesc, values, nulls);
728+
CatalogTupleInsert(aggdesc, tup);
729+
}
683730

684731
table_close(aggdesc, RowExclusiveLock);
685732

@@ -688,6 +735,10 @@ AggregateCreate(const char *aggName,
688735
* made by ProcedureCreate). Note: we don't need an explicit dependency
689736
* on aggTransType since we depend on it indirectly through transfn.
690737
* Likewise for aggmTransType using the mtransfunc, if it exists.
738+
*
739+
* If we're replacing an existing definition, ProcedureCreate deleted all
740+
* our existing dependencies, so we have to do the same things here either
741+
* way.
691742
*/
692743

693744
/* Depends on transition function */

‎src/backend/catalog/pg_proc.c

Lines changed: 4 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -404,7 +404,9 @@ ProcedureCreate(const char *procedureName,
404404
errdetail("\"%s\" is a window function.", procedureName) :
405405
0)));
406406

407-
dropcmd = (prokind == PROKIND_PROCEDURE ? "DROP PROCEDURE" : "DROP FUNCTION");
407+
dropcmd = (prokind == PROKIND_PROCEDURE ? "DROP PROCEDURE" :
408+
prokind == PROKIND_AGGREGATE ? "DROP AGGREGATE" :
409+
"DROP FUNCTION");
408410

409411
/*
410412
* Not okay to change the return type of the existing proc, since
@@ -421,7 +423,7 @@ ProcedureCreate(const char *procedureName,
421423
prokind == PROKIND_PROCEDURE
422424
? errmsg("cannot change whether a procedure has output parameters")
423425
: errmsg("cannot change return type of existing function"),
424-
/* translator: first %s is DROP FUNCTION or DROP PROCEDURE */
426+
/* translator: first %s is DROP FUNCTION, DROP PROCEDURE or DROP AGGREGATE */
425427
errhint("Use %s %s first.",
426428
dropcmd,
427429
format_procedure(oldproc->oid))));

‎src/backend/commands/aggregatecmds.c

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -54,7 +54,12 @@ static char extractModify(DefElem *defel);
5454
* "parameters" is a list of DefElem representing the agg's definition clauses.
5555
*/
5656
ObjectAddress
57-
DefineAggregate(ParseState *pstate, List *name, List *args, bool oldstyle, List *parameters)
57+
DefineAggregate(ParseState *pstate,
58+
List *name,
59+
List *args,
60+
bool oldstyle,
61+
List *parameters,
62+
bool replace)
5863
{
5964
char *aggName;
6065
Oid aggNamespace;
@@ -436,6 +441,7 @@ DefineAggregate(ParseState *pstate, List *name, List *args, bool oldstyle, List
436441
*/
437442
return AggregateCreate(aggName, /* aggregate name */
438443
aggNamespace, /* namespace */
444+
replace,
439445
aggKind,
440446
numArgs,
441447
numDirectArgs,

‎src/backend/nodes/copyfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -3372,6 +3372,7 @@ _copyDefineStmt(const DefineStmt *from)
33723372
COPY_NODE_FIELD(args);
33733373
COPY_NODE_FIELD(definition);
33743374
COPY_SCALAR_FIELD(if_not_exists);
3375+
COPY_SCALAR_FIELD(replace);
33753376

33763377
return newnode;
33773378
}

‎src/backend/nodes/equalfuncs.c

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -1265,6 +1265,7 @@ _equalDefineStmt(const DefineStmt *a, const DefineStmt *b)
12651265
COMPARE_NODE_FIELD(args);
12661266
COMPARE_NODE_FIELD(definition);
12671267
COMPARE_SCALAR_FIELD(if_not_exists);
1268+
COMPARE_SCALAR_FIELD(replace);
12681269

12691270
return true;
12701271
}

‎src/backend/parser/gram.y

Lines changed: 9 additions & 7 deletions
Original file line numberDiff line numberDiff line change
@@ -5618,25 +5618,27 @@ CreateAssertionStmt:
56185618
*****************************************************************************/
56195619

56205620
DefineStmt:
5621-
CREATE AGGREGATE func_name aggr_args definition
5621+
CREATE opt_or_replace AGGREGATE func_name aggr_args definition
56225622
{
56235623
DefineStmt *n = makeNode(DefineStmt);
56245624
n->kind = OBJECT_AGGREGATE;
56255625
n->oldstyle = false;
5626-
n->defnames = $3;
5627-
n->args = $4;
5628-
n->definition = $5;
5626+
n->replace = $2;
5627+
n->defnames = $4;
5628+
n->args = $5;
5629+
n->definition = $6;
56295630
$$ = (Node *)n;
56305631
}
5631-
| CREATE AGGREGATE func_name old_aggr_definition
5632+
| CREATE opt_or_replace AGGREGATE func_name old_aggr_definition
56325633
{
56335634
/* old-style (pre-8.2) syntax for CREATE AGGREGATE */
56345635
DefineStmt *n = makeNode(DefineStmt);
56355636
n->kind = OBJECT_AGGREGATE;
56365637
n->oldstyle = true;
5637-
n->defnames = $3;
5638+
n->replace = $2;
5639+
n->defnames = $4;
56385640
n->args = NIL;
5639-
n->definition = $4;
5641+
n->definition = $5;
56405642
$$ = (Node *)n;
56415643
}
56425644
| CREATE OPERATOR any_operator definition

‎src/backend/tcop/utility.c

Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1237,7 +1237,8 @@ ProcessUtilitySlow(ParseState *pstate,
12371237
address =
12381238
DefineAggregate(pstate, stmt->defnames, stmt->args,
12391239
stmt->oldstyle,
1240-
stmt->definition);
1240+
stmt->definition,
1241+
stmt->replace);
12411242
break;
12421243
case OBJECT_OPERATOR:
12431244
Assert(stmt->args == NIL);

‎src/include/catalog/pg_aggregate.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -142,6 +142,7 @@ typedef FormData_pg_aggregate *Form_pg_aggregate;
142142

143143
extern ObjectAddress AggregateCreate(const char *aggName,
144144
Oid aggNamespace,
145+
bool replace,
145146
char aggKind,
146147
int numArgs,
147148
int numDirectArgs,

‎src/include/commands/defrem.h

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -94,7 +94,7 @@ extern void UpdateStatisticsForTypeChange(Oid statsOid,
9494

9595
/* commands/aggregatecmds.c */
9696
extern ObjectAddress DefineAggregate(ParseState *pstate, List *name, List *args, bool oldstyle,
97-
List *parameters);
97+
List *parameters, bool replace);
9898

9999
/* commands/opclasscmds.c */
100100
extern ObjectAddress DefineOpClass(CreateOpClassStmt *stmt);

‎src/include/nodes/parsenodes.h

Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -2532,6 +2532,7 @@ typedef struct DefineStmt
25322532
List *args; /* a list of TypeName (if needed) */
25332533
List *definition; /* a list of DefElem */
25342534
bool if_not_exists; /* just do nothing if it already exists? */
2535+
bool replace; /* replace if already exists? */
25352536
} DefineStmt;
25362537

25372538
/* ----------------------

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

Lines changed: 71 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -160,6 +160,77 @@ WHERE aggfnoid = 'myavg'::REGPROC;
160160
myavg | numeric_avg_accum | numeric_avg_combine | internal | numeric_avg_serialize | numeric_avg_deserialize | s
161161
(1 row)
162162

163+
DROP AGGREGATE myavg (numeric);
164+
-- create or replace aggregate
165+
CREATE AGGREGATE myavg (numeric)
166+
(
167+
stype = internal,
168+
sfunc = numeric_avg_accum,
169+
finalfunc = numeric_avg
170+
);
171+
CREATE OR REPLACE AGGREGATE myavg (numeric)
172+
(
173+
stype = internal,
174+
sfunc = numeric_avg_accum,
175+
finalfunc = numeric_avg,
176+
serialfunc = numeric_avg_serialize,
177+
deserialfunc = numeric_avg_deserialize,
178+
combinefunc = numeric_avg_combine,
179+
finalfunc_modify = shareable -- just to test a non-default setting
180+
);
181+
-- Ensure all these functions made it into the catalog again
182+
SELECT aggfnoid, aggtransfn, aggcombinefn, aggtranstype::regtype,
183+
aggserialfn, aggdeserialfn, aggfinalmodify
184+
FROM pg_aggregate
185+
WHERE aggfnoid = 'myavg'::REGPROC;
186+
aggfnoid | aggtransfn | aggcombinefn | aggtranstype | aggserialfn | aggdeserialfn | aggfinalmodify
187+
----------+-------------------+---------------------+--------------+-----------------------+-------------------------+----------------
188+
myavg | numeric_avg_accum | numeric_avg_combine | internal | numeric_avg_serialize | numeric_avg_deserialize | s
189+
(1 row)
190+
191+
-- can change stype:
192+
CREATE OR REPLACE AGGREGATE myavg (numeric)
193+
(
194+
stype = numeric,
195+
sfunc = numeric_add
196+
);
197+
SELECT aggfnoid, aggtransfn, aggcombinefn, aggtranstype::regtype,
198+
aggserialfn, aggdeserialfn, aggfinalmodify
199+
FROM pg_aggregate
200+
WHERE aggfnoid = 'myavg'::REGPROC;
201+
aggfnoid | aggtransfn | aggcombinefn | aggtranstype | aggserialfn | aggdeserialfn | aggfinalmodify
202+
----------+-------------+--------------+--------------+-------------+---------------+----------------
203+
myavg | numeric_add | - | numeric | - | - | r
204+
(1 row)
205+
206+
-- can't change return type:
207+
CREATE OR REPLACE AGGREGATE myavg (numeric)
208+
(
209+
stype = numeric,
210+
sfunc = numeric_add,
211+
finalfunc = numeric_out
212+
);
213+
ERROR: cannot change return type of existing function
214+
HINT: Use DROP AGGREGATE myavg(numeric) first.
215+
-- can't change to a different kind:
216+
CREATE OR REPLACE AGGREGATE myavg (order by numeric)
217+
(
218+
stype = numeric,
219+
sfunc = numeric_add
220+
);
221+
ERROR: cannot change routine kind
222+
DETAIL: "myavg" is an ordinary aggregate function.
223+
-- can't change plain function to aggregate:
224+
create function sum4(int8,int8,int8,int8) returns int8 as
225+
'select $1 + $2 + $3 + $4' language sql strict immutable;
226+
CREATE OR REPLACE AGGREGATE sum3 (int8,int8,int8)
227+
(
228+
stype = int8,
229+
sfunc = sum4
230+
);
231+
ERROR: cannot change routine kind
232+
DETAIL: "sum3" is a function.
233+
drop function sum4(int8,int8,int8,int8);
163234
DROP AGGREGATE myavg (numeric);
164235
-- invalid: bad parallel-safety marking
165236
CREATE AGGREGATE mysum (int)

0 commit comments

Comments
 (0)