Re: Should we document how column DEFAULT expressions work?
| От | David G. Johnston |
|---|---|
| Тема | Re: Should we document how column DEFAULT expressions work? |
| Дата | |
| Msg-id | CAKFQuwZjuQX4H48MbaHZAX3s__LoHgkXsf2Eg--E=qtWFss0bg@mail.gmail.com обсуждение исходный текст |
| Ответ на | Re: Should we document how column DEFAULT expressions work? (David Rowley <dgrowleyml@gmail.com>) |
| Ответы |
Re: Should we document how column DEFAULT expressions work?
|
| Список | pgsql-hackers |
On Sun, Jun 30, 2024 at 5:47 PM David Rowley <dgrowleyml@gmail.com> wrote:
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 PM David Rowley <dgrowleyml@gmail.com> 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 immediately 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.
В списке pgsql-hackers по дате отправления: