public inbox for [email protected]
help / color / mirror / Atom feed[PATCH v9 07/21] lazy_scan_prune reorder freeze execution logic
17+ messages / 7 participants
[nested] [flat]
* [PATCH v9 07/21] lazy_scan_prune reorder freeze execution logic
@ 2024-03-25 23:39 Melanie Plageman <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Melanie Plageman @ 2024-03-25 23:39 UTC (permalink / raw)
To combine the prune and freeze records, freezing must be done before a
pruning WAL record is emitted. We will move the freeze execution into
heap_page_prune() in future commits. lazy_scan_prune() currently
executes freezing, updates vacrel->NewRelfrozenXid and
vacrel->NewRelminMxid, and resets the snapshotConflictHorizon that the
visibility map update record may use in the same block of if statements.
This commit starts reordering that logic so that the freeze execution
can be separated from the other updates which should not be done in
pruning.
---
src/backend/access/heap/vacuumlazy.c | 93 +++++++++++++++-------------
1 file changed, 50 insertions(+), 43 deletions(-)
diff --git a/src/backend/access/heap/vacuumlazy.c b/src/backend/access/heap/vacuumlazy.c
index 2a3cc5c7cd3..f474e661428 100644
--- a/src/backend/access/heap/vacuumlazy.c
+++ b/src/backend/access/heap/vacuumlazy.c
@@ -1421,6 +1421,7 @@ lazy_scan_prune(LVRelState *vacrel,
recently_dead_tuples;
HeapPageFreeze pagefrz;
bool hastup = false;
+ bool do_freeze;
int64 fpi_before = pgWalUsage.wal_fpi;
OffsetNumber deadoffsets[MaxHeapTuplesPerPage];
@@ -1576,10 +1577,15 @@ lazy_scan_prune(LVRelState *vacrel,
* freeze when pruning generated an FPI, if doing so means that we set the
* page all-frozen afterwards (might not happen until final heap pass).
*/
- if (pagefrz.freeze_required || presult.nfrozen == 0 ||
+ do_freeze = pagefrz.freeze_required ||
(presult.all_visible_except_removable && presult.all_frozen &&
- fpi_before != pgWalUsage.wal_fpi))
+ presult.nfrozen > 0 &&
+ fpi_before != pgWalUsage.wal_fpi);
+
+ if (do_freeze)
{
+ TransactionId snapshotConflictHorizon;
+
/*
* We're freezing the page. Our final NewRelfrozenXid doesn't need to
* be affected by the XIDs that are just about to be frozen anyway.
@@ -1587,52 +1593,53 @@ lazy_scan_prune(LVRelState *vacrel,
vacrel->NewRelfrozenXid = pagefrz.FreezePageRelfrozenXid;
vacrel->NewRelminMxid = pagefrz.FreezePageRelminMxid;
- if (presult.nfrozen == 0)
- {
- /*
- * We have no freeze plans to execute, so there's no added cost
- * from following the freeze path. That's why it was chosen. This
- * is important in the case where the page only contains totally
- * frozen tuples at this point (perhaps only following pruning).
- * Such pages can be marked all-frozen in the VM by our caller,
- * even though none of its tuples were newly frozen here (note
- * that the "no freeze" path never sets pages all-frozen).
- *
- * We never increment the frozen_pages instrumentation counter
- * here, since it only counts pages with newly frozen tuples
- * (don't confuse that with pages newly set all-frozen in VM).
- */
- }
+ vacrel->frozen_pages++;
+
+ /*
+ * We can use frz_conflict_horizon as our cutoff for conflicts when
+ * the whole page is eligible to become all-frozen in the VM once
+ * we're done with it. Otherwise we generate a conservative cutoff by
+ * stepping back from OldestXmin.
+ */
+ if (presult.all_visible_except_removable && presult.all_frozen)
+ snapshotConflictHorizon = presult.visibility_cutoff_xid;
else
{
- TransactionId snapshotConflictHorizon;
+ /* Avoids false conflicts when hot_standby_feedback in use */
+ snapshotConflictHorizon = pagefrz.cutoffs->OldestXmin;
+ TransactionIdRetreat(snapshotConflictHorizon);
+ }
- vacrel->frozen_pages++;
+ /* Using same cutoff when setting VM is now unnecessary */
+ if (presult.all_visible_except_removable && presult.all_frozen)
+ presult.visibility_cutoff_xid = InvalidTransactionId;
- /*
- * We can use visibility_cutoff_xid as our cutoff for conflicts
- * when the whole page is eligible to become all-frozen in the VM
- * once we're done with it. Otherwise we generate a conservative
- * cutoff by stepping back from OldestXmin.
- */
- if (presult.all_visible_except_removable && presult.all_frozen)
- {
- /* Using same cutoff when setting VM is now unnecessary */
- snapshotConflictHorizon = presult.visibility_cutoff_xid;
- presult.visibility_cutoff_xid = InvalidTransactionId;
- }
- else
- {
- /* Avoids false conflicts when hot_standby_feedback in use */
- snapshotConflictHorizon = vacrel->cutoffs.OldestXmin;
- TransactionIdRetreat(snapshotConflictHorizon);
- }
+ /* Execute all freeze plans for page as a single atomic action */
+ heap_freeze_execute_prepared(vacrel->rel, buf,
+ snapshotConflictHorizon,
+ presult.frozen, presult.nfrozen);
- /* Execute all freeze plans for page as a single atomic action */
- heap_freeze_execute_prepared(vacrel->rel, buf,
- snapshotConflictHorizon,
- presult.frozen, presult.nfrozen);
- }
+ }
+ else if (presult.all_frozen && presult.nfrozen == 0)
+ {
+ /* Page should be all visible except to-be-removed tuples */
+ Assert(presult.all_visible_except_removable);
+
+ /*
+ * We have no freeze plans to execute, so there's no added cost from
+ * following the freeze path. That's why it was chosen. This is
+ * important in the case where the page only contains totally frozen
+ * tuples at this point (perhaps only following pruning). Such pages
+ * can be marked all-frozen in the VM by our caller, even though none
+ * of its tuples were newly frozen here (note that the "no freeze"
+ * path never sets pages all-frozen).
+ *
+ * We never increment the frozen_pages instrumentation counter here,
+ * since it only counts pages with newly frozen tuples (don't confuse
+ * that with pages newly set all-frozen in VM).
+ */
+ vacrel->NewRelfrozenXid = pagefrz.FreezePageRelfrozenXid;
+ vacrel->NewRelminMxid = pagefrz.FreezePageRelminMxid;
}
else
{
--
2.40.1
--caj67xgx3lukmr5f
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment;
filename="v9-0008-Execute-freezing-in-heap_page_prune.patch"
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Should we document how column DEFAULT expressions work?
@ 2024-06-27 11:57 Peter Eisentraut <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Peter Eisentraut @ 2024-06-27 11:57 UTC (permalink / raw)
To: David Rowley <[email protected]>; Tom Lane <[email protected]>; +Cc: David G. Johnston <[email protected]>; James Coleman <[email protected]>; pgsql-hackers
On 27.06.24 02:34, David Rowley wrote:
> 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. :-(
Maybe we should really be thinking about deprecating these special
values and steering users more urgently toward more robust alternatives.
Imagine if 'random' were a valid input value for numeric types.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Should we document how column DEFAULT expressions work?
@ 2024-06-30 23:54 David Rowley <[email protected]>
parent: Peter Eisentraut <[email protected]>
0 siblings, 2 replies; 17+ messages in thread
From: David Rowley @ 2024-06-30 23:54 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: Tom Lane <[email protected]>; David G. Johnston <[email protected]>; James Coleman <[email protected]>; pgsql-hackers
On Thu, 27 Jun 2024 at 23:57, Peter Eisentraut <[email protected]> wrote:
> Maybe we should really be thinking about deprecating these special
> values and steering users more urgently toward more robust alternatives.
>
> Imagine if 'random' were a valid input value for numeric types.
I think there are valid reasons to use the special timestamp input
values. One that I can think of is for use with partition pruning. If
you have a time-range partitioned table and want the planner to prune
the partitions rather than the executor, you could use
'now'::timestamp in your queries to allow the planner to prune. That
works providing that you never use that in combination with PREPARE
and never put the query with the WHERE clause inside a VIEW. I don't
have any other good examples, but I suppose that if someone needed to
capture the time some statement was executed and record that
somewhere, sort of like the __DATE__ and __TIME__ macros in C. Perhaps
that's useful to record the last time some DDL script was executed.
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?
David
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Should we document how column DEFAULT expressions work?
@ 2024-07-01 00:15 David G. Johnston <[email protected]>
parent: David Rowley <[email protected]>
1 sibling, 2 replies; 17+ messages in thread
From: David G. Johnston @ 2024-07-01 00:15 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; Tom Lane <[email protected]>; James Coleman <[email protected]>; pgsql-hackers
On Sun, Jun 30, 2024 at 4:55 PM David Rowley <[email protected]> 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.
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Should we document how column DEFAULT expressions work?
@ 2024-07-01 00:47 David Rowley <[email protected]>
parent: David G. Johnston <[email protected]>
1 sibling, 1 reply; 17+ messages in thread
From: David Rowley @ 2024-07-01 00:47 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; Tom Lane <[email protected]>; James Coleman <[email protected]>; pgsql-hackers
On Mon, 1 Jul 2024 at 12:16, David G. Johnston
<[email protected]> wrote:
>
> On Sun, Jun 30, 2024 at 4:55 PM David Rowley <[email protected]> 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.
David
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Should we document how column DEFAULT expressions work?
@ 2024-07-01 01:41 David G. Johnston <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: David G. Johnston @ 2024-07-01 01:41 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; Tom Lane <[email protected]>; James Coleman <[email protected]>; pgsql-hackers
On Sun, Jun 30, 2024 at 5:47 PM David Rowley <[email protected]> wrote:
> On Mon, 1 Jul 2024 at 12:16, David G. Johnston
> <[email protected]> wrote:
> >
> > On Sun, Jun 30, 2024 at 4:55 PM David Rowley <[email protected]>
> 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.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Should we document how column DEFAULT expressions work?
@ 2024-07-01 02:52 David Rowley <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 3 replies; 17+ messages in thread
From: David Rowley @ 2024-07-01 02:52 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; Tom Lane <[email protected]>; James Coleman <[email protected]>; pgsql-hackers
On Mon, 1 Jul 2024 at 13:41, David G. Johnston
<[email protected]> wrote:
> I presume the relatively new atomic SQL functions pose a similar hazard.
Do you have an example of this?
> The fact that 'now()'::timestamp fails to fail doesn't help...
If that's the case, maybe a tiny step towards what Peter proposed is
just to make trailing punctuation fail for timestamp special values in
v18.
David
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Should we document how column DEFAULT expressions work?
@ 2024-07-01 03:08 David G. Johnston <[email protected]>
parent: David Rowley <[email protected]>
2 siblings, 0 replies; 17+ messages in thread
From: David G. Johnston @ 2024-07-01 03:08 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; Tom Lane <[email protected]>; James Coleman <[email protected]>; pgsql-hackers
On Sun, Jun 30, 2024 at 7:52 PM David Rowley <[email protected]> wrote:
> On Mon, 1 Jul 2024 at 13:41, David G. Johnston
> <[email protected]> wrote:
> > I presume the relatively new atomic SQL functions pose a similar hazard.
>
> Do you have an example of this?
>
>
create function testnow() returns timestamptz language sql
return 'now'::timestamptz;
select testnow();
select pg_sleep(5);
select testnow(); -- same time as the first call
Which conforms with the documentation and expression parsing rules for
literals:
"This form is parsed at function definition time, the string constant form
is parsed at execution time;..."
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Should we document how column DEFAULT expressions work?
@ 2024-07-01 03:14 David G. Johnston <[email protected]>
parent: David Rowley <[email protected]>
2 siblings, 0 replies; 17+ messages in thread
From: David G. Johnston @ 2024-07-01 03:14 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; Tom Lane <[email protected]>; James Coleman <[email protected]>; pgsql-hackers
On Sun, Jun 30, 2024 at 7:52 PM David Rowley <[email protected]> wrote:
> If that's the case, maybe a tiny step towards what Peter proposed is
> just to make trailing punctuation fail for timestamp special values in
> v18.
>
>
I'm game. If anyone is using the ambiguous spelling it is probably to their
benefit to have it break and realize they wanted a function expression, not
a constant expression.
David J.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Should we document how column DEFAULT expressions work?
@ 2024-07-01 13:37 James Coleman <[email protected]>
parent: David G. Johnston <[email protected]>
1 sibling, 0 replies; 17+ messages in thread
From: James Coleman @ 2024-07-01 13:37 UTC (permalink / raw)
To: David G. Johnston <[email protected]>; +Cc: David Rowley <[email protected]>; Peter Eisentraut <[email protected]>; Tom Lane <[email protected]>; pgsql-hackers
On Sun, Jun 30, 2024 at 8:16 PM David G. Johnston
<[email protected]> wrote:
>
> On Sun, Jun 30, 2024 at 4:55 PM David Rowley <[email protected]> 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.
I have a similar story to tell: I've honestly never thought about it
deeply until I started this thread, but just through experimentation a
few things were obvious:
- now() as a function call gives you the current timestamp in a query
- now() as a function call in a DDL DEFAULT clause sets that as a
default function call
- Quoting that function call (using the function call syntax is the
natural thing to try, I think, if you've already done the first two)
-- because some examples online show quoting it -- gives you DDL time
evaluation.
So I suspect -- though I've been doing this for so long I couldn't
tell you for certain -- that I largely intuitive the behavior by
observation.
And similarly to David J. I'd then assumed -- but never had a need to
test it -- that this was generalized.
I think DDL is also different conceptually from SQL/DML here in a kind
of insidious way: the "bare" function call in DEFAULT is *not*
executed as part of the query for DDL like it is with other queries.
Hope this helps explain things.
James Coleman
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Should we document how column DEFAULT expressions work?
@ 2024-07-01 14:26 Peter Eisentraut <[email protected]>
parent: David Rowley <[email protected]>
1 sibling, 1 reply; 17+ messages in thread
From: Peter Eisentraut @ 2024-07-01 14:26 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: Tom Lane <[email protected]>; David G. Johnston <[email protected]>; James Coleman <[email protected]>; pgsql-hackers
On 01.07.24 01:54, David Rowley wrote:
> On Thu, 27 Jun 2024 at 23:57, Peter Eisentraut <[email protected]> wrote:
>> Maybe we should really be thinking about deprecating these special
>> values and steering users more urgently toward more robust alternatives.
>>
>> Imagine if 'random' were a valid input value for numeric types.
>
> I think there are valid reasons to use the special timestamp input
> values. One that I can think of is for use with partition pruning. If
> you have a time-range partitioned table and want the planner to prune
> the partitions rather than the executor, you could use
> 'now'::timestamp in your queries to allow the planner to prune.
Yeah, but is that a good user interface? Or is that just something that
happens to work now with the pieces that happened to be there, rather
than a really designed interface?
Hypothetically, what would need to be done to make this work with now()
or current_timestamp or something similar? Do we need a new stability
level that somehow encompasses this behavior, so that the function call
can be evaluated at planning time?
> That
> works providing that you never use that in combination with PREPARE
> and never put the query with the WHERE clause inside a VIEW.
And this kind of thing obviously makes this interface even worse.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Should we document how column DEFAULT expressions work?
@ 2024-07-01 14:43 Tom Lane <[email protected]>
parent: Peter Eisentraut <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2024-07-01 14:43 UTC (permalink / raw)
To: Peter Eisentraut <[email protected]>; +Cc: David Rowley <[email protected]>; David G. Johnston <[email protected]>; James Coleman <[email protected]>; pgsql-hackers
Peter Eisentraut <[email protected]> writes:
> On 01.07.24 01:54, David Rowley wrote:
>> I think there are valid reasons to use the special timestamp input
>> values. One that I can think of is for use with partition pruning. If
>> you have a time-range partitioned table and want the planner to prune
>> the partitions rather than the executor, you could use
>> 'now'::timestamp in your queries to allow the planner to prune.
> Yeah, but is that a good user interface? Or is that just something that
> happens to work now with the pieces that happened to be there, rather
> than a really designed interface?
That's not a very useful argument to make. What percentage of the
SQL language as a whole is legacy cruft that we'd do differently if
we could? I think the answer is depressingly high. Adding more
special-purpose features to the ones already there doesn't move
that needle in a desirable direction.
I'd be more excited about this discussion if I didn't think that
the chances of removing 'now'::timestamp are exactly zero. You
can't just delete useful decades-old features, whether there's
a better way or not.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Should we document how column DEFAULT expressions work?
@ 2024-07-02 01:48 David Rowley <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: David Rowley @ 2024-07-02 01:48 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; David G. Johnston <[email protected]>; James Coleman <[email protected]>; pgsql-hackers
On Tue, 2 Jul 2024 at 02:43, Tom Lane <[email protected]> wrote:
> I'd be more excited about this discussion if I didn't think that
> the chances of removing 'now'::timestamp are exactly zero. You
> can't just delete useful decades-old features, whether there's
> a better way or not.
Do you have any thoughts on rejecting trailing punctuation with the
timestamp special values?
For me, I've mixed feelings about it. I think it would be good to
break things for people who are doing this and getting the wrong
behaviour who haven't noticed yet, however, there could be a series of
people doing this and have these embedded in statements that are
parsed directly before execution, and they just happen to get the
right behaviour. It might be better not to upset the latter set of
people needlessly. Perhaps the former set of people don't exist since
the behaviour is quite different and it seems quite obviously wrong.
David
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Should we document how column DEFAULT expressions work?
@ 2024-07-05 20:31 Bruce Momjian <[email protected]>
parent: David Rowley <[email protected]>
2 siblings, 1 reply; 17+ messages in thread
From: Bruce Momjian @ 2024-07-05 20:31 UTC (permalink / raw)
To: David Rowley <[email protected]>; +Cc: David G. Johnston <[email protected]>; Peter Eisentraut <[email protected]>; Tom Lane <[email protected]>; James Coleman <[email protected]>; pgsql-hackers
On Mon, Jul 1, 2024 at 02:52:42PM +1200, David Rowley wrote:
> On Mon, 1 Jul 2024 at 13:41, David G. Johnston
> <[email protected]> wrote:
> > I presume the relatively new atomic SQL functions pose a similar hazard.
>
> Do you have an example of this?
>
> > The fact that 'now()'::timestamp fails to fail doesn't help...
>
> If that's the case, maybe a tiny step towards what Peter proposed is
> just to make trailing punctuation fail for timestamp special values in
> v18.
I dug into this and I have a suggestion at the end. First, the special
values like 'now' are the only values that can be optionally quoted:
SELECT current_timestamp::timestamptz;
current_timestamp
-------------------------------
2024-07-05 15:15:22.692072-04
SELECT 'current_timestamp'::timestamptz;
ERROR: invalid input syntax for type timestamp with time zone: "current_timestamp"
Also interestingly, "now" without quotes requires parentheses to make it
a function call:
SELECT 'now'::timestamptz;
timestamptz
-------------------------------
2024-07-05 15:17:11.394182-04
SELECT 'now()'::timestamptz;
timestamptz
-------------------------------
2024-07-05 15:17:15.201621-04
SELECT now()::timestamptz;
now
-------------------------------
2024-07-05 15:17:21.925611-04
SELECT now::timestamptz;
ERROR: column "now" does not exist
LINE 1: SELECT now::timestamptz;
^
And the quoting shows "now" evaluation at function creation time:
CREATE OR REPLACE FUNCTION testnow() RETURNS timestamptz LANGUAGE SQL
RETURN 'now'::timestamptz;
SELECT testnow();
SELECT pg_sleep(5);
SELECT testnow();
testnow
-------------------------------
2024-07-05 15:19:38.915255-04
testnow
-------------------------------
2024-07-05 15:19:38.915255-04 -- same
---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION testnow() RETURNS timestamptz LANGUAGE SQL
RETURN 'now()'::timestamptz;
SELECT testnow();
SELECT pg_sleep(5);
SELECT testnow();
testnow
-------------------------------
2024-07-05 15:20:41.475997-04
testnow
-------------------------------
2024-07-05 15:20:41.475997-04 -- same
---------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION testnow() RETURNS timestamptz LANGUAGE SQL
RETURN now()::timestamptz;
SELECT testnow();
SELECT pg_sleep(5);
SELECT testnow();
testnow
-------------------------------
2024-07-05 15:21:18.204574-04
testnow
-------------------------------
2024-07-05 15:21:23.210442-04 -- different
I don't think we can bounce people around to different sections to
explain this --- I think we need text in the CREATE TABLE ... DEFAULT
section. I think the now() case is unusual since there are few cases
where function calls can be put inside of single quotes.
I have written the attached patch to clarify the behavior.
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
Attachments:
[text/x-diff] create_default.diff (881B, ../../[email protected]/2-create_default.diff)
download | inline diff:
diff --git a/doc/src/sgml/ref/create_table.sgml b/doc/src/sgml/ref/create_table.sgml
index f19306e7760..9bab4ec141e 100644
--- a/doc/src/sgml/ref/create_table.sgml
+++ b/doc/src/sgml/ref/create_table.sgml
@@ -888,6 +888,14 @@ WITH ( MODULUS <replaceable class="parameter">numeric_literal</replaceable>, REM
match the data type of the column.
</para>
+ <para>
+ Note, a string that returns a volatile result once cast to
+ a data type, like <literal>'now'::timestamptz</literal> and
+ <literal>'now()'::timestamptz</literal>, is evaluated at table
+ creation time, while <literal>now()::timestamptz</literal> (without
+ quotes) is evaluated at data insertion time.
+ </para>
+
<para>
The default expression will be used in any insert operation that
does not specify a value for the column. If there is no default
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Should we document how column DEFAULT expressions work?
@ 2024-07-05 20:50 Tom Lane <[email protected]>
parent: Bruce Momjian <[email protected]>
0 siblings, 1 reply; 17+ messages in thread
From: Tom Lane @ 2024-07-05 20:50 UTC (permalink / raw)
To: Bruce Momjian <[email protected]>; +Cc: David Rowley <[email protected]>; David G. Johnston <[email protected]>; Peter Eisentraut <[email protected]>; James Coleman <[email protected]>; pgsql-hackers
Bruce Momjian <[email protected]> writes:
> Also interestingly, "now" without quotes requires parentheses to make it
> a function call:
I'm not sure why you find that surprising, or why you think that
'now()'::timestamptz is a function call. (Well, it is a call of
timestamptz_in, but not of the SQL function now().) Documentation
that is equally confused won't help any.
regards, tom lane
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Should we document how column DEFAULT expressions work?
@ 2024-07-05 20:55 Bruce Momjian <[email protected]>
parent: Tom Lane <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: Bruce Momjian @ 2024-07-05 20:55 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: David Rowley <[email protected]>; David G. Johnston <[email protected]>; Peter Eisentraut <[email protected]>; James Coleman <[email protected]>; pgsql-hackers
On Fri, Jul 5, 2024 at 04:50:32PM -0400, Tom Lane wrote:
> Bruce Momjian <[email protected]> writes:
> > Also interestingly, "now" without quotes requires parentheses to make it
> > a function call:
>
> I'm not sure why you find that surprising, or why you think that
> 'now()'::timestamptz is a function call. (Well, it is a call of
> timestamptz_in, but not of the SQL function now().) Documentation
> that is equally confused won't help any.
Well, 'now()' certainly _looks_ like a function call, though it isn't.
The fact that 'now()'::timestamptz and 'now'::timestamptz generate
volatile results via a function call was my point.
--
Bruce Momjian <[email protected]> https://momjian.us
EDB https://enterprisedb.com
Only you can decide what is important to you.
^ permalink raw reply [nested|flat] 17+ messages in thread
* Re: Should we document how column DEFAULT expressions work?
@ 2024-07-05 22:43 David Rowley <[email protected]>
parent: David Rowley <[email protected]>
0 siblings, 0 replies; 17+ messages in thread
From: David Rowley @ 2024-07-05 22:43 UTC (permalink / raw)
To: Tom Lane <[email protected]>; +Cc: Peter Eisentraut <[email protected]>; David G. Johnston <[email protected]>; James Coleman <[email protected]>; pgsql-hackers
On Tue, 2 Jul 2024 at 13:48, David Rowley <[email protected]> wrote:
>
> On Tue, 2 Jul 2024 at 02:43, Tom Lane <[email protected]> wrote:
> > I'd be more excited about this discussion if I didn't think that
> > the chances of removing 'now'::timestamp are exactly zero. You
> > can't just delete useful decades-old features, whether there's
> > a better way or not.
>
> Do you have any thoughts on rejecting trailing punctuation with the
> timestamp special values?
Cancel that idea. I'd thought that these special values must be
standalone, but I didn't realise until a few minutes ago that it's
perfectly valid to mix them:
select 'yesterday 13:00:00'::timestamp, 'yesterday allballs'::timestamp;
David
^ permalink raw reply [nested|flat] 17+ messages in thread
end of thread, other threads:[~2024-07-05 22:43 UTC | newest]
Thread overview: 17+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-03-25 23:39 [PATCH v9 07/21] lazy_scan_prune reorder freeze execution logic Melanie Plageman <[email protected]>
2024-06-27 11:57 Re: Should we document how column DEFAULT expressions work? Peter Eisentraut <[email protected]>
2024-06-30 23:54 ` Re: Should we document how column DEFAULT expressions work? David Rowley <[email protected]>
2024-07-01 00:15 ` Re: Should we document how column DEFAULT expressions work? David G. Johnston <[email protected]>
2024-07-01 00:47 ` Re: Should we document how column DEFAULT expressions work? David Rowley <[email protected]>
2024-07-01 01:41 ` Re: Should we document how column DEFAULT expressions work? David G. Johnston <[email protected]>
2024-07-01 02:52 ` Re: Should we document how column DEFAULT expressions work? David Rowley <[email protected]>
2024-07-01 03:08 ` Re: Should we document how column DEFAULT expressions work? David G. Johnston <[email protected]>
2024-07-01 03:14 ` Re: Should we document how column DEFAULT expressions work? David G. Johnston <[email protected]>
2024-07-05 20:31 ` Re: Should we document how column DEFAULT expressions work? Bruce Momjian <[email protected]>
2024-07-05 20:50 ` Re: Should we document how column DEFAULT expressions work? Tom Lane <[email protected]>
2024-07-05 20:55 ` Re: Should we document how column DEFAULT expressions work? Bruce Momjian <[email protected]>
2024-07-01 13:37 ` Re: Should we document how column DEFAULT expressions work? James Coleman <[email protected]>
2024-07-01 14:26 ` Re: Should we document how column DEFAULT expressions work? Peter Eisentraut <[email protected]>
2024-07-01 14:43 ` Re: Should we document how column DEFAULT expressions work? Tom Lane <[email protected]>
2024-07-02 01:48 ` Re: Should we document how column DEFAULT expressions work? David Rowley <[email protected]>
2024-07-05 22:43 ` Re: Should we document how column DEFAULT expressions work? David Rowley <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox