Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sMLKI-001oJG-HP for pgsql-hackers@arkaria.postgresql.org; Wed, 26 Jun 2024 05:36:14 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1sMLKG-0024tK-5w for pgsql-hackers@arkaria.postgresql.org; Wed, 26 Jun 2024 05:36:12 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sMLKF-0024tC-Q7 for pgsql-hackers@lists.postgresql.org; Wed, 26 Jun 2024 05:36:12 +0000 Received: from mail-ot1-x32d.google.com ([2607:f8b0:4864:20::32d]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sMLKC-003bFs-T5 for pgsql-hackers@postgresql.org; Wed, 26 Jun 2024 05:36:11 +0000 Received: by mail-ot1-x32d.google.com with SMTP id 46e09a7af769-6f9fbec4fd9so3330864a34.2 for ; Tue, 25 Jun 2024 22:36:08 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1719380167; x=1719984967; darn=postgresql.org; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:from:to:cc:subject:date:message-id:reply-to; bh=7SMgYzylMNRnx4glymC/pQToU+jCTJdxBEk6croZ+u4=; b=E1H4UVzomOb0wCNShmtm8Jlrt7KGQ4Yn7mLAXaa3Cklh0PSaPA2AwlFK6yMhJK4luW p6zWBCcfiXT+ux5QYwOby8AiY/NzH3Tx0FpysM/8J8Af9ZBNBhsb0c7lD5c4sauTnm9y jHy/x2jBT/z/EZG020swA6mWjmCM5W8dcINXclzxH6lWttTptSdbZ/AS0xeVJnrq1evE dO1XGurxhFoXqgN0SHxdyzjTIKxe4eWal+A0Ns7n7BcpuxiM6iAVEaoHMRAZ9fg2c6jh RkcaRCSs4fpTqz1k2i/fOMpHFavYv39A8wDCeaFBGSnr8cydbNG9mqZ7bR6lo8YessFu wpdw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1719380167; x=1719984967; h=cc:to:subject:message-id:date:from:in-reply-to:references :mime-version:x-gm-message-state:from:to:cc:subject:date:message-id :reply-to; bh=7SMgYzylMNRnx4glymC/pQToU+jCTJdxBEk6croZ+u4=; b=d+wtIsb05citAXh6UCs3gvK1HFEYuzBZZTTlTijrmI6rbxVCZJco1gBLxX8m8xnD4P +OAqyVmQd035kxo35rVleKfkbmYlSMdIjR9mMhciLoqwCv7lDsv/3mZin/bUSS+X1g4d bBchjNgyqNu10hdWTyDah06N0ZvUc0xrmMBOThn1U2GyG11zTexEfRjbFIiWsRgdYKBk EtzKhplQzxo4lEMoPfTpkVDdPBrH73FJj/v4LQEYHnphyd648WOARmWNwdCwv1UkBGh8 Ghr2a04kcdNfL+UaCeCAIIuiwpuVf3VDGcPSwgIpbXfadFIDmx5w4JdNQya25laC13xn MHGQ== X-Forwarded-Encrypted: i=1; AJvYcCUOdanTLCSxnbhGM9mNJvZCYGSSwwUcfb5mpBB4ybM82JDIdnAYhf1UTU8NXy9QETtY8G6RDYYfdSV8mutMMNYvZDQGsDacR3eWDhyJ X-Gm-Message-State: AOJu0Yx4GLVYXa+1y30SuULb6c0xTf/T7PM8tGuK31t2nicqGQUaE+S7 df8lGP6O2ugTRvQ/GNc8TPWjo9wdJrWFAF16IIhoQ/fxqkMlM1uHM9hZuSOFN4XqJO629srPdDE hxqRxE2WZP5Tm8F5LIpijnunhIME= X-Google-Smtp-Source: AGHT+IE6EfvdaL1phVTNuRUHGTlzrkLeCNlg9tkv2tiSQ7laF/Id9S+7qH28O+trdWYxtCvFko9GLNMEJ6NotECr/Jg= X-Received: by 2002:a05:6870:96a1:b0:254:bf41:de9d with SMTP id 586e51a60fabf-25d06e8c2a2mr10308168fac.48.1719380166610; Tue, 25 Jun 2024 22:36:06 -0700 (PDT) MIME-Version: 1.0 References: <1362410.1719349169@sss.pgh.pa.us> <1390099.1719357084@sss.pgh.pa.us> In-Reply-To: From: "David G. Johnston" Date: Tue, 25 Jun 2024 22:35:29 -0700 Message-ID: Subject: Re: Should we document how column DEFAULT expressions work? To: David Rowley Cc: Tom Lane , James Coleman , pgsql-hackers Content-Type: multipart/alternative; boundary="00000000000010939f061bc46410" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --00000000000010939f061bc46410 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Tue, Jun 25, 2024 at 9:50=E2=80=AFPM David Rowley = wrote: > On Wed, 26 Jun 2024 at 13:31, David G. Johnston > 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 curre= nt > 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=3D# create table tdts2 (ts timestamptz default 'now()'); CREATE TABLE postgres=3D# \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=3D# create table testboold2 (expr boolean default boolean 'false')= ; CREATE TABLE postgres=3D# \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-DAT= ETIME-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. --00000000000010939f061bc46410 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Tue, Jun 25, 2024 at 9:50=E2=80=AFPM David Rowley <<= a href=3D"mailto:dgrowleyml@gmail.com">dgrowleyml@gmail.com> wrote:<= /span>
On Wed, 26 Jun 2024 at 13:31, David G. Johnston
<david.g= .johnston@gmail.com> wrote:
> I'd suggest adding to:
>
> DEFAULT default_expr
> The DEFAULT clause assigns a default data value for the column whose c= olumn definition it appears within. The value is any variable-free expressi= on (in particular, cross-references to other columns in the current table a= re not allowed). Subqueries are not allowed either. The data type of the de= fault 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, th= en the default is null.
>
> + Be aware that the [special timestamp values 1] are resolved immediat= ely, not upon insert.=C2=A0 Use the [date/time constructor functions 2] to = produce a time relative to the future insertion.

<= /div>
Annoyingly even this advice isn't correct:

postgres=3D# create table tdts2 (ts timestamptz default 'now()');<= br>CREATE TABLE
postgres=3D# \d tdts2
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0Table = "public.tdts2"
=C2=A0Column | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 Type =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | Collation | Nullable | =C2=A0= =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0 =C2=A0Default =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
=C2=A0 =C2=A0 =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0
--------+-----------------------= ---+-----------+----------+-------------------------------------------
-= ---------------
=C2=A0ts =C2=A0 =C2=A0 | timestamp with time zone | =C2= =A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| '= 2024-06-25 18:05:33.055377-07'::timestamp
=C2=A0with 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.=C2=A0 I think any argument for doing this could<= br> 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 equivalenc= e.=C2=A0 The time literals are clear deviations from expected behavior.=C2= =A0 Knowing operator precedence rules, they apply everywhere equally.=C2=A0= And we should document the deviations directly where they happen.=C2=A0 Ev= en if it's just a short link back to the source that describes the devi= ation.=C2=A0 I'm fine with something less verbose pointing only to the = data types page, but not with nothing.

Also, what about all the other pla= ces where someone could
use one of the special timestamp input values? Should CREATE VIEW get
a memo too?=C2=A0 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.=C2=A0 This intends to increase the former.=C2=A0 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 j= ust seems misleading.

postgres=3D# create table testb= oold2 (expr boolean default boolean 'false');
CREATE TABLE
po= stgres=3D# \d testboold2
=C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2= =A0Table "public.testboold2"
=C2=A0Column | =C2=A0Type =C2=A0 = | Collation | Nullable | Default
--------+---------+-----------+-------= ---+---------
=C2=A0expr =C2=A0 | boolean | =C2=A0 =C2=A0 =C2=A0 =C2=A0 = =C2=A0 | =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0| false

I would expect 'f' in the default column if the boolean casting of= the literal happened sooner.=C2=A0 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 def= ault expression contains any constants [1] they are converted into their ty= ped value during create table execution.=C2=A0 Thus time constants [1] save= into the default expression the time the command was executed."
=

[1]=C2=A0https://www.postgresql.org/d= ocs/current/sql-syntax-lexical.html#SQL-SYNTAX-CONSTANTS

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

= David J.

--00000000000010939f061bc46410--