Inconsistent behaviour calling pg_try_advisory_xact_lock with sub-query and when JOIN'ing

Поиск
Список
Период
Сортировка
От Andreas Joseph Krogh
Тема Inconsistent behaviour calling pg_try_advisory_xact_lock with sub-query and when JOIN'ing
Дата
Msg-id VisenaEmail.6.7024d6d4139fd6c4.14f0932cb5d@tc7-visena
обсуждение исходный текст
Ответы Re: Inconsistent behaviour calling pg_try_advisory_xact_lock with sub-query and when JOIN'ing
Список pgsql-general
Hi all.
 
The following query returns and locks 1 row as expected (only one row in pg_locks with locktype='advisory' and objid=sequence_id):
 
begin;
select qe.entity_id, qe.version, qe.queue_id, qe.sequence_id, qe.tx_id
from origo_queue_entry qe
WHERE
    qe.queue_id = (SELECT q.entity_id FROM origo_queue q WHERE q.name = 'EMAIL_IMPORT_STORE')    AND pg_try_advisory_xact_lock(sequence_id)
ORDER BY qe.sequence_id ASC
LIMIT 1 FOR UPDATE
;
 
But when JOIN'ing with origo_queue instead of using a sub-query:
 
begin;
select qe.entity_id, qe.version, qe.queue_id, qe.sequence_id, qe.tx_id
from origo_queue_entry qe JOIN origo_queue q ON q.entity_id = qe.queue_id
WHERE
    q.name = 'EMAIL_IMPORT_STORE'
    AND pg_try_advisory_xact_lock(sequence_id)
ORDER BY qe.sequence_id ASC
LIMIT 1 FOR UPDATE
;
 
it returns 1 row, but locks all of them; pg_locks is now full af advisory-locks for all "sequence_id" in origo_queue_entry
 
Is this by design?
 
Thanks.
 
--
Andreas Joseph Krogh
CTO / Partner - Visena AS
Mobile: +47 909 56 963
Вложения

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

Предыдущее
От: Alvaro Herrera
Дата:
Сообщение: Re: pg_start_backup: file has vanished from pg_subtrans/
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Inconsistent behaviour calling pg_try_advisory_xact_lock with sub-query and when JOIN'ing