BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks

Hi,

On Mon, 14 Sept 2026 at 20:23, PG Bug reporting form
<noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 19687
Logged by: Alexander Lakhin
Email address: exclusion@gmail.com
PostgreSQL version: 19beta3
Operating system: Ubuntu 24.04
Description:

The following script:
psql -c "CREATE SEQUENCE s AS bigint MAXVALUE 1000000;"
for ((i=1; i<=100; i++)); do
echo "iteration $i"
for ((i=1; i<=100; i++)); do echo "SELECT * FROM s;"; done | psql

/dev/null &

for ((i=1; i<=100; i++)); do echo "ALTER SEQUENCE s AS int;"; done | psql

/dev/null

grep -A1 "ERROR: could not read block" server.log && break;
wait
done

triggers:
iteration 3
ERROR: could not read blocks 0..0 in file "base/16384/16597": read only 0
of 8192 bytes
2026-09-14 07:53:16.554 EEST [2078499:4] psql XX001 ERROR: could not read
blocks 0..0 in file "base/16384/16597": read only 0 of 8192 bytes
2026-09-14 07:53:16.554 EEST [2078499:5] psql XX001 STATEMENT: SELECT *
FROM s;

This error is easily reproduced with io_workers, but it can be reproduced
starting from 3d79013b9, with increased number of iterations and SELECT
clients.

Thanks for the report! I was looking at it for some time partially.

I reproduced this with io_method=worker. AlterSequence() takes
ShareRowExclusiveLock, which doesn't conflict with a plain SELECT's
AccessShareLock. A scan could still be using the old storage when ALTER
commits and removes it, which seems to explain the short read(?)

Would it make sense to take AccessExclusiveLock at the initial lookup?

diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -447,7 +447,7 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt)

/* Open and lock sequence, and check for ownership along the way. */
relid = RangeVarGetRelidExtended(stmt->sequence,
- ShareRowExclusiveLock,
+ AccessExclusiveLock,
stmt->missing_ok ? RVR_MISSING_OK : 0,
RangeVarCallbackOwnsRelation,
NULL);

Regards,
Ayush

Proposed a fix:
https://commitfest.postgresql.org/patch/7303/

Thank you for sharing.

Regards,
Alexandre

Hello Ayush and Alexandre,

14.09.2026 22:07, Ayush Tiwari wrote:

On Mon, 14 Sept 2026 at 20:23, PG Bug reporting form
<noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 19687
...
The following script:
...

triggers:
iteration 3
ERROR: could not read blocks 0..0 in file "base/16384/16597": read only 0
of 8192 bytes
2026-09-14 07:53:16.554 EEST [2078499:4] psql XX001 ERROR: could not read
blocks 0..0 in file "base/16384/16597": read only 0 of 8192 bytes
2026-09-14 07:53:16.554 EEST [2078499:5] psql XX001 STATEMENT: SELECT *
FROM s;

Thank you for working on the fix!

Just for the record: with these parameters:
cpu_tuple_cost = 10000
min_parallel_table_scan_size = 1

set, the same script triggers also:
TRAP: failed Assert("RelFileLocatorEquals(relation->rd_locator, pscan->phs_locator)"), File: "tableam.c", Line: 173,
PID: 4140896
ExceptionalCondition at assert.c:51:13
table_beginscan_parallel at tableam.c:178:14
ExecSeqScanInitializeWorker at nodeSeqscan.c:450:30
ExecParallelInitializeWorker at execParallel.c:1406:5
ParallelQueryMain at execParallel.c:1566:2
ParallelWorkerMain at parallel.c:1571:2
BackgroundWorkerMain at bgworker.c:868:2
postmaster_child_launch at launch_backend.c:269:3
StartBackgroundWorker at postmaster.c:4222:5
 (inlined by) maybe_start_bgworkers at postmaster.c:4385:9
ServerLoop at postmaster.c:1745:6
CreateOptsFile at postmaster.c:4166:3
 (inlined by) PostmasterMain at postmaster.c:1301:7
check_root at main.c:448:3
 (inlined by) main at main.c:195:3
...

Best regards,
Alexander

On Wed, Sep 23, 2026, 19:00 Alexander Lakhin <exclusion@gmail.com> wrote:

Hello Ayush and Alexandre,

14.09.2026 22:07, Ayush Tiwari wrote:

On Mon, 14 Sept 2026 at 20:23, PG Bug reporting form<noreply@postgresql.org> <noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 19687
...
The following script:
...

triggers:
iteration 3
ERROR: could not read blocks 0..0 in file "base/16384/16597": read only 0
of 8192 bytes
2026-09-14 07:53:16.554 EEST [2078499:4] psql XX001 ERROR: could not read
blocks 0..0 in file "base/16384/16597": read only 0 of 8192 bytes
2026-09-14 07:53:16.554 EEST [2078499:5] psql XX001 STATEMENT: SELECT *
FROM s;

Thank you for working on the fix!

Just for the record: with these parameters:
cpu_tuple_cost = 10000
min_parallel_table_scan_size = 1

