Merge lp:~allenap/storm/oneiric-admin-shutdown-bug-871596 into lp:storm

[Gavin Panella]

This branch adds three tests for disconnection errors:

- When the connection is terminated with pg_terminate_backend.

- When the connection is terminated with pg_terminate_backend and the
  connection goes via pgbouncer.

- When the connection is terminated because pgbouncer is terminated.

It then updates is_disconnection_error to match those situations.

Additionally, under Oneiric, some disconnection tests were failing
anyway, so is_disconnection_error has been updated to catch those too.

This branch adds two optional test dependencies: python-fixtures and
python-pgbouncer. If these are not available the tests will not be
attempted.

[Stuart Bishop (stub)]

Thanks for working on this!

So the only time you ever saw the 57P01 error was with pgbouncer? That is interesting as in the past we often never saw the error codes at all (hence the string matching).

The code seems good. Just a few suggestions and points for discussion:

- Rather than check just for 57P01, I think we might as well check for the following list:

pg_connection_failure_codes = frozenset([
    '08006', # CONNECTION FAILURE
    '08001', # SQLCLIENT UNABLE TO ESTABLISH SQLCONNECTION
    '08004', # SQLSERVER REJECTED ESTABLISHMENT OF SQLCONNECTION
    '53300', # TOO MANY CONNECTIONS
    '57000', # OPERATOR INTERVENTION
    '57014', # QUERY CANCELED
    '57P01', # ADMIN SHUTDOWN
    '57P02', # CRASH SHUTDOWN
    '57P03', # CANNOT CONNECT NOW
    ])

- Should we document installing python-pgbouncer using the Python egg from pypi rather than checking out the Bazaar branch and using a symlink hack?

- Should python-fixtures be an optional dependency? Maybe if it gets used elsewhere.

- Do we want python-pgbouncer and fixtures as optional dependencies for running the PostgreSQL tests? I understand not needing the environment setup for testing every database backend, but there seems little point for allowing people to run a subset of the tests for a particular DB backend.

review: Approve

[Gavin Panella (allenap)]

> Thanks for working on this!
>
> So the only time you ever saw the 57P01 error was with pgbouncer? That is
> interesting as in the past we often never saw the error codes at all (hence
> the string matching).

Yeah, I don't fully understand what was happening there.

>
> The code seems good. Just a few suggestions and points for discussion:
>
> - Rather than check just for 57P01, I think we might as well check for the
> following list:
>
> pg_connection_failure_codes = frozenset([
> '08006', # CONNECTION FAILURE
> '08001', # SQLCLIENT UNABLE TO ESTABLISH SQLCONNECTION
> '08004', # SQLSERVER REJECTED ESTABLISHMENT OF SQLCONNECTION
> '53300', # TOO MANY CONNECTIONS
> '57000', # OPERATOR INTERVENTION
> '57014', # QUERY CANCELED
> '57P01', # ADMIN SHUTDOWN
> '57P02', # CRASH SHUTDOWN
> '57P03', # CANNOT CONNECT NOW
> ])

Tip top, done.

>
> - Should we document installing python-pgbouncer using the Python egg from
> pypi rather than checking out the Bazaar branch and using a symlink hack?
>
> - Should python-fixtures be an optional dependency? Maybe if it gets used
> elsewhere.
>
> - Do we want python-pgbouncer and fixtures as optional dependencies for
> running the PostgreSQL tests? I understand not needing the environment setup
> for testing every database backend, but there seems little point for allowing
> people to run a subset of the tests for a particular DB backend.

I've switched everything to use setuptools, and have defined all the
test dependencies in setup.py. I've switched make check over to use
setuptools' testing support, and I've also made the ./test script use
the eggs that setuptools downloads. There's also an additional develop
Makefile target that downloads the test dependencies without running
the tests.

This means you can do make check from a fresh branch of Storm and run
*all* the tests without further intervention (other than doing the
one-off package installations and database set-up). This has already
shown me that there's another Django disconnection test failure that I
need to address.

[Robert Collins (lifeless)]

van.pg can do postgresql cluster setup as a test fixture, if you want :)

[Gavin Panella (allenap)]

