[Checkins] SVN: relstorage/branches/1.1/ Merge trunk revision 94214

Shane Hathaway shane at hathawaymix.org
Sun Dec 21 00:58:07 EST 2008


Log message for revision 94215:
  Merge trunk revision 94214
  

Changed:
  U   relstorage/branches/1.1/CHANGES.txt
  D   relstorage/branches/1.1/notes/migrate-1.0-beta.txt
  D   relstorage/branches/1.1/notes/migrate-1.0.1.txt
  A   relstorage/branches/1.1/notes/migrate-to-1.0.txt
  A   relstorage/branches/1.1/notes/migrate-to-1.1.1.txt
  A   relstorage/branches/1.1/notes/migrate-to-1.1.txt
  U   relstorage/branches/1.1/notes/oracle_notes.txt
  U   relstorage/branches/1.1/relstorage/adapters/common.py
  U   relstorage/branches/1.1/relstorage/adapters/mysql.py
  U   relstorage/branches/1.1/relstorage/adapters/oracle.py
  U   relstorage/branches/1.1/relstorage/adapters/postgresql.py

-=-
Modified: relstorage/branches/1.1/CHANGES.txt
===================================================================
--- relstorage/branches/1.1/CHANGES.txt	2008-12-21 05:55:10 UTC (rev 94214)
+++ relstorage/branches/1.1/CHANGES.txt	2008-12-21 05:58:07 UTC (rev 94215)
@@ -1,3 +1,13 @@
+Version 1.1.1
+
+- Worked around MySQL performance bugs in packing.  Used temporary
+  tables and another column in the pack_object table.  The
+  other databases may benefit from the optimization as well.
+
+- Applied an optimization using setinputsizes() to the Oracle code,
+  bringing write speed back up to where it was in version 1.0.
+
+
 Version 1.1
 
 - Normalized poll-invalidation patches as Solaris' patch command would not
@@ -17,7 +27,8 @@
   Kazuhiko Shiozaki.  Thanks!
 
 - Optimized Oracle object retrieval by causing BLOBs to be sent inline
-  when possible, based on a patch by Helge Tesdal.
+  when possible, based on a patch by Helge Tesdal.  By default, the
+  optimization is activated automatically when cx_Oracle 5 is used.
 
 - Updated the storage iterator code to be compatible with ZODB 3.9.
   The RelStorage tests now pass with the shane-poll-invalidations branch

Deleted: relstorage/branches/1.1/notes/migrate-1.0-beta.txt
===================================================================
--- relstorage/branches/1.1/notes/migrate-1.0-beta.txt	2008-12-21 05:55:10 UTC (rev 94214)
+++ relstorage/branches/1.1/notes/migrate-1.0-beta.txt	2008-12-21 05:58:07 UTC (rev 94215)
@@ -1,46 +0,0 @@
-1.0 Beta Migration
-
-Use one of the following scripts to migrate from RelStorage 1.0 beta to
-RelStorage 1.0.  Alter the scripts to match the Python default encoding.
-For example, if 'import sys; print sys.getdefaultencoding()' says the
-encoding is "iso-8859-1", change all occurrences of 'UTF-8' or 'UTF8'
-to 'ISO-8859-1'.
-
-
-PostgreSQL 8.3 (using the psql command):
-
-    ALTER TABLE transaction
-        ALTER username TYPE BYTEA USING (convert_to(username, 'UTF-8')),
-        ALTER description TYPE BYTEA USING (convert_to(description, 'UTF-8'));
-
-PostgreSQL 8.2 and below (using the psql command):
-
-    ALTER TABLE transaction
-        ALTER username TYPE BYTEA USING
-            (decode(replace(convert(username, 'UTF-8'), '\\', '\\\\'), 'escape')),
-        ALTER description TYPE BYTEA USING
-            (decode(replace(convert(description, 'UTF-8'), '\\', '\\\\'), 'escape'));
-
-MySQL (using the mysql command):
-
-    ALTER TABLE transaction
-        MODIFY username BLOB NOT NULL,
-        MODIFY description BLOB NOT NULL;
-
-Oracle (using the sqlplus command):
-
-    ALTER TABLE transaction ADD (
-        new_username    RAW(500),
-        new_description RAW(2000),
-        new_extension   RAW(2000));
-
-    UPDATE transaction
-        SET new_username = UTL_I18N.STRING_TO_RAW(username, 'UTF8'),
-            new_description = UTL_I18N.STRING_TO_RAW(description, 'UTF8'),
-            new_extension = extension;
-
-    ALTER TABLE transaction DROP (username, description, extension);
-    ALTER TABLE transaction RENAME COLUMN new_username TO username;
-    ALTER TABLE transaction RENAME COLUMN new_description TO description;
-    ALTER TABLE transaction RENAME COLUMN new_extension TO extension;
-

