Re: Should we document how column DEFAULT expressions work?

On Sun, Jun 30, 2024 at 5:47 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Mon, 1 Jul 2024 at 12:16, David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> >
> > On Sun, Jun 30, 2024 at 4:55 PM David Rowley <dgrowleyml(at)gmail(dot)com>
> wrote:
> >>
> >>
> >> 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?
> >>
> >
> > 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.
>
>
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.