set, the same script triggers also:
TRAP: failed Assert("RelFileLocatorEquals(relation->rd_locator,
pscan->phs_locator)"), File: "tableam.c", Line: 173, PID: 4140896
ExceptionalCondition at assert.c:51:13
table_beginscan_parallel at tableam.c:178:14
ExecSeqScanInitializeWorker at nodeSeqscan.c:450:30
ExecParallelInitializeWorker at execParallel.c:1406:5
ParallelQueryMain at execParallel.c:1566:2
ParallelWorkerMain at parallel.c:1571:2
BackgroundWorkerMain at bgworker.c:868:2
postmaster_child_launch at launch_backend.c:269:3
StartBackgroundWorker at postmaster.c:4222:5
(inlined by) maybe_start_bgworkers at postmaster.c:4385:9
ServerLoop at postmaster.c:1745:6
CreateOptsFile at postmaster.c:4166:3
(inlined by) PostmasterMain at postmaster.c:1301:7
check_root at main.c:448:3
(inlined by) main at main.c:195:3

With the patch or without it?

Regards,
Alexandre

Hi,

On Wed, 23 Sept 2026 at 23:30, Alexander Lakhin <exclusion@gmail.com> wrote:

Hello Ayush and Alexandre,

14.09.2026 22:07, Ayush Tiwari wrote:

On Mon, 14 Sept 2026 at 20:23, PG Bug reporting form
<noreply@postgresql.org> wrote:

The following bug has been logged on the website:

Bug reference: 19687
...
The following script:
...

triggers:
iteration 3
ERROR: could not read blocks 0..0 in file "base/16384/16597": read only 0
of 8192 bytes
2026-09-14 07:53:16.554 EEST [2078499:4] psql XX001 ERROR: could not read
blocks 0..0 in file "base/16384/16597": read only 0 of 8192 bytes
2026-09-14 07:53:16.554 EEST [2078499:5] psql XX001 STATEMENT: SELECT *
FROM s;

Thank you for working on the fix!

Just for the record: with these parameters:
cpu_tuple_cost = 10000
min_parallel_table_scan_size = 1

set, the same script triggers also:
TRAP: failed Assert("RelFileLocatorEquals(relation->rd_locator, pscan->phs_locator)"), File: "tableam.c", Line: 173, PID: 4140896
ExceptionalCondition at assert.c:51:13
table_beginscan_parallel at tableam.c:178:14
ExecSeqScanInitializeWorker at nodeSeqscan.c:450:30
ExecParallelInitializeWorker at execParallel.c:1406:5
ParallelQueryMain at execParallel.c:1566:2
ParallelWorkerMain at parallel.c:1571:2
BackgroundWorkerMain at bgworker.c:868:2
postmaster_child_launch at launch_backend.c:269:3
StartBackgroundWorker at postmaster.c:4222:5
(inlined by) maybe_start_bgworkers at postmaster.c:4385:9
ServerLoop at postmaster.c:1745:6
CreateOptsFile at postmaster.c:4166:3
(inlined by) PostmasterMain at postmaster.c:1301:7
check_root at main.c:448:3
(inlined by) main at main.c:195:3

I don't have much background on the lock levels needed here, but taking
AccessExclusiveLock upfront seems reasonable given the storage replacement.
I'm less sure whether it's too strong for cases like OWNED BY.
[I've sent a diff upthread, can add a patch if that's the right way to go]

FWIW, I reproduced the parallel assertion without the change, but didn't
see it in 100 rounds with it.

Cc'ing Andres, Michael and Peter, who were involved in the original
sequence locking and transactional changes. Does this approach make
sense, or am I missing something here?

Regards,
Ayush

On Thu, Sep 24, 2026 at 01:09:12AM +0530, Ayush Tiwari wrote:

I don't have much background on the lock levels needed here, but taking
AccessExclusiveLock upfront seems reasonable given the storage replacement.
I'm less sure whether it's too strong for cases like OWNED BY.
[I've sent a diff upthread, can add a patch if that's the right way to go]

Cc'ing Andres, Michael and Peter, who were involved in the original
sequence locking and transactional changes. Does this approach make
sense, or am I missing something here?

Where do you mean to add this extra level of locking?
--
Michael

Hi,

On Thu, 24 Sept 2026 at 06:50, Michael Paquier <michael@paquier.xyz> wrote:

On Thu, Sep 24, 2026 at 01:09:12AM +0530, Ayush Tiwari wrote:

I don't have much background on the lock levels needed here, but taking
AccessExclusiveLock upfront seems reasonable given the storage replacement.
I'm less sure whether it's too strong for cases like OWNED BY.
[I've sent a diff upthread, can add a patch if that's the right way to go]

Cc'ing Andres, Michael and Peter, who were involved in the original
sequence locking and transactional changes. Does this approach make
sense, or am I missing something here?

Where do you mean to add this extra level of locking?

I meant the initial RangeVarGetRelidExtended() call in AlterSequence(),
before init_sequence(), replacing the existing lock mode.

My thinking was that, since ALTER can replace the sequence's storage,
we'd want to exclude ordinary scans until the transaction finishes too.
Taking AccessExclusiveLock at the initial lookup seemed consistent
with that, much like the locking required by ResetSequence()?

diff --git a/src/backend/commands/sequence.c b/src/backend/commands/sequence.c
--- a/src/backend/commands/sequence.c
+++ b/src/backend/commands/sequence.c
@@ -447,7 +447,7 @@ AlterSequence(ParseState *pstate, AlterSeqStmt *stmt)

/* Open and lock sequence, and check for ownership along the way. */
relid = RangeVarGetRelidExtended(stmt->sequence,
- ShareRowExclusiveLock,
+ AccessExclusiveLock,
stmt->missing_ok ? RVR_MISSING_OK : 0,
RangeVarCallbackOwnsRelation,
NULL);

Regards,
Ayush