Deleted: relstorage/branches/1.1/notes/migrate-1.0.1.txt
===================================================================
--- relstorage/branches/1.1/notes/migrate-1.0.1.txt	2008-12-21 05:55:10 UTC (rev 94214)
+++ relstorage/branches/1.1/notes/migrate-1.0.1.txt	2008-12-21 05:58:07 UTC (rev 94215)
@@ -1,89 +0,0 @@
-
-Migrating from RelStorage version 1.0.1 to version 1.1
-
-PostgreSQL:
-
-    CREATE INDEX object_state_prev_tid ON object_state (prev_tid);
-
-    DROP INDEX pack_object_keep_zoid;
-    CREATE INDEX pack_object_keep_false ON pack_object (zoid)
-        WHERE keep = false;
-    CREATE INDEX pack_object_keep_true ON pack_object (zoid, keep_tid)
-        WHERE keep = true;
-
-    ALTER TABLE transaction ADD COLUMN empty BOOLEAN NOT NULL DEFAULT FALSE;
-
-    CREATE INDEX current_object_tid ON current_object (tid);
-
-    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;
-
-    CREATE TABLE pack_state (
-        tid         BIGINT NOT NULL,
-        zoid        BIGINT NOT NULL,
-        PRIMARY KEY (tid, zoid)
-    );
-
-    CREATE TABLE pack_state_tid (
-        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);
-
-    ALTER TABLE transaction ADD COLUMN empty BOOLEAN NOT NULL DEFAULT FALSE;
-
-    CREATE INDEX current_object_tid ON current_object (tid);
-
-    ALTER TABLE object_ref ADD PRIMARY KEY (tid, zoid, to_zoid);
-    ALTER TABLE object_ref DROP INDEX object_ref_from;
-    ALTER TABLE object_ref DROP INDEX object_ref_tid;
-    ALTER TABLE object_ref DROP INDEX object_ref_to;
-
-    CREATE TABLE pack_state (
-        tid         BIGINT NOT NULL,
-        zoid        BIGINT NOT NULL,
-        PRIMARY KEY (tid, zoid)
-    ) ENGINE = MyISAM;
-
-    CREATE TABLE pack_state_tid (
-        tid         BIGINT NOT NULL PRIMARY KEY
-    ) ENGINE = MyISAM;
-
-
-Oracle:
-
-    CREATE INDEX object_state_prev_tid ON object_state (prev_tid);
-
-    ALTER TABLE transaction ADD empty CHAR DEFAULT 'N' CHECK (empty IN ('N', 'Y'));
-
-    CREATE INDEX current_object_tid ON current_object (tid);
-
-    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;
-
-    CREATE TABLE pack_state (
-        tid         NUMBER(20) NOT NULL,
-        zoid        NUMBER(20) NOT NULL,
-        PRIMARY KEY (tid, zoid)
-    );
-
-    CREATE TABLE pack_state_tid (
-        tid         NUMBER(20) NOT NULL PRIMARY KEY
-    );

