The situation, with PostgreSQL 9.6:
- table A with integer primary key
- table B with foreign key constraint on its primary key referencing table A's primary key
SELECT id FROM A FOR UPDATE; blocks
UPDATE B SET x=y; until the lock on A is released.
I assume this is because the referenced value might change. What can I to avoid the first statement blocking execution of the second one without dropping the foreign key constraint?
If I drop the foreign key constraint, what bad things should I expect? In the actual database where this problem occurs, it wouldn't be a significant issue if table B had rows left over after deleting from A. We also don't ever update primary keys.
Edit with code:
-- Setup create table a (id int primary key); create table b (id int primary key references a (id) on update cascade on delete cascade, x varchar); insert into a values (1), (2); insert into b values (1, null), (2, null); -- Left begin; select 1 from a for update; /* Keep the transaction open */ -- Right begin; update b set x = 'abc' where id = 1; update b set x = 'xyz'; /* It blocks here /*