[DocDB] [Table locks] Observing regression in pg_locks query validation inside DO block query

[hariharasudhan-yb]

Jira Link: DB-18372

Description

Version - 2.27.0.0-b588

Observing some regression in pg_locks . Looks like recent change impacted here ? Please take a look
After b70 it started failing ;

We have this do block query which used to run in one of the Table level locks tests got failed ;
Test run - Run
Logs - https://jenkins.dev.yugabyte.com/job/itest-system/5866/artifact/logs/2.27.0.0_testtablele[…]ransactionalddlnontransactional-aws-rf3_20250922_130521/

Repro :
create table sam (i int);

DO $$
	       DECLARE
	           result RECORD;
	           found BOOLEAN := FALSE;
	           lock_found BOOLEAN := FALSE;
	       BEGIN
	           -- Execute DDL in nontxn_db_1
	           ALTER TABLE sam ALTER COLUMN i TYPE bigint;
	           RAISE NOTICE 'DDL EXECUTED';
	
	           -- Run verification query
	           FOR result IN
	               SELECT data_type FROM information_schema.columns
	               WHERE table_name='sam' AND column_name='i'
	               
	           LOOP
	               RAISE NOTICE '%', result.data_type;
	               found := TRUE;
	           END LOOP;
	
	           IF NOT found THEN
	               RAISE NOTICE '%', 'None';
	           END IF;
	
	           
	           -- Lock verification query
	           FOR result IN
	               SELECT
	                   c.relname AS table_name,
	                   l.mode,
	                   l.granted
	               FROM pg_locks l
	               JOIN pg_class c ON l.relation = c.oid
	               WHERE c.relname = 'sam'
	               AND l.mode = 'AccessExclusiveLock'
	               AND l.granted = true
	           LOOP
	               RAISE NOTICE 'Lock mode: % relation_name: %', result.mode, result.table_name;
	               lock_found := TRUE;
	           END LOOP;
	
	           IF NOT lock_found THEN
	               RAISE NOTICE 'Lock mode: % relation_name: %', 'None', 'None';
	           END IF;
	       
	
	           -- Perform Advisory lock
	           PERFORM pg_advisory_xact_lock(1);
	       END;
	       $$;

Output in latest master :

NOTICE:  DDL EXECUTED
NOTICE:  bigint
NOTICE:  Lock mode: None relation_name: None
DO

but it should output like

NOTICE:  DDL EXECUTED
NOTICE:  bigint
NOTICE:  Lock mode: AccessExclusiveLock relation_name: sam
DO

Basically, this entire DO block runs under a single transaction; hence, it should provide the lock details here, which is the expected behavior, I believe.

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.