> van.pg can do postgresql cluster setup as a test fixture, if you
> want :)

Indeed, I do want! I did some work on van.pg last week or the week
before, but it was a little more complicated than I had imagined... or
I was misunderstanding. I won't attempt it again in this branch, but I
feel like I might have a much better chance of success with the things
I've learnt doing this.

[Stuart Bishop (stub)]

On Wed, Oct 19, 2011 at 3:42 PM, Gavin Panella
<email address hidden> wrote:
>> van.pg can do postgresql cluster setup as a test fixture, if you
>> want :)
>
> Indeed, I do want! I did some work on van.pg last week or the week
> before, but it was a little more complicated than I had imagined... or
> I was misunderstanding. I won't attempt it again in this branch, but I
> feel like I might have a much better chance of success with the things
> I've learnt doing this.

Yer, separate branch for that or this will never land :)

--
Stuart Bishop <email address hidden>
http://www.stuartbishop.net/

[Gavin Panella (allenap)]

I'm going to split this branch into two, to help review.

[lp:~allenap/storm/oneiric-admin-shutdown-bug-871596]

443. By Gavin Panella on 2011-10-25

Include ez_setup.py in tarballs.

444. By Gavin Panella on 2011-10-26

Rely upon pgbouncer 0.0.6 or later, owing to unreliable shutdown problems in earlier versions.

445. By Gavin Panella on 2011-10-26

Deal with a weird ProgrammingError when connecting via pgbouncer.

446. By Gavin Panella on 2011-10-26

QUERY CANCELED is not a disconnection.

447. By Gavin Panella on 2011-10-27

Merged go-setuptools into oneiric-admin-shutdown-bug-871596, resolving conflicts.

[Raphaël Badin (rvb)]

I'm getting weird Segmentation fault errors when running the test suite but I know this is not unheard of when dealing with storm :/.

    test_terminated_backend (tests.databases.postgres.PostgresDisconnectionTestWithoutProxyUnixSockets) ... Segmentation fault

I suppose you have all the tests passing so it must be something with my setup.

Apart from that, this looks good. (I confess I'd like to know more about pgbouncer but right now http://pgfoundry.org/projects/pgbouncer says "PgFoundry Could Not Connect to Database:").

[0]

8 -include MANIFEST.in LICENSE README TODO NEWS Makefile setup.cfg test
9 +include MANIFEST.in LICENSE README TODO NEWS Makefile setup.cfg test ez_setup.py

This should probably go in the prereq branch but since you're going to land them together I guess that's ok ;)

[1]

317 +if has_fixtures:
318 + # Upgrade to full test case class with fixtures.
319 + from fixtures import TestWithFixtures
320 + class PostgresDisconnectionTestWithPGBouncer(
321 + PostgresDisconnectionTestWithPGBouncerBase,
322 + TestWithFixtures, TestHelper): pass

I don't think that's very readable but I have no better idea atm…

review: Approve (code)

[Gavin Panella (allenap)]

Stuart reviewed an earlier version of this branch. I have not made
huge changes since then so I'm going to assume that his +1 still
stands.

review: Abstain

[Gavin Panella (allenap)]

> I'm getting weird Segmentation fault errors when running the test suite but I
> know this is not unheard of when dealing with storm :/.
>
> test_terminated_backend
> (tests.databases.postgres.PostgresDisconnectionTestWithoutProxyUnixSockets)
> ... Segmentation fault
>
> I suppose you have all the tests passing so it must be something with my
> setup.

Grargh, there are two outstanding tests failing which I ought to
address:

  test_wb_disconnect
    in tests.django.backend.PostgresDjangoBackendDisconnectionTests

  test_configure_stores_configures_store_uris
    in tests.django.stores.DjangoStoreTests

> Apart from that, this looks good. (I confess I'd like to know more about
> pgbouncer but right now http://pgfoundry.org/projects/pgbouncer says
> "PgFoundry Could Not Connect to Database:").

man 1 pgbouncer is where I read about it.

> [0]
>
> 8 -include MANIFEST.in LICENSE README TODO NEWS Makefile setup.cfg test
> 9 +include MANIFEST.in LICENSE README TODO NEWS Makefile setup.cfg test
> ez_setup.py
>
> This should probably go in the prereq branch but since you're going to land
> them together I guess that's ok ;)

