SELECT FOR UPDATE locking other tables in PostgreSQL

Question

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

Answer 1

We also don't ever update primary keys.

In that case, I think you can use FOR NO KEY UPDATE instead of FOR UPDATE. It's a weaker lock, as explained in Postgres docs about Explicit Locking:

FOR NO KEY UPDATE

Behaves similarly to FOR UPDATE, except that the lock acquired is weaker: this lock will not block SELECT FOR KEY SHARE commands that attempt to acquire a lock on the same rows. This lock mode is also acquired by any UPDATE that does not acquire a FOR UPDATE lock.

Test:

-- Setup
create table a (id int primary key, 
 x varchar);
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 no key update;
/* Keep the transaction open */
 -- Right
 begin;
 update b set x = 'abc' where id = 1;
 update b set x = 'xyz'; -- doesn't block
-- Left
update a set x = 'left' where id = 1;
commit ;
 -- Right
 commit ;

Answer 2

FOR UPDATE causes the rows retrieved by the SELECT statement to be locked as though for the update. This prevents them from being locked, modified or deleted by other transactions until the current transaction ends. That is, other transactions that attempt UPDATE of these rows will be blocked until the current transaction ends.

SO just remove the lock by removing FOR UPDATE..I couldn't get why do you require the "FOR UPDATE" on the first statement.

• Julien: The FOR UPDATE is really intended; I do want any other update on table A to be blocked. I don't want updates on table B to be blocked though.
• ypercubeᵀᴹ: @Julien do you want to lock a specific row or the whole table?
• Julien: @ypercubeTM I want to lock specific rows, just to avoid deadlocks. The actual statement in the application is something like SELECT 1 FROM table WHERE ... ORDER BY id FOR UPDATE;