Copied: relstorage/branches/1.1/notes/migrate-to-1.0.txt (from rev 94214, relstorage/trunk/notes/migrate-to-1.0.txt)
===================================================================
--- relstorage/branches/1.1/notes/migrate-to-1.0.txt	                        (rev 0)
+++ relstorage/branches/1.1/notes/migrate-to-1.0.txt	2008-12-21 05:58:07 UTC (rev 94215)
@@ -0,0 +1,46 @@
+1.0 Beta Migration
+
+Use one of the following scripts to migrate from RelStorage 1.0 beta to
+RelStorage 1.0.  Alter the scripts to match the Python default encoding.
+For example, if 'import sys; print sys.getdefaultencoding()' says the
+encoding is "iso-8859-1", change all occurrences of 'UTF-8' or 'UTF8'
+to 'ISO-8859-1'.
+
+
+PostgreSQL 8.3 (using the psql command):
+
+    ALTER TABLE transaction
+        ALTER username TYPE BYTEA USING (convert_to(username, 'UTF-8')),
+        ALTER description TYPE BYTEA USING (convert_to(description, 'UTF-8'));
+
+PostgreSQL 8.2 and below (using the psql command):
+
+    ALTER TABLE transaction
+        ALTER username TYPE BYTEA USING
+            (decode(replace(convert(username, 'UTF-8'), '\\', '\\\\'), 'escape')),
+        ALTER description TYPE BYTEA USING
+            (decode(replace(convert(description, 'UTF-8'), '\\', '\\\\'), 'escape'));
+
+MySQL (using the mysql command):
+
+    ALTER TABLE transaction
+        MODIFY username BLOB NOT NULL,
+        MODIFY description BLOB NOT NULL;
+
+Oracle (using the sqlplus command):
+
+    ALTER TABLE transaction ADD (
+        new_username    RAW(500),
+        new_description RAW(2000),
+        new_extension   RAW(2000));
+
+    UPDATE transaction
+        SET new_username = UTL_I18N.STRING_TO_RAW(username, 'UTF8'),
+            new_description = UTL_I18N.STRING_TO_RAW(description, 'UTF8'),
+            new_extension = extension;
+
+    ALTER TABLE transaction DROP (username, description, extension);
+    ALTER TABLE transaction RENAME COLUMN new_username TO username;
+    ALTER TABLE transaction RENAME COLUMN new_description TO description;
+    ALTER TABLE transaction RENAME COLUMN new_extension TO extension;
+

Copied: relstorage/branches/1.1/notes/migrate-to-1.1.1.txt (from rev 94214, relstorage/trunk/notes/migrate-to-1.1.1.txt)
===================================================================
--- relstorage/branches/1.1/notes/migrate-to-1.1.1.txt	                        (rev 0)
+++ relstorage/branches/1.1/notes/migrate-to-1.1.1.txt	2008-12-21 05:58:07 UTC (rev 94215)
@@ -0,0 +1,43 @@
+
+Migrating from RelStorage version 1.1 to version 1.1.1
+
+Before following these directions, first upgrade to the schema of
+RelStorage version 1.1 by following the directions in "migrate-to-1.1.txt".
+
+PostgreSQL:
+
+    DROP TABLE pack_object;
+    CREATE TABLE pack_object (
+        zoid        BIGINT NOT NULL PRIMARY KEY,
+        keep        BOOLEAN NOT NULL,
+        keep_tid    BIGINT NOT NULL,
+        visited     BOOLEAN NOT NULL DEFAULT FALSE
+    );
+    CREATE INDEX pack_object_keep_false ON pack_object (zoid)
+        WHERE keep = false;
+    CREATE INDEX pack_object_keep_true ON pack_object (visited)
+        WHERE keep = true;
+
+
+MySQL:
+
+    DROP TABLE pack_object;
+    CREATE TABLE pack_object (
+        zoid        BIGINT NOT NULL PRIMARY KEY,
+        keep        BOOLEAN NOT NULL,
+        keep_tid    BIGINT NOT NULL,
+        visited     BOOLEAN NOT NULL DEFAULT FALSE
+    ) ENGINE = MyISAM;
+    CREATE INDEX pack_object_keep_zoid ON pack_object (keep, zoid);
+
+
+Oracle:
+
+    DROP TABLE pack_object;
+    CREATE TABLE pack_object (
+        zoid        NUMBER(20) NOT NULL PRIMARY KEY,
+        keep        CHAR NOT NULL CHECK (keep IN ('N', 'Y')),
+        keep_tid    NUMBER(20) NOT NULL,
+        visited     CHAR DEFAULT 'N' NOT NULL CHECK (visited IN ('N', 'Y'))
+    );
+    CREATE INDEX pack_object_keep_zoid ON pack_object (keep, zoid);

