On 11/16/2015 08:24 PM, zh1029 wrote:
Hi, While start PostgreSQL(9.3.6) and execute pg_restore soon after PostgreSQL bringing up. I encounter pg_restore failure because of deadlock detected. postgres[2737]: [3-1] LOG: process 2737 detected deadlock while waiting for AccessExclusiveLock on relation 33337 of database 24577 after 1000.070 ms postgres[2737]: [3-2] STATEMENT: DROP SCHEMA public CASCADE; postgres[2737]: [4-1] err-1: deadlock detected postgres[2737]: [4-2] DETAIL: Process 2737 waits for AccessExclusiveLock on relation 33337 of database 24577; blocked by process 2720. postgres[2737]: [4-3] Process 2720 waits for AccessShareLock on relation 33344 of database 24577; blocked by process 2737. postgres[2737]: [4-4] Process 2737: DROP SCHEMA public CASCADE; postgres[2737]: [4-5] Process 2720: SELECT sequence_name, start_value, increment_by, CASE WHEN increment_by > 0 AND max_value = 9223372036854775807 THEN NULL WHEN increment_by < 0 AND max_value = -1 THEN NULL ELSE max_value END AS max_value, CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL WHEN increment_by < 0 AND min_value = -9223372036854775807 THEN NULL ELSE min_value END AS min_value, cache_value, is_cycled FROM zonepreprovisioningrules_id_seq postgres[2737]: [4-6] HINT: See server log for query details. postgres[2737]: [4-7] STATEMENT: DROP SCHEMA public CASCADE; I suspect competition between process that bringing up PostgreSQL and process drop schema by pg_restore. So my question is how to guarantee (e.g by inquiring some parameters from system tables? ) PostgreSQL is totally start up to accept drop schema via pg_restore?
To me this: SELECT sequence_name, start_value, increment_by, CASE WHEN increment_by > 0 AND max_value = 9223372036854775807 THEN NULL WHEN increment_by < 0 AND max_value = -1 THEN NULL ELSE max_value END AS max_value, CASE WHEN increment_by > 0 AND min_value = 1 THEN NULL WHEN increment_by < 0 AND min_value = -9223372036854775807 THEN NULL ELSE min_value END AS min_value, cache_value, is_cycled FROM zonepreprovisioningrules_id_seq looks strange. Can you look in the dump file and see where that is coming from?
Brs. -- View this message in context: http://postgresql.nabble.com/pg-restore-encounter-deadlock-since-PostgreSQL-bringing-up-tp5874146.html Sent from the PostgreSQL - general mailing list archive at Nabble.com.
-- Adrian Klaver adrian.klaver@xxxxxxxxxxx -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
