Command order bug in pg_dump

Kirill Reshke <reshkekirill@gmail.com>

From: Kirill Reshke <reshkekirill@gmail.com>
To: pgsql-bugs@postgresql.org
Date: 2025-04-21T10:56:39Z
Lists: pgsql-bugs
tested against 706cbed351037fb5e886815506515d1281e62d40

Execute this in first db (say, db1):

```
create table tfk (i int unique) partition by range (i );
create table tfk_po partition of tfk for values from ( 0 ) to (1);
create table tt (i int) partition by range (i );
create table tt_po partition of tt for values from ( 0 ) to (1);
ALTER TABLE public.tt
    ADD CONSTRAINT tt_i_fkey FOREIGN KEY (i) REFERENCES public.tfk(i);
ALTER TABLE public.tt
    ADD CONSTRAINT tt_a_fkey FOREIGN KEY (i) REFERENCES public.tfk(i);
```

create new database and dump-restore

./pgbin/bin/pg_dump -d db1 --schema-only > dump-p.sql
./pgbin/bin/createdb db2


restore fails

db2=# \i dump-p.sql
SET
SET
SET
SET
SET
SET
 set_config
------------

(1 row)

SET
SET
SET
SET
SET
CREATE TABLE
ALTER TABLE
SET
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
CREATE TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER TABLE
ALTER INDEX
ALTER TABLE
psql:dump-p.sql:120: ERROR:  constraint "tt_i_fkey" for relation "tt"
already exists
db2=#


This bug is about now postgresql chooses generated name for inherited contains

I think this is a problem, when pg_dump creates sql which is
non-applicable for restore. Bug discovered when digging out pg_upgarde
failure reasons.


-- 
Best regards,
Kirill Reshke



Commits

Same data as JSON: GET /api/v1/messages/:b64id/commits the thread's linked commits as JSON, with link sources. API reference →
  1. Change the names generated for child foreign key constraints.

  2. doc: Fix memory context level in pg_log_backend_memory_contexts() example.