pg.ddx.io pgsql-general@postgresql.org mailing list archive
help / color / mirror / Atom feedUnexpected behavior when combining `generated always` columns and update rules
7+ messages / 4 participants
[nested] [flat]
* Unexpected behavior when combining `generated always` columns and update rules
@ 2023-04-13 14:02 Ciprian Craciun <ciprian.craciun@gmail.com>
2023-04-13 14:32 ` Re: Unexpected behavior when combining `generated always` columns and update rules David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Ciprian Craciun @ 2023-04-13 14:02 UTC (permalink / raw)
To: pgsql-general@lists.postgresql.org
Hello all!
I'm uncertain if what I've observed (and describe below) is a bug,
works as expected (but I've not found it mentioned in the
documentation), or it's just a by-product of combining two advanced
features that haven't been thoroughly tested together.
So, to summarize: I'm trying to use the rules system (as opposed to
triggers) to propagate a "generated always" column update to another
table (actually the update of other columns that are used to compute
the generated column); however even though I use
`new.<generated_column_name>` I actually get the old computed value.
(My concrete use-case is propagating something resembling a `disabled`
column, computed based on various other columns, from an account to
say some other related tables. I do this mainly for performance and
ease of use reasons.)
The following is a minimal example that demonstrates the behavior:
~~~~
create table x (x int, d int generated always as (x * 10) stored);
create table y (x int, d int);
create or replace rule propagate_x_and_d as on update to x do also
update y set x = new.x, d = new.d where y.x = old.x;
insert into x (x) values (1), (2);
insert into y (x) values (1), (2);
select x.x as x, x.d as xd, y.d as yd from x, y where x.x = y.x;
x | xd | yd
---+----+----
1 | 10 |
2 | 20 |
update x set x = x + 1;
select x.x as x, x.d as xd, y.d as yd from x, y where x.x = y.x;
x | xd | yd
---+----+----
2 | 20 | 10
3 | 30 | 20
~~~~
As seen above, although the rule correctly propagates the change to
the `x` column, it fails to use the new value for the `d` column, but
instead uses the previous one.
Thanks,
Ciprian.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unexpected behavior when combining `generated always` columns and update rules
2023-04-13 14:02 Unexpected behavior when combining `generated always` columns and update rules Ciprian Craciun <ciprian.craciun@gmail.com>
@ 2023-04-13 14:32 ` David G. Johnston <david.g.johnston@gmail.com>
2023-04-13 14:37 ` Re: Unexpected behavior when combining `generated always` columns and update rules Tom Lane <tgl@sss.pgh.pa.us>
2023-04-13 16:27 ` Re: Unexpected behavior when combining `generated always` columns and update rules Ciprian Craciun <ciprian.craciun@gmail.com>
0 siblings, 2 replies; 7+ messages in thread
From: David G. Johnston @ 2023-04-13 14:32 UTC (permalink / raw)
To: Ciprian Craciun <ciprian.craciun@gmail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Thursday, April 13, 2023, Ciprian Craciun <ciprian.craciun@gmail.com>
wrote:
>
> create or replace rule propagate_x_and_d as on update to x do also
> update y set x = new.x, d = new.d where y.x = old.x;
>
> As seen above, although the rule correctly propagates the change to
> the `x` column, it fails to use the new value for the `d` column, but
> instead uses the previous one.
>
ALSO rules behave like before triggers, not after triggers. The original
command is appended to the end of the list of commands, not the start.
David J.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unexpected behavior when combining `generated always` columns and update rules
2023-04-13 14:02 Unexpected behavior when combining `generated always` columns and update rules Ciprian Craciun <ciprian.craciun@gmail.com>
2023-04-13 14:32 ` Re: Unexpected behavior when combining `generated always` columns and update rules David G. Johnston <david.g.johnston@gmail.com>
@ 2023-04-13 14:37 ` Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 0 replies; 7+ messages in thread
From: Tom Lane @ 2023-04-13 14:37 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Ciprian Craciun <ciprian.craciun@gmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
"David G. Johnston" <david.g.johnston@gmail.com> writes:
> On Thursday, April 13, 2023, Ciprian Craciun <ciprian.craciun@gmail.com>
> wrote:
>> create or replace rule propagate_x_and_d as on update to x do also
>> update y set x = new.x, d = new.d where y.x = old.x;
>>
>> As seen above, although the rule correctly propagates the change to
>> the `x` column, it fails to use the new value for the `d` column, but
>> instead uses the previous one.
> ALSO rules behave like before triggers, not after triggers. The original
> command is appended to the end of the list of commands, not the start.
It depends actually ... per [1],
For ON INSERT rules, the original query (if not suppressed by INSTEAD)
is done before any actions added by rules. This allows the actions to
see the inserted row(s). But for ON UPDATE and ON DELETE rules, the
original query is done after the actions added by rules. This ensures
that the actions can see the to-be-updated or to-be-deleted rows;
otherwise, the actions might do nothing because they find no rows
matching their qualifications.
regards, tom lane
[1] https://www.postgresql.org/docs/current/rules-update.html
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unexpected behavior when combining `generated always` columns and update rules
2023-04-13 14:02 Unexpected behavior when combining `generated always` columns and update rules Ciprian Craciun <ciprian.craciun@gmail.com>
2023-04-13 14:32 ` Re: Unexpected behavior when combining `generated always` columns and update rules David G. Johnston <david.g.johnston@gmail.com>
@ 2023-04-13 16:27 ` Ciprian Craciun <ciprian.craciun@gmail.com>
2023-04-13 16:57 ` Re: Unexpected behavior when combining `generated always` columns and update rules Adrian Klaver <adrian.klaver@aklaver.com>
2023-04-13 17:03 ` Re: Unexpected behavior when combining `generated always` columns and update rules Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 2 replies; 7+ messages in thread
From: Ciprian Craciun @ 2023-04-13 16:27 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On Thu, Apr 13, 2023 at 5:32 PM David G. Johnston
<david.g.johnston@gmail.com> wrote:
> ALSO rules behave like before triggers, not after triggers. The original command is appended to the end of the list of commands, not the start.
As Tom observed, the documentation states that in case of update
rules, the original query is executed at the end.
However, regardless of the order of the execution between new and
original query, as per the documentation the `new` table should
contain the new values regardless.
In fact, from my example above, one can see that the `y.x` is properly
updated with the new value, meanwhile the `y.d` is the previous one
(i.e. `old.d`).
So, based on these observations, I think that `generated always`
columns are actually computed on insertion, and thus they are not
reflected in `new` on rules.
Ciprian.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unexpected behavior when combining `generated always` columns and update rules
2023-04-13 14:02 Unexpected behavior when combining `generated always` columns and update rules Ciprian Craciun <ciprian.craciun@gmail.com>
2023-04-13 14:32 ` Re: Unexpected behavior when combining `generated always` columns and update rules David G. Johnston <david.g.johnston@gmail.com>
2023-04-13 16:27 ` Re: Unexpected behavior when combining `generated always` columns and update rules Ciprian Craciun <ciprian.craciun@gmail.com>
@ 2023-04-13 16:57 ` Adrian Klaver <adrian.klaver@aklaver.com>
1 sibling, 0 replies; 7+ messages in thread
From: Adrian Klaver @ 2023-04-13 16:57 UTC (permalink / raw)
To: Ciprian Craciun <ciprian.craciun@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On 4/13/23 09:27, Ciprian Craciun wrote:
> On Thu, Apr 13, 2023 at 5:32 PM David G. Johnston
> <david.g.johnston@gmail.com> wrote:
>> ALSO rules behave like before triggers, not after triggers. The original command is appended to the end of the list of commands, not the start.
>
>
> As Tom observed, the documentation states that in case of update
> rules, the original query is executed at the end.
>
> However, regardless of the order of the execution between new and
> original query, as per the documentation the `new` table should
> contain the new values regardless.
>
> In fact, from my example above, one can see that the `y.x` is properly
> updated with the new value, meanwhile the `y.d` is the previous one
> (i.e. `old.d`).
>
> So, based on these observations, I think that `generated always`
> columns are actually computed on insertion, and thus they are not
> reflected in `new` on rules.
That is not the case:
create table x (x int, d int generated always as (x * 10) stored);
insert into x (x) values (1);
select * from x;
x | d
---+----
1 | 10
update x set x = 2 where x = 1;
select * from x;
x | d
---+----
2 | 20
What I believe is happening is that the generated always is not done
until the original query is run after the rule action. So at the time of
the rule action x.d is still 10.
What this points out is that you will lead a simpler life if you use
triggers instead of rules.
>
> Ciprian.
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unexpected behavior when combining `generated always` columns and update rules
2023-04-13 14:02 Unexpected behavior when combining `generated always` columns and update rules Ciprian Craciun <ciprian.craciun@gmail.com>
2023-04-13 14:32 ` Re: Unexpected behavior when combining `generated always` columns and update rules David G. Johnston <david.g.johnston@gmail.com>
2023-04-13 16:27 ` Re: Unexpected behavior when combining `generated always` columns and update rules Ciprian Craciun <ciprian.craciun@gmail.com>
@ 2023-04-13 17:03 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-04-13 17:30 ` Re: Unexpected behavior when combining `generated always` columns and update rules Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 7+ messages in thread
From: Adrian Klaver @ 2023-04-13 17:03 UTC (permalink / raw)
To: Ciprian Craciun <ciprian.craciun@gmail.com>; Tom Lane <tgl@sss.pgh.pa.us>; David G. Johnston <david.g.johnston@gmail.com>; +Cc: pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
On 4/13/23 09:27, Ciprian Craciun wrote:
> On Thu, Apr 13, 2023 at 5:32 PM David G. Johnston
> <david.g.johnston@gmail.com> wrote:
>> ALSO rules behave like before triggers, not after triggers. The original command is appended to the end of the list of commands, not the start.
>
>
> As Tom observed, the documentation states that in case of update
> rules, the original query is executed at the end.
>
> However, regardless of the order of the execution between new and
> original query, as per the documentation the `new` table should
> contain the new values regardless.
>
> In fact, from my example above, one can see that the `y.x` is properly
> updated with the new value, meanwhile the `y.d` is the previous one
> (i.e. `old.d`).
>
> So, based on these observations, I think that `generated always`
> columns are actually computed on insertion, and thus they are not
> reflected in `new` on rules.
Just realized we may have both being saying the same thing. That your
'...actually computed on insertion,...' meant not just for an INSERT but
for any change in the data. In other words when the original query
actually ran.
>
> Ciprian.
>
>
--
Adrian Klaver
adrian.klaver@aklaver.com
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Unexpected behavior when combining `generated always` columns and update rules
2023-04-13 14:02 Unexpected behavior when combining `generated always` columns and update rules Ciprian Craciun <ciprian.craciun@gmail.com>
2023-04-13 14:32 ` Re: Unexpected behavior when combining `generated always` columns and update rules David G. Johnston <david.g.johnston@gmail.com>
2023-04-13 16:27 ` Re: Unexpected behavior when combining `generated always` columns and update rules Ciprian Craciun <ciprian.craciun@gmail.com>
2023-04-13 17:03 ` Re: Unexpected behavior when combining `generated always` columns and update rules Adrian Klaver <adrian.klaver@aklaver.com>
@ 2023-04-13 17:30 ` Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 0 replies; 7+ messages in thread
From: Tom Lane @ 2023-04-13 17:30 UTC (permalink / raw)
To: Adrian Klaver <adrian.klaver@aklaver.com>; +Cc: Ciprian Craciun <ciprian.craciun@gmail.com>; David G. Johnston <david.g.johnston@gmail.com>; pgsql-general@lists.postgresql.org <pgsql-general@lists.postgresql.org>
Adrian Klaver <adrian.klaver@aklaver.com> writes:
> On 4/13/23 09:27, Ciprian Craciun wrote:
>> So, based on these observations, I think that `generated always`
>> columns are actually computed on insertion, and thus they are not
>> reflected in `new` on rules.
> Just realized we may have both being saying the same thing. That your
> '...actually computed on insertion,...' meant not just for an INSERT but
> for any change in the data. In other words when the original query
> actually ran.
Yeah. In an ON UPDATE rule, NEW is basically a macro that, for each
column of the target table, expands to the SET expression if that column
is in the SET list, and a reference to the existing column value
otherwise. The OP wishes that for a generated column, we'd expand it
to the generation expression instead, presumably with the SET expressions
replacing any references to those columns. I can see the argument
for that, but it's probably several years too late to change it now.
Even if anybody wanted to put the work into it, which frankly I doubt.
Non-SELECT rules are a development backwater, and are unlikely to
escape that category given how hard they are to use safely.
Looking at the code, it appears that generated columns are also not
updated till after BEFORE INSERT/BEFORE UPDATE triggers fire. That's
not a development backwater, so I suppose the decision was deliberate
in that case. Probably the reasoning was that we'd have to calculate
the generated columns twice in case a BEFORE trigger makes any relevant
changes in the row, and that seemed unduly costly. But anyway, it seems
like being consistent with that behavior is another reason not to change
the behavior of ON UPDATE rules.
regards, tom lane
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2023-04-13 17:30 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-04-13 14:02 Unexpected behavior when combining `generated always` columns and update rules Ciprian Craciun <ciprian.craciun@gmail.com>
2023-04-13 14:32 ` David G. Johnston <david.g.johnston@gmail.com>
2023-04-13 14:37 ` Tom Lane <tgl@sss.pgh.pa.us>
2023-04-13 16:27 ` Ciprian Craciun <ciprian.craciun@gmail.com>
2023-04-13 16:57 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-04-13 17:03 ` Adrian Klaver <adrian.klaver@aklaver.com>
2023-04-13 17:30 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox