Standard compliant DEFAULT clause

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/