pg_restore: fails to restore post-data items due to circular FK deadlock
*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