Re: pg_restore: fails to restore post-data items due to circular FK deadlock

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: vaibhave postgres <postgresvaibhave(at)gmail(dot)com>
Cc: pgsql-bugs(at)lists(dot)postgresql(dot)org, vsekar(at)microsoft(dot)com
Subject: Re: pg_restore: fails to restore post-data items due to circular FK deadlock
Date: 2024-05-30 18:29:29
Message-ID: 3908.1717093769@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

vaibhave postgres <postgresvaibhave(at)gmail(dot)com> writes:
> 1. Create a database which has circular foreign key dependencies. (or use
> the sql script which I have shared), restore the pre-data section first.
> 2. pg_dump -Fc *--section=post-data*
> 3. pg_restore --jobs > 1

> pg_restore fails to record the dependency between the foreign keys and
> tries to process them in parallel (see the attached log file for details)

The reason this works in other cases is that pg_restore recognizes
that two ADD CONSTRAINT steps shouldn't be run concurrently when they
have dependencies on the same table(s). However, when you use
--section=post-data to create the dump file, there are no entries for
the tables.

So this seems like a "don't do that" case. You could get the results
you want by using --schema-only at dump time and then using
--section=post-data as a pg_restore switch. (That'd also avoid the
need to make two separate dump files.)

Possibly we should add something to the docs about this.

regards, tom lane

In response to

Browse pgsql-bugs by date

  From Date Subject
Next Message Tobey, Paul SOPT 6151 2024-05-30 21:40:56 COPY FROM STDIN BINARY failure
Previous Message PG Bug reporting form 2024-05-30 15:42:51 BUG #18488: Installation of postgis30_13 fails on Rocky 9