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

Re: odd deadlock on CREATE TABLE AS SELECT

From: digitaldeath <digital(dot)death(at)gmx(dot)it>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Re: odd deadlock on CREATE TABLE AS SELECT
Date: 2009-11-09 09:48:27
Message-ID: 24b661c20911090148nce91575k10d0502cc99ab21a@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-bugs
Oh no, I thought my message was rejected and I reposted it in a
slightly modified form..

2009/11/9 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>:
> 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.

Ok, I have split my function in two pieces, one for CREATE TABLE AS
SELECT and one for ALTER TABLE, but:

If acquired lock is only a read lock, why can't I SELECT from the adc
table while CREATEing TABLE
AS SELECT? Nobody is trying to change nothing...

> 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.

Everybody is doing SELECT only on "adc" table, so why deadlocks if there
are no write attempts on any row?

That's the reason why I posted my message, it seems strange: SELECTs
only do read...

Thank you for your help

In response to

Responses

pgsql-bugs by date

Next:From: Greg StarkDate: 2009-11-09 11:25:03
Subject: Re: odd deadlock on CREATE TABLE AS SELECT
Previous:From: Peter EisentrautDate: 2009-11-09 08:33:04
Subject: Re: BUG #5171: Composite type with array does not translate in plpythonu

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