2

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;
  • @a_horse_with_no_name No, still happening. – KdgDev Nov 28 '18 at 12:31
  • @a_horse_with_no_name 9.1 it is indeed quite old and out of my control. – KdgDev Nov 28 '18 at 12:41
  • @a_horse_with_no_name It is as I feared then. Well, thanks for your time. – KdgDev Nov 28 '18 at 13:09
  • I have added this as an answer. The proper way to "close" a question is to accept an answer. – a_horse_with_no_name Nov 28 '18 at 13:17
3

There is no way to fix this with PostgreSQL 9.1

Up until 9.2 (including) any referencing table was locked during an update.

This was fixed in the PostgreSQL 9.3 (also unsupported by now)

Quote from the release notes:

Prevent non-key-field row updates from blocking foreign key checks

| improve this answer | |
  • Totally forgot about this. Answer accepted. – KdgDev Feb 19 '19 at 17:26

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.