CODEWITHSUNDEEP
postgresql
apatniv
apatniv

Reputation: 1856

Advisory locks scope in postgresql

I am new to postgresql and trying to understand advisory locks. I have the following two scenarios:

  1. With different databases in two different sessions: (Works in expected manner)

    Session 1: SELECT pg_advisory_lock(1); Successfully acquires the lock

    Session 2 (note in different database): SELECT pg_advisory_lock(1); Successfully acquires the lock

  2. With Different Schemas in same database: When I do the same operation, the second 'session' blocks.

It appears that advisory locks operate at database level rather than (database and schema) combination. Is my assumption correct or is there anything I am missing?

Upvotes: 1

Views: 1818

Answers (1)

Vao Tsun
Vao Tsun

Reputation: 51519

In postgres schema is a namespace. More than just a prefix, but less than another database. in your case two, second session not "blocks", but rather is waiting as per docs:

If another session already holds a lock on the same resource identifier, this function will wait until the resource becomes available.

Regarding successful locking on different databases: After you run SELECT pg_advisory_lock(1); checkout pg_locks, column objid

OID of the lock target within its system catalog, or null if the target is not a general database object

So this number is per database - you can reference same 1 for many databases - those will be different OIDs.

Upvotes: 2

Related Questions