(unnamed)

text/plain

Download

-- cannot be a temporary view because other sessions must see it
DROP VIEW IF EXISTS lockview CASCADE;

CREATE VIEW lockview AS
SELECT	pid, virtualtransaction AS vxid, locktype AS lock_type, 
	mode AS lock_mode, granted,
	CASE
		WHEN virtualxid IS NOT NULL AND transactionid IS NOT NULL
		THEN	virtualxid || ' ' || transactionid
		WHEN virtualxid::text IS NOT NULL
		THEN	virtualxid
		ELSE	transactionid::text
	END AS xid_lock, relname,
	page, tuple, classid, objid, objsubid
FROM	pg_locks LEFT OUTER JOIN pg_class ON (pg_locks.relation = pg_class.oid)
WHERE	-- do not show our view's locks
     	pid != pg_backend_pid() AND
	-- no need to show self-vxid locks
	virtualtransaction IS DISTINCT FROM virtualxid
-- granted is ordered earlier
ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7;

DROP VIEW IF EXISTS lockview1 CASCADE;

CREATE VIEW lockview1 AS
SELECT	pid, vxid, lock_type, lock_mode, granted, xid_lock, relname
FROM	lockview
-- granted is ordered earlier
ORDER BY 1, 2, 5 DESC, 6, 3, 4, 7;

DROP VIEW IF EXISTS lockview2 CASCADE;

CREATE VIEW lockview2 AS
SELECT	pid, vxid, lock_type, page, tuple, classid, objid, objsubid
FROM	lockview
-- granted is first
-- add non-display columns to match ordering of lockview
ORDER BY 1, 2, granted DESC, vxid, xid_lock::text, 3, 4, 5, 6, 7, 8;

DROP TABLE IF EXISTS lockdemo;
CREATE TABLE lockdemo (col int);
INSERT INTO lockdemo VALUES (1);
-- do two UPDATEs to cause a wait

CREATE VIEW lockinfo_hierarchy AS
	WITH RECURSIVE lockinfo1 AS (
		SELECT '1', pid, vxid, granted, xid_lock, lock_type, relname, page, tuple
		FROM lockview
		WHERE xid_lock IS NOT NULL AND
		      relname IS NULL AND
		      granted
		UNION ALL
		SELECT '2', lockview.pid, lockview.vxid, lockview.granted, lockview.xid_lock, 
			lockview.lock_type, lockview.relname, lockview.page, lockview.tuple
		FROM lockinfo1 JOIN lockview ON (lockinfo1.xid_lock = lockview.xid_lock)
		WHERE lockview.xid_lock IS NOT NULL AND
		      lockview.relname IS NULL AND
		      NOT lockview.granted AND
		      lockinfo1.granted),
	lockinfo2 AS (
		SELECT '3', pid, vxid, granted, xid_lock, lock_type, relname, page, tuple
		FROM lockview
		WHERE lock_type = 'tuple' AND
		      granted
		UNION ALL
		SELECT '4', lockview.pid, lockview.vxid, lockview.granted, lockview.xid_lock,
			lockview.lock_type, lockview.relname, lockview.page, lockview.tuple
		FROM lockinfo2 JOIN lockview ON (
			lockinfo2.lock_type = lockview.lock_type AND
			lockinfo2.relname = lockview.relname AND
			lockinfo2.page = lockview.page AND
			lockinfo2.tuple = lockview.tuple)
		WHERE lockview.lock_type = 'tuple' AND
		      NOT lockview.granted AND
		      lockinfo2.granted
	)
	SELECT * FROM lockinfo1
	UNION ALL
	SELECT * FROM lockinfo2;

-- try several updates
BEGIN WORK;
SELECT ctid, xmin, * FROM lockdemo;
UPDATE lockdemo SET col = 4;
SELECT ctid, xmin, * FROM lockdemo;
SELECT pg_backend_pid();
SELECT txid_current();
\! psql -e -c 'BEGIN WORK; UPDATE lockdemo SET col = 5; SELECT pg_sleep(0.300); COMMIT;' | sed 's/^/\t/g' &
\! psql -e -c 'BEGIN WORK; UPDATE lockdemo SET col = 6; SELECT pg_sleep(0.300); COMMIT;' | sed 's/^/\t/g' &
\! psql -e -c 'BEGIN WORK; UPDATE lockdemo SET col = 7; SELECT pg_sleep(0.300); COMMIT;' | sed 's/^/\t/g' &
SELECT pg_sleep(0.100);
\! psql -e -c 'SELECT * FROM lockview1;' | sed 's/^/\t/g'
\! psql -e -c 'SELECT * FROM lockview2;' | sed 's/^/\t/g'
\! psql -e -c 'SELECT * FROM lockinfo_hierarchy;' | sed 's/^/\t/g'
\! psql -e -c 'SELECT * FROM pg_locks;' | sed 's/^/\t/g'
COMMIT;
\! psql -e -c 'SELECT * FROM lockview1;' | sed 's/^/\t/g'
\! psql -e -c 'SELECT * FROM lockview2;' | sed 's/^/\t/g'
SELECT pg_sleep(0.300);

DELETE FROM lockdemo;
INSERT INTO lockdemo VALUES (1);