launchpad-reviewers team mailing list archive

[Guruprasad <mp+477791@xxxxxxxxxxxxxxxxxx>]

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.