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

From: "David G. Johnston" <[email-masked]>
Date: Tue, 25 Jun 2024 18:30:42 -0700

On Tue, Jun 25, 2024 at 4:11 PM Tom Lane <[email-masked]> wrote:

> James Coleman <[email-masked]> writes:
> > On Tue, Jun 25, 2024 at 4:59 PM Tom Lane <[email-masked]> wrote:
> >> Uh ... what?  I recall something about that with respect to certain
> >> features such as nextval(), but you're making it sound like there
> >> is something generic going on with DEFAULT.
>
> > Hmm, I guess I'd never considered anything besides cases like
> > nextval() and now(), but I see now that now() must also be special
> > cased (when quoted) since 'date_trunc(day, now())'::timestamp doesn't
> > work but 'now()'::timestamp does.
>
> Hmm, both of those behaviors are documented, but not in the same place
> and possibly not anywhere near where you looked for info about
> DEFAULT.  For instance, the Tip at the bottom of section 9.9.5
>
>
> https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT
>
> explains about how 'now'::timestamp isn't what to use in DEFAULT.
>
>
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.

[1]
https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-SPECIAL-VALUES
[2]
https://www.postgresql.org/docs/current/functions-datetime.html#FUNCTIONS-DATETIME-CURRENT

David J.

---

From: David Rowley <[email-masked]>
Date: Wed, 26 Jun 2024 16:50:00 +1200

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.

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

If people don't properly understand these special timestamp input
values, then maybe the documentation in [1] needs to be improved.  At
the moment the details are within parentheses. Namely "(In particular,
now and related strings are converted to a specific time value as soon
as they are read.)".  Maybe it would be better to be more explicit
there and mention that these are special values that the input
function understands which are translated to actual timestamp values
when the type's input function is called.  That could maybe be tied
into the DEFAULT clause documentation to mention that the input
function for constant values is called at DML time rather than DDL
time.  That way, we're not adding these (unsustainable) special cases
to the documentation.

David

[1] https://www.postgresql.org/docs/current/datatype-datetime.html#DATATYPE-DATETIME-SPECIAL-VALUES

---

From: Tom Lane <[email-masked]>
Date: Wed, 26 Jun 2024 01:12:57 -0400

> time.  That way, we're not adding these (unsustainable) special cases

This sounds like a reasonable approach to me for the
magic-input-values issue.  Do we want to do anything about
nextval()?  I guess if you hold your head at the correct
angle, that's also a magic-input-value issue, in the sense
that the question is when does regclass input get resolved.

			regards, tom lane

---

From: "David G. Johnston" <[email-masked]>
Date: Tue, 25 Jun 2024 23:00:11 -0700

On Tue, Jun 25, 2024 at 10:12 PM Tom Lane <[email-masked]> wrote:

>From observations we transform constants into the: " 'value'::type " syntax
which then makes it an operator resolved at execution time.  For every type
except time types the transformation leaves the constant as-is.  The
special time values are the exception whereby they get evaluated to a
specific time during the transformation.

postgres=# create table tser3 (id integer not null default nextval(regclass
'tser2_id_seq'));
CREATE TABLE
postgres=# \d tser3
                            Table "public.tser3"
 Column |  Type   | Collation | Nullable |              Default

--------+---------+-----------+----------+-----------------------------------
 id     | integer |           | not null | nextval('tser2_id_seq'::regclass)

I cannot figure out how to get "early binding" into the default. I.e.,
nextval(9000)

Since early binding is similar to the special timestamp behavior I'd say
nextval is behaving just as expected - literal transform, no evaluation.
We need only document the transforms that also evaluate.

David J.

---

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

On Wed, 26 Jun 2024 at 17:12, Tom Lane <[email-masked]> wrote:
> Do we want to do anything about

I think I'm not understanding what's special about that.  Aren't
'now'::timestamp and 'seq_name'::regclass are just casts that are
evaluated during parse time in transformExpr()?

David

---

From: Tom Lane <[email-masked]>
Date: Wed, 26 Jun 2024 19:38:25 -0400

> evaluated during parse time in transformExpr()?

Right.  But there is an example in the manual explaining how
these two things act differently:

	'seq_name'::regclass
	'seq_name'::text::regclass

The latter produces a constant of type text with a run-time
cast to regclass (and hence a run-time pg_class lookup).
IIRC, we document that mainly because the latter provides a way
to duplicate nextval()'s old behavior of run-time lookup.

Now that I think about it, there's a very parallel difference in
the behavior of

	'now'::timestamp
	'now'::text::timestamp

but I doubt that that example is shown anywhere.

			regards, tom lane