The following bug has been logged online:
Bug reference: 3067
Logged by: Axel Noltemeier
Email address: axel.noltemeier@gmx.de
PostgreSQL version: 8.2.1
Operating system: linux: Ubuntu 6.10, Edgy Eft; Mandriva 9.2
Description: Unnecessary lock blocks reindex
Details:
Data:
CREATE TABLE factory (
id smallint NOT NULL,
type_id smallint,
name character varying(50) NOT NULL,
asdb_id integer
);
INSERT INTO factory VALUES (2, 1, 'Hannover', 10418);
INSERT INTO factory VALUES (3, 1, 'Bonn', 10218);
ALTER TABLE ONLY factory ADD CONSTRAINT factory_asdb_id_ukey UNIQUE
(asdb_id);
ALTER TABLE ONLY factory ADD CONSTRAINT factory_pkey PRIMARY KEY (id);
ALTER TABLE ONLY factory ADD CONSTRAINT factory_ukey UNIQUE (type_id, name,
ip, asdb_id);
CREATE TABLE machine (
id integer NOT NULL,
factory_id smallint NOT NULL,
name character varying(50) NOT NULL,
factory_machine_id integer NOT NULL
);
INSERT INTO machine VALUES (1, 3, 'Mach1', 10303);
INSERT INTO machine VALUES (2, 3, 'Mach2', 10103);
ALTER TABLE ONLY machine ADD CONSTRAINT machine_factory_id_ukey UNIQUE
(factory_id, factory_machine_id);
ALTER TABLE ONLY machine ADD CONSTRAINT machine_pkey PRIMARY KEY (id);
ALTER TABLE ONLY machine ADD CONSTRAINT fk_machine_factory FOREIGN KEY
(factory_id) REFERENCES factory(id);
Query:
BEGIN;
-- This statement generates an unnecessary(?) lock
-- on factory_pkey. That index is not used in the plan.
EXPLAIN ANALYZE SELECT *
FROM factory f
Where f.id IN (select m.factory_id from machine m);
-- Show the lock
select now(), db.datname, c.relname, l.*
from pg_locks l
left outer join pg_class c on (l.relation = c.oid)
left outer join pg_database db on (l.database = db.oid)
where relname = 'factory_pkey' ;
-- At this point of execution "reindex table factory;" called from
-- another transaction is blocked
COMMIT;