Doh, of course. Oops.

>
> [1]
>
> 317 +if has_fixtures:
> 318 + # Upgrade to full test case class with fixtures.
> 319 + from fixtures import TestWithFixtures
> 320 + class PostgresDisconnectionTestWithPGBouncer(
> 321 + PostgresDisconnectionTestWithPGBouncerBase,
> 322 + TestWithFixtures, TestHelper): pass
>
> I don't think that's very readable but I have no better idea atm…

No, me neither :-/

[lp:~allenap/storm/oneiric-admin-shutdown-bug-871596]

448. By Gavin Panella on 2011-10-28

Merged go-setuptools into oneiric-admin-shutdown-bug-871596, resolving conflicts.

449. By Gavin Panella on 2011-10-28

Merged go-setuptools into oneiric-admin-shutdown-bug-871596.

450. By Gavin Panella on 2011-10-28

Use has_fixtures from tests/__init__.py.

451. By Gavin Panella on 2011-10-28

Update NEWS.

452. By Gavin Panella on 2011-10-28

pgbouncer 0.0.7 is needed.

453. By Gavin Panella on 2011-10-28

Add eggs to path before importing tests.

454. By Gavin Panella on 2011-10-28

Depend on testtools >= 0.9.8; newer versions of python-fixtures needs it.

=== modified file 'MANIFEST.in'
--- MANIFEST.in	2008-01-28 18:17:33 +0000
+++ MANIFEST.in	2011-10-28 14:51:59 +0000
@@ -1,4 +1,4 @@
 recursive-include storm *.py *.c *.zcml
 recursive-include tests *.py *.txt
 
-include MANIFEST.in LICENSE README TODO NEWS Makefile setup.cfg test
+include MANIFEST.in LICENSE README TODO NEWS Makefile setup.cfg test ez_setup.py

=== modified file 'NEWS'
--- NEWS	2011-10-27 13:19:00 +0000
+++ NEWS	2011-10-28 14:51:59 +0000
@@ -14,10 +14,21 @@
 
   You will need the python-fixtures package in order to use this feature.
 
+- Setuptools <http://pypi.python.org/pypi/setuptools> is now required
+  to run setup.py. This makes it much easier to install the majority
+  of the dependencies required to run the test suite in its entirety.
+
+- Disconnection errors arising from PostgreSQL are now more reliably
+  detected, especially with regard to recent libpq updates in Ubuntu.
+  There are also disconnection tests that simulate sudden termination
+  of pgbouncer <http://pgfoundry.org/projects/pgbouncer/>.
+
 Bug fixes
 ---------
 
