pg_restore: fails to restore post-data items due to circular FK deadlock
Hackorum builds and tests every patch posted to the lists, not only commitfest submissions. This is Hackorum's own CI rather than the PostgreSQL project's, and it is still under testing - please report anything that looks wrong.
You can run a PostgreSQL built from this patch straight from Docker, with no checkout and no build:
docker run --rm -p 5432:5432 ghcr.io/hackorum-dev/postgres-patch:t72558psql -h localhost -U postgresBuilt from patchset v3 (message #3), September 20, 2026 at 01:47 AM.
Every patchset is also pushed to a branch of our PostgreSQL fork, so you can check out the same tree CI built. Without a PostgreSQL checkout:
git clone --branch t72558_3 https://github.com/hackorum-dev/postgres.gitIn a checkout you already have, add the fork once:
git remote add hackorum https://github.com/hackorum-dev/postgres.gitthen, for this patchset and every later one:
git fetch hackorum t72558_3 && git checkout t72558_3Patchset v3 (message #3) is on t72558_3
*Repro steps*
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)
vaibhave postgres <postgresvaibhave@gmail.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
On Thu, May 30, 2024 at 11:59 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Show quoted text
vaibhave postgres <postgresvaibhave@gmail.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 > 1pg_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