[Checkins] SVN: relstorage/branches/1.1/ In the PostgreSQL adapter, made the pack lock fall back to table locking

Shane Hathaway shane at hathawaymix.org
Sat May 10 02:13:46 EDT 2008


Log message for revision 86601:
  In the PostgreSQL adapter, made the pack lock fall back to table locking
  rather than advisory locks for PostgreSQL 8.1.
  

Changed:
  U   relstorage/branches/1.1/CHANGELOG.txt
  U   relstorage/branches/1.1/notes/migrate-1.0.1.txt
  U   relstorage/branches/1.1/relstorage/adapters/postgresql.py

-=-
Modified: relstorage/branches/1.1/CHANGELOG.txt
===================================================================
--- relstorage/branches/1.1/CHANGELOG.txt	2008-05-10 06:10:48 UTC (rev 86600)
+++ relstorage/branches/1.1/CHANGELOG.txt	2008-05-10 06:13:46 UTC (rev 86601)
@@ -1,4 +1,13 @@
 
+RelStorage 1.1b2
+
+- Made the MySQL locks database-specific rather than server-wide.  This is
+  important for multi-database configurations.
+
+- In the PostgreSQL adapter, made the pack lock fall back to table locking
+  rather than advisory locks for PostgreSQL 8.1.
+
+
 RelStorage 1.1b1
 
 - Fixed the use of setup.py without setuptools.  Thanks to Chris Withers.

Modified: relstorage/branches/1.1/notes/migrate-1.0.1.txt
===================================================================
--- relstorage/branches/1.1/notes/migrate-1.0.1.txt	2008-05-10 06:10:48 UTC (rev 86600)
+++ relstorage/branches/1.1/notes/migrate-1.0.1.txt	2008-05-10 06:13:46 UTC (rev 86601)
@@ -5,8 +5,6 @@
 
     CREATE INDEX object_state_prev_tid ON object_state (prev_tid);
 
-    DROP TABLE pack_lock;
-
     DROP INDEX pack_object_keep_zoid;
     CREATE INDEX pack_object_keep_false ON pack_object (zoid)
         WHERE keep = false;
@@ -17,7 +15,7 @@
 
     CREATE INDEX current_object_tid ON current_object (tid);
 
-    ALTER TABLE object_ref PRIMARY KEY (tid, zoid, to_zoid);
+    ALTER TABLE object_ref ADD PRIMARY KEY (tid, zoid, to_zoid);
     DROP INDEX object_ref_from;
     DROP INDEX object_ref_tid;
     DROP INDEX object_ref_to;
@@ -32,6 +30,17 @@
         tid         BIGINT NOT NULL PRIMARY KEY
     );
 
+Users of PostgreSQL 8.2 and above should also drop the pack_lock table since
+it has been replaced with an advisory lock:
+
+    DROP TABLE pack_lock;
+
+Users of PostgreSQL 8.1 and below still need the pack_lock table.  If you
+have deleted it, please create it again with the following statement:
+
+    CREATE TABLE pack_lock ();
+
+
 MySQL:
 
     CREATE INDEX object_state_prev_tid ON object_state (prev_tid);
@@ -55,6 +64,7 @@
         tid         BIGINT NOT NULL PRIMARY KEY
     ) ENGINE = MyISAM;
 
+
 Oracle:
 
     CREATE INDEX object_state_prev_tid ON object_state (prev_tid);

Modified: relstorage/branches/1.1/relstorage/adapters/postgresql.py
===================================================================
--- relstorage/branches/1.1/relstorage/adapters/postgresql.py	2008-05-10 06:10:48 UTC (rev 86600)
+++ relstorage/branches/1.1/relstorage/adapters/postgresql.py	2008-05-10 06:13:46 UTC (rev 86601)
@@ -16,6 +16,7 @@
 from base64 import decodestring, encodestring
 import logging
 import psycopg2, psycopg2.extensions
+import re
 from ZODB.POSException import StorageError
 
 from common import Adapter
@@ -135,7 +136,10 @@
         """
         cursor.execute(stmt)
 
+        if not self._pg_has_advisory_locks(cursor):
+            cursor.execute("CREATE TABLE pack_lock ()")
 
+
     def prepare_schema(self):
         """Create the database schema if it does not already exist."""
         conn, cursor = self.open()
@@ -207,6 +211,20 @@
                         psycopg2.OperationalError):
                     pass
 
+    def _pg_version(self, cursor):
+        """Return the (major, minor) version of PostgreSQL"""
+        cursor.execute("SELECT version()")
+        v = cursor.fetchone()[0]
+        m = re.search(r"([0-9]+)[.]([0-9]+)", v)
+        if m is None:
+            raise AssertionError("Unable to detect PostgreSQL version: " + v)
+        else:
+            return int(m.group(1)), int(m.group(2))
+
+    def _pg_has_advisory_locks(self, cursor):
+        """Return true if this version of PostgreSQL supports advisory locks"""
+        return self._pg_version(cursor) >= (8, 2)
+
     def open_for_load(self):
         """Open and initialize a connection for loading objects.
 
@@ -570,21 +588,27 @@
         cursor.execute(stmt)
         return cursor.fetchone()[0]
 
-
     def hold_pack_lock(self, cursor):
         """Try to acquire the pack lock.
 
         Raise an exception if packing or undo is already in progress.
         """
-        stmt = "SELECT pg_try_advisory_lock(1)"
-        cursor.execute(stmt)
-        locked = cursor.fetchone()[0]
-        if not locked:
-            raise StorageError('A pack or undo operation is in progress')
+        if self._pg_has_advisory_locks(cursor):
+            cursor.execute("SELECT pg_try_advisory_lock(1)")
+            locked = cursor.fetchone()[0]
+            if not locked:
+                raise StorageError('A pack or undo operation is in progress')
+        else:
+            # b/w compat
+            try:
+                cursor.execute("LOCK pack_lock IN EXCLUSIVE MODE NOWAIT")
+            except psycopg2.DatabaseError:
+                raise StorageError('A pack or undo operation is in progress')
 
     def release_pack_lock(self, cursor):
         """Release the pack lock."""
-        stmt = "SELECT pg_advisory_unlock(1)"
-        cursor.execute(stmt)
+        if self._pg_has_advisory_locks(cursor):
+            cursor.execute("SELECT pg_advisory_unlock(1)")
+        # else no action needed since the lock will be released at txn commit
 
     _poll_query = "EXECUTE get_latest_tid"



More information about the Checkins mailing list