Partitioned schema dumps can fail to replay or omit indexes when inherited index names change #653

Problem

PostgreSQL automatically creates indexes for inherited PRIMARY KEY and UNIQUE constraints when creating a partition.

During dump replay, pgschema can create objects in a different order from the source database. PostgreSQL can therefore assign different names to those inherited indexes.

This can cause a name collision with another index in the dump. Replay can then fail, or PostgreSQL can skip an intended index because its name already exists.

Minimal reproduction

Use two empty databases, source_db and replay_db.

Create this schema in source_db:

CREATE TABLE public.events (
    id integer PRIMARY KEY
) PARTITION BY RANGE (id);

CREATE INDEX events_leaf_pkey ON public.events (id);

CREATE TABLE public.events_leaf
    PARTITION OF public.events
    FOR VALUES FROM (0) TO (10);

The standalone parent index occupies events_leaf_pkey. PostgreSQL therefore names the inherited primary-key index on events_leaf as events_leaf_pkey1.

Dump and replay:

pgschema dump \
  --host localhost --user postgres --db source_db \
  > schema.sql

psql \
  --host localhost --username postgres --dbname replay_db \
  --set ON_ERROR_STOP=1 --file schema.sql

Actual behavior

During replay:

1. pgschema creates events_leaf before the standalone parent index.
2. PostgreSQL names the inherited primary-key index events_leaf_pkey.
3. PostgreSQL skips the subsequent CREATE INDEX IF NOT EXISTS events_leaf_pkey, because that name now exists.
4. PostgreSQL rejects the subsequent index attachment with SQLSTATE 42809, because the existing index is not the intended partitioned parent index.

A related case can silently omit an index. If a user renames a child's primary-key index and reuses its original name for an index on another column, replay can assign that name back to the primary-key index and skip the other index.

Expected behavior

Dump replay should preserve:

• Index names and definitions.
• PRIMARY KEY and UNIQUE constraint ownership.
• Parent-child index attachments.
• Separate standalone indexes, even when their names overlap with names PostgreSQL would otherwise generate.

Replaying a dump into an empty database should reproduce the source schema. A subsequent plan against that dump should contain no changes.

Context

The reproduction concerns the partition-index dump behavior following #643. PR #645 proposes a fix.

Opened following the maintainer's request: #645 (comment)

This issue describes the required behavior without prescribing a particular SQL-generation strategy.