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

From: "David G. Johnston" <[email-masked]>
Date: Tue, 25 Jun 2024 22:35:29 -0700

On Tue, Jun 25, 2024 at 9:50 PM David Rowley <[email-masked]> wrote:

> On Wed, 26 Jun 2024 at 13:31, David G. Johnston
> <[email-masked]> 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.

---

From: Alvaro Herrera <[email-masked]>
Date: Wed, 26 Jun 2024 15:36:10 +0200

> only to the data types page, but not with nothing.

I agree that it'd be good to have _something_ -- the other stance seems
super unhelpful.  "We're not going to spend two lines to explain some
funny rules that determine surprising behavior here, because we assume
you have read all of our other 3000 pages of almost impenetrably dense
documentation" is not great from a user's point of view.  The behavior
of 'now' in DEFAULT clauses is something that has been asked about for
decades.

Arithmetic precedence is a terrible straw man argument.  Let's put that
aside.

-- 
Álvaro Herrera        Breisgau, Deutschland  —  https://www.EnterpriseDB.com/
"If it is not right, do not do it.
If it is not true, do not say it." (Marcus Aurelius, Meditations)

---

From: David Rowley <[email-masked]>
Date: Thu, 27 Jun 2024 11:54:01 +1200

On Wed, 26 Jun 2024 at 17:36, David G. Johnston
<[email-masked]> wrote:
> 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.

Are you able to share what the special behaviour is with DEFAULT
constraints and time literals that does not apply everywhere equally?

Maybe I'm slow on the uptake, but I've yet to see anything here where
time literals act in a special way DEFAULT constraints. This is why I
couldn't understand why we should be adding documentation about this
under CREATE TABLE.

I'd be happy to reconsider or retract my argument if you can show me
what I'm missing.

David