odd deadlock 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 deadlock on CREATE TABLE AS SELECT
Date: 2009-11-08 11:40:13
Message-ID: 20091108124013.47d01bcf@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 a deadlock error in a
function/transaction with these statements:

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);
-- then I create other indexes on new table --
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;

I think "adc" table is locked in exclusive mode because I can't select
(it waits for a long long time) and in logs I can see this:

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.

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 amd64 Opteron with Debian Linux.

Hope it's my fault and not really a bug. I guess I must give you more
infos, right?

Thank you in advance

D

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Richard Neill 2009-11-08 18:16:56 BUG #5174: [minor] directories symlinked into base/ are not recursively removed
Previous Message Viisard 2009-11-07 18:56:36 BUG #5172: ecpg - cursor with regexp containing '.*/' fails to compile with gcc