Since this doesn't change the database schema, it can be applied live as a hot patch.
Diff comments: > diff --git a/database/schema/patch-2211-32-0.sql > b/database/schema/patch-2211-32-0.sql > new file mode 100644 > index 0000000..156bf96 > --- /dev/null > +++ b/database/schema/patch-2211-32-0.sql > @@ -0,0 +1,111 @@ > +SET client_min_messages=ERROR; > + > +CREATE OR REPLACE FUNCTION public.activity() RETURNS SETOF pg_stat_activity > + LANGUAGE plpgsql SECURITY DEFINER > + SET search_path TO 'public' > + AS $$ > +DECLARE > + a pg_stat_activity%ROWTYPE; > +BEGIN > + IF EXISTS ( > + SELECT 1 FROM pg_attribute WHERE > + attrelid = > + (SELECT oid FROM pg_class > + WHERE relname = 'pg_stat_activity') > + AND attname = 'leader_pid') THEN The above condition checks for a new field that was added to the `pg_stat_activity` table in PostgreSQL 13 to return the appropriate expected columns in the block below. The current function definition can be seen in https://git.launchpad.net/launchpad/tree/database/schema/launchpad-2211-00-0.sql?id=39bd251485adde1a3ef538479f6c030babb8e251#n385 and this only adds one more if condition and a corresponding block. > + -- >= 13 > + RETURN QUERY SELECT > + datid, datname, pid, leader_pid, usesysid, usename, > + application_name, client_addr, client_hostname, client_port, > + backend_start, xact_start, query_start, state_change, > + wait_event_type, wait_event, state, backend_xid, backend_xmin, > + query_id, backend_type, > + CASE > + WHEN query LIKE '<IDLE>%' > + OR query LIKE 'autovacuum:%' > + THEN query > + ELSE > + '<HIDDEN>' > + END AS query > + FROM pg_catalog.pg_stat_activity; > + ELSIF EXISTS ( > + SELECT 1 FROM pg_attribute WHERE > + attrelid = > + (SELECT oid FROM pg_class > + WHERE relname = 'pg_stat_activity') > + AND attname = 'backend_type') THEN > + -- >= 10 > + RETURN QUERY SELECT > + datid, datname, pid, usesysid, usename, application_name, > + client_addr, client_hostname, client_port, backend_start, > + xact_start, query_start, state_change, wait_event_type, > + wait_event, state, backend_xid, backend_xmin, backend_type, > + CASE > + WHEN query LIKE '<IDLE>%' > + OR query LIKE 'autovacuum:%' > + THEN query > + ELSE > + '<HIDDEN>' > + END AS query > + FROM pg_catalog.pg_stat_activity; > + ELSIF EXISTS ( > + SELECT 1 FROM pg_attribute WHERE > + attrelid = > + (SELECT oid FROM pg_class > + WHERE relname = 'pg_stat_activity') > + AND attname = 'wait_event_type') THEN > + -- >= 9.6 > + RETURN QUERY SELECT > + datid, datname, pid, usesysid, usename, application_name, > + client_addr, client_hostname, client_port, backend_start, > + xact_start, query_start, state_change, wait_event_type, > + wait_event, state, backend_xid, backend_xmin, > + CASE > + WHEN query LIKE '<IDLE>%' > + OR query LIKE 'autovacuum:%' > + THEN query > + ELSE > + '<HIDDEN>' > + END AS query > + FROM pg_catalog.pg_stat_activity; > + ELSIF EXISTS ( > + SELECT 1 FROM pg_attribute WHERE > + attrelid = > + (SELECT oid FROM pg_class > + WHERE relname = 'pg_stat_activity') > + AND attname = 'backend_xid') THEN > + -- >= 9.4 > + RETURN QUERY SELECT > + datid, datname, pid, usesysid, usename, application_name, > + client_addr, client_hostname, client_port, backend_start, > + xact_start, query_start, state_change, waiting, state, > + backend_xid, backend_xmin, > + CASE > + WHEN query LIKE '<IDLE>%' > + OR query LIKE 'autovacuum:%' > + THEN query > + ELSE > + '<HIDDEN>' > + END AS query > + FROM pg_catalog.pg_stat_activity; > + ELSE > + -- >= 9.2; anything older is unsupported > + RETURN QUERY SELECT > + datid, datname, pid, usesysid, usename, application_name, > + client_addr, client_hostname, client_port, backend_start, > + xact_start, query_start, state_change, waiting, state, > + CASE > + WHEN query LIKE '<IDLE>%' > + OR query LIKE 'autovacuum:%' > + THEN query > + ELSE > + '<HIDDEN>' > + END AS query > + FROM pg_catalog.pg_stat_activity; > + END IF; > +END; > +$$; > + > +COMMENT ON FUNCTION public.activity() IS 'SECURITY DEFINER wrapper around > pg_stat_activity allowing unprivileged users to access most of its > information.'; > + > +INSERT INTO LaunchpadDatabaseRevision VALUES (2211, 32, 0); -- https://code.launchpad.net/~lgp171188/launchpad/+git/launchpad/+merge/477791 Your team Launchpad code reviewers is requested to review the proposed merge of ~lgp171188/launchpad:update-public-activity-plsql-function-support-postgres-13-and-higher into launchpad:master. _______________________________________________ Mailing list: https://launchpad.net/~launchpad-reviewers Post to : launchpad-reviewers@lists.launchpad.net Unsubscribe : https://launchpad.net/~launchpad-reviewers More help : https://help.launchpad.net/ListHelp
