Фантастические advisory locks, и где они обитают

[Tangeman]

… корректно завершив соединение с клиентской стороны, можно избавиться от всех наложенных им блокировок.

На самом деле любое завершение соединения (в том числе некорректное) приводит к неявному вызову pg_advisory_unlock_all:

pg_advisory_unlock_all освобождает все блокировки на уровне сеанса, закреплённые за текущим сеансом. (Эта функция неявно вызывается в конце любого сеанса, даже при нештатном отключении клиента.)

[Kilor]

Да, но завершение соединения снимает не только advisory. :)

[qideil]

Применительно к

… чтобы работать на этих данных мог всего лишь один процесс одновременно.

Как решаете проблему с перезагрузкой сервера после получения лока? Насколько я знаю, advisory locks не восстанавливаются после рестарта.

[Kilor]

Вообще-то, pg_locks — это структура in-memory, поэтому после рестарта не восстанавливаются вообще никакие блокировки. Но их можно восстановить со стороны БЛ, которые их сначала захватили, а потом обнаружили разрыв коннекта до сервера.

[Tatikoma]

Касаемо pgbouncer — я для себя решил поднять отдельный postgresql сервер для этого. Пока только решил, но не поднял. По идее подключив нужные таблицы через postgres_fdw всё будет вполне прозрачно.

[Kilor]

В смысле отдельный сервер-хранилище, а остальные экземпляры PG к нему по FDW ходят?

[vektory79]

И как быть с отказоустойчивостью? pgbouncer обычно не от хорошей жизни ставят, а чтобы надёжность повысить.

[Tatikoma]

pgbouncer ставят чтобы повысить производительность.

[Kilor]

Еще его ставят, чтобы периодически соединение с БД закрывалось, и освобождалась память процесса, занятая под metainfo.

[RinNas]

к pg_try_advisory_lock('queue_table'::regclass::oid нужно дописать ::int, иначе будет ошибка

[abdulgalimov]

SELECT
  *
FROM
  queue_table
WHERE
  pg_try_advisory_lock('queue_table'::regclass::oid::int, pk_id)
ORDER BY
  pk_id
LIMIT 1;

такая конструкция берет из таблицы одну строку, но блокирует все. Почему так?

[Kilor]

Как правило, причина одна - нет полностью соответствующего индекса, и запрос уходит в Bitmap Heap Scan или Seq Scan.

1. нужен индекс

2. SET enable_seqscan TO off; SET enable_bitmapscan TO off;

[abdulgalimov]

так id - это primary key с индексом.

[Kilor]

А вот тогда уже надо смотреть EXPLAIN ANALYZE.

[RekGRpth]

дак у вас условие ГДЕ сначала блокирует все строки, а потом уже по ЛИМИТу возвращается одна из всех

[ris58h]

Тоже не понял что за странный запрос автором представлен. Как это потом разблокировать всё?

[Kilor]

Такая конструкция выполняет WHERE-условие до первой найденной (т.е. на которую смогла-таки наложиться блокировка) записи, точнее, ее ID.

[ris58h]

Объясните пожалуйста как это работает. Where выполняется до limit и order by, насколько мне известно.

Upd: похоже расчёт на оптимизацию по limit 1. Хитро.

[Kilor]

LIMIT 1 + обязательное наличие индекса

[RekGRpth]

Можно ещё так попробовать

with s as (select * from queue_table order by pk_id limit 1 for update of queue_table skip locked) update queue_table set ... returning ...

[ris58h]

Если приложение переиспользует соединения к БД (connection pool), то возможна ситуация, когда несколько неких worker-ов получат доступ к одной и той же блокировке?

[Kilor]

Если речь про pgbouncer, то в session mode - нет, каждому клиентскому подключению будет выдаваться свое серверное на все время. Если не забывать разблокировать свое - проблем не будет. В transaction mode - запросто при не-xact-функциях.

[ris58h]

Речь про connection pool на стороне приложения. Предположим, что таски из очереди из примера обрабатывает одно приложение в несколько тредов. Причём количество обработчиков больше размера пула.

[Kilor]

Зависит от реализации. Если все команды льются просто вперемешку в одно подключение, то блокировки будут самой мелкой из проблем, если запросы хоть чуть сложнее атомарных SELECT.