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.
