[DocDB][Advisory Locks] Advisory locks queries in active sessions don't fail after database deletion when those queries are already executed before db deletion

[shishir2001-yb]

Jira Link: DB-15866

Description

Version: 2.25.2.0.0-b151

If a database is deleted by a session while other sessions are still connected, all queries from those sessions should fail with the following error:

ERROR: catalog version for database <db_oid> was not found.
HINT: Database might have been dropped by another user

However, this doesn't seem to be the case if an advisory lock query has already been executed in the session. The session appears to continue executing queries without triggering the expected failure.

Session 1                                                     Session 2
                                                               yugabyte=# \c some_db


                                                               some_db=# SELECT pg_advisory_lock(1);

                                                               pg_advisory_lock 
                                                              ------------------
 
                                                              (1 row)


                                                               some_db=# SELECT pg_advisory_unlock(1);
                                                                pg_advisory_unlock 
                                                               --------------------
                                                                t
                                                               (1 row)
                                                              
yugabyte=# drop some_db;

               
                                                              some_db=# SELECT pg_advisory_lock(1); ----------> This query should fail but gets executed

                                                               pg_advisory_lock 
                                                              ------------------
 
                                                              (1 row)


                                                              some_db=# SELECT pg_advisory_unlock(1);----------> This query should fail but gets executed
                                                               pg_advisory_unlock 
                                                              --------------------
                                                               t
                                                              (1 row)     




                                                              some_db=# SELECT pg_advisory_lock_shared(1);-->This query was not executed before drop db hence it doesn't executed and fails
                                                              ERROR:  catalog version for database 16391 was not found.
                                                              HINT:  Database might have been dropped by another user

Issue Type

kind/bug

Warning: Please confirm that this issue does not contain any sensitive information

• I confirm this issue does not contain any sensitive information.