Recently ran into the following:
Consider the tables created by this:
create table table_a ( id bigserial not null, last_update timestamp, primary key (id) ); create table table_b ( id bigserial not null, primary key (id) ); alter table table_b add column a_id int8; alter table table_b add constraint fk_c foreign key (a_id) references table_a;
And then, the following query(which takes a bit longer than the example shown here might have you suspect):
update table_a set last_update = now() where id in(<list of ids>);
I observed that this update query created an
ExclusiveLock on table_b.
That seems really weird, as table_b isn't being used by the query at all. Or so I think.
What's going on here? Does
where id in create this lock implicitly?
Either way, how can I avoid this? Where does my understanding fail?
Also, the query that showed me this lock being taken on table_b:
SELECT a.datname, c.relname, l.transactionid, l.mode, l.GRANTED, a.usename, a.current_query, a.query_start, age(now(), a.query_start) AS "age", a.procpid FROM pg_stat_activity a JOIN pg_locks l ON l.pid = a.procpid JOIN pg_class c ON c.oid = l.relation WHERE pid <> pg_backend_pid() ORDER BY a.query_start;