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