Launchpad itself

Merge lp:~wgrant/launchpad/postgresql-10-db into lp:launchpad

  1. postgresql-10-db
  2. Merge into devel
Proposed by William Grant
Status: Merged
Merged at revision: 18660
Proposed branch: lp:~wgrant/launchpad/postgresql-10-db
Merge into: lp:launchpad
Prerequisite: lp:~wgrant/launchpad/no-tsearch2
Diff against target: 98 lines (+94/-0)
1 file modified
database/schema/patch-2209-84-1.sql (+94/-0)
To merge this branch: bzr merge lp:~wgrant/launchpad/postgresql-10-db
Reviewer Review Type Date Requested Status
Colin Watson (community) db Approve
Review via email: mp+345516@code.launchpad.net

Commit message

Update activity() for PostgreSQL 10 support, in addition to 9.6, 9.5 and 9.3.

Description of the change

Update activity() for PostgreSQL 10 support, in addition to 9.6, 9.5 and 9.3.

To post a comment you must log in.
Revision history for this message
Colin Watson (cjwatson) :
review: Approve (db)

Preview Diff

[H/L] Next/Prev Comment, [J/K] Next/Prev File, [N/P] Next/Prev Hunk
1=== added file 'database/schema/patch-2209-84-1.sql'
2--- database/schema/patch-2209-84-1.sql 1970-01-01 00:00:00 +0000
3+++ database/schema/patch-2209-84-1.sql 2018-05-14 11:26:32 +0000
4@@ -0,0 +1,94 @@
5+-- Copyright 2018 Canonical Ltd. This software is licensed under the
6+-- GNU Affero General Public License version 3 (see the file LICENSE).
7+
8+SET client_min_messages = ERROR;
9+
10+-- Update functions for PostgreSQL 10 support, in addition to 9.6, 9.5 and 9.3.
11+
12+-- From 2209-81-0
13+CREATE OR REPLACE FUNCTION activity()
14+RETURNS SETOF pg_stat_activity
15+VOLATILE SECURITY DEFINER SET search_path = public
16+LANGUAGE plpgsql AS $$
17+DECLARE
18+ a pg_stat_activity%ROWTYPE;
19+BEGIN
20+ IF EXISTS (
21+ SELECT 1 FROM pg_attribute WHERE
22+ attrelid =
23+ (SELECT oid FROM pg_class
24+ WHERE relname = 'pg_stat_activity')
25+ AND attname = 'backend_type') THEN
26+ -- >= 10
27+ RETURN QUERY SELECT
28+ datid, datname, pid, usesysid, usename, application_name,
29+ client_addr, client_hostname, client_port, backend_start,
30+ xact_start, query_start, state_change, wait_event_type,
31+ wait_event, state, backend_xid, backend_xmin, backend_type,
32+ CASE
33+ WHEN query LIKE '<IDLE>%'
34+ OR query LIKE 'autovacuum:%'
35+ THEN query
36+ ELSE
37+ '<HIDDEN>'
38+ END AS query
39+ FROM pg_catalog.pg_stat_activity;
40+ ELSIF EXISTS (
41+ SELECT 1 FROM pg_attribute WHERE
42+ attrelid =
43+ (SELECT oid FROM pg_class
44+ WHERE relname = 'pg_stat_activity')
45+ AND attname = 'wait_event_type') THEN
46+ -- >= 9.6
47+ RETURN QUERY SELECT
48+ datid, datname, pid, usesysid, usename, application_name,
49+ client_addr, client_hostname, client_port, backend_start,
50+ xact_start, query_start, state_change, wait_event_type,
51+ wait_event, state, backend_xid, backend_xmin,
52+ CASE
53+ WHEN query LIKE '<IDLE>%'
54+ OR query LIKE 'autovacuum:%'
55+ THEN query
56+ ELSE
57+ '<HIDDEN>'
58+ END AS query
59+ FROM pg_catalog.pg_stat_activity;
60+ ELSIF EXISTS (
61+ SELECT 1 FROM pg_attribute WHERE
62+ attrelid =
63+ (SELECT oid FROM pg_class
64+ WHERE relname = 'pg_stat_activity')
65+ AND attname = 'backend_xid') THEN
66+ -- >= 9.4
67+ RETURN QUERY SELECT
68+ datid, datname, pid, usesysid, usename, application_name,
69+ client_addr, client_hostname, client_port, backend_start,
70+ xact_start, query_start, state_change, waiting, state,
71+ backend_xid, backend_xmin,
72+ CASE
73+ WHEN query LIKE '<IDLE>%'
74+ OR query LIKE 'autovacuum:%'
75+ THEN query
76+ ELSE
77+ '<HIDDEN>'
78+ END AS query
79+ FROM pg_catalog.pg_stat_activity;
80+ ELSE
81+ -- >= 9.2; anything older is unsupported
82+ RETURN QUERY SELECT
83+ datid, datname, pid, usesysid, usename, application_name,
84+ client_addr, client_hostname, client_port, backend_start,
85+ xact_start, query_start, state_change, waiting, state,
86+ CASE
87+ WHEN query LIKE '<IDLE>%'
88+ OR query LIKE 'autovacuum:%'
89+ THEN query
90+ ELSE
91+ '<HIDDEN>'
92+ END AS query
93+ FROM pg_catalog.pg_stat_activity;
94+ END IF;
95+END;
96+$$;
97+
98+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 84, 1);