Skip site navigation (1) Skip section navigation (2)

Re: odd deadlock on CREATE TABLE AS SELECT

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "digital(dot)death(at)gmx(dot)it" <digital(dot)death(at)gmx(dot)it>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: odd deadlock on CREATE TABLE AS SELECT
Date: 2009-11-09 00:38:59
Message-ID: 20805.1257727139@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"digital(dot)death(at)gmx(dot)it" <digital(dot)death(at)gmx(dot)it> writes:
> I hope it's not a bug, but I get a deadlock error in a
> function/transaction with these statements:

It's not a bug.  The CREATE TABLE AS SELECT is acquiring a read lock on
table "adc", and then the ALTER TABLE RENAME tries to upgrade that lock
to exclusive.  If you've got some other stuff going on with "adc" at
the same time, a deadlock isn't surprising in the least.

You could make the function safe by adding "LOCK TABLE adc" before
the select.  However, if the idea is to not hold a strong lock on adc
while the CREATE is going on, this approach isn't going to work :-(

I kinda think you have more bugs than that, btw.  If a deadlock is
happening it's probably because some other process also had read lock
on "adc" and is trying to upgrade it, which would strongly suggest
that the other process is trying to modify the contents of "adc",
which would be a Real Bad Thing because it implies that you're losing
data with this.  Any changes committed into "adc" after the function
starts are not going to be reflected in the updated version of "adc",
which cannot be what you want.

			regards, tom lane

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2009-11-09 00:41:37
Subject: Re: BUG #5174: [minor] directories symlinked into base/ are not recursively removed
Previous:From: Greg StarkDate: 2009-11-08 23:45:03
Subject: Re: odd deadlock on CREATE TABLE AS SELECT

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group