Re: ERROR: tuple to be updated was already modified by an operation triggered by the current command - Mailing list pgsql-bugs

On Fri, Apr 11, 2025 at 10:18:04PM +0930, Robins Tharakan wrote:
> On Thu, 23 Jan 2025 at 19:42, Richard Guo <guofenglinux@gmail.com> wrote:

> Repro SQL
> =========
> CREATE TEMPORARY TABLE a(b boolean , UNIQUE(b)) ON COMMIT DELETE ROWS ;
> CREATE TEMP TABLE d() INHERITS(a) ON COMMIT DROP ;
> ANALYSE;

Confirmed.  Thanks for the report.  This is a variant of the bug that commit
7102070 fixed.  Key events:

- The second CREATE sets relhassubclass=t on "a".
- ANALYZE does heap_update() to set relhassubclass=f, since ON COMMIT DROP
  removed the child.
- ON COMMIT DELETE ROWS does an empty index build at end-of-xact, which does
  an inplace update on the pg_class row of "a".  There was no
  CommandCounterIncrement() since the heap_update(), hence this error.

Changing to "ANALYSE a" makes the error go away, because
standard_ProcessUtility() does a CCI (added for bug #15631).  So I'm inclined
to fix that by putting the CCI here for plain "ANALYSE":

--- a/src/backend/commands/vacuum.c
+++ b/src/backend/commands/vacuum.c
@@ -657,6 +657,8 @@ vacuum(List *relations, VacuumParams *params, BufferAccessStrategy bstrategy,
                 if (use_own_xacts)
                 {
                     PopActiveSnapshot();
+                    /* standard_ProcessUtility() does CCI if !use_own_xacts */
+                    CommandCounterIncrement();
                     CommitTransactionCommand();
                 }
                 else

> > This error happens in heap_inplace_lock(), and git-bisect says the
> > first bad commit is:
> >
> > a07e03fd8fa7daf4d1356f7cb501ffe784ea6257 is the first bad commit

That commit made it an error to inplace-update an invisible tuple; before
that, we silently lost the inplace update.