Skip to content

Instantly share code, notes, and snippets.

@ivaliullinbars

ivaliullinbars/get_postgresql_full_query_via_gdb

Created March 20, 2019 09:20
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save ivaliullinbars/b343b9393cc951869df5c36ab3698dde to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save ivaliullinbars/b343b9393cc951869df5c36ab3698dde to your computer and use it in GitHub Desktop.
Download ZIP
PostgreSQL queries
Raw
get_postgresql_full_query_via_gdb
gdb postgres PID
printf “%s\n”, debug_query_string
printf "%s\n", debug_query_string
Raw
grant_select_from_pg_stat_activity_for_ro_user.sql
CREATE OR REPLACE FUNCTION public.pg_stat_activity() RETURNS SETOF pg_catalog.pg_stat_activity
AS $BODY$
DECLARE
rec RECORD;
BEGIN
-- Author: Tony Wasson (part of nagiosplugins for postgresql)
-- Overview: Let non super users see query details from pg_stat_activity
-- Revisions: (when, who, what)
-- 2006-08-29 TW - Checked into CVS after a user request.
FOR rec IN SELECT * FROM pg_stat_activity
LOOP
RETURN NEXT rec;
END LOOP;
RETURN;
END;
$BODY$ LANGUAGE plpgsql SECURITY DEFINER;
CREATE VIEW public.pg_stat_activity AS SELECT * FROM public.pg_stat_activity();
GRANT SELECT ON TABLE public.pg_stat_activity TO ro_user;
Raw
select_create_databases_date.sql
-- work only default tablespace
SELECT (pg_stat_file('base/'||oid ||'/PG_VERSION')).modification, datname FROM pg_database;
Raw
select_locks_tree.sql
-- COPY (
WITH RECURSIVE
c(requested, CURRENT) AS
( VALUES
('AccessShareLock'::text, 'AccessExclusiveLock'::text),
('RowShareLock'::text, 'ExclusiveLock'::text),
('RowShareLock'::text, 'AccessExclusiveLock'::text),
('RowExclusiveLock'::text, 'ShareLock'::text),
('RowExclusiveLock'::text, 'ShareRowExclusiveLock'::text),
('RowExclusiveLock'::text, 'ExclusiveLock'::text),
('RowExclusiveLock'::text, 'AccessExclusiveLock'::text),
('ShareUpdateExclusiveLock'::text, 'ShareUpdateExclusiveLock'::text),
('ShareUpdateExclusiveLock'::text, 'ShareLock'::text),
('ShareUpdateExclusiveLock'::text, 'ShareRowExclusiveLock'::text),
('ShareUpdateExclusiveLock'::text, 'ExclusiveLock'::text),
('ShareUpdateExclusiveLock'::text, 'AccessExclusiveLock'::text),
('ShareLock'::text, 'RowExclusiveLock'::text),
('ShareLock'::text, 'ShareUpdateExclusiveLock'::text),
('ShareLock'::text, 'ShareRowExclusiveLock'::text),
('ShareLock'::text, 'ExclusiveLock'::text),
('ShareLock'::text, 'AccessExclusiveLock'::text),
('ShareRowExclusiveLock'::text, 'RowExclusiveLock'::text),
('ShareRowExclusiveLock'::text, 'ShareUpdateExclusiveLock'::text),
('ShareRowExclusiveLock'::text, 'ShareLock'::text),
('ShareRowExclusiveLock'::text, 'ShareRowExclusiveLock'::text),
('ShareRowExclusiveLock'::text, 'ExclusiveLock'::text),
('ShareRowExclusiveLock'::text, 'AccessExclusiveLock'::text),
('ExclusiveLock'::text, 'RowShareLock'::text),
('ExclusiveLock'::text, 'RowExclusiveLock'::text),
('ExclusiveLock'::text, 'ShareUpdateExclusiveLock'::text),
('ExclusiveLock'::text, 'ShareLock'::text),
('ExclusiveLock'::text, 'ShareRowExclusiveLock'::text),
('ExclusiveLock'::text, 'ExclusiveLock'::text),
('ExclusiveLock'::text, 'AccessExclusiveLock'::text),
('AccessExclusiveLock'::text, 'AccessShareLock'::text),
('AccessExclusiveLock'::text, 'RowShareLock'::text),
('AccessExclusiveLock'::text, 'RowExclusiveLock'::text),
('AccessExclusiveLock'::text, 'ShareUpdateExclusiveLock'::text),
('AccessExclusiveLock'::text, 'ShareLock'::text),
('AccessExclusiveLock'::text, 'ShareRowExclusiveLock'::text),
('AccessExclusiveLock'::text, 'ExclusiveLock'::text),
('AccessExclusiveLock'::text, 'AccessExclusiveLock'::text)
),
l AS
(
SELECT
(locktype,DATABASE,relation::regclass::text,page,tuple,virtualxid,transactionid,classid,objid,objsubid) AS target,
virtualtransaction,
pid,
mode,
GRANTED
FROM pg_catalog.pg_locks
),
t AS
(
SELECT
blocker.target AS blocker_target,
blocker.pid AS blocker_pid,
blocker.mode AS blocker_mode,
blocked.target AS target,
blocked.pid AS pid,
blocked.mode AS mode
FROM l blocker
JOIN l blocked
ON ( NOT blocked.GRANTED
AND blocker.GRANTED
AND blocked.pid != blocker.pid
AND blocked.target IS NOT DISTINCT FROM blocker.target)
JOIN c ON (c.requested = blocked.mode AND c.CURRENT = blocker.mode)
),
r AS
(
SELECT
blocker_target,
blocker_pid,
blocker_mode,
'1'::INT AS depth,
target,
pid,
mode,
blocker_pid::text || ' -> ' || pid::text AS seq
FROM t
UNION ALL
SELECT
blocker.blocker_target,
blocker.blocker_pid,
blocker.blocker_mode,
blocker.depth + 1,
blocked.target,
blocked.pid,
blocked.mode,
blocker.seq || ' -> ' || blocked.pid::text
FROM r blocker
JOIN t blocked
ON (blocked.blocker_pid = blocker.pid)
WHERE blocker.depth < 1000
)
SELECT
-- r.blocker_target,
-- r.blocker_pid,
-- r.blocker_mode,
-- r.depth,
-- r.target,
-- r.mode,
-- r.seq,
-- blocking.query AS blocker_query,
-- blocked.query AS blocked_query
now() AS time_now,
r.seq,
-- r.depth,
-- r.pid AS blocked_pid,
r.blocker_mode AS blocker_mode,
r.mode AS blocked_mode,
blocking.backend_start AS blocker_transaction_start,
blocked.backend_start AS blocked_transaction_start,
blocking.client_addr AS blocker_client_addr,
blocked.client_addr AS blocked_client_addr,
blocking.query AS blocker_query,
blocked.query AS blocked_query
FROM r
INNER JOIN pg_stat_activity AS blocking
ON (r.blocker_pid=blocking.pid)
INNER JOIN pg_stat_activity AS blocked
ON (r.pid=blocked.pid)
ORDER BY blocker_transaction_start
-- ) TO PROGRAM 'cat >> /var/lib/pgsql/locks.csv' WITH CSV DELIMITER ';' HEADER
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment