Re: Checkpoints stuck after a failed bulk write inside a savepoint

From: surya poondla <suryapoondla4(at)gmail(dot)com>
To: Michael Paquier <michael(at)paquier(dot)xyz>
Cc: shihao zhong <zhong950419(at)gmail(dot)com>, Joao Rito <joaovictor(dot)rito(at)gmail(dot)com>, PostgreSQL Hackers <pgsql-hackers(at)lists(dot)postgresql(dot)org>, Heikki Linnakangas <hlinnaka(at)iki(dot)fi>
Subject: Re: Checkpoints stuck after a failed bulk write inside a savepoint
Date: 2026-11-21 17:49:28
Message-ID: CAOVWO5o4uBNY6wXhuHZ4kMh83en_Sze07hQ+=-EyWHyVhEfDvw@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

Hi Michael,

Please find attached to show the idea. I have not spent much time
> testing it. (Note that the CI has failed once on Windows, not sure if
> this was related to this patch or not.)
>
>
I have tested v2 on master, with two injection points added locally
just before and just after smgrregistersync().

The double check of the redo pointer looks right to me. It works
because CreateCheckPoint() publishes XLogCtl->RedoRecPtr before
CheckPointGuts() calls ProcessSyncRequests(), and that absorbs the queued
requests before it bumps the cycle counter.
So if the second check still sees the old redo pointer, any checkpoint that
moves it
later will pick up our request. If the second check sees a new redo
pointer, we fsync the relation ourselves. The flag used to enforce this
ordering explicitly. Now it is implicit, so it may be worth one
sentence in the comment, in case CreateCheckPoint() is reordered later.

What I tested:
1. Error inside a savepoint for each caller of the bulk write facility:
btree CREATE INDEX and REINDEX, sorted GiST build, btree
and SP-GiST on an unlogged table (init fork), ALTER TABLE ... TYPE (index
rebuild), CLUSTER, and ALTER TABLE SET TABLESPACE in both
directions. On master the CHECKPOINT after ROLLBACK TO SAVEPOINT
blocks in all cases. With an assert-enabled build, a second CREATE INDEX
in the same transaction fails the Assert.
With v2 the CHECKPOINT completes in a few ms, and the retry and COMMIT work.

2. A checkpoint forced while the backend waits at each injection point. In
both windows the backend notices that the redo pointer
moved and calls smgrimmedsync(). Without a checkpoint there is no
immediate fsync.

3. Four sessions running REINDEX, CLUSTER, SET TABLESPACE and GiST index
rebuilds in a loop (one of them on an unlogged table), with an error
injected and caught in one of them, and a fifth session running CHECKPOINT
in a loop (about 1100 checkpoints). No hang and no failure. amcheck
(heapallindexed) and GiST scan counts are fine, before and after crash
recovery with an immediate stop.

4. make check passes, and so does Shihao's TAP test from
noncfbot-repro.patch, adapted to v2.

Regarding the backpatch, I agree that the error itself is rare. But no
explicit SAVEPOINT is
needed, a PL/pgSQL EXCEPTION block around the DDL is enough, and that
pattern is common. When it happens, no checkpoint can complete until the
transaction ends.
I tested a DO block that catches the error and keeps running, with
max_wal_size = 32MB, while another session generated WAL. On master
pg_wal grew from 16MB to 192MB and no checkpoint completed. With v2 pg_wal
stayed at 32MB .
So I would lean towards backpatching down to 17, but I leave that to you.

If you want a test, the two injection points around
smgrregistersync() cover the savepoint case and both windows. I can send
them.
Note that the test in noncfbot-repro.patch does not apply on v2, because
its injection point is placed in the v1 code.

Regards,
Surya Poondla

In response to

Browse pgsql-hackers by date

  From Date Subject
Previous Message shihao zhong 2026-11-21 17:47:08 Re: Checkpoints stuck after a failed bulk write inside a savepoint