launchpad-reviewers team mailing list archive
-
launchpad-reviewers team
-
Mailing list archive
-
Message #16379
[Merge] lp:~wgrant/launchpad/trusty into lp:launchpad
William Grant has proposed merging lp:~wgrant/launchpad/trusty into lp:launchpad.
Commit message:
Initial PostgreSQL 9.3 and Ubuntu 14.04 LTS support.
Requested reviews:
Launchpad code reviewers (launchpad-reviewers)
For more details, see:
https://code.launchpad.net/~wgrant/launchpad/trusty/+merge/201752
This branch adds initial PostgreSQL 9.3 support. The only significant change is adjusting queries to use pg_stat_activity.pid/query instead of procpid/current_query, which ends up pretty ugly. The worst bit is that patch-2209-00-5.sql fails to apply to 9.3, so I had to change history by replacing it with a version compatible with both 9.1 and 9.3; I duplicated the new version as patch-2209-53-1.sql to ensure that it gets reapplied everywhere.
--
https://code.launchpad.net/~wgrant/launchpad/trusty/+merge/201752
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~wgrant/launchpad/trusty into lp:launchpad.
=== modified file 'Makefile'
--- Makefile 2013-09-09 00:32:16 +0000
+++ Makefile 2014-01-15 11:06:21 +0000
@@ -2,7 +2,7 @@
# Licensed under the ZPL, (c) Zope Corporation and contributors.
PYTHON:=$(shell sed -e \
- '/RELEASE/!d; s/.*=1[23].*/python2.7/; s/.*=.*/python2.6/' /etc/lsb-release)
+ '/RELEASE/!d; s/.*=1[234].*/python2.7/; s/.*=.*/python2.6/' /etc/lsb-release)
WD:=$(shell pwd)
PY=$(WD)/bin/py
=== modified file 'database/schema/patch-2209-00-5.sql'
--- database/schema/patch-2209-00-5.sql 2012-01-30 14:59:44 +0000
+++ database/schema/patch-2209-00-5.sql 2014-01-15 11:06:21 +0000
@@ -1,24 +1,15 @@
SET client_min_messages = ERROR;
+-- NOTE: This is not the original patch 2209-00-5. That was originally
+-- to support 8.4 and 9.1, but it failed to apply in 9.3, so history was
+-- altered to make it support 9.1 and 9.3 instead. It's duplicated as
+-- 2209-53-1 to ensure the new version is applied to systems that
+-- already have 2209-00-5.
+
-- Compatibility code. During transition, we need code that runs with
--- both PostgreSQL 8.4 and 9.1.
+-- both PostgreSQL 9.1 and 9.3. Once we're on 9.3 this can probably be
+-- replaced with a simple SQL function.
--- This used to be a simple SQL function, but PG 9.1 grew an extra
--- column to pg_stat_activity. We can revert once PG 8.4
--- compatibility is not needed.
--- SELECT
--- datid, datname, procpid, usesysid, usename,
--- CASE
--- WHEN current_query LIKE '<IDLE>%'
--- OR current_query LIKE 'autovacuum:%'
--- THEN current_query
--- ELSE
--- '<HIDDEN>'
--- END AS current_query,
--- waiting, xact_start, query_start,
--- backend_start, client_addr, client_port
--- FROM pg_catalog.pg_stat_activity;
---
CREATE OR REPLACE FUNCTION activity()
RETURNS SETOF pg_stat_activity
VOLATILE SECURITY DEFINER SET search_path = public
@@ -26,13 +17,38 @@
DECLARE
a pg_stat_activity%ROWTYPE;
BEGIN
- FOR a IN SELECT * FROM pg_stat_activity LOOP
- IF a.current_query NOT LIKE '<IDLE>%'
- AND a.current_query NOT LIKE 'autovacuum:%' THEN
- a.current_query := '<HIDDEN>';
- END IF;
- RETURN NEXT a;
- END LOOP;
+ IF EXISTS (
+ SELECT 1 FROM pg_attribute WHERE
+ attrelid =
+ (SELECT oid FROM pg_class
+ WHERE relname = 'pg_stat_activity')
+ AND attname = 'procpid') THEN
+ RETURN QUERY SELECT
+ datid, datname, procpid, usesysid, usename, application_name,
+ client_addr, client_hostname, client_port, backend_start,
+ xact_start, query_start, waiting,
+ CASE
+ WHEN current_query LIKE '<IDLE>%'
+ OR current_query LIKE 'autovacuum:%'
+ THEN current_query
+ ELSE
+ '<HIDDEN>'
+ END AS current_query
+ FROM pg_catalog.pg_stat_activity;
+ ELSE
+ 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;
$$;
=== added file 'database/schema/patch-2209-53-1.sql'
--- database/schema/patch-2209-53-1.sql 1970-01-01 00:00:00 +0000
+++ database/schema/patch-2209-53-1.sql 2014-01-15 11:06:21 +0000
@@ -0,0 +1,49 @@
+SET client_min_messages = ERROR;
+
+-- NOTE: This is a duplicate of 2209-00-5. See 2209-00-5.sql for
+-- rationale.
+
+CREATE OR REPLACE FUNCTION activity()
+RETURNS SETOF pg_stat_activity
+VOLATILE SECURITY DEFINER SET search_path = public
+LANGUAGE plpgsql 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 = 'procpid') THEN
+ RETURN QUERY SELECT
+ datid, datname, procpid, usesysid, usename, application_name,
+ client_addr, client_hostname, client_port, backend_start,
+ xact_start, query_start, waiting,
+ CASE
+ WHEN current_query LIKE '<IDLE>%'
+ OR current_query LIKE 'autovacuum:%'
+ THEN current_query
+ ELSE
+ '<HIDDEN>'
+ END AS current_query
+ FROM pg_catalog.pg_stat_activity;
+ ELSE
+ 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;
+$$;
+
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2209, 53, 1);
=== modified file 'database/schema/preflight.py'
--- database/schema/preflight.py 2012-10-02 14:03:44 +0000
+++ database/schema/preflight.py 2014-01-15 11:06:21 +0000
@@ -24,6 +24,7 @@
DBController,
streaming_sync,
)
+from lp.services.database import activity_cols
from lp.services.database.sqlbase import (
ISOLATION_LEVEL_AUTOCOMMIT,
sqlvalues,
@@ -147,9 +148,9 @@
FROM pg_stat_activity
WHERE
datname=current_database()
- AND procpid <> pg_backend_pid()
+ AND %(pid)s <> pg_backend_pid()
GROUP BY datname, usename
- """)
+ """ % activity_cols(cur))
for datname, usename, num_connections in cur.fetchall():
if usename in SYSTEM_USERS:
self.log.debug(
@@ -173,15 +174,16 @@
success = True
for node in self.lpmain_nodes:
cur = node.con.cursor()
- cur.execute("""
+ cur.execute(("""
SELECT datname, usename, COUNT(*) AS num_connections
FROM pg_stat_activity
WHERE
datname=current_database()
- AND procpid <> pg_backend_pid()
- AND usename IN %s
+ AND %(pid)s <> pg_backend_pid()
+ AND usename IN %%s
GROUP BY datname, usename
- """ % sqlvalues(FRAGILE_USERS))
+ """ % activity_cols(cur))
+ % sqlvalues(FRAGILE_USERS))
for datname, usename, num_connections in cur.fetchall():
self.log.fatal(
"Fragile system %s running. %s has %d connections.",
@@ -211,13 +213,13 @@
cur.execute("""
SELECT
datname, usename,
- age(current_timestamp, xact_start) AS age, current_query
+ age(current_timestamp, xact_start) AS age
FROM pg_stat_activity
WHERE
age(current_timestamp, xact_start) > interval '%d secs'
AND datname=current_database()
""" % max_secs)
- for datname, usename, age, current_query in cur.fetchall():
+ for datname, usename, age in cur.fetchall():
if usename in BAD_USERS:
self.log.info(
"%s has transactions by %s open %s (ignoring)",
@@ -349,30 +351,31 @@
all_clear = True
for node in nodes:
cur = node.con.cursor()
- cur.execute("""
+ cur.execute(("""
SELECT
- procpid, datname, usename,
- pg_terminate_backend(procpid)
+ %(pid)s, datname, usename,
+ pg_terminate_backend(%(pid)s)
FROM pg_stat_activity
WHERE
datname=current_database()
- AND procpid <> pg_backend_pid()
- AND usename NOT IN %s
- """ % sqlvalues(SYSTEM_USERS))
- for procpid, datname, usename, ignored in cur.fetchall():
+ AND %(pid)s <> pg_backend_pid()
+ AND usename NOT IN %%s
+ """ % activity_cols(cur))
+ % sqlvalues(SYSTEM_USERS))
+ for pid, datname, usename, ignored in cur.fetchall():
all_clear = False
if loop_count == num_tries - 1:
self.log.fatal(
"Unable to kill %s [%s] on %s.",
- usename, procpid, datname)
+ usename, pid, datname)
elif usename in BAD_USERS:
self.log.info(
"Killed %s [%s] on %s.",
- usename, procpid, datname)
+ usename, pid, datname)
else:
self.log.warning(
"Killed %s [%s] on %s.",
- usename, procpid, datname)
+ usename, pid, datname)
if all_clear:
break
=== modified file 'database/schema/unautovacuumable.py'
--- database/schema/unautovacuumable.py 2013-01-07 02:40:55 +0000
+++ database/schema/unautovacuumable.py 2014-01-15 11:06:21 +0000
@@ -23,6 +23,7 @@
import sys
import time
+from lp.services.database import activity_cols
from lp.services.database.sqlbase import (
connect,
ISOLATION_LEVEL_AUTOCOMMIT,
@@ -83,16 +84,16 @@
# Sleep long enough for pg_stat_activity to be updated.
time.sleep(0.6)
cur.execute("""
- SELECT procpid FROM pg_stat_activity
+ SELECT %(pid)s FROM pg_stat_activity
WHERE
datname=current_database()
- AND current_query LIKE 'autovacuum: %'
- """)
+ AND %(query)s LIKE 'autovacuum: %%'
+ """ % activity_cols(cur))
autovacuums = [row[0] for row in cur.fetchall()]
num_autovacuums = len(autovacuums)
- for procpid in autovacuums:
- log.debug("Cancelling %d" % procpid)
- cur.execute("SELECT pg_cancel_backend(%d)" % procpid)
+ for pid in autovacuums:
+ log.debug("Cancelling %d" % pid)
+ cur.execute("SELECT pg_cancel_backend(%d)" % pid)
if __name__ == '__main__':
=== modified file 'lib/lp/services/database/__init__.py'
--- lib/lp/services/database/__init__.py 2012-04-16 23:02:44 +0000
+++ lib/lp/services/database/__init__.py 2014-01-15 11:06:21 +0000
@@ -5,6 +5,7 @@
__metaclass__ = type
__all__ = [
+ 'activity_cols',
'read_transaction',
'write_transaction',
]
@@ -14,6 +15,7 @@
DisconnectionError,
IntegrityError,
)
+from storm.store import Store
import transaction
from twisted.python.util import mergeFunctionMetadata
@@ -23,6 +25,20 @@
RETRY_ATTEMPTS = 3
+def activity_cols(cur):
+ """Adapt pg_stat_activity column names for the current DB server."""
+ if isinstance(cur, Store):
+ ver_str = cur.execute("SHOW server_version").get_one()
+ else:
+ cur.execute("SHOW server_version")
+ ver_str = cur.fetchone()
+ ver = tuple(map(int, ver_str[0].split('.')[:2]))
+ if ver < (9, 2):
+ return {'query': 'current_query', 'pid': 'procpid'}
+ else:
+ return {'query': 'query', 'pid': 'pid'}
+
+
def retry_transaction(func):
"""Decorator used to retry database transaction failures.
=== modified file 'lib/lp/services/looptuner.py'
--- lib/lp/services/looptuner.py 2013-06-20 05:50:00 +0000
+++ lib/lp/services/looptuner.py 2014-01-15 11:06:21 +0000
@@ -21,6 +21,7 @@
Interface,
)
+from lp.services.database import activity_cols
from lp.services.database.interfaces import IMasterStore
import lp.services.scripts
@@ -311,18 +312,19 @@
store = IMasterStore(LibraryFileAlias)
msg_counter = 0
while not self._isTimedOut():
- results = list(store.execute("""
+ results = list(store.execute(("""
SELECT
CURRENT_TIMESTAMP - xact_start,
- procpid,
+ %(pid)s,
usename,
datname,
- current_query
+ %(query)s
FROM activity()
- WHERE xact_start < CURRENT_TIMESTAMP - interval '%f seconds'
+ WHERE xact_start < CURRENT_TIMESTAMP - interval '%%f seconds'
AND datname = current_database()
ORDER BY xact_start LIMIT 4
- """ % self.long_running_transaction).get_all())
+ """ % activity_cols(store))
+ % self.long_running_transaction).get_all())
if not results:
break
@@ -330,10 +332,10 @@
# only report every 10 minutes to avoid log spam.
msg_counter += 1
if msg_counter % 60 == 1:
- for runtime, procpid, usename, datname, query in results:
+ for runtime, pid, usename, datname, query in results:
self.log.info(
"Blocked on %s old xact %s@%s/%d - %s.",
- runtime, usename, datname, procpid, query)
+ runtime, usename, datname, pid, query)
self.log.info("Sleeping for up to 10 minutes.")
# Don't become a long running transaction!
transaction.abort()
=== modified file 'lib/lp/testing/pgsql.py'
--- lib/lp/testing/pgsql.py 2012-06-29 08:40:05 +0000
+++ lib/lp/testing/pgsql.py 2014-01-15 11:06:21 +0000
@@ -18,6 +18,7 @@
import psycopg2
from lp.services.config import config
+from lp.services.database import activity_cols
from lp.services.database.postgresql import (
generateResetSequencesSQL,
resetSequences,
@@ -416,10 +417,11 @@
try:
cur = con.cursor()
cur.execute("""
- SELECT pg_terminate_backend(procpid)
+ SELECT pg_terminate_backend(%(pid)s)
FROM pg_stat_activity
- WHERE procpid <> pg_backend_pid() AND datname=%s
- """, [self.dbname])
+ WHERE %(pid)s <> pg_backend_pid() AND datname=%%s
+ """ % activity_cols(cur),
+ [self.dbname])
except psycopg2.DatabaseError:
pass
=== modified file 'test_on_merge.py'
--- test_on_merge.py 2012-06-29 08:40:05 +0000
+++ test_on_merge.py 2014-01-15 11:06:21 +0000
@@ -4,6 +4,7 @@
# GNU Affero General Public License version 3 (see the file LICENSE).
"""Tests that get run automatically on a merge."""
+
import _pythonpath
import errno
@@ -15,18 +16,18 @@
SIGKILL,
SIGTERM,
)
-from StringIO import StringIO
from subprocess import (
PIPE,
Popen,
STDOUT,
)
import sys
-import tabnanny
import time
import psycopg2
+from lp.services.database import activity_cols
+
# The TIMEOUT setting (expressed in seconds) affects how long a test will run
# before it is deemed to be hung, and then appropriately terminated.
# It's principal use is preventing a PQM job from hanging indefinitely and
@@ -89,17 +90,17 @@
# rogue processes still connected to the database.
for loop in range(2):
cur.execute("""
- SELECT usename, current_query
+ SELECT usename, %(query)s
FROM pg_stat_activity
WHERE datname IN (
'launchpad_dev', 'launchpad_ftest_template', 'launchpad_ftest')
- """)
+ """ % activity_cols(cur))
results = list(cur.fetchall())
if not results:
break
# Rogue processes. Report, sleep for a bit, and try again.
- for usename, current_query in results:
- print '!! Open connection %s - %s' % (usename, current_query)
+ for usename, query in results:
+ print '!! Open connection %s - %s' % (usename, query)
print 'Sleeping'
time.sleep(20)
else:
=== modified file 'utilities/launchpad-database-setup'
--- utilities/launchpad-database-setup 2012-07-12 09:58:32 +0000
+++ utilities/launchpad-database-setup 2014-01-15 11:06:21 +0000
@@ -18,7 +18,7 @@
# https://dev.launchpad.net/DatabaseSetup which are intended for
# initial Launchpad setup on an otherwise unconfigured postgresql instance
-for pgversion in 9.1
+for pgversion in 9.1 9.3
do
sudo grep -q "^auto" /etc/postgresql/$pgversion/main/start.conf
if [ $? -eq 0 ]; then
=== modified file 'utilities/pgkillactive.py'
--- utilities/pgkillactive.py 2012-01-01 03:10:25 +0000
+++ utilities/pgkillactive.py 2014-01-15 11:06:21 +0000
@@ -9,15 +9,17 @@
__metaclass__ = type
__all__ = []
+import _pythonpath
from optparse import OptionParser
import os
import signal
import sys
-import time
import psycopg2
+from lp.services.database import activity_cols
+
def main():
parser = OptionParser()
@@ -27,7 +29,7 @@
)
parser.add_option(
'-s', '--max-seconds', type='int',
- dest='max_seconds', default=60*60,
+ dest='max_seconds', default=60 * 60,
help='Maximum seconds time connections are allowed to remain active.',
)
parser.add_option(
@@ -52,12 +54,13 @@
con = psycopg2.connect(options.connect_string)
cur = con.cursor()
- cur.execute("""
- SELECT usename, procpid, backend_start, xact_start
+ cur.execute(("""
+ SELECT usename, %(pid)s, backend_start, xact_start
FROM pg_stat_activity
- WHERE xact_start < CURRENT_TIMESTAMP - '%d seconds'::interval %s
- ORDER BY procpid
- """ % (options.max_seconds, user_match_sql), options.users)
+ WHERE xact_start < CURRENT_TIMESTAMP - '%%d seconds'::interval %%s
+ ORDER BY %(pid)s
+ """ % activity_cols(cur))
+ % (options.max_seconds, user_match_sql), options.users)
rows = list(cur.fetchall())
@@ -66,12 +69,12 @@
print 'No transactions to kill'
return 0
- for usename, procpid, backend_start, transaction_start in rows:
+ for usename, pid, backend_start, transaction_start in rows:
print 'Killing %s (%d), %s, %s' % (
- usename, procpid, backend_start, transaction_start,
+ usename, pid, backend_start, transaction_start,
)
if not options.dry_run:
- os.kill(procpid, signal.SIGTERM)
+ os.kill(pid, signal.SIGTERM)
return 0
=== modified file 'utilities/pgkillidle.py'
--- utilities/pgkillidle.py 2012-01-01 03:10:25 +0000
+++ utilities/pgkillidle.py 2014-01-15 11:06:21 +0000
@@ -9,6 +9,7 @@
__metaclass__ = type
__all__ = []
+import _pythonpath
from optparse import OptionParser
import os
@@ -17,6 +18,8 @@
import psycopg2
+from lp.services.database import activity_cols
+
def main():
parser = OptionParser()
@@ -26,7 +29,7 @@
)
parser.add_option(
'-s', '--max-idle-seconds', type='int',
- dest='max_idle_seconds', default=10*60,
+ dest='max_idle_seconds', default=10 * 60,
help='Maximum seconds time idle but open transactions are allowed',
)
parser.add_option(
@@ -48,13 +51,14 @@
con = psycopg2.connect(options.connect_string)
cur = con.cursor()
- cur.execute("""
- SELECT usename, procpid, backend_start, query_start
+ cur.execute(("""
+ SELECT usename, %(pid)s, backend_start, query_start
FROM pg_stat_activity
- WHERE current_query = '<IDLE> in transaction'
- AND query_start < CURRENT_TIMESTAMP - '%d seconds'::interval %s
- ORDER BY procpid
- """ % (options.max_idle_seconds, ignore_sql), options.ignore)
+ WHERE %(query)s = '<IDLE> in transaction'
+ AND query_start < CURRENT_TIMESTAMP - '%%d seconds'::interval %%s
+ ORDER BY %(pid)s
+ """ % activity_cols(cur))
+ % (options.max_idle_seconds, ignore_sql), options.ignore)
rows = cur.fetchall()
@@ -63,12 +67,12 @@
print 'No IDLE transactions to kill'
return 0
- for usename, procpid, backend_start, query_start in rows:
+ for usename, pid, backend_start, query_start in rows:
print 'Killing %s(%d), %s, %s' % (
- usename, procpid, backend_start, query_start,
+ usename, pid, backend_start, query_start,
)
if not options.dryrun:
- os.kill(procpid, signal.SIGTERM)
+ os.kill(pid, signal.SIGTERM)
return 0
=== modified file 'utilities/pgmassacre.py'
--- utilities/pgmassacre.py 2012-06-29 08:40:05 +0000
+++ utilities/pgmassacre.py 2014-01-15 11:06:21 +0000
@@ -13,6 +13,8 @@
(but do nothing that could put the system into recovery mode).
"""
+import _pythonpath
+
from optparse import OptionParser
import sys
import time
@@ -20,6 +22,8 @@
import psycopg2
import psycopg2.extensions
+from lp.services.database import activity_cols
+
def connect(dbname='template1'):
"""Connect to the database, returning the DB-API connection."""
@@ -66,10 +70,10 @@
cur.execute("""
SELECT TRUE FROM pg_stat_activity
WHERE
- datname=%(database)s
- AND procpid != pg_backend_pid()
+ datname=%%s
+ AND %(pid)s != pg_backend_pid()
LIMIT 1
- """, vars())
+ """ % activity_cols(cur), [database])
if cur.fetchone() is None:
return False
time.sleep(0.6) # Stats only updated every 500ms.
@@ -105,14 +109,14 @@
# Terminate open connections.
cur.execute("""
- SELECT procpid, pg_terminate_backend(procpid)
+ SELECT %(pid)s, pg_terminate_backend(%(pid)s)
FROM pg_stat_activity
- WHERE datname=%s AND procpid <> pg_backend_pid()
- """, [database])
- for procpid, success in cur.fetchall():
+ WHERE datname=%%s AND %(pid)s <> pg_backend_pid()
+ """ % activity_cols(cur), [database])
+ for pid, success in cur.fetchall():
if not success:
print >> sys.stderr, (
- "pg_terminate_backend(%s) failed" % procpid)
+ "pg_terminate_backend(%s) failed" % pid)
con.close()
if still_open(database):
@@ -184,10 +188,10 @@
cur.execute("""
SELECT usename, datname, count(*)
FROM pg_stat_activity
- WHERE procpid != pg_backend_pid()
+ WHERE %(pid)s != pg_backend_pid()
GROUP BY usename, datname
ORDER BY datname, usename
- """, [database])
+ """ % activity_cols(cur))
for usename, datname, num_connections in cur.fetchall():
print >> sys.stderr, "%d connections by %s to %s" % (
num_connections, usename, datname)
Follow ups
