Locking and postgres_fdw extension

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
2 messages Options
Reply | Threaded
Open this post in threaded view
|

Locking and postgres_fdw extension

Steve Baldwin
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
Reply | Threaded
Open this post in threaded view
|

Re: Locking and postgres_fdw extension

Tom Lane-2
Steve Baldwin <[hidden email]> 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