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

Поиск
Список
Период
Сортировка
От Ciprian Craciun
Тема Unexpected behavior when combining `generated always` columns and update rules
Дата
Msg-id CA+Tk8fz+Vuov584ff_PxxptB=dG3tX_buFr+4RTvFCN5ERFN4Q@mail.gmail.com
обсуждение исходный текст
Ответы Re: Unexpected behavior when combining `generated always` columns and update rules  ("David G. Johnston" <david.g.johnston@gmail.com>)
Список 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.



В списке pgsql-general по дате отправления:

Предыдущее
От: "Mike Bayer"
Дата:
Сообщение: Re: Guidance on INSERT RETURNING order
Следующее
От: "David G. Johnston"
Дата:
Сообщение: Re: Unexpected behavior when combining `generated always` columns and update rules