Copied: relstorage/branches/1.1/notes/migrate-to-1.1.txt (from rev 94214, relstorage/trunk/notes/migrate-to-1.1.txt)
===================================================================
--- relstorage/branches/1.1/notes/migrate-to-1.1.txt	                        (rev 0)
+++ relstorage/branches/1.1/notes/migrate-to-1.1.txt	2008-12-21 05:58:07 UTC (rev 94215)
@@ -0,0 +1,89 @@
+
+Migrating from RelStorage version 1.0 or 1.0.1 to version 1.1
+
+PostgreSQL:
+
+    CREATE INDEX object_state_prev_tid ON object_state (prev_tid);
+
+    DROP INDEX pack_object_keep_zoid;
+    CREATE INDEX pack_object_keep_false ON pack_object (zoid)
+        WHERE keep = false;
+    CREATE INDEX pack_object_keep_true ON pack_object (zoid, keep_tid)
+        WHERE keep = true;
+
+    ALTER TABLE transaction ADD COLUMN empty BOOLEAN NOT NULL DEFAULT FALSE;
+
+    CREATE INDEX current_object_tid ON current_object (tid);
+
+    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;
+
+    CREATE TABLE pack_state (
+        tid         BIGINT NOT NULL,
+        zoid        BIGINT NOT NULL,
+        PRIMARY KEY (tid, zoid)
+    );
+
+    CREATE TABLE pack_state_tid (
+        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);
+
+    ALTER TABLE transaction ADD COLUMN empty BOOLEAN NOT NULL DEFAULT FALSE;
+
+    CREATE INDEX current_object_tid ON current_object (tid);
+
+    ALTER TABLE object_ref ADD PRIMARY KEY (tid, zoid, to_zoid);
+    ALTER TABLE object_ref DROP INDEX object_ref_from;
+    ALTER TABLE object_ref DROP INDEX object_ref_tid;
+    ALTER TABLE object_ref DROP INDEX object_ref_to;
+
+    CREATE TABLE pack_state (
+        tid         BIGINT NOT NULL,
+        zoid        BIGINT NOT NULL,
+        PRIMARY KEY (tid, zoid)
+    ) ENGINE = MyISAM;
+
+    CREATE TABLE pack_state_tid (
+        tid         BIGINT NOT NULL PRIMARY KEY
+    ) ENGINE = MyISAM;
+
+
+Oracle:
+
+    CREATE INDEX object_state_prev_tid ON object_state (prev_tid);
+
+    ALTER TABLE transaction ADD empty CHAR DEFAULT 'N' CHECK (empty IN ('N', 'Y'));
+
+    CREATE INDEX current_object_tid ON current_object (tid);
+
+    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;
+
+    CREATE TABLE pack_state (
+        tid         NUMBER(20) NOT NULL,
+        zoid        NUMBER(20) NOT NULL,
+        PRIMARY KEY (tid, zoid)
+    );
+
+    CREATE TABLE pack_state_tid (
+        tid         NUMBER(20) NOT NULL PRIMARY KEY
+    );

