BUG #3067: Unnecessary lock blocks reindex - Mailing list pgsql-bugs

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;