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