Thread

BUG #19365: postgres 18 pg_dump fails whan drop sequence concurrently

PG Bug reporting form <noreply@postgresql.org> — 2025-12-27T23:57:29Z

The following bug has been logged on the website:

Bug reference:      19365
Logged by:          Paveł Tyślacki
Email address:      pavel.tyslacki@gmail.com
PostgreSQL version: 18.1
Operating system:   docker: Debian 14.2.0-19 14.2.0, 64-bit
Description:        

POSTGRES VERSION: PostgreSQL 18.1 (Debian 18.1-1.pgdg13+2) on
x86_64-pc-linux-gnu, compiled by gcc (Debian 14.2.0-19) 14.2.0, 64-bit
PG_DUMP VERSION: pg_dump (PostgreSQL) 18.1

My code creates many schemas in parallel, runs DDL SQL on them, runs
`pg_dump` for each one, and then drops the schemas. This works fine for
postgres 13, 14, 15, 16, and 17.

In postgres 18, I started getting random errors from `pg_dump`:

```
pg_dump: error: query failed: ERROR:  could not open relation with OID 16741
pg_dump: detail: Query was: SELECT seqrelid, format_type(seqtypid, NULL),
seqstart, seqincrement, seqmax, seqmin, seqcache, seqcycle, last_value,
is_called FROM pg_catalog.pg_sequence, pg_get_sequence_data(seqrelid) ORDER
BY seqrelid;
```

The following python code reproduces the issue by creating and dropping
table with sequence while running `pg_dump` concurrently.

```
import asyncio
from asyncio.subprocess import PIPE

CONN = "postgresql://postgres:test@127.0.0.1:5432/postgres"
MAX_CONCURRENCY = 20
NUMBER_OF_SCHEMAS = 20

async def run_command(cmd: list[str]) -> str:
    proc = await asyncio.create_subprocess_exec(*cmd, stdout=PIPE,
stderr=PIPE)
    stdout, stderr = await proc.communicate()
    if proc.returncode != 0:
        raise RuntimeError(
            f"Command failed: {' '.join(cmd)}\n{stderr.decode()}"
        )
    return stdout.decode()

async def run_test(i: int, sem: asyncio.Semaphore):
    async with sem:
        schema = f"test_{i}"
        await run_command([
            "psql",
            CONN,
            "-c",
            f"""
            DROP SCHEMA IF EXISTS {schema} CASCADE;
            CREATE SCHEMA {schema};
            CREATE TABLE {schema}.main (
                id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
                value TEXT NOT NULL
            );
            """
        ])
        await run_command([
            "pg_dump",
            CONN,
            f"--schema={schema}",
        ])
        await run_command([
            "psql",
            CONN,
            "-c",
            f"""
            DROP SCHEMA IF EXISTS {schema} CASCADE;
            """
        ])

async def main():
    sem = asyncio.Semaphore(MAX_CONCURRENCY)
    await asyncio.gather(*[
        asyncio.create_task(run_test(i, sem))
        for i in range(NUMBER_OF_SCHEMAS)
    ])

if __name__ == "__main__":
    asyncio.run(main())
```

I expected `pg_dump` to be able to handle sequences being dropped in
parallel without errors.

I’m not sure what output `pg_dump` should produce when trying to dump
sequences that are being dropped, but I believe it should behave similarly
to how it handles standard relations, constraints, indexes, etc.