Subject: Re: Should we document how column DEFAULT expressions work?

From: Peter Eisentraut <[email-masked]>
Date: Thu, 27 Jun 2024 13:57:37 +0200

On 27.06.24 02:34, David Rowley wrote:
> For the special timestamp stuff, that place is probably the special
> timestamp table in [1].  It looks like the large caution you added in
> 540849814 might not be enough or perhaps wasn't done soon enough to
> catch the people who read that part of the manual before the caution
> was added. Hard to fix if it's the latter without a time machine. :-(

Maybe we should really be thinking about deprecating these special 
values and steering users more urgently toward more robust alternatives.

Imagine if 'random' were a valid input value for numeric types.

---

From: David Rowley <[email-masked]>
Date: Mon, 1 Jul 2024 11:54:50 +1200

> Imagine if 'random' were a valid input value for numeric types.

I think there are valid reasons to use the special timestamp input
values.  One that I can think of is for use with partition pruning. If
you have a time-range partitioned table and want the planner to prune
the partitions rather than the executor, you could use
'now'::timestamp in your queries to allow the planner to prune. That
works providing that you never use that in combination with PREPARE
and never put the query with the WHERE clause inside a VIEW. I don't
have any other good examples, but I suppose that if someone needed to
capture the time some statement was executed and record that
somewhere, sort of like the __DATE__ and __TIME__ macros in C. Perhaps
that's useful to record the last time some DDL script was executed.

I'd like to know what led someone down the path of doing something
like DEFAULT 'now()'::timestamp in a CREATE TABLE. Could it be a
faulty migration tool that created these and people copy them thinking
it's a legitimate syntax?

David

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 30 Jun 2024 17:15:43 -0700

> it's a legitimate syntax?
My thought process on this used to be:  Provide a text string of the
expression that is then stored within the catalog and eval'd during
runtime.  If the only thing you are providing is a single literal and not
some compound expression it isn't that obvious that you are supposed to
provide an unquoted expression - which feels like it should be immediately
evaluated - versus something that is a constant.  Kinda like dynamic SQL.

David J.

---

From: David Rowley <[email-masked]>
Date: Mon, 1 Jul 2024 12:47:31 +1200

On Mon, 1 Jul 2024 at 12:16, David G. Johnston
<[email-masked]> wrote:
> My thought process on this used to be:  Provide a text string of the expression that is then stored within the catalog and eval'd during runtime.  If the only thing you are providing is a single literal and not some compound expression it isn't that obvious that you are supposed to provide an unquoted expression - which feels like it should be immediately evaluated - versus something that is a constant.  Kinda like dynamic SQL.

Thanks for sharing that.  Any idea where that thinking came from?

Maybe it was born from the fact that nothing complains when you do:
'now()'::timestamp? A quick test evaluation of that with a SELECT
statement might trick someone into thinking it'll work.

I wonder if there's anything else like this that might help fool
people into thinking this is some valid way of getting delayed
evaluation.

David

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 30 Jun 2024 18:41:17 -0700

> people into thinking this is some valid way of getting delayed
I presume the relatively new atomic SQL functions pose a similar hazard.

It probably boils down, for me, that I learned about, though never used,
eval functions from javascript, and figured this is probably implemented
something like that and I should thus supply a string.  Internalizing that
DDL can treat the unquoted content of expression in "DEFAULT expression" as
basically text hadn't happened; nor that the actual difference between just
treating it as text and the parsing to a standard form that really happens,
is quite important.  Namely that, in reverse of expectations, quoted
things, which are literals, are transformed to their typed values during
parse while functions, which are not quoted, don't have a meaningfully
different parsed form and are indeed executed at runtime.

The fact that 'now()'::timestamp fails to fail doesn't help...

Consider this phrasing for default:

The DEFAULT clause assigns a default data value for the column whose column
definition it appears within.  The expression is parsed according to
Section X.X.X, with the limitation that it may neither include references
to other columns nor subqueries, and then stored for later evaluation of
any functions it contains.  The data type of the default expression must
match the data type of the column.

Then in Section X.X.X we note, in part:
During parsing, all constants are immediately converted to their internal
representation.  In particular, the time-related literals noted in Section
8.5.1.4 get set to their date/time values.

Then, in 8.5.1.4 we should call out:
Caution:
'now' is a special time value, evaluated during parsing.
now() is a function, evaluated during execution.
'now()' is a special time value due to the quoting, PostgreSQL ignored the
parentheses.

The above doesn't make the special constants particularly special in how
they behave within parse-bind-execute while still noting that what they do
during parsing is a bit unique since a timestamp has not representation of
'tomorrow' that is can hold but instead is a short-hand for writing the
constant representing "whatever tomorrow is" at that moment.

I hope the reason for the additional caution in this framing is intuitive
for everyone.

There is probably a good paragraph or two that could be added under the new
Section X.X.X to centralize this for views, atomic sql, defaults, etc... to
refer to and give the reader the needed framing.

David J.

---

From: David Rowley <[email-masked]>
Date: Mon, 1 Jul 2024 14:52:42 +1200

> 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.

