[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