From 4632aa09fe82d80e378123ca46fdf8aecdda795f Mon Sep 17 00:00:00 2001 From: Oskari Saarenmaa Date: Mon, 14 Mar 2016 18:34:24 +0200 Subject: [PATCH] pg_stat_activity: display backends for dropped roles --- src/backend/catalog/system_views.sql | 13 ++++++------- src/test/regress/expected/rules.out | 14 ++++++-------- 2 files changed, 12 insertions(+), 15 deletions(-) diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql index 84aa061..e0b583e 100644 --- a/src/backend/catalog/system_views.sql +++ b/src/backend/catalog/system_views.sql @@ -642,9 +642,9 @@ CREATE VIEW pg_stat_activity AS S.backend_xid, s.backend_xmin, S.query - FROM pg_database D, pg_stat_get_activity(NULL) AS S, pg_authid U - WHERE S.datid = D.oid AND - S.usesysid = U.oid; + FROM pg_stat_get_activity(NULL) AS S + JOIN pg_database AS D ON (S.datid = D.oid) + LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid); CREATE VIEW pg_stat_replication AS SELECT @@ -664,10 +664,9 @@ CREATE VIEW pg_stat_replication AS W.replay_location, W.sync_priority, W.sync_state - FROM pg_stat_get_activity(NULL) AS S, pg_authid U, - pg_stat_get_wal_senders() AS W - WHERE S.usesysid = U.oid AND - S.pid = W.pid; + FROM pg_stat_get_activity(NULL) AS S + JOIN pg_stat_get_wal_senders() AS W ON (S.pid = W.pid) + LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid); CREATE VIEW pg_stat_wal_receiver AS SELECT diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out index 22ea06c..54d7a7b 100644 --- a/src/test/regress/expected/rules.out +++ b/src/test/regress/expected/rules.out @@ -1656,10 +1656,9 @@ pg_stat_activity| SELECT s.datid, s.backend_xid, s.backend_xmin, s.query - FROM pg_database d, - pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn), - pg_authid u - WHERE ((s.datid = d.oid) AND (s.usesysid = u.oid)); + FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn) + JOIN pg_database d ON ((s.datid = d.oid))) + LEFT JOIN pg_authid u ON ((s.usesysid = u.oid))); pg_stat_all_indexes| SELECT c.oid AS relid, i.oid AS indexrelid, n.nspname AS schemaname, @@ -1763,10 +1762,9 @@ pg_stat_replication| SELECT s.pid, w.replay_location, w.sync_priority, w.sync_state - FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn), - pg_authid u, - pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) - WHERE ((s.usesysid = u.oid) AND (s.pid = w.pid)); + FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, ssl, sslversion, sslcipher, sslbits, sslcompression, sslclientdn) + JOIN pg_stat_get_wal_senders() w(pid, state, sent_location, write_location, flush_location, replay_location, sync_priority, sync_state) ON ((s.pid = w.pid))) + LEFT JOIN pg_authid u ON ((s.usesysid = u.oid))); pg_stat_ssl| SELECT s.pid, s.ssl, s.sslversion AS version, -- 2.5.0