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 -- Sent via pgsql-bugs mailing list (pgsql-bugs@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-bugs