odd lock on CREATE TABLE AS SELECT

From: "digital(dot)death(at)gmx(dot)it" <digital(dot)death(at)gmx(dot)it>
To: pgsql-bugs(at)postgresql(dot)org
Subject: odd lock on CREATE TABLE AS SELECT
Date: 2009-11-08 21:19:17
Message-ID: 20091108221917.48a4c3ea@maglie.elkos.it
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Hello,

I hope it's not a bug, but I get an exclusive lock in a CREATE TABLE AS
SELECT statement, contained in the following function/transaction:

-- this is an alternative to CLUSTER TABLE, you know, create a
-- sorted clone of the table,then create all indexes, FKs and
-- then "switch" to new table with two ALTER TABLE ... RENAME.
-- "adc" is the old table, not ordered
-- "adc_clustered" is the brand-new created table

CREATE OR REPLACE FUNCTION cluster_adc_table () RETURNS INTEGER AS $$
BEGIN
DROP TABLE IF EXISTS adc_clustered;
RAISE NOTICE 'start creating clustered table at %s',
clock_timestamp();
CREATE TABLE adc_clustered AS (
SELECT * FROM adc ORDER BY somecolumn DESC NULLS LAST);
ALTER TABLE adc_clustered ADD PRIMARY KEY (id);
CREATE INDEX adc_ft_idx ON adc_clustered USING gin(somecol);

RAISE NOTICE 'finished creating clustered table at %s',
clock_timestamp();
ANALYZE adc_clustered;
ALTER TABLE adc RENAME TO adc_old;
ALTER TABLE adc_clustered RENAME TO adc;
RETURN 1;

EXCEPTION
WHEN DEADLOCK_DETECTED THEN
RETURN 0;
END;

"adc" table is locked in exclusive mode because I can't select from it
(it waits till the end of transaction). Meanwhile, people are trying to
do lots of SELECT so when creation finishes, it tries to ALTER
TABLE and yeah, here comes the deadlock:

ERROR: deadlock detected
DETAIL: Process 5087 waits for AccessShareLock on relation 63704 of
database 16385; blocked by process 5095.
Process 5095 waits for AccessExclusiveLock on relation 63301 of
database 16385; blocked by process 5087.
Process 5087: SELECT COUNT(adc.datepublished) AS c FROM ad WHERE
ad.COLUMN1='t' AND adc.COLUMN2<=1 AND ad.FT1 @@
'word1'::tsquery
Process 5095: SELECT cluster_adc_table() AS cluster_result
HINT: See server log for query details.

This behavior seems strange to me, and I ask myself:

- If transaction locks "adc" in exclusive mode from the beginning, how
can a deadlock occur?
- If not, why can't I SELECT from the adc table while "CREATEing TABLE
adc_clustered AS SELECT * FROM adc"?
- Everybody is doing SELECT on "adc" table, so why deadlocks if there
are no write attempts on any row?

Pg version: 8.4beta2, pulled out from CVS trunk on June, but I can't
figure which revision is it, I normally use SVN, whose command I launch
is `grep revision .svn/entries | awk -F\" '{print $2}' `.

Machine is a quad-core Opteron with Debian Linux amd64.

Hope it's my fault and not really a bug. I can give you more infos if
you want. Thank you in advance

D

Browse pgsql-bugs by date

  From Date Subject
Next Message Robert Haas 2009-11-08 21:33:17 Re: BUG #5171: Composite type with array does not translate in plpythonu
Previous Message Richard Neill 2009-11-08 18:16:56 BUG #5174: [minor] directories symlinked into base/ are not recursively removed