MIME-Version: 1.0
References: <CAAaqYe-wQPbchCUE7pocPNEQ3PMY8a98Txmxc7q0+FvXtk1vvg@mail.gmail.com>
 <1362410.1719349169@sss.pgh.pa.us> <CAAaqYe9_RE2WximHuYRpyfU-H2G+W--49e_8GDqS-vHLOzPvmg@mail.gmail.com>
 <1390099.1719357084@sss.pgh.pa.us> <CAKFQuwZkgm0yqHhf05thqu1gzUK__gXBL5tyBuFvKoOe7Dd6=A@mail.gmail.com>
 <CAApHDvoiHYxM2VQYZeiF7JAdP7Y0JxEr86NZpUjoZ9AA4cpzYw@mail.gmail.com>
 <CAKFQuwbyjn0p5aKz1sKfJHQCTbGy1xhWaNR2xdnXv-X4oPdOGA@mail.gmail.com>
 <CAApHDvqShXhOat03iEV1VqXM68DBJ+zM95Ghbu0rcqs8KnhQQQ@mail.gmail.com> <1642803.1719447063@sss.pgh.pa.us>
In-Reply-To: <1642803.1719447063@sss.pgh.pa.us>
From: David Rowley <dgrowleyml@gmail.com>
Date: Thu, 27 Jun 2024 12:34:37 +1200
Message-ID: <CAApHDvouPRyga3-5ZYLt_RUhU942bk0WPVgro+U=R3cvBRXbeQ@mail.gmail.com>
Subject: Re: Should we document how column DEFAULT expressions work?
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>, James Coleman <jtc331@gmail.com>, 
	pgsql-hackers <pgsql-hackers@postgresql.org>
Content-Type: text/plain; charset="UTF-8"
Archived-At: <https://www.postgresql.org/message-id/CAApHDvouPRyga3-5ZYLt_RUhU942bk0WPVgro%2BU%3DR3cvBRXbeQ%40mail.gmail.com>
Precedence: bulk

On Thu, 27 Jun 2024 at 12:11, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
> David Rowley <dgrowleyml@gmail.com> writes:
> > 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.
>
> It's not that the parsing rules are any different: it's that in
> ordinary DML queries, it seldom matters very much whether a
> subexpression is evaluated at parse time versus run time.
> In CREATE TABLE that difference is very in-your-face, so people
> who haven't understood the rules clearly can get burnt.

Aha, now I understand. Thanks. So, seems like CREATE TABLE is being
targeted or maybe victimised here as it's probably the most common
place people learn about their misuse of the timestamp special input
values.

> However, there are certainly other places where it matters,
> such as queries in plpgsql functions.  So I understand your
> reluctance to go on about it in CREATE TABLE.  At the same
> time, I see where David J. is coming from.
>
> Maybe we could have a discussion of this in some single spot,
> and link to it from CREATE TABLE and other relevant places?
> ISTR there is something about it in the plpgsql doco already.

For the special timestamp stuff, that place is probably the special
timestamp table in [1].  It looks like the large caution you added in
540849814 might not be enough or perhaps wasn't done soon enough to
catch the people who read that part of the manual before the caution
was added. Hard to fix if it's the latter without a time machine. :-(

I'm open to having some section that fleshes this stuff out a bit more
with a few examples with CREATE TABLE and maybe CREATE VIEW that we
can link to.  Linking seems like a much more sustainable practice than
adding special case documentation for non-special case behaviour.

David

[1] https://www.postgresql.org/docs/devel/datatype-datetime.html