Regarding experiencing
The following documentation comment has been logged on the website:
Page: https://www.postgresql.org/docs/17/functions-admin.html
Description:
In this session of
REFERENCE :9.28.10. Advisory Lock Functions
The functions shown in Table 9.106 manage advisory locks. For details about
proper use of these functions, see Section 13.3.5.All these functions are
intended to be used to lock application-defined resources, which can be
identified either by a single 64-bit key value or two 32-bit key values
(note that these two key spaces do not overlap). If another session already
holds a conflicting lock on the same resource identifier, the functions will
either wait until the resource becomes available, or return a false result,
as appropriate for the function. Locks can be either shared or exclusive: a
shared lock does not conflict with other shared locks on the same resource,
only with exclusive locks. Locks can be taken at session level (so that they
are held until released or the session ends) or at transaction level (so
that they are held until the current transaction ends; there is no provision
for manual release).
ISSUE : According to the above paragraph , locks should either be shared or
exclusive. But when I tried assigning a shared lock and exclusive lock
without unlock of the shared lock , it doesn't show a conflict. I think this
may contradict what is said in above paragraph of documentation. Can you
enlighten me more on this , regarding the understanding if my assumption is
wrong. I could have attached snaps of the same but I don't find an
attachment option.
Hello
On 2025-Feb-11, PG Doc comments form wrote:
ISSUE : According to the above paragraph , locks should either be shared or
exclusive. But when I tried assigning a shared lock and exclusive lock
without unlock of the shared lock , it doesn't show a conflict. I think this
may contradict what is said in above paragraph of documentation. Can you
enlighten me more on this , regarding the understanding if my assumption is
wrong.
Your understanding is correct -- there should be a conflict. The lack
of one suggests that you're using the lock interfaces incorrectly.
Please show the exact commands you're running. Maybe there's something
about the documentation text that needs to be made clearer.
I could have attached snaps of the same but I don't find an
attachment option.
True. You can reply to this email with attachments. However, I would
instead suggest to copy and paste text from terminal window(s), rather
than screenshots.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
These are the commands I have run in the terminal
psql (17.0)
Type "help" for help.
postgres=# SELECT pg_advisory_lock_shared(1001); //initialising the shared
lock
pg_advisory_lock_shared
-------------------------
(1 row)
postgres=# SELECT pg_advisory_lock(1001); //exclusive lock
pg_advisory_lock
------------------
(1 row)....
now i have logged in session 2 to check whether both locks are granted
without conflict, heres the output :
postgres=# SELECT * FROM pg_locks WHERE locktype = 'advisory';
locktype | database | relation | page | tuple | virtualxid | transactionid
| classid | objid | objsubid | virtualtransaction | pid | mode |
granted | fastpath | waitstart
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+------+---------------+---------+----------+-----------
advisory | 5 | | | | |
| 0 | 1001 | 1 | 3/0 | 7126 | ShareLock |
t | f |
advisory | 5 | | | | |
| 0 | 1001 | 1 | 3/0 | 7126 | ExclusiveLock |
t | f |
(2 rows)
UNDERSTANDING : The same resource ( oid and pid) are allocate to both
exclusive and shared lock . I am also attaching the screenshots.
On Tue, 11 Feb 2025 at 14:01, Álvaro Herrera <alvherre@alvh.no-ip.org>
wrote:
Show quoted text
Hello
On 2025-Feb-11, PG Doc comments form wrote:
ISSUE : According to the above paragraph , locks should either be shared
or
exclusive. But when I tried assigning a shared lock and exclusive lock
without unlock of the shared lock , it doesn't show a conflict. I thinkthis
may contradict what is said in above paragraph of documentation. Can you
enlighten me more on this , regarding the understanding if my assumptionis
wrong.
Your understanding is correct -- there should be a conflict. The lack
of one suggests that you're using the lock interfaces incorrectly.
Please show the exact commands you're running. Maybe there's something
about the documentation text that needs to be made clearer.I could have attached snaps of the same but I don't find an
attachment option.True. You can reply to this email with attachments. However, I would
instead suggest to copy and paste text from terminal window(s), rather
than screenshots.--
Álvaro Herrera PostgreSQL Developer —
https://www.EnterpriseDB.com/
On 2025-Feb-11, Blessy Thomas wrote:
These are the commands I have run in the terminal
psql (17.0)
Type "help" for help.postgres=# SELECT pg_advisory_lock_shared(1001); //initialising the shared
lock
pg_advisory_lock_shared
-------------------------(1 row)
postgres=# SELECT pg_advisory_lock(1001); //exclusive lock
pg_advisory_lock
------------------(1 row)
Ah yes, there's no conflict in this case because the holder of both
locks is the same session. You'd have to request the exclusive lock in
another psql session and you should see it block.
--
Álvaro Herrera PostgreSQL Developer — https://www.EnterpriseDB.com/
Thank you for the clarification. Now this explains the scenario.
Regards
Blessy Thomas
On Tue, 11 Feb 2025 at 15:56, Álvaro Herrera <alvherre@alvh.no-ip.org>
wrote:
Show quoted text
On 2025-Feb-11, Blessy Thomas wrote:
These are the commands I have run in the terminal
psql (17.0)
Type "help" for help.postgres=# SELECT pg_advisory_lock_shared(1001); //initialising the
shared
lock
pg_advisory_lock_shared
-------------------------(1 row)
postgres=# SELECT pg_advisory_lock(1001); //exclusive lock
pg_advisory_lock
------------------(1 row)
Ah yes, there's no conflict in this case because the holder of both
locks is the same session. You'd have to request the exclusive lock in
another psql session and you should see it block.--
Álvaro Herrera PostgreSQL Developer —
https://www.EnterpriseDB.com/