Re: 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(at)postgresql(dot)org> wrote:
>
> The following bug has been logged on the website:
>
> Bug reference: 19687
> Logged by: Alexander Lakhin
> Email address: exclusion(at)gmail(dot)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