Обсуждение: Locking and postgres_fdw extension

Поиск
Список
Период
Сортировка

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

Re: Locking and postgres_fdw extension

От
Tom Lane
Дата:
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