Locking and postgres_fdw extension
Hi all,
I have two DB instances - 'online' and 'offline'. In 'offline' I have a
foreign table 'f' imported from 'online'. I want to execute a query from
'offline' to obtain a row lock, and this works fine:
select id from f where id = 1 for no key update;
However if I want it to fail immediately if unable to obtain the lock, it
seems nowait is ignored for foreign tables (or at least with my version of
postgres_fdw). The waiting session blocks until the holding session commits
or rolls back.
My 'workaround' was to create a view in 'online':
create or replace view f_lock as select * from f for no key update nowait;
Then use import foreign schema to bring that view into 'offline'. I can
then execute:
select id from f_lock where id = 1;
and it will fail immediately if the row is already locked.
Is there a better way to do this?
Thanks,
Steve
Steve Baldwin <steve.baldwin@gmail.com> writes:
However if I want it to fail immediately if unable to obtain the lock, it
seems nowait is ignored for foreign tables (or at least with my version of
postgres_fdw).
Hmm ... this seems to be intentional, per the comments in
deparseLockingClause():
* For now, just ignore any [NO] KEY specification, since (a)
* it's not clear what that means for a remote table that we
* don't have complete information about, and (b) it wouldn't
* work anyway on older remote servers. Likewise, we don't
* worry about NOWAIT.
However, it's not super clear whether the concern about NOWAIT is just
that the remote server might be too old to know that keyword, or
whether there's some deeper issue.
(I see your example is also falling foul of the non-implementation
of NO KEY, which might be confusing matters still more.)
regards, tom lane