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 1sO63P-00CKTc-9R for pgsql-hackers@arkaria.postgresql.org; Mon, 01 Jul 2024 01:42:03 +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 1sO63M-00EJ6f-OE for pgsql-hackers@arkaria.postgresql.org; Mon, 01 Jul 2024 01:42:01 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1sO63M-00EJ6W-3f for pgsql-hackers@lists.postgresql.org; Mon, 01 Jul 2024 01:42:00 +0000 Received: from mail-oa1-x33.google.com ([2001:4860:4864:20::33]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1sO63J-003x1H-6E for pgsql-hackers@postgresql.org; Mon, 01 Jul 2024 01:41:58 +0000 Received: by mail-oa1-x33.google.com with SMTP id 586e51a60fabf-250ca14422aso1452641fac.0 for ; Sun, 30 Jun 2024 18:41:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20230601; t=1719798116; x=1720402916; 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=vpAHsSRidIdMPQenMBWHD7gYtyhmr8SJuhYI3RTheCM=; b=YdTUtkEd+cp0vzT7fDx60E2T/kSLp5GH3pT9s0FuEJoap75j/LJAiooeU7TtKfoQtM cW6C0Foj3dxM6OE+vRvwg2q7efTJCTMAdyPUvedC0vto/n1H6oVrqG6PXZc0hRW1gjV7 +DanDEC7unWSRKM0IPoL+K1jqlgbESDqaiajjmkKjKHulVLkJU/GOLsRGC8ehnEHNvlO /R5LPek4AEVccJzZwYW9Mpt6hKLUQPiVL3Pxc8P9hnPJ+cbG765X74CWtAIilctI6jOQ iWhjWNniKWnuQZGLev7EwEHVOqbwpY8JXvWbZnSStLy86X1SAt0TVBGPjSTG6eB5TbwN Bv7A== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20230601; t=1719798116; x=1720402916; 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=vpAHsSRidIdMPQenMBWHD7gYtyhmr8SJuhYI3RTheCM=; b=WwgzV6GpBhcCzu9UROy80MKcvi9KBlH9XtXokDIuzlP7jdjvynuYxbh89PzbgBXmtX b10jyMjXTXC8Ahrnrzb8jv/XZIBnDbG6LbVgIJw/q/pu/3LX6AJ7mgZs4cAwZQQJmZm4 rCuRDawTXuFAgs2t091l7Rjs67H88KscTD3VNaoUfVf2jR9iQVO1homyWNw2ll2Qqq13 dPNIZgjc6DUMYTnKPQpnc1D8hKZ5KQ7PpeoC8Bwlpt1pVaHzNu2DTgK9RgDlv57m4OFL 5at106AoqIooNSpWB+zHmAxAY7GST6G0Q+qPrJf2hje4Gg9EbHY5aWtSZwIk0egKrMhI FdRQ== X-Forwarded-Encrypted: i=1; AJvYcCWBNl/bwUMr/G/VEnmF7Gs9BIhWSNBuh2g3a/jrRLVmiQR5i+H3E8XxUgYW3XOO3dgly50+vgE4cTCcJXfvGxsrbqeopHXg8Qb7wRmD X-Gm-Message-State: AOJu0Yx6HAlssSagtafiWG4cOeexEYHfchITdyGXRBe76YIHgk+jHOg/ bLBKneMlH8/z//1H6bTDqKBrtnhFaRrMhhDJHpQe+zsUWMhJM4735niE6Ko1VDL2JEIlG0cBfzX iMfCBWQO9u9bq11Us5TyWeF2tYxI= X-Google-Smtp-Source: AGHT+IEl4WJbPiSWW4ZClHXeHs51pbK6ZKzP/2twNIPN5ULkkEjbkacoy/vlbjLY7pjt8WueAM/AM2WU5fl31bw1x6A= X-Received: by 2002:a05:6871:7806:b0:24f:d498:5e01 with SMTP id 586e51a60fabf-25db361fb3bmr3575871fac.57.1719798116212; Sun, 30 Jun 2024 18:41:56 -0700 (PDT) MIME-Version: 1.0 References: <1362410.1719349169@sss.pgh.pa.us> <1390099.1719357084@sss.pgh.pa.us> <1642803.1719447063@sss.pgh.pa.us> In-Reply-To: From: "David G. Johnston" Date: Sun, 30 Jun 2024 18:41:17 -0700 Message-ID: Subject: Re: Should we document how column DEFAULT expressions work? To: David Rowley Cc: Peter Eisentraut , Tom Lane , James Coleman , pgsql-hackers Content-Type: multipart/alternative; boundary="000000000000cd67f4061c25b3c0" List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --000000000000cd67f4061c25b3c0 Content-Type: text/plain; charset="UTF-8" Content-Transfer-Encoding: quoted-printable On Sun, Jun 30, 2024 at 5:47=E2=80=AFPM David Rowley = wrote: > On Mon, 1 Jul 2024 at 12:16, David G. Johnston > wrote: > > > > On Sun, Jun 30, 2024 at 4:55=E2=80=AFPM David Rowley > 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 immediatel= y > 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. --000000000000cd67f4061c25b3c0 Content-Type: text/html; charset="UTF-8" Content-Transfer-Encoding: quoted-printable
On Sun, Jun 30, 2024 at 5:47=E2=80=AFPM David Rowley <<= a href=3D"mailto:dgrowleyml@gmail.com">dgrowleyml@gmail.com> wrote:<= /span>
On Mon, 1 Jul 2024 at 12:16, David G. Johnston
<david.g= .johnston@gmail.com> wrote:
>
> On Sun, Jun 30, 2024 at 4:55=E2=80=AFPM David Rowley <dgrowleyml@gmail.com> w= rote:
>>
>>
>> I'd like to know what led someone down the path of doing somet= hing
>> like DEFAULT 'now()'::timestamp in a CREATE TABLE. Could i= t be a
>> faulty migration tool that created these and people copy them thin= king
>> it's a legitimate syntax?
>>
>
> My thought process on this used to be:=C2=A0 Provide a text string of = the expression that is then stored within the catalog and eval'd during= runtime.=C2=A0 If the only thing you are providing is a single literal and= not some compound expression it isn't that obvious that you are suppos= ed to provide an unquoted expression - which feels like it should be immedi= ately evaluated - versus something that is a constant.=C2=A0 Kinda like dyn= amic SQL.

Thanks for sharing that.=C2=A0 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 pro= bably boils down, for me, that I learned about, though never used, eval fun= ctions from javascript, and figured this is probably implemented something = like that and I should thus supply a string.=C2=A0 Internalizing that DDL c= an treat the unquoted content of expression in "DEFAULT expression&quo= t; as basically text hadn't happened; nor that the actual difference be= tween just treating it as text and the parsing to a standard form that real= ly happens, is quite important.=C2=A0 Namely that, in reverse of expectatio= ns, quoted things, which are literals, are transformed to their typed value= s during parse while functions, which are not quoted, don't have a mean= ingfully different parsed form and are indeed executed at runtime.
=
The fact that 'now()'::timestamp fails to fail doesn&#= 39;t help...

Consider this phrasing for default:
=

The DEFAULT clause assigns a default data value for the col= umn whose column definition it appears within.=C2=A0 The expression is pars= ed according to Section X.X.X, with the limitation that it may neither incl= ude references to other columns nor subqueries, and then stored for later e= valuation of any functions it=C2=A0contains.=C2=A0 The data type of the def= ault expression must match the data type of the column.

Then in Section X.X.X we note, in part:
During parsing, all c= onstants are immediately converted to their internal representation.=C2=A0 = In particular, the time-related literals noted in Section 8.5.1.4 get set t= o their date/time values.

Then, in 8.5.1.4 we should c= all out:
Caution:
'now' is a special time value, ev= aluated during parsing.
now() is a function, evaluated during execu= tion.
'now()' is a special time value due to the quoting, P= ostgreSQL ignored the parentheses.


The a= bove doesn't make the special constants particularly special in how the= y behave within parse-bind-execute while still noting that what they do dur= ing parsing is a bit unique since a timestamp has not representation of = 9;tomorrow' that is can hold but instead is a short-hand for writing th= e constant representing "whatever tomorrow is" at that moment.

I hope the reason for the additional caution in this fra= ming is intuitive for everyone.

There is probably a go= od paragraph or two that could be added under the new Section X.X.X to cent= ralize this for views, atomic sql, defaults, etc... to refer to and give th= e reader the needed framing.

David J.







=


--000000000000cd67f4061c25b3c0--