pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedgenerated columns bug, see inconsistent data
2+ messages / 2 participants
[nested] [flat]
* generated columns bug, see inconsistent data
@ 2022-10-26 00:58 Jeff Janes <jeff.janes@gmail.com>
0 siblings, 1 reply; 2+ messages in thread
From: Jeff Janes @ 2022-10-26 00:58 UTC (permalink / raw)
To: pgsql-bugs <pgsql-bugs@lists.postgresql.org>
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
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
Attachments:
gen_column_bug.sql (application/octet-stream, 1.2K) download
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: generated columns bug, see inconsistent data
@ 2022-10-26 01:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Jeff Janes <jeff.janes@gmail.com>
0 siblings, 0 replies; 2+ messages in thread
From: Tom Lane @ 2022-10-26 01:35 UTC (permalink / raw)
To: Jeff Janes <jeff.janes@gmail.com>; +Cc: pgsql-bugs <pgsql-bugs@lists.postgresql.org>; Peter Eisentraut <peter.eisentraut@enterprisedb.com>
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2022-10-26 01:35 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2022-10-26 00:58 generated columns bug, see inconsistent data Jeff Janes <jeff.janes@gmail.com>
2022-10-26 01:35 ` Tom Lane <tgl@sss.pgh.pa.us>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox