Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.





[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux