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 /*