launchpad-reviewers team mailing list archive

[Stuart Bishop <stuart.bishop@xxxxxxxxxxxxx>]

Stuart Bishop has proposed merging lp:~stub/launchpad/db-cleanups into lp:launchpad/db-devel with lp:~stub/launchpad/pending-db-changes as a prerequisite.

Requested reviews:
  Launchpad code reviewers (launchpad-reviewers)
Related bugs:
  Bug #813761 in Launchpad itself: "Warnings from nondowntime-security-update.sh"
  https://bugs.launchpad.net/launchpad/+bug/813761

For more details, see:
https://code.launchpad.net/~stub/launchpad/db-cleanups/+merge/68658

= Summary =

Running security.py on production and staging generates warnings.

== Proposed fix ==

Fix the stuff it is warning about.
-- 
https://code.launchpad.net/~stub/launchpad/db-cleanups/+merge/68658
Your team Launchpad code reviewers is requested to review the proposed merge of lp:~stub/launchpad/db-cleanups into lp:launchpad/db-devel.

=== modified file 'database/schema/Makefile'
--- database/schema/Makefile	2011-01-31 11:10:39 +0000
+++ database/schema/Makefile	2011-07-21 11:09:44 +0000
@@ -128,7 +128,6 @@
 	@ psql -d ${EMPTY_DBNAME} -q -c "CREATE SCHEMA todrop;"
 	@ echo "* Creating functions"
 	@ psql -d ${EMPTY_DBNAME} -f trusted.sql   | grep : | cat
-	@ psql -d ${EMPTY_DBNAME} -f testfuncs.sql | grep : | cat
 	@ echo "* Installing tsearch2 into ts2 schema"
 	@ ${PYTHON} fti.py -q --setup-only -d ${EMPTY_DBNAME}
 	@ echo "* Loading base database schema"

=== added file 'database/schema/patch-2208-76-3.sql'
--- database/schema/patch-2208-76-3.sql	1970-01-01 00:00:00 +0000
+++ database/schema/patch-2208-76-3.sql	2011-07-21 11:09:44 +0000
@@ -0,0 +1,15 @@
+-- Copyright 2011 Canonical Ltd.  This software is licensed under the
+-- GNU Affero General Public License version 3 (see the file LICENSE).
+
+SET client_min_messages = ERROR;
+
+-- Drop old unused functions still lurking on production.
+DROP FUNCTION IF EXISTS is_blacklisted_name(text);
+DROP FUNCTION IF EXISTS name_blacklist_match(text);
+DROP FUNCTION IF EXISTS reverse(text);
+DROP FUNCTION IF EXISTS bug_summary_temp_journal_clean_row(bugsummary);
+DROP FUNCTION IF EXISTS valid_version(text);
+DROP FUNCTION IF EXISTS decendantrevision(integer);
+DROP FUNCTION IF EXISTS sleep_for_testing(float);
+
+INSERT INTO LaunchpadDatabaseRevision VALUES (2208, 76, 3);

=== modified file 'database/schema/security.cfg'
--- database/schema/security.cfg	2011-07-15 15:35:03 +0000
+++ database/schema/security.cfg	2011-07-21 11:09:44 +0000
@@ -11,7 +11,6 @@
 
 [public]
 type=group
-public._killall_backends(text)             =
 public.activity()                          = EXECUTE
 public.add_test_openid_identifier(integer) = EXECUTE
 public.alllocks                            =

=== removed file 'database/schema/testfuncs.sql'
--- database/schema/testfuncs.sql	2009-06-24 21:17:33 +0000
+++ database/schema/testfuncs.sql	1970-01-01 00:00:00 +0000
@@ -1,29 +0,0 @@
-/*
-Copyright 2009 Canonical Ltd.  This software is licensed under the
-GNU Affero General Public License version 3 (see the file LICENSE).
-
-Stored procedures designed for use only by the test suite. These
-will not be loaded onto the production database
-*/
-
-CREATE OR REPLACE FUNCTION _killall_backends(text)
-RETURNS Boolean AS $$
-    import os
-    from signal import SIGTERM
-
-    plan = plpy.prepare(
-        "SELECT procpid FROM pg_stat_activity WHERE datname=$1", ['text']
-        )
-    success = True
-    for row in plpy.execute(plan, args):
-        try:
-            plpy.info("Killing %d" % row['procpid'])
-            os.kill(row['procpid'], SIGTERM)
-        except OSError:
-            success = False
-
-    return success
-$$ LANGUAGE plpythonu;
-
-COMMENT ON FUNCTION _killall_backends(text) IS 'Kill all backend processes connected to the given database. Note that this is unlikely to work if you are connected to the database you are killing, as you are likely to kill your own connection before all the others have been killed.';
-

=== modified file 'lib/lp/testing/pgsql.py'
--- lib/lp/testing/pgsql.py	2011-07-01 07:11:27 +0000
+++ lib/lp/testing/pgsql.py	2011-07-21 11:09:44 +0000
@@ -399,7 +399,11 @@
                 # always having this is a problem.
                 try:
                     cur = con.cursor()
-                    cur.execute('SELECT _killall_backends(%s)', [self.dbname])
+                    cur.execute("""
+                        SELECT pg_terminate_backend(procpid)
+                        FROM pg_stat_activity
+                        WHERE procpid <> pg_backend_pid() AND datname=%s
+                        """, [self.dbname])
                 except psycopg2.DatabaseError:
                     pass