From: | hubert depesz lubaczewski <depesz(at)depesz(dot)com> |
---|---|
To: | PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org> |
Subject: | pg_restore deadlocks with itself |
Date: | 2022-08-25 12:44:11 |
Message-ID: | 20220825124411.GA8257@depesz.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi,
I have Pg 14.4 on arm64, and I'm loading schema dump from pg12.
Specifically, command to run it is:
sudo -u postgres pg_restore -j 64 -d database -L /tmp/schema-post-data.nopkey.list /tmp/schema-post-data.dump
where /tmp/schema-post-data.dump is dump made using:
sudo -u postgres pg_dump --schema-only --compress=9 --format=c --section='post-data' --schema='*(something|else|xxx)*' database
and /tmp/schema-post-data.nopkey.list is made using:
#v+
pkey_regexp='^\d+; \d+ \d+ CONSTRAINT \S+ \S+ \S+_pkey '
pg_restore -l "${local_dump_dir}/schema-post-data.dump" | grep -vP "${pkey_regexp}" > "${local_dump_dir}/schema-post-data.nopkey.list"
#v-
The thing is that the list file has 7676 lines, out of which 2313 are
foreign keys.
database has basically 3 schemas, and fkeys NEVER cross schema.
When loading the dump I got reported 4 errors, but log shows 2 deadlocks.
perhaps all sides got cancelled, not sure, will investigate further, but logs
show:
2022-08-24 20:01:04.466 UTC,"postgres","database",3343477,"[local]",630624ad.330475,42,"ALTER TABLE waiting",2022-08-24 13:16:29 UTC,21/1932,0,LOG,00000,"process 3343477 detected deadlock while waiting for ShareRowExclusiveLock on relation 742617610 of database 16641 after 1000.647 ms","Process holding the lock: 3587718. Wait queue: .",,,,,"ALTER TABLE ONLY some_schema.table_a_o
ADD CONSTRAINT table_a_o_q_id_fk FOREIGN KEY (q_id) REFERENCES some_schema.table_q(id);
",,,"pg_restore","client backend",,3355460102417501954
2022-08-24 20:01:50.291 UTC,"postgres","database",3343477,"[local]",630624ad.330475,46,"ALTER TABLE waiting",2022-08-24 13:16:29 UTC,21/1933,0,LOG,00000,"process 3343477 detected deadlock while waiting for ShareRowExclusiveLock on relation 742617610 of database 16641 after 1000.030 ms","Process holding the lock: 3587718. Wait queue: .",,,,,"ALTER TABLE ONLY some_schema.table_a
ADD CONSTRAINT fk_rails_46718e626a FOREIGN KEY (migrate_from_id) REFERENCES some_schema.table_q(id);
",,,"pg_restore","client backend",,-2548896815899838768
Now, I know I can fix the situation by adding missing fkeys myself, but
I don't think pg_restore should be putting itself in deadlock.
Best regards,
depesz
From | Date | Subject | |
---|---|---|---|
Next Message | Ajin Cherian | 2022-08-25 14:07:37 | Re: Excessive number of replication slots for 12->14 logical replication |
Previous Message | Alexander Kukushkin | 2022-08-25 08:34:40 | Re: pg_rewind WAL segments deletion pitfall |