Summary:
This change ensures that the `classid`, `objid`, and `objsubid` fields in `pg_locks` are correctly populated for advisory locks. Also, updates the `mode` field. Previously, the `mode` field was displayed as `[STRONG_READ]` or `[STRONG_READ, STRONG_WRITE]` for advisory lock. Now, `[STRONG_READ]` is replaced with `ShareLock` and `[STRONG_READ, STRONG_WRITE]` is replaced with `ExclusiveLock` for advisory lock.
Sample Output:
```
yugabyte=# SELECT pg_advisory_lock(1);
pg_advisory_lock
------------------
(1 row)
yugabyte=# SELECT pg_advisory_lock_shared(2, 2);
pg_advisory_lock_shared
-------------------------
(1 row)
yugabyte=# select * from pg_locks;
locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction | pid | mode | granted | fastpath | waitstart | waitend | ybdetails
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-----+---------------+---------+----------+-----------+-------------------------------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
advisory | 13515 | | | | | | 0 | 1 | 1 | | | ExclusiveLock | t | f | | 2025-02-20 17:08:48.848268+00 | {"node": "3ed9c63599c8495bbadf5a13fddc681d", "tablet_id": "2fe0732fe0b34958bdce0e6e2ee34297", "blocked_by": null, "is_explicit": true, "transactionid": "8a1f337e-9ecd-4b56-81f5-ef00b1653f22", "keyrangedetails": {"cols": ["13515", "0", "1", "1"], "attnum": null, "column_id": null, "multiple_rows_locked": false}, "subtransaction_id": 1}
advisory | 13515 | | | | | | 2 | 2 | 2 | | | ShareLock | t | f | | 2025-02-20 17:08:54.310935+00 | {"node": "3ed9c63599c8495bbadf5a13fddc681d", "tablet_id": "2fe0732fe0b34958bdce0e6e2ee34297", "blocked_by": null, "is_explicit": true, "transactionid": "8a1f337e-9ecd-4b56-81f5-ef00b1653f22", "keyrangedetails": {"cols": ["13515", "2", "2", "2"], "attnum": null, "column_id": null, "multiple_rows_locked": false}, "subtransaction_id": 1}
(2 rows)
```
Upgrade safety:
Introduced a migration file to update `yb_lock_status` and `pg_locks` view, adding the new columns (classid, objid, objsubid).
Added `yb_pg_locks_integrate_advisory_locks` auto flag to ensure proper functionality of `pg_locks` during upgrade
Note that If a connection starts a transaction during the monitor phase (after new code is used and before the YSQL upgrade), executing pg_locks within that transaction after the upgrade will fail. Here are the steps leading to failure:
1. The TServer restarts with the new code.
2. A PostgreSQL connection is established, and a transaction begins.
3. The YSQL upgrade executes the migration file, updating yb_lock_status in the PG catalog.
4. Running `SELECT * FROM pg_locks;` within the ongoing transaction will fail.
This happens because the ongoing transaction prevents the connection from refreshing its catalog cache. Since pg_locks relies on an updated catalog, using a stale cache causes it to fail, aborting the transaction. Applications should handle this scenario appropriately.
Other changes:
Ported pg regress test for advisory locks.
Jira: DB-13788
Test Plan:
./yb_build.sh --java-test 'org.yb.pgsql.TestPgRegressAdvisoryLock#testPgRegressAdvisoryLock'
./yb_build.sh --cxx-test integration-tests_pg_locks_v76_upgrade-test --gtest_filter PgLocksV76UpgradeTest.TestPgLocksViewAdvisoryLocksSupport
Reviewers: bkolagani, jason, pjain, hsunder, rthallam, myang
Reviewed By: hsunder, rthallam, myang
Subscribers: ybase, slingam, rthallam, yql
Differential Revision: https://phorge.dev.yugabyte.com/D42047
