Locking and postgres_fdw extension

Поиск
Список
Период
Сортировка
От Steve Baldwin
Тема Locking and postgres_fdw extension
Дата
Msg-id CAKE1AiaRGUUB11quexXcjRL45bmkCiNzsevF-TSmKDB+tQgHNw@mail.gmail.com
обсуждение исходный текст
Ответы Re: Locking and postgres_fdw extension  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
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

В списке pgsql-general по дате отправления:

Предыдущее
От: Asya Nevra Buyuksoy
Дата:
Сообщение: Re: Upgrade 9.4 to 12 on windows system
Следующее
От: Yi Sun
Дата:
Сообщение: Re: received immediate shutdown request caused cluster failover