BUG #3067: Unnecessary lock blocks reindex

The following bug has been logged online:

Bug reference: 3067
Logged by: Axel Noltemeier
Email address: axel(dot)noltemeier(at)gmx(dot)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;