Date: Fri, 5 Jul 2024 16:31:13 -0400
From: Bruce Momjian <bruce@momjian.us>
To: David Rowley <dgrowleyml@gmail.com>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
	Peter Eisentraut <peter@eisentraut.org>,
	Tom Lane <tgl@sss.pgh.pa.us>, James Coleman <jtc331@gmail.com>,
	pgsql-hackers <pgsql-hackers@postgresql.org>
Subject: Re: Should we document how column DEFAULT expressions work?
Message-ID: <ZohYEScvGh3gn136@momjian.us>
References: <CAKFQuwbyjn0p5aKz1sKfJHQCTbGy1xhWaNR2xdnXv-X4oPdOGA@mail.gmail.com>
 <CAApHDvqShXhOat03iEV1VqXM68DBJ+zM95Ghbu0rcqs8KnhQQQ@mail.gmail.com>
 <1642803.1719447063@sss.pgh.pa.us>
 <CAApHDvouPRyga3-5ZYLt_RUhU942bk0WPVgro+U=R3cvBRXbeQ@mail.gmail.com>
 <ef4d9b38-b7a5-4c85-ba12-062929c2d136@eisentraut.org>
 <CAApHDvrURTcDn3GTsmXzm9VqT3acLh=T=aUYMuKrtozw0nq8Tg@mail.gmail.com>
 <CAKFQuwbOOQ20=Pb5j-k+Sws22H5W9tnj-vve4MC-GQ9RmmtTxA@mail.gmail.com>
 <CAApHDvogKE8RrHx=qmYfvpD_AiVLX14nLvvSrPbFCXWgiYrKxg@mail.gmail.com>
 <CAKFQuwZjuQX4H48MbaHZAX3s__LoHgkXsf2Eg--E=qtWFss0bg@mail.gmail.com>
 <CAApHDvrQ--Mzp00Yn=3SVB=YS_ZB15aeLywaK0n05-adk8WWmw@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="5LFC0ZuhqVL/lrXN"
Content-Disposition: inline
In-Reply-To: <CAApHDvrQ--Mzp00Yn=3SVB=YS_ZB15aeLywaK0n05-adk8WWmw@mail.gmail.com>
Archived-At: <https://www.postgresql.org/message-id/ZohYEScvGh3gn136%40momjian.us>
Precedence: bulk


--5LFC0ZuhqVL/lrXN
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Mon, Jul  1, 2024 at 02:52:42PM +1200, David Rowley wrote:
> On Mon, 1 Jul 2024 at 13:41, David G. Johnston
> <david.g.johnston@gmail.com> wrote:
> > I presume the relatively new atomic SQL functions pose a similar hazard.
> 
> Do you have an example of this?
> 
> > The fact that 'now()'::timestamp fails to fail doesn't help...
> 
> If that's the case, maybe a tiny step towards what Peter proposed is
> just to make trailing punctuation fail for timestamp special values in
> v18.

I dug into this and I have a suggestion at the end.  First, the special
values like 'now' are the only values that can be optionally quoted:

	SELECT current_timestamp::timestamptz;
	       current_timestamp
	-------------------------------
	 2024-07-05 15:15:22.692072-04
	
	SELECT 'current_timestamp'::timestamptz;
	ERROR:  invalid input syntax for type timestamp with time zone: "current_timestamp"

Also interestingly, "now" without quotes requires parentheses to make it
a function call:

	SELECT 'now'::timestamptz;
	          timestamptz
	-------------------------------
	 2024-07-05 15:17:11.394182-04
	
	SELECT 'now()'::timestamptz;
	          timestamptz
	-------------------------------
	 2024-07-05 15:17:15.201621-04
	
	SELECT now()::timestamptz;
	              now
	-------------------------------
	 2024-07-05 15:17:21.925611-04

	SELECT now::timestamptz;
	ERROR:  column "now" does not exist
	LINE 1: SELECT now::timestamptz;
	               ^
And the quoting shows "now" evaluation at function creation time:

	CREATE OR REPLACE FUNCTION testnow() RETURNS timestamptz LANGUAGE SQL
	RETURN 'now'::timestamptz;
	
	SELECT testnow();
	SELECT pg_sleep(5);
	SELECT testnow();
	            testnow
	-------------------------------
	 2024-07-05 15:19:38.915255-04
	
	            testnow
	-------------------------------
	 2024-07-05 15:19:38.915255-04 -- same
	
---------------------------------------------------------------------------	
	
	CREATE OR REPLACE FUNCTION testnow() RETURNS timestamptz LANGUAGE SQL
	RETURN 'now()'::timestamptz;

	SELECT testnow();
	SELECT pg_sleep(5);
	SELECT testnow();
	            testnow
	-------------------------------
	 2024-07-05 15:20:41.475997-04
	
	            testnow
	-------------------------------
	 2024-07-05 15:20:41.475997-04 -- same
	
---------------------------------------------------------------------------	
	
	CREATE OR REPLACE FUNCTION testnow() RETURNS timestamptz LANGUAGE SQL
	RETURN now()::timestamptz;

	SELECT testnow();
	SELECT pg_sleep(5);
	SELECT testnow();
	            testnow
	-------------------------------
	 2024-07-05 15:21:18.204574-04
	
	            testnow
	-------------------------------
	 2024-07-05 15:21:23.210442-04 -- different

I don't think we can bounce people around to different sections to
explain this --- I think we need text in the CREATE TABLE ... DEFAULT
section.  I think the now() case is unusual since there are few cases
where function calls can be put inside of single quotes.

I have written the attached patch to clarify the behavior.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.

--5LFC0ZuhqVL/lrXN
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="create_default.diff"

diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index f19306e7760..9bab4ec141e 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -888,6 +888,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
       match the data type of the column.
      </para>
 
+     <para>
+      Note, a string that returns a volatile result once cast to
+      a data type, like <literal>'now'::timestamptz</literal> and
+      <literal>'now()'::timestamptz</literal>, is evaluated at table
+      creation time, while <literal>now()::timestamptz</literal> (without
+      quotes) is evaluated at data insertion time.
+     </para>
+
      <para>
       The default expression will be used in any insert operation that
       does not specify a value for the column.  If there is no default

--5LFC0ZuhqVL/lrXN--