Modified: relstorage/branches/1.1/notes/oracle_notes.txt
===================================================================
--- relstorage/branches/1.1/notes/oracle_notes.txt	2008-12-21 05:55:10 UTC (rev 94214)
+++ relstorage/branches/1.1/notes/oracle_notes.txt	2008-12-21 05:58:07 UTC (rev 94215)
@@ -1,8 +1,4 @@
 
-If you are using Oracle 10g XE, use the "universal" version,
-since the smaller Western European version has minimal support
-for Unicode and will not pass all of the RelStorage tests.
-
 Docs:
     http://www.oracle.com/pls/db102/homepage
 
@@ -17,11 +13,3 @@
 Manually rollback an in-dispute transaction:
     select local_tran_id, state from DBA_2PC_PENDING;
     rollback force '$local_tran_id';
-
-It might be necessary to add the following lines to adapters/oracle.py,
-before all imports, to solve Oracle encoding issues.  (Let me know
-if you have to do this!)
-
-    import os
-    os.environ["NLS_LANG"] = ".AL32UTF8"
-

Modified: relstorage/branches/1.1/relstorage/adapters/common.py
===================================================================
--- relstorage/branches/1.1/relstorage/adapters/common.py	2008-12-21 05:55:10 UTC (rev 94214)
+++ relstorage/branches/1.1/relstorage/adapters/common.py	2008-12-21 05:58:07 UTC (rev 94215)
@@ -55,16 +55,6 @@
     }
 
     _scripts = {
-        'select_keep_tid': """
-            SELECT tid
-            FROM object_state
-            WHERE zoid = pack_object.zoid
-                AND tid > 0
-                AND tid <= %(pack_tid)s
-            ORDER BY tid DESC
-            LIMIT 1
-            """,
-
         'choose_pack_transaction': """
             SELECT tid
             FROM transaction
@@ -435,8 +425,7 @@
                         AND tid > 0
                         AND tid <= %(pack_tid)s
                     """
-                    self._run_script_stmt(cursor, stmt, {'pack_tid':
-                        pack_tid})
+                    self._run_script_stmt(cursor, stmt, {'pack_tid': pack_tid})
                     to_remove += cursor.rowcount
 
                 # Pack object states with the keep flag set to true.
@@ -484,18 +473,16 @@
         object references.
         """
         # Fill the pack_object table with OIDs, but configure them
-        # all to be kept by setting keep and keep_tid.
+        # all to be kept by setting keep to true.
         log.debug("pre_pack: populating pack_object")
-        subselect = self._scripts['select_keep_tid']
         stmt = """
         %(TRUNCATE)s pack_object;
 
-        INSERT INTO pack_object (zoid, keep)
-        SELECT DISTINCT zoid, %(TRUE)s
+        INSERT INTO pack_object (zoid, keep, keep_tid)
+        SELECT zoid, %(TRUE)s, MAX(tid)
         FROM object_state
-        WHERE tid <= %(pack_tid)s;
-
-        UPDATE pack_object SET keep_tid = (""" + subselect + """)
+        WHERE tid > 0 AND tid <= %(pack_tid)s
+        GROUP BY zoid
         """
         self._run_script(cursor, stmt, {'pack_tid': pack_tid})
 
@@ -519,10 +506,11 @@
         stmt = """
         %(TRUNCATE)s pack_object;
 
-        INSERT INTO pack_object (zoid, keep)
-        SELECT DISTINCT zoid, %(FALSE)s
+        INSERT INTO pack_object (zoid, keep, keep_tid)
+        SELECT zoid, %(FALSE)s, MAX(tid)
         FROM object_state
-        WHERE tid <= %(pack_tid)s;
+        WHERE tid > 0 AND tid <= %(pack_tid)s
+        GROUP BY zoid;
 
         -- If the root object is in pack_object, keep it.
         UPDATE pack_object SET keep = %(TRUE)s
@@ -538,12 +526,19 @@
 
         -- Keep objects that are still referenced by object states in
         -- transactions that will not be packed.
+        -- Use temp_pack_visit for temporary state; otherwise MySQL 5 chokes.
+        INSERT INTO temp_pack_visit
+        SELECT DISTINCT to_zoid
+        FROM object_ref
+        WHERE tid > %(pack_tid)s;
+
         UPDATE pack_object SET keep = %(TRUE)s
         WHERE zoid IN (
-            SELECT to_zoid
-            FROM object_ref
-            WHERE tid > %(pack_tid)s
+            SELECT zoid
+            FROM temp_pack_visit
         );
+
+        %(TRUNCATE)s temp_pack_visit;
         """
         self._run_script(cursor, stmt, {'pack_tid': pack_tid})
 
@@ -558,11 +553,8 @@
                 "pass %d", pass_num)
 
             # Make a list of all parent objects that still need
-            # to be visited.  Then set keep_tid for all pack_object
+            # to be visited.  Then set pack_object.visited for all pack_object
             # rows with keep = true.
-            # keep_tid must be set before _fill_pack_object_refs examines
-            # references.
-            subselect = self._scripts['select_keep_tid']
             stmt = """
             %(TRUNCATE)s temp_pack_visit;
 
@@ -570,13 +562,13 @@
             SELECT zoid
             FROM pack_object
             WHERE keep = %(TRUE)s
-                AND keep_tid IS NULL;
+                AND visited = %(FALSE)s;
 
-            UPDATE pack_object SET keep_tid = (""" + subselect + """)
+            UPDATE pack_object SET visited = %(TRUE)s
             WHERE keep = %(TRUE)s
-                AND keep_tid IS NULL
+                AND visited = %(FALSE)s
             """
-            self._run_script(cursor, stmt, {'pack_tid': pack_tid})
+            self._run_script(cursor, stmt)
             visit_count = cursor.rowcount
 
             if verify_sane_database:
@@ -585,7 +577,7 @@
                 stmt = """
                 SELECT 1
                 FROM pack_object
-                WHERE keep = %(TRUE)s AND keep_tid IS NULL
+                WHERE keep = %(TRUE)s AND visited = %(FALSE)s
                 """
                 self._run_script_stmt(cursor, stmt)
                 if list(cursor):
@@ -630,18 +622,15 @@
 
 
     def _fill_pack_object_refs(self, conn, cursor, get_references):
-        """Fill object_ref for all pack_object rows that have keep_tid."""
+        """Fill object_ref for all objects that are to be kept."""
         stmt = """
         SELECT DISTINCT keep_tid
         FROM pack_object
-        WHERE keep_tid IS NOT NULL
-            AND NOT EXISTS (
-                SELECT 1
-                FROM object_refs_added
-                WHERE tid = keep_tid
-            )
+            LEFT JOIN object_refs_added ON (keep_tid = tid)
+        WHERE keep = %(TRUE)s
+            AND object_refs_added.tid IS NULL
         """
-        cursor.execute(stmt)
+        self._run_script_stmt(cursor, stmt)
         tids = [tid for (tid,) in cursor]
         self._add_refs_for_tids(conn, cursor, tids, get_references)
 
@@ -728,7 +717,7 @@
 
 
     def pack(self, pack_tid, options):
-        """Pack.  Requires populated pack tables."""
+        """Pack.  Requires the information provided by pre_pack."""
 
         # Read committed mode is sufficient.
         conn, cursor = self.open()

Modified: relstorage/branches/1.1/relstorage/adapters/mysql.py
===================================================================
--- relstorage/branches/1.1/relstorage/adapters/mysql.py	2008-12-21 05:55:10 UTC (rev 94214)
+++ relstorage/branches/1.1/relstorage/adapters/mysql.py	2008-12-21 05:58:07 UTC (rev 94215)
@@ -64,19 +64,34 @@
     """MySQL adapter for RelStorage."""
 
     _scripts = Adapter._scripts.copy()
-    # work around a MySQL performance bug
+    # Work around a MySQL performance bug by avoiding an expensive subquery.
     # See: http://mail.zope.org/pipermail/zodb-dev/2008-May/011880.html
     #      http://bugs.mysql.com/bug.php?id=28257
+    _scripts['create_temp_pack_visit'] = """
+    CREATE TEMPORARY TABLE temp_pack_visit (
+        zoid BIGINT NOT NULL
+    );
+    CREATE UNIQUE INDEX temp_pack_visit_zoid ON temp_pack_visit (zoid);
+    CREATE TEMPORARY TABLE temp_pack_child (
+        zoid BIGINT NOT NULL
+    );
+    CREATE UNIQUE INDEX temp_pack_child_zoid ON temp_pack_child (zoid);
+    """
+
+    # Note: UPDATE must be the last statement in the script
+    # because it returns a value.
     _scripts['prepack_follow_child_refs'] = """
-    UPDATE pack_object SET keep = %(TRUE)s
+    %(TRUNCATE)s temp_pack_child;
+
+    INSERT INTO temp_pack_child
+    SELECT DISTINCT to_zoid
+    FROM object_ref
+        JOIN temp_pack_visit USING (zoid);
+
+    -- MySQL-specific syntax for table join in update
+    UPDATE pack_object, temp_pack_child SET keep = %(TRUE)s
     WHERE keep = %(FALSE)s
-        AND zoid IN (
-            SELECT * FROM (
-                SELECT DISTINCT to_zoid
-                FROM object_ref
-                    JOIN temp_pack_visit USING (zoid)
-            ) AS child_zoids
-        )
+        AND pack_object.zoid = temp_pack_child.zoid;
     """
 
     def __init__(self, **params):
@@ -154,17 +169,19 @@
         ) ENGINE = MyISAM;
 
         -- Temporary state during packing:
-        -- The list of objects to pack.  If keep is 'N',
+        -- The list of objects to pack.  If keep is false,
         -- the object and all its revisions will be removed.
-        -- If keep is 'Y', instead of removing the object,
+        -- If keep is true, instead of removing the object,
         -- the pack operation will cut the object's history.
-        -- If keep is 'Y' then the keep_tid field must also be set.
         -- The keep_tid field specifies which revision to keep within
         -- the list of packable transactions.
+        -- The visited flag is set when pre_pack is visiting an object's
+        -- references, and remains set.
         CREATE TABLE pack_object (
             zoid        BIGINT NOT NULL PRIMARY KEY,
             keep        BOOLEAN NOT NULL,
-            keep_tid    BIGINT
+            keep_tid    BIGINT NOT NULL,
+            visited     BOOLEAN NOT NULL DEFAULT FALSE
         ) ENGINE = MyISAM;
         CREATE INDEX pack_object_keep_zoid ON pack_object (keep, zoid);
 

Modified: relstorage/branches/1.1/relstorage/adapters/oracle.py
===================================================================
--- relstorage/branches/1.1/relstorage/adapters/oracle.py	2008-12-21 05:55:10 UTC (rev 94214)
+++ relstorage/branches/1.1/relstorage/adapters/oracle.py	2008-12-21 05:58:07 UTC (rev 94215)
@@ -62,14 +62,6 @@
     }
 
     _scripts = {
-        'select_keep_tid': """
-            SELECT MAX(tid)
-            FROM object_state
-            WHERE zoid = pack_object.zoid
-                AND tid > 0
-                AND tid <= %(pack_tid)s
-            """,
-
         'choose_pack_transaction': """
             SELECT MAX(tid)
             FROM transaction
@@ -239,7 +231,8 @@
         CREATE TABLE pack_object (
             zoid        NUMBER(20) NOT NULL PRIMARY KEY,
             keep        CHAR NOT NULL CHECK (keep IN ('N', 'Y')),
-            keep_tid    NUMBER(20)
+            keep_tid    NUMBER(20) NOT NULL,
+            visited     CHAR DEFAULT 'N' NOT NULL CHECK (visited IN ('N', 'Y'))
         );
         CREATE INDEX pack_object_keep_zoid ON pack_object (keep, zoid);
 
@@ -544,13 +537,25 @@
 
     def store_temp(self, cursor, oid, prev_tid, md5sum, data):
         """Store an object in the temporary table."""
-        cursor.setinputsizes(data=cx_Oracle.BLOB)
-        stmt = """
-        INSERT INTO temp_store (zoid, prev_tid, md5, state)
-        VALUES (:oid, :prev_tid, :md5sum, :data)
-        """
-        cursor.execute(stmt, oid=oid, prev_tid=prev_tid,
-            md5sum=md5sum, data=cx_Oracle.Binary(data))
+        if len(data) <= 2000:
+            # Send data inline for speed.  Oracle docs say maximum size
+            # of a RAW is 2000 bytes.  cx_Oracle.BINARY corresponds with RAW.
+            cursor.setinputsizes(rawdata=cx_Oracle.BINARY)
+            stmt = """
+            INSERT INTO temp_store (zoid, prev_tid, md5, state)
+            VALUES (:oid, :prev_tid, :md5sum, :rawdata)
+            """
+            cursor.execute(stmt, oid=oid, prev_tid=prev_tid,
+                md5sum=md5sum, rawdata=data)
+        else:
+            # Send data as a BLOB
+            cursor.setinputsizes(blobdata=cx_Oracle.BLOB)
+            stmt = """
+            INSERT INTO temp_store (zoid, prev_tid, md5, state)
+            VALUES (:oid, :prev_tid, :md5sum, :blobdata)
+            """
+            cursor.execute(stmt, oid=oid, prev_tid=prev_tid,
+                md5sum=md5sum, blobdata=data)
 
     def replace_temp(self, cursor, oid, prev_tid, md5sum, data):
         """Replace an object in the temporary table."""

Modified: relstorage/branches/1.1/relstorage/adapters/postgresql.py
===================================================================
--- relstorage/branches/1.1/relstorage/adapters/postgresql.py	2008-12-21 05:55:10 UTC (rev 94214)
+++ relstorage/branches/1.1/relstorage/adapters/postgresql.py	2008-12-21 05:58:07 UTC (rev 94215)
@@ -102,21 +102,23 @@
         );
 
         -- Temporary state during packing:
-        -- The list of objects to pack.  If keep is 'N',
+        -- The list of objects to pack.  If keep is false,
         -- the object and all its revisions will be removed.
-        -- If keep is 'Y', instead of removing the object,
+        -- If keep is true, instead of removing the object,
         -- the pack operation will cut the object's history.
-        -- If keep is 'Y' then the keep_tid field must also be set.
         -- The keep_tid field specifies which revision to keep within
         -- the list of packable transactions.
+        -- The visited flag is set when pre_pack is visiting an object's
+        -- references, and remains set.
         CREATE TABLE pack_object (
             zoid        BIGINT NOT NULL PRIMARY KEY,
             keep        BOOLEAN NOT NULL,
-            keep_tid    BIGINT
+            keep_tid    BIGINT NOT NULL,
+            visited     BOOLEAN NOT NULL DEFAULT FALSE
         );
         CREATE INDEX pack_object_keep_false ON pack_object (zoid)
             WHERE keep = false;
-        CREATE INDEX pack_object_keep_true ON pack_object (zoid, keep_tid)
+        CREATE INDEX pack_object_keep_true ON pack_object (visited)
             WHERE keep = true;
 
         -- Temporary state during packing: the list of object states to pack.



More information about the Checkins mailing list