Обсуждение: generated columns bug, see inconsistent data
This is a bug report refined from here: https://stackoverflow.com/questions/74171028/postgresql-generated-column-is-not-always-up-to-date
I can reproduce the issue in all recent versions (didn't check before 12), including 15.0 and 16dev. You can see rows in which the generated column has a value inconsistent with the columns it is generated from. The attached script sets up the schema and does \watch on a monitoring query to detect inconsistency (no rows should be reported for consistent data), and the last (commented out) line has the necessary pgbench command to cause the problem to occur.
On my system, inconsistencies are seen in about 1 out of 10 \watch queries, so it is not hard to spot. Inconsistencies only show up when pgbench is called with -c greater than 1.
Once an inconsistency gets generated, it seems to be stable, sticking around until the next time that row gets updated upon which it will get fixed. (That is generally less than one reporting period, so you generally won't see the same row in consecutive reports.)
If I copy the core expression of the immutable function into the generated column expression, leaving the function just for the monitoring query, then I never see the inconsistency.
Here is a snippet of the output showing two consecutive inconsistent watch queries:
Tue Oct 25 20:26:16 2022 (every 1s)
id | placed_count | picked_count | packed_count | generated_status
-----+--------------+--------------+--------------+------------------
521 | 3 | 2 | 3 | 4
670 | 3 | 0 | 3 | 4
(2 rows)
Time: 1.715 ms
Tue Oct 25 20:26:17 2022 (every 1s)
id | placed_count | picked_count | packed_count | generated_status
-----+--------------+--------------+--------------+------------------
492 | 4 | 2 | 2 | 2
id | placed_count | picked_count | packed_count | generated_status
-----+--------------+--------------+--------------+------------------
521 | 3 | 2 | 3 | 4
670 | 3 | 0 | 3 | 4
(2 rows)
Time: 1.715 ms
Tue Oct 25 20:26:17 2022 (every 1s)
id | placed_count | picked_count | packed_count | generated_status
-----+--------------+--------------+--------------+------------------
492 | 4 | 2 | 2 | 2
This specific excerpt was from "PostgreSQL 15.0-2a7ce2e2ce on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0, 64-bit". I did not test any other OS.
Cheers,
Jeff
Вложения
Jeff Janes <jeff.janes@gmail.com> writes:
> I can reproduce the issue in all recent versions (didn't check before 12),
> including 15.0 and 16dev. You can see rows in which the generated column
> has a value inconsistent with the columns it is generated from.
My first guess about this is that we're failing to recalculate generated
columns in the EPQ code path where an update is re-applied to a
just-modified row. Can you tell whether the inconsistencies are
associated with rows that get updated twice in quick succession (by
different sessions)? Does the problem go away under serializable mode?
> If I copy the core expression of the immutable function into the generated
> column expression, leaving the function just for the monitoring query, then
> I never see the inconsistency.
Maybe that changes the timing?
regards, tom lane