-- When a SQLObjectResultSet object was sliced with slice.start and slice.end both zero (sqlobject[0:0]), the full, unsliced resultset was returned (bug #872086).
+- When a SQLObjectResultSet object was sliced with slice.start and
+  slice.end both zero (sqlobject[0:0]), the full, unsliced resultset
+  was returned (bug #872086).
 
 0.19 (2011-10-03)
 =================

=== modified file 'README'
--- README	2011-10-28 14:51:59 +0000
+++ README	2011-10-28 14:51:59 +0000
@@ -100,7 +100,8 @@
 
  $ sudo apt-get install \
        python-mysqldb mysql-server \
-       postgresql build-essential
+       postgresql pgbouncer \
+       build-essential
 
 These will take a few minutes to download (its a bit under 200MB all
 together).  Once the download is complete, a screen called
@@ -115,9 +116,15 @@
 apt-get:
 
  $ apt-get install \
-       python-django python-psycopg2 python-testresources \
-       python-transaction python-twisted python-zope.component \
-       python-zope.security
+       python-django python-fixtures python-psycopg2 \
+       python-testresources python-transaction python-twisted \
+       python-zope.component python-zope.security
+
+Two modules - pgbouncer and timeline - are not yet packaged in
+Ubuntu. These can be installed from PyPI:
+
+  http://pypi.python.org/pypi/pgbouncer
+  http://pypi.python.org/pypi/timeline
 
 Alternatively, dependencies can be downloaded as eggs into the current
 directory with:

=== modified file 'setup.py'
--- setup.py	2011-10-28 14:51:59 +0000
+++ setup.py	2011-10-28 14:51:59 +0000
@@ -54,8 +54,12 @@
     tests_require=[
         # Versions based on Lucid, where packaged.
         "django >= 1.1.1",
+        "fixtures >= 0.3.5",
+        # pgbouncer (the Python module) is not yet packaged in Ubuntu.
+        "pgbouncer >= 0.0.7",
         "psycopg2 >= 2.0.13",
         "testresources >= 0.2.4",
+        "testtools >= 0.9.8",
         # timeline is not yet packaged in Ubuntu.
         "timeline >= 0.0.2",
         "transaction >= 1.0.0",

=== modified file 'storm/databases/postgres.py'
--- storm/databases/postgres.py	2011-09-22 09:16:49 +0000
+++ storm/databases/postgres.py	2011-10-28 14:51:59 +0000
@@ -47,7 +47,7 @@
 from storm.database import Database, Connection, Result
 from storm.exceptions import (
     install_exceptions, DatabaseError, DatabaseModuleError, InterfaceError,
-    OperationalError, ProgrammingError, TimeoutError)
+    OperationalError, ProgrammingError, TimeoutError, Error)
 from storm.tracer import TimeoutTracer
 
 
@@ -217,6 +217,18 @@
         return And(*equals)
 
 
+pg_connection_failure_codes = frozenset([
+    '08006',  # CONNECTION FAILURE
+    '08001',  # SQLCLIENT UNABLE TO ESTABLISH SQLCONNECTION
+    '08004',  # SQLSERVER REJECTED ESTABLISHMENT OF SQLCONNECTION
+    '53300',  # TOO MANY CONNECTIONS
+    '57000',  # OPERATOR INTERVENTION
+    '57P01',  # ADMIN SHUTDOWN
+    '57P02',  # CRASH SHUTDOWN
+    '57P03',  # CANNOT CONNECT NOW
+    ])
+
+
 class PostgresConnection(Connection):
 
     result_factory = PostgresResult
@@ -277,22 +289,41 @@
                 yield param
 
     def is_disconnection_error(self, exc, extra_disconnection_errors=()):
-        if not isinstance(exc, (InterfaceError, OperationalError,
-                                ProgrammingError, extra_disconnection_errors)):
-            return False
-
-        # XXX: 2007-09-17 jamesh
-        # The last message is for the benefit of old versions of
-        # psycopg2 (prior to 2.0.7) which have a bug related to
-        # stripping the error severity from the message.
-        msg = str(exc)
-        return ("server closed the connection unexpectedly" in msg or
+        # Attempt to use pgcode to determine the nature of the error. This is
+        # more reliable than string matching because it is not affected by
+        # locale settings. Fall through if pgcode is not available.
+        if isinstance(exc, Error):
+            pgcode = getattr(exc, "pgcode", None)
+            if pgcode in pg_connection_failure_codes:
+                return True
+
+        disconnection_errors = (
+            InterfaceError, OperationalError, ProgrammingError,
+            extra_disconnection_errors)
+
+        if isinstance(exc, disconnection_errors):
+            # When the connection is closed by a termination of pgbouncer, a
+            # ProgrammingError is raised. If the raw connection is closed we
+            # assume it's actually a disconnection.
+            if isinstance(exc, ProgrammingError):
+                if self._raw_connection.closed:
+                    return True
+            msg = str(exc)
+            return (
+                "connection already closed" in msg or
+                "connection not open" in msg or
                 "could not connect to server" in msg or
+                "could not receive data from server" in msg or
+                "losed the connection unexpectedly" in msg or
                 "no connection to the server" in msg or
-                "connection not open" in msg or
-                "connection already closed" in msg or
-                "losed the connection unexpectedly" in msg or
-                "could not receive data from server" in msg)
+                "terminating connection due to administrator" in msg)
+        elif isinstance(exc, DatabaseError):
+            msg = str(exc)
+            return (
+                "EOF detected" in msg or
+                "server closed the connection unexpectedly" in msg)
+        else:
+            return False
 
 
 class Postgres(Database):

=== modified file 'test'
--- test	2011-10-28 14:51:59 +0000
+++ test	2011-10-28 14:51:59 +0000
@@ -26,8 +26,6 @@
 import sys
 import os
 
-import tests
-
 
 def add_eggs_to_path():
     here = os.path.dirname(__file__)
@@ -50,7 +48,8 @@
     # top directory, so we add them to sys.path here for convenience.
     add_eggs_to_path()
 
-    suite = tests.find_tests(args)
+    from tests import find_tests
+    suite = find_tests(args)
     result = runner.run(suite)
     return not result.wasSuccessful()
 

=== modified file 'tests/__init__.py'
--- tests/__init__.py	2011-10-28 14:51:59 +0000
+++ tests/__init__.py	2011-10-28 14:51:59 +0000
@@ -21,12 +21,21 @@
 
 __all__ = [
     'find_tests',
+    'has_fixtures',
     ]
 
 import doctest
 import os
 import unittest
 
+try:
+    import fixtures
+    fixtures  # Silence lint.
+except ImportError:
+    has_fixtures = False
+else:
+    has_fixtures = True
+
 
 def find_tests(testpaths=()):
     """Find all test paths, or test paths contained in the provided sequence.

=== modified file 'tests/databases/postgres.py'
--- tests/databases/postgres.py	2011-02-25 10:26:26 +0000
+++ tests/databases/postgres.py	2011-10-28 14:51:59 +0000
@@ -22,26 +22,53 @@
 import os
 
 from storm.databases.postgres import (
-    Postgres, compile, currval, Returning, PostgresTimeoutTracer)
+    Postgres, compile, currval, Returning, PostgresTimeoutTracer, make_dsn)
 from storm.database import create_database
 from storm.exceptions import InterfaceError, ProgrammingError
 from storm.variables import DateTimeVariable, RawStrVariable
 from storm.variables import ListVariable, IntVariable, Variable
 from storm.properties import Int
+from storm.exceptions import DisconnectionError
 from storm.expr import (Union, Select, Insert, Alias, SQLRaw, State,
                         Sequence, Like, Column, COLUMN)
 from storm.tracer import install_tracer, TimeoutError
+from storm.uri import URI
 
 # We need the info to register the 'type' compiler.  In normal
 # circumstances this is naturally imported.
 import storm.info
+storm  # Silence lint.
 
+from tests import has_fixtures
 from tests.databases.base import (
     DatabaseTest, DatabaseDisconnectionTest, UnsupportedDatabaseTest)
 from tests.expr import column1, column2, column3, elem1, table1, TrackContext
 from tests.tracer import TimeoutTracerTestBase
 from tests.helper import TestHelper
 
+try:
+    import pgbouncer
+except ImportError:
+    has_pgbouncer = False
+else:
+    has_pgbouncer = True
+
+
+def terminate_other_backends(connection):
+    """Terminate all connections to the database except the one given."""
+    connection.execute(
+        "SELECT pg_terminate_backend(procpid)"
+        "  FROM pg_stat_activity"
+        " WHERE datname = current_database()"
+        "   AND procpid != pg_backend_pid()")
+
+
+def terminate_all_backends(database):
+    """Terminate all connections to the given database."""
+    connection = database.connect()
+    terminate_other_backends(connection)
+    connection.close()
+
 
 class PostgresTest(DatabaseTest, TestHelper):
 
@@ -542,6 +569,98 @@
             self.fail('Exception should have been swallowed: %s' % repr(exc))
 
 
+class PostgresDisconnectionTestWithoutProxyBase(object):
+    # DatabaseDisconnectionTest uses a socket proxy to simulate broken
+    # connections. This class tests some other causes of disconnection.
+
+    database_uri = None
+
+    def is_supported(self):
+        return bool(self.database_uri)
+
+    def setUp(self):
+        super(PostgresDisconnectionTestWithoutProxyBase, self).setUp()
+        self.database = create_database(self.database_uri)
+
+    def test_terminated_backend(self):
+        # The error raised when trying to use a connection that has been
+        # terminated at the server is considered a disconnection error.
+        connection = self.database.connect()
+        terminate_all_backends(self.database)
+        self.assertRaises(
+            DisconnectionError, connection.execute,
+            "SELECT current_database()")
+
+
+class PostgresDisconnectionTestWithoutProxyUnixSockets(
+    PostgresDisconnectionTestWithoutProxyBase, TestHelper):
+    """Disconnection tests using Unix sockets."""
+
+    database_uri = os.environ.get("STORM_POSTGRES_URI")
+
+
+class PostgresDisconnectionTestWithoutProxyTCPSockets(
+    PostgresDisconnectionTestWithoutProxyBase, TestHelper):
+    """Disconnection tests using TCP sockets."""
+
+    database_uri = os.environ.get("STORM_POSTGRES_HOST_URI")
+
+
+class PostgresDisconnectionTestWithPGBouncerBase(object):
+    # Connecting via pgbouncer <http://pgfoundry.org/projects/pgbouncer>
+    # introduces new possible causes of disconnections.
+
+    def is_supported(self):
+        return (
+            has_fixtures and has_pgbouncer and
+            bool(os.environ.get("STORM_POSTGRES_HOST_URI")))
+
+    def setUp(self):
+        super(PostgresDisconnectionTestWithPGBouncerBase, self).setUp()
+        database_uri = URI(os.environ["STORM_POSTGRES_HOST_URI"])
+        database_user = database_uri.username or os.environ['USER']
+        database_dsn = make_dsn(database_uri)
+        # Create a pgbouncer fixture.
+        self.pgbouncer = pgbouncer.fixture.PGBouncerFixture()
+        self.pgbouncer.databases[database_uri.database] = database_dsn
+        self.pgbouncer.users[database_user] = "trusted"
+        self.pgbouncer.admin_users = [database_user]
+        self.useFixture(self.pgbouncer)
+        # Create a Database that uses pgbouncer.
+        pgbouncer_uri = database_uri.copy()
+        pgbouncer_uri.host = self.pgbouncer.host
+        pgbouncer_uri.port = self.pgbouncer.port
+        self.database = create_database(pgbouncer_uri)
+
+    def test_terminated_backend(self):
+        # The error raised when trying to use a connection through pgbouncer
+        # that has been terminated at the server is considered a disconnection
+        # error.
+        connection = self.database.connect()
+        terminate_all_backends(self.database)
+        self.assertRaises(
+            DisconnectionError, connection.execute,
+            "SELECT current_database()")
+
+    def test_pgbouncer_stopped(self):
+        # The error raised from a connection that is no longer connected
+        # because pgbouncer has been immediately shutdown (via SIGTERM; see
+        # man 1 pgbouncer) is considered a disconnection error.
+        connection = self.database.connect()
+        self.pgbouncer.stop()
+        self.assertRaises(
+            DisconnectionError, connection.execute,
+            "SELECT current_database()")
+
+
+if has_fixtures:
+    # Upgrade to full test case class with fixtures.
+    from fixtures import TestWithFixtures
+    class PostgresDisconnectionTestWithPGBouncer(
+        PostgresDisconnectionTestWithPGBouncerBase,
+        TestWithFixtures, TestHelper): pass
+
+
 class PostgresTimeoutTracerTest(TimeoutTracerTestBase):
 
     tracer_class = PostgresTimeoutTracer

=== modified file 'tests/tracer.py'
--- tests/tracer.py	2011-10-27 13:22:32 +0000
+++ tests/tracer.py	2011-10-28 14:51:59 +0000
@@ -2,14 +2,14 @@
 import sys
 from unittest import TestCase
 
-try:
-    # Optional dependency, if missing Fixture tests are skipped.
+from tests import has_fixtures
+
+# Optional dependency. If missing, Fixture tests are skipped.
+if has_fixtures:
+    import fixtures.testcase
+    TestWithFixtures = fixtures.testcase.TestWithFixtures
+else:
     TestWithFixtures = object
-    from fixtures.testcase import TestWithFixtures
-except ImportError:
-    has_fixtures = False
-else:
-    has_fixtures = True
 
 try:
     # Optional dependency, if missing TimelineTracer tests are skipped.