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

Commit01bde4f

Browse files
committed
Implement OR REPLACE option for CREATE AGGREGATE.
Aggregates have acquired a dozen or so optional attributes in recentyears for things like parallel query and moving-aggregate mode; thelack of an OR REPLACE option to add or change these for an existingagg makes extension upgrades gratuitously hard. Rectify.
1 parentf2004f1 commit01bde4f

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(constchar*aggName,
4747
OidaggNamespace,
48+
boolreplace,
4849
charaggKind,
4950
intnumArgs,
5051
intnumDirectArgs,
@@ -77,8 +78,10 @@ AggregateCreate(const char *aggName,
7778
{
7879
Relationaggdesc;
7980
HeapTupletup;
81+
HeapTupleoldtup;
8082
boolnulls[Natts_pg_aggregate];
8183
Datumvalues[Natts_pg_aggregate];
84+
boolreplaces[Natts_pg_aggregate];
8285
Form_pg_procproc;
8386
Oidtransfn;
8487
Oidfinalfn=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_aggregateoldagg= (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 FUNCTIONor DROPPROCEDURE */
426+
/* translator: first %s is DROP FUNCTION, DROP PROCEDUREor DROPAGGREGATE */
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,boololdstyle,List*parameters)
57+
DefineAggregate(ParseState*pstate,
58+
List*name,
59+
List*args,
60+
boololdstyle,
61+
List*parameters,
62+
boolreplace)
5863
{
5964
char*aggName;
6065
OidaggNamespace;
@@ -436,6 +441,7 @@ DefineAggregate(ParseState *pstate, List *name, List *args, bool oldstyle, List
436441
*/
437442
returnAggregateCreate(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
returnnewnode;
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-
CREATEAGGREGATEfunc_nameaggr_argsdefinition
5621+
CREATEopt_or_replaceAGGREGATEfunc_nameaggr_argsdefinition
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-
|CREATEAGGREGATEfunc_nameold_aggr_definition
5632+
|CREATEopt_or_replaceAGGREGATEfunc_nameold_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
|CREATEOPERATORany_operatordefinition

‎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
caseOBJECT_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
externObjectAddressAggregateCreate(constchar*aggName,
144144
OidaggNamespace,
145+
boolreplace,
145146
charaggKind,
146147
intnumArgs,
147148
intnumDirectArgs,

‎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
externObjectAddressDefineAggregate(ParseState*pstate,List*name,List*args,boololdstyle,
97-
List*parameters);
97+
List*parameters,boolreplace);
9898

9999
/* commands/opclasscmds.c */
100100
externObjectAddressDefineOpClass(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
boolif_not_exists;/* just do nothing if it already exists? */
2535+
boolreplace;/* 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)
[8]ページ先頭
©2009-2025 Movatter.jp