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))
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

Reply via email to