Request a Demo
Home   >   mailing lists

pg_restore failed on foreign key constraint - Mailing list pgsql-general

From Ron Johnson
Subject pg_restore failed on foreign key constraint
Date
Msg-id CANzqJaD1JNeJfNJkxZOvqSw=ueJLzq3hM3tUf5FJNkbxEr8Z4A@mail.gmail.com
Whole thread
Responses Re: pg_restore failed on foreign key constraint
List pgsql-general
Tree view
When running pg_restore 17.7 against a PG 14.20 database directory dump, I got this in the log:

pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 171927; 2606 3149123877 FK CONSTRAINT rel_user_email fk_rel_user_email_2 TAP
pg_restore: error: could not execute query: ERROR:  insert or update on table "rel_user_email" violates foreign key constraint "fk_rel_user_email_2"
DETAIL:  Key (access_email_id)=(2073) is not present in table "access_email".
Command was: ALTER TABLE ONLY public.rel_user_email
    ADD CONSTRAINT fk_rel_user_email_2 FOREIGN KEY (access_email_id) REFERENCES public.access_email(access_email_id);

So, I went to the source database:

TAPd=# \d rel_user_email
                         Table "public.rel_user_email"
     Column      |            Type             | Collation | Nullable | Default
-----------------+-----------------------------+-----------+----------+---------
 user_id         | integer                     |           | not null |
 access_email_id | integer                     |           | not null |
 modified_by     | integer                     |           |          |
 modified_on     | timestamp without time zone |           | not null |
Indexes:
    "idx_rel_user_email" UNIQUE, btree (user_id, access_email_id)
Foreign-key constraints:
    "fk_rel_user_email_1" FOREIGN KEY (user_id) REFERENCES access_user(user_id)
    "fk_rel_user_email_2" FOREIGN KEY (access_email_id) REFERENCES access_email(access_email_id)

TAPd=# select * from rel_user_email where access_email_id=2073;
 user_id | access_email_id | modified_by |       modified_on      
---------+-----------------+-------------+-------------------------
    2452 |            2073 |          41 | 2013-03-11 10:52:20.331
(1 row)

TAPd=# \d access_email
                                                 Table "public.access_email"
     Column      |            Type             | Collation | Nullable |                        Default                        
-----------------+-----------------------------+-----------+----------+-------------------------------------------------------
 access_email_id | integer                     |           | not null | nextval('access_email_access_email_id_seq'::regclass)
 type            | numeric(10,0)               |           |          |
 email_address   | character varying(255)      |           |          |
 created_on      | timestamp without time zone |           | not null |
 modified_on     | timestamp without time zone |           |          |
 created_by      | integer                     |           |          |
 modified_by     | integer                     |           |          |
Indexes:
    "pk_access_email" PRIMARY KEY, btree (access_email_id)
Referenced by:
    TABLE "rel_user_email" CONSTRAINT "fk_rel_user_email_2" FOREIGN KEY (access_email_id) REFERENCES access_email(access_email_id)

TAPd=# select * from access_email where access_email_id=2073;
 access_email_id | type | email_address | created_on | modified_on | created_by | modified_by
-----------------+------+---------------+------------+-------------+------------+-------------
(0 rows)

Looks like index corruption.  

$ pg_amcheck -t public.rel_user_email -t public.access_email TAPd
$ echo $?
0
$ pg_amcheck -i idx_rel_user_email -i pk_access_email --parent-check --heapallindexed TAPd
$ echo $?
0

But amcheck shows no problems.

Before I get worried that there' s corrupt data: am I missing something obvious?

--
Death to <Redacted>, and butter sauce.
Don't boil me, I'm still alive.
<Redacted> lobster!

pgsql-general by date:

Previous
From: Nico Heller
Date:
Subject: Re: Guarantee order of batched pg_advisory_xact_lock
Next
From: Laurenz Albe
Date:
Subject: Re: pg_restore failed on foreign key constraint