Обсуждение: Standard compliant DEFAULT clause
Hi, here's a fix for a _very_ longstanding bug in PostgreSQL. According to SQL:2003 DEFAULT may only contain certain functional expressions and constant literals. Please, note the year of the standard. Or I know a better one, PostgreSQL is not even SQL92 compliant in this regard, after 14 years! http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt Please review and apply immediately. Or not, it's just a bitter and late (because of my bitterness) response to the rejection of my IDENTITY/GENERATED patches. Where's the much praised standard behaviour on standard syntax? So much for hypocrisy. -- ---------------------------------- Zoltán Böszörményi Cybertec Geschwinde & Schönig GmbH http://www.postgresql.at/ --- pgsql.orig/src/backend/catalog/heap.c 2007-05-15 09:34:25.000000000 +0200 +++ pgsql/src/backend/catalog/heap.c 2007-05-18 21:33:04.000000000 +0200 @@ -1935,6 +1935,43 @@ errmsg("cannot use column references in default expression"))); /* + * Make sure default expr may contain only + * standard compliant functions as of SQL:2003: + * - CURRENT_DATE + * - CURRENT_TIME[ ( precision ) ] + * - CURRENT_TIMESTAMP[ ( precision ) ] + * - LOCALTIME[ ( precision ) ] + * - LOCALTIMESTAMP[ ( precision ) ] + * - as a PostgreSQL extension, + * all others that call now() implicitely or explicitely + * - USER + * - CURRENT_USER + * - CURRENT_ROLE + * - SESSION_USER + * with two other PostgreSQL extensions: + * - nextval() so SERIALs work + * - any immutable functions to pave the way for GENERATED columns + * Please note that PostgreSQL lacks SYSTEM_USER and CURRENT_PATH. + */ + if (is_opclause(expr)) { + OpExpr *clause = (OpExpr *)expr; + + switch (clause->opfuncid) + { + case 745: /* current_user */ + case 746: /* session_user */ + case 1299: /* now() */ + case 1574: /* nextval() */ + break; + default: + if (contain_mutable_functions(expr)) + ereport(ERROR, + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), + errmsg("cannot use non-IMMUTABLE functions in default expression"))); + } + } + + /* * It can't return a set either. */ if (expression_returns_set(expr))
Zoltan Boszormenyi wrote: > Hi, > > here's a fix for a _very_ longstanding bug in PostgreSQL. > > According to SQL:2003 DEFAULT may only contain > certain functional expressions and constant literals. > Please, note the year of the standard. Or I know a better one, > PostgreSQL is not even SQL92 compliant in this regard, after 14 years! > http://www.contrib.andrew.cmu.edu/~shadow/sql/sql1992.txt > > Please review and apply immediately. > Or not, it's just a bitter and late (because of my bitterness) response > to the rejection of my IDENTITY/GENERATED patches. > Where's the much praised standard behaviour on standard syntax? > So much for hypocrisy. Antagonism will not help your cause. Joshua D. Drake > > > ------------------------------------------------------------------------ > > --- pgsql.orig/src/backend/catalog/heap.c 2007-05-15 09:34:25.000000000 +0200 > +++ pgsql/src/backend/catalog/heap.c 2007-05-18 21:33:04.000000000 +0200 > @@ -1935,6 +1935,43 @@ > errmsg("cannot use column references in default expression"))); > > /* > + * Make sure default expr may contain only > + * standard compliant functions as of SQL:2003: > + * - CURRENT_DATE > + * - CURRENT_TIME[ ( precision ) ] > + * - CURRENT_TIMESTAMP[ ( precision ) ] > + * - LOCALTIME[ ( precision ) ] > + * - LOCALTIMESTAMP[ ( precision ) ] > + * - as a PostgreSQL extension, > + * all others that call now() implicitely or explicitely > + * - USER > + * - CURRENT_USER > + * - CURRENT_ROLE > + * - SESSION_USER > + * with two other PostgreSQL extensions: > + * - nextval() so SERIALs work > + * - any immutable functions to pave the way for GENERATED columns > + * Please note that PostgreSQL lacks SYSTEM_USER and CURRENT_PATH. > + */ > + if (is_opclause(expr)) { > + OpExpr *clause = (OpExpr *)expr; > + > + switch (clause->opfuncid) > + { > + case 745: /* current_user */ > + case 746: /* session_user */ > + case 1299: /* now() */ > + case 1574: /* nextval() */ > + break; > + default: > + if (contain_mutable_functions(expr)) > + ereport(ERROR, > + (errcode(ERRCODE_INVALID_OBJECT_DEFINITION), > + errmsg("cannot use non-IMMUTABLE functions in default expression"))); > + } > + } > + > + /* > * It can't return a set either. > */ > if (expression_returns_set(expr)) > > > ------------------------------------------------------------------------ > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: Don't 'kill -9' the postmaster -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/ Donate to the PostgreSQL Project: http://www.postgresql.org/about/donate PostgreSQL Replication: http://www.commandprompt.com/products/
Zoltan Boszormenyi <zb@cybertec.at> writes:
> Or not, it's just a bitter and late (because of my bitterness) response
> to the rejection of my IDENTITY/GENERATED patches.
> Where's the much praised standard behaviour on standard syntax?
> So much for hypocrisy.
Hm? There's a difference between extensions and failing to comply with
what the spec says is the behavior of the syntax it provides.
I feel bad that you put so much work into what now seems to be a dead
end (at least until we can get some clarification about what the
committee really intends). But look at the bright side: you learned
quite a bit about the innards of Postgres. Hopefully your next project
will be more successful.
regards, tom lane
Tom Lane írta: > Zoltan Boszormenyi <zb@cybertec.at> writes: > >> Or not, it's just a bitter and late (because of my bitterness) response >> to the rejection of my IDENTITY/GENERATED patches. >> Where's the much praised standard behaviour on standard syntax? >> So much for hypocrisy. >> > > Hm? There's a difference between extensions and failing to comply with > what the spec says is the behavior of the syntax it provides. > OK, that's where POVs and interpretations may differ. :-) The standard says one thing (allow these and only these kinds of expressions) which is a description of a behaviour, or can be interpreted as one. Now, if you allow others as well, is it an extension or failing to comply? :-) I have another question. How many features PostgreSQL have that copies other DMBS' behaviour (say, because of easy porting) and as such, differs slightly from the standard? Someone quoted DB2 during the early life of my patch, and it seems to me after reading DB2's online docs that GENERATED BY DEFAULT AS IDENTITY there behaves the was SERIAL behaves in PostgreSQL and the standard draft's text can be interpreted that way as well. > I feel bad that you put so much work into what now seems to be a dead > end (at least until we can get some clarification about what the > committee really intends). But look at the bright side: you learned > quite a bit about the innards of Postgres. Hopefully your next project > will be more successful. > > regards, tom lane > Thanks for the encouragement. I just needed to blow the the steam off somehow. Maybe the word "hypocrisy" was too harsh, sorry for that. I will shut up now. -- ---------------------------------- Zoltán Böszörményi Cybertec Geschwinde & Schönig GmbH http://www.postgresql.at/