generated columns bug, see inconsistent data

From: Jeff Janes <jeff(dot)janes(at)gmail(dot)com>
To: pgsql-bugs <pgsql-bugs(at)lists(dot)postgresql(dot)org>
Subject: generated columns bug, see inconsistent data
Date: 2022-10-26 00:58:50
Message-ID: CAMkU=1xBMZ1-qq3+ef-j75g-X2JN+cZdDm_pGou9gceBvhqe_w@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Attachment Content-Type Size
gen_column_bug.sql application/octet-stream 1.2 KB

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Tom Lane 2022-10-26 01:35:00 Re: generated columns bug, see inconsistent data
Previous Message Tom Lane 2022-10-25 14:13:32 Re: BUG #17662: Error on UPDATE with ()