pg_restore encounter deadlock since PostgreSQL bringing up

On Tue, Nov 17, 2015 at 3:24 PM, zh1029 <zh1029@sina.com> 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?

"pg_ctl -D <data-directory> status" command will let you know if the
PostgreSQL cluster is up and running.

Are you sure, you do not have any other processes running while pg_restore
process is running ? Whats the background of the process 2720 ?

Regards,
Venkata B N

Fujitsu Australia

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@aklaver.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,
pg_restore is executed by our own process actually, before that, our
process connects the data base via ODBC to access database template1 see if
PostgreSQL server is up. But pg_restore still failed after connect database
I understand the result is same as pg_ctrl output, is that true? Thanks!

--
View this message in context: http://postgresql.nabble.com/pg-restore-encounter-deadlock-since-PostgreSQL-bringing-up-tp5874146p5874157.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Unfortunately what's the process 2720 is unknown as no any else log to
indicate it. It might because the debug level of PostgreSQL had been set too
lower to show more.

--
View this message in context: http://postgresql.nabble.com/pg-restore-encounter-deadlock-since-PostgreSQL-bringing-up-tp5874146p5874159.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hi,
Per my understanding, From the query which resulted in the deadlock
“SELECT sequence_name, start_value, increment_by***********”, it appears
that the query which holds the lock is related to sequence numbers. From our
understanding it appears that, whenever there is a serial type of data
member, PostgreSQL internally needs to keep track of sequence numbers across
the dump and restore. Is it possible select query is triggered by PostgreSQL
bringing up(to bring up internal database) instead of dump file? because
from the log, it looks failed while doing DROP SCHEMA public CASCADE.

--
View this message in context: http://postgresql.nabble.com/pg-restore-encounter-deadlock-since-PostgreSQL-bringing-up-tp5874146p5874160.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On 11/16/2015 10:55 PM, zh1029 wrote:

Unfortunately what's the process 2720 is unknown as no any else log to
indicate it. It might because the debug level of PostgreSQL had been set too
lower to show more.

if its a linux system, try...

ps uww -p 2720

logged on as root, and it should show you the command

--
john r pierce, recycling bits in santa cruz

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

zh1029 wrote:

While start PostgreSQL(9.3.6) and execute pg_restore soon after PostgreSQL
bringing up. I encounter pg_restore failure because of deadlock detected.

pg_restore will not deadlock with itself or system startup.

Set log_connections to "on", then you will see who connected and
caused the deadlock.

Yours,
Laurenz Albe

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

zh1029 <zh1029@sina.com> writes:

Unfortunately what's the process 2720 is unknown as no any else log to
indicate it. It might because the debug level of PostgreSQL had been set too
lower to show more.

The query you quoted for 2720 looks exactly like one that pg_dump will
issue, cf dumpSequence(). What I think is going on here is that you've
got some cron job that aggressively connects to the database and runs a
pg_dump the moment you start it. It's unsurprising that that would
deadlock against a pg_restore -C.

regards, tom lane

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general