Unexpected behavior when combining `generated always` columns and update rules

From: Ciprian Craciun <ciprian(dot)craciun(at)gmail(dot)com>
To: pgsql-general(at)lists(dot)postgresql(dot)org
Subject: Unexpected behavior when combining `generated always` columns and update rules
Date: 2023-04-13 14:02:17
Message-ID: CA+Tk8fz+Vuov584ff_PxxptB=dG3tX_buFr+4RTvFCN5ERFN4Q@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message David G. Johnston 2023-04-13 14:32:08 Re: Unexpected behavior when combining `generated always` columns and update rules
Previous Message Mike Bayer 2023-04-13 13:32:37 Re: Guidance on INSERT RETURNING order