Command order bug in pg_dump

From: Kirill Reshke <reshkekirill(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Command order bug in pg_dump
Date: 2025-04-21 10:56:39
Message-ID: CALdSSPhGitjpTfzEMJN-Y2x+Q-5QChSxAsmSJ1-E8mQJLkHOqQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message David Rowley 2025-04-21 12:11:08 Re: BUG #18885: ERROR: corrupt MVNDistinct entry - 2
Previous Message Hayato Kuroda (Fujitsu) 2025-04-21 10:08:18 RE: Disabled logical replication origin session causes primary key errors