Horton

Thread

Commits

  1. Fix some more cases of missed GENERATED-column updates.

  2. Fill EState.es_rteperminfos more systematically.

  1. BUG #17823: Generated columns not always updated correctly

    The Post Office <noreply@postgresql.org> — 2023-03-06T07:26:42Z

    The following bug has been logged on the website:

Bug reference:      17823
Logged by:          Hisahiro Kauchi
Email address:      hisahiro@freemind.co.jp
PostgreSQL version: 15.2
Operating system:   CentOS 7
Description:        

I found that the generated columns are sometimes not updated.

1. Create a table with a generated column, and insert a row:

    test=# create table test(id serial primary key, a int, b int generated
always as (a + 1) stored);
    CREATE TABLE
    test=# insert into test(a) values (1);
    INSERT 0 1
    test=# select * from test;
     id | a | b
    ----+---+---
      1 | 1 | 2
    (1 row)

2. Start Transaction A and update the row:

    ==== Transaction A ====

    test=# begin;
    BEGIN
    test=*# update test set a=2 where id=1;
    UPDATE 1
    test=*# select * from test;
     id | a | b
    ----+---+---
      1 | 2 | 3
    (1 row)

3. Before committing transaction A, start Transaction B to update the same
row:

    ==== Transaction B ====

    test=# begin;
    BEGIN
    test=*# update test set a=3 where id=1;

    (Waiting for Transaction A to commit)

4. Commit Transaction A:

    ==== Transaction A ====

    test=*# end;
    COMMIT

5. The UPDATE of Transaction B is executed:

    ==== Transaction B ====

    UPDATE 1
    test=*# end;
    COMMIT

6. Check the result:

    test=*# select * from test;
     id | a | b
    ----+---+---
      1 | 3 | 3
    (1 row)

The generated column "b" should be updated correctly to reflect the new
value of "a" (i.e., b=4).

  2. Re: BUG #17823: Generated columns not always updated correctly

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-03-06T16:35:29Z

    PG Bug reporting form <noreply@postgresql.org> writes:
> I found that the generated columns are sometimes not updated.

Yeah.  Looking into nodeModifyTable.c, we miss re-doing
ExecComputeStoredGenerated when looping back after an EPQ update
(which is what this case is).  I see that we also fail to redo that
after a cross-partition move, which is a bug since 8bf6ec3ba.

The attached seems to be enough to fix it, but I want to also devise
an isolation test for these cases ...

			regards, tom lane

  3. Re: BUG #17823: Generated columns not always updated correctly

    Tom Lane <tgl@sss.pgh.pa.us> — 2023-03-06T21:18:10Z

    I wrote:
> Yeah.  Looking into nodeModifyTable.c, we miss re-doing
> ExecComputeStoredGenerated when looping back after an EPQ update
> (which is what this case is).  I see that we also fail to redo that
> after a cross-partition move, which is a bug since 8bf6ec3ba.
> The attached seems to be enough to fix it, but I want to also devise
> an isolation test for these cases ...

Building a test case for cross-partition updates showed that there's
a second problem: when we convert an UPDATE into an INSERT on another
partition, we really need to compute all the other partition's
GENERATED columns (as a real INSERT would); but we might only compute
some of them, if we'd already initialized the target partition's
ri_GeneratedExprs data as for an UPDATE.

AFAICS the only true fix for this is to keep separate ri_GeneratedExprs
data for INSERT and UPDATE cases.  Most of the time we'd only compute
one set for any given target partition; but a given partition could
receive both local UPDATEs and cross-partition INSERTs in the same
UPDATE command, so it can happen that we need both.

Hence the attached.  One improvement we can make is to drop the
early call of ExecInitStoredGenerated in ExecInitModifyTable, and
calculate this stuff only upon-demand.  The claim that we have to
do it to pre-fill ri_extraUpdatedCols is wrong given that
ExecGetExtraUpdatedCols now knows to call ExecInitStoredGenerated.

I'm not sure how much of this needs to be back-patched.  I think that
before 8bf6ec3ba, it might not matter if a cross-partition INSERT misses
doing some of the target partition's GENERATED expressions, since they
should match those of the source partition which we'd have already
computed correctly in the to-be-inserted tuple.

			regards, tom lane