Re: Should we document how column DEFAULT expressions work?

On Tue, Jun 25, 2024 at 9:50 PM David Rowley <dgrowleyml(at)gmail(dot)com> wrote:

> On Wed, 26 Jun 2024 at 13:31, David G. Johnston
> <david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> > I'd suggest adding to:
> >
> > DEFAULT default_expr
> > The DEFAULT clause assigns a default data value for the column whose
> column definition it appears within. The value is any variable-free
> expression (in particular, cross-references to other columns in the current
> table are not allowed). Subqueries are not allowed either. The data type of
> the default expression must match the data type of the column.
> >
> > The default expression will be used in any insert operation that does
> not specify a value for the column. If there is no default for a column,
> then the default is null.
> >
> > + Be aware that the [special timestamp values 1] are resolved
> immediately, not upon insert. Use the [date/time constructor functions 2]
> to produce a time relative to the future insertion.
>

Annoyingly even this advice isn't correct:

postgres=# create table tdts2 (ts timestamptz default 'now()');
CREATE TABLE
postgres=# \d tdts2
Table "public.tdts2"
Column | Type | Collation | Nullable |
Default

--------+--------------------------+-----------+----------+-------------------------------------------
----------------
ts | timestamp with time zone | | | '2024-06-25
18:05:33.055377-07'::timestamp
with time zone

I expected writing what looked like the function now() to be delayed
evaluated but since I put it into quotes, the OPs complaint, it got read as
the literal with ignored extra bits.

> FWIW, I disagree that we need to write anything about that in this
> part of the documentation. I think any argument for doing this could
> equally be applied to something like re-iterating what the operator
> precedence rules for arithmetic are, and I don't think that should be
> mentioned.

I disagree on this equivalence. The time literals are clear deviations
from expected behavior. Knowing operator precedence rules, they apply
everywhere equally. And we should document the deviations directly where
they happen. Even if it's just a short link back to the source that
describes the deviation. I'm fine with something less verbose pointing
only to the data types page, but not with nothing.

Also, what about all the other places where someone could
> use one of the special timestamp input values? Should CREATE VIEW get
> a memo too? How about PREPARE?
>

Yes.

> If people don't properly understand these special timestamp input
> values, then maybe the documentation in [1] needs to be improved.

Recall, and awareness, is the greater issue, not comprehension. This
intends to increase the former. I don't believe the latter is an issue,
though I haven't deep dived into it.

And the whole type casting happening right away just seems misleading.

postgres=# create table testboold2 (expr boolean default boolean 'false');
CREATE TABLE
postgres=# \d testboold2
Table "public.testboold2"
Column | Type | Collation | Nullable | Default
--------+---------+-----------+----------+---------
expr | boolean | | | false

I would expect 'f' in the default column if the boolean casting of the
literal happened sooner. Or I'd expect to see "boolean 'false'" as the
default expression if it is captured as-is.

So yes, saving an expression into the default column has nuances that
should be documented where default is defined.

Maybe the wording needs to be:

"If the default expression contains any constants [1] they are converted
into their typed value during create table execution. Thus time constants
[1] save into the default expression the time the command was executed."

[1]
https://www.postgresql.org/docs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS
[2]
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-SPECIAL-VALUES

I'd be happy to be pointed to other constants that resolve to an
execution-time specific environment in a similar manner. If there is
another one I'll rethink the wisdom of trying to document all of them in
each place. But reminding people that time is special and we have these
special values seems to provide meaningful reader benefit for the cost of a
couple of sentences repeated in a few places. That were valid a decade ago
no more or less than they are valid now.

David J.