David

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 30 Jun 2024 20:08:33 -0700

> Do you have an example of this?
create function testnow() returns timestamptz language sql
return 'now'::timestamptz;

select testnow();
select pg_sleep(5);
select testnow(); -- same time as the first call

Which conforms with the documentation and expression parsing rules for
literals:

"This form is parsed at function definition time, the string constant form
is parsed at execution time;..."

David J.

---

From: "David G. Johnston" <[email-masked]>
Date: Sun, 30 Jun 2024 20:14:58 -0700

> just to make trailing punctuation fail for timestamp special values in
I'm game. If anyone is using the ambiguous spelling it is probably to their
benefit to have it break and realize they wanted a function expression, not
a constant expression.

David J.

---

From: James Coleman <[email-masked]>
Date: Mon, 1 Jul 2024 09:37:17 -0400

> My thought process on this used to be:  Provide a text string of the expression that is then stored within the catalog and eval'd during runtime.  If the only thing you are providing is a single li…

I have a similar story to tell: I've honestly never thought about it
deeply until I started this thread, but just through experimentation a
few things were obvious:

- now() as a function call gives you the current timestamp in a query
- now() as a function call in a DDL DEFAULT clause sets that as a
default function call
- Quoting that function call (using the function call syntax is the
natural thing to try, I think, if you've already done the first two)
-- because some examples online show quoting it -- gives you DDL time
evaluation.

So I suspect -- though I've been doing this for so long I couldn't
tell you for certain -- that I largely intuitive the behavior by
observation.

And similarly to David J. I'd then assumed -- but never had a need to
test it -- that this was generalized.

I think DDL is also different conceptually from SQL/DML here in a kind
of insidious way: the "bare" function call in DEFAULT is *not*
executed as part of the query for DDL like it is with other queries.

Hope this helps explain things.

James Coleman

---

From: Peter Eisentraut <[email-masked]>
Date: Mon, 1 Jul 2024 16:26:22 +0200

On 01.07.24 01:54, David Rowley wrote:
> 'now'::timestamp in your queries to allow the planner to prune.

Yeah, but is that a good user interface?  Or is that just something that 
happens to work now with the pieces that happened to be there, rather 
than a really designed interface?

Hypothetically, what would need to be done to make this work with now() 
or current_timestamp or something similar?  Do we need a new stability 
level that somehow encompasses this behavior, so that the function call 
can be evaluated at planning time?

> That
> and never put the query with the WHERE clause inside a VIEW.

And this kind of thing obviously makes this interface even worse.

---

From: Tom Lane <[email-masked]>
Date: Mon, 01 Jul 2024 10:43:53 -0400

> than a really designed interface?

That's not a very useful argument to make.  What percentage of the
SQL language as a whole is legacy cruft that we'd do differently if
we could?  I think the answer is depressingly high.  Adding more
special-purpose features to the ones already there doesn't move
that needle in a desirable direction.

I'd be more excited about this discussion if I didn't think that
the chances of removing 'now'::timestamp are exactly zero.  You
can't just delete useful decades-old features, whether there's
a better way or not.

			regards, tom lane

---

From: David Rowley <[email-masked]>
Date: Tue, 2 Jul 2024 13:48:28 +1200

> can't just delete useful decades-old features, whether there's

Do you have any thoughts on rejecting trailing punctuation with the
timestamp special values?

For me, I've mixed feelings about it. I think it would be good to
break things for people who are doing this and getting the wrong
behaviour who haven't noticed yet, however, there could be a series of
people doing this and have these embedded in statements that are
parsed directly before execution, and they just happen to get the
right behaviour.  It might be better not to upset the latter set of
people needlessly.  Perhaps the former set of people don't exist since
the behaviour is quite different and it seems quite obviously wrong.

David

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 5 Jul 2024 16:31:13 -0400

> just to make trailing punctuation fail for timestamp special values in

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  <[email-masked]>        https://momjian.us
  EDB                                      https://enterprisedb.com

  Only you can decide what is important to you.
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

---

From: Tom Lane <[email-masked]>
Date: Fri, 05 Jul 2024 16:50:32 -0400

> Also interestingly, "now" without quotes requires parentheses to make it

I'm not sure why you find that surprising, or why you think that
'now()'::timestamptz is a function call.  (Well, it is a call of
timestamptz_in, but not of the SQL function now().)  Documentation
that is equally confused won't help any.

			regards, tom lane

---

From: Bruce Momjian <[email-masked]>
Date: Fri, 5 Jul 2024 16:55:42 -0400

> that is equally confused won't help any.

Well, 'now()' certainly _looks_ like a function call, though it isn't. 
The fact that 'now()'::timestamptz and 'now'::timestamptz generate
volatile results via a function call was my point.

---

From: David Rowley <[email-masked]>
Date: Sat, 6 Jul 2024 10:43:44 +1200

> timestamp special values?

Cancel that idea. I'd thought that these special values must be
standalone, but I didn't realise until a few minutes ago that it's
perfectly valid to mix them:

select 'yesterday 13:00:00'::timestamp, 'yesterday allballs'::timestamp;

David