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;
