pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedBUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks
8+ messages / 5 participants
[nested] [flat]
* BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks
@ 2026-09-14 05:00 PG Bug reporting form <noreply@postgresql.org>
2026-09-14 19:07 ` Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-09-15 06:57 ` Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks Alexandre Felipe <o.alexandre.felipe@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: PG Bug reporting form @ 2026-09-14 05:00 UTC (permalink / raw)
To: pgsql-bugs@lists.postgresql.org; +Cc: exclusion@gmail.com
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.
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks
2026-09-14 05:00 BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks PG Bug reporting form <noreply@postgresql.org>
@ 2026-09-14 19:07 ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-09-23 18:00 ` Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks Alexander Lakhin <exclusion@gmail.com>
1 sibling, 1 reply; 8+ messages in thread
From: Ayush Tiwari @ 2026-09-14 19:07 UTC (permalink / raw)
To: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks
2026-09-14 05:00 BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks PG Bug reporting form <noreply@postgresql.org>
2026-09-14 19:07 ` Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks Ayush Tiwari <ayushtiwari.slg01@gmail.com>
@ 2026-09-23 18:00 ` Alexander Lakhin <exclusion@gmail.com>
2026-09-23 18:59 ` Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks Alexandre Felipe <o.alexandre.felipe@gmail.com>
2026-09-23 19:39 ` Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks Ayush Tiwari <ayushtiwari.slg01@gmail.com>
0 siblings, 2 replies; 8+ messages in thread
From: Alexander Lakhin @ 2026-09-23 18:00 UTC (permalink / raw)
To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; pgsql-bugs@lists.postgresql.org, Alexandre Felipe <o.alexandre.felipe@gmail.com>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks
2026-09-14 05:00 BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks PG Bug reporting form <noreply@postgresql.org>
2026-09-14 19:07 ` Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-09-23 18:00 ` Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks Alexander Lakhin <exclusion@gmail.com>
@ 2026-09-23 18:59 ` Alexandre Felipe <o.alexandre.felipe@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Alexandre Felipe @ 2026-09-23 18:59 UTC (permalink / raw)
To: Alexander Lakhin <exclusion@gmail.com>; +Cc: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; pgsql-bugs@lists.postgresql.org
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks
2026-09-14 05:00 BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks PG Bug reporting form <noreply@postgresql.org>
2026-09-14 19:07 ` Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-09-23 18:00 ` Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks Alexander Lakhin <exclusion@gmail.com>
@ 2026-09-23 19:39 ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-09-24 01:19 ` Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks Michael Paquier <michael@paquier.xyz>
1 sibling, 1 reply; 8+ messages in thread
From: Ayush Tiwari @ 2026-09-23 19:39 UTC (permalink / raw)
To: Alexander Lakhin <exclusion@gmail.com>; +Cc: pgsql-bugs@lists.postgresql.org, Alexandre Felipe <o.alexandre.felipe@gmail.com>; Andres Freund <andres@anarazel.de>; Peter Eisentraut <peter@eisentraut.org>; Michael Paquier <michael@paquier.xyz>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks
2026-09-14 05:00 BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks PG Bug reporting form <noreply@postgresql.org>
2026-09-14 19:07 ` Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-09-23 18:00 ` Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks Alexander Lakhin <exclusion@gmail.com>
2026-09-23 19:39 ` Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks Ayush Tiwari <ayushtiwari.slg01@gmail.com>
@ 2026-09-24 01:19 ` Michael Paquier <michael@paquier.xyz>
2026-09-24 02:47 ` Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks Ayush Tiwari <ayushtiwari.slg01@gmail.com>
0 siblings, 1 reply; 8+ messages in thread
From: Michael Paquier @ 2026-09-24 01:19 UTC (permalink / raw)
To: Ayush Tiwari <ayushtiwari.slg01@gmail.com>; +Cc: Alexander Lakhin <exclusion@gmail.com>; pgsql-bugs@lists.postgresql.org, Alexandre Felipe <o.alexandre.felipe@gmail.com>; Andres Freund <andres@anarazel.de>; Peter Eisentraut <peter@eisentraut.org>
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
Attachments:
[application/pgp-signature] signature.asc (832B, ../../arR6vutn730-lQ84@paquier.xyz/2-signature.asc)
download
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks
2026-09-14 05:00 BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks PG Bug reporting form <noreply@postgresql.org>
2026-09-14 19:07 ` Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-09-23 18:00 ` Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks Alexander Lakhin <exclusion@gmail.com>
2026-09-23 19:39 ` Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-09-24 01:19 ` Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks Michael Paquier <michael@paquier.xyz>
@ 2026-09-24 02:47 ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
0 siblings, 0 replies; 8+ messages in thread
From: Ayush Tiwari @ 2026-09-24 02:47 UTC (permalink / raw)
To: Michael Paquier <michael@paquier.xyz>; +Cc: Alexander Lakhin <exclusion@gmail.com>; pgsql-bugs@lists.postgresql.org, Alexandre Felipe <o.alexandre.felipe@gmail.com>; Andres Freund <andres@anarazel.de>; Peter Eisentraut <peter@eisentraut.org>
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
^ permalink raw reply [nested|flat] 8+ messages in thread
* Re: BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks
2026-09-14 05:00 BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks PG Bug reporting form <noreply@postgresql.org>
@ 2026-09-15 06:57 ` Alexandre Felipe <o.alexandre.felipe@gmail.com>
1 sibling, 0 replies; 8+ messages in thread
From: Alexandre Felipe @ 2026-09-15 06:57 UTC (permalink / raw)
To: exclusion@gmail.com; pgsql-bugs@lists.postgresql.org
Proposed a fix:
https://commitfest.postgresql.org/patch/7303/
Thank you for sharing.
Regards,
Alexandre
^ permalink raw reply [nested|flat] 8+ messages in thread
end of thread, other threads:[~2026-09-24 02:47 UTC | newest]
Thread overview: 8+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2026-09-14 05:00 BUG #19687: ALTER SEQUENCE provokes error XX001 could not read blocks PG Bug reporting form <noreply@postgresql.org>
2026-09-14 19:07 ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-09-23 18:00 ` Alexander Lakhin <exclusion@gmail.com>
2026-09-23 18:59 ` Alexandre Felipe <o.alexandre.felipe@gmail.com>
2026-09-23 19:39 ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-09-24 01:19 ` Michael Paquier <michael@paquier.xyz>
2026-09-24 02:47 ` Ayush Tiwari <ayushtiwari.slg01@gmail.com>
2026-09-15 06:57 ` Alexandre Felipe <o.alexandre.felipe@gmail.com>
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