Help with new trac 12 plugin upgrade
bobbysmith007
Estimation and Estimator primarily).
The issue I am having is this: When I was managing transactions myself
a single sql statement throwing an exception was not an issue. As such
I could take certain actions that were valid for a specific backend and
invalid for other backends, and by handling this error, the upgrade
process would still go through.
An example: updating the sequences in postgresql to contain the new
correct value (after manually inserting rows with new ids). This is not
necessary and invalid in sqllite but seemed necessary in Postgresql, so
that there would not be duplicate ids.
Another example is checking for table existence. Given there is not
cross platform way to do this, the easiest I had been able to come up
with was to select a row from it, and if that succeeds the table exists,
otherwise it is false.
Is there anyway to get a second, out of transaction connection (yes I
know all the inherent dangers of this)?
Are there better backend methods to be using?
Is there a way to not have a single exception on a connection invalidate
the entire transaction?
Do I just need to write special cased update code based on the database
backend type I am connecting to?
Thanks for the advice,
Russ
Nick Piper
On 22/06/10 16:43, bobbysmith007 wrote:
> I am trying to port all of the plugins I maintain to trac 12 (Timing and
> Estimation and Estimator primarily).
We've been thinking about this too. I asked a couple of questions on
IRC, but not come up with anything great yet.
Attached is what we have so far, but it's not at all good. Mostly kind
of a hack to "make it work" for now...
I think the dbhelper.py layer functions are not so necessary these days,
and it could be easier to read without them?
(note the revision numbers are not those of trac-hacks.org)
Regards,
Nick
--
nick....@logica.com | www.logica.com
Logica UK Limited, registered in England & Wales (registered number 947968)
Registered Office: 250 Brook Drive, Green Park, Reading RG2 6UA, United
Kingdom
Sorry for this disclaimer:
Christian Boos
The post and this answer is probably belonging to Trac-dev, but anyway...
On 6/22/2010 5:43 PM, bobbysmith007 wrote:
> I am trying to port all of the plugins I maintain to trac 12 (Timing and
> Estimation and Estimator primarily).
>
> The issue I am having is this: When I was managing transactions myself
> a single sql statement throwing an exception was not an issue. As such
> I could take certain actions that were valid for a specific backend and
> invalid for other backends, and by handling this error, the upgrade
> process would still go through.
>
If you're talking about the upgrade process only, then you have to
realize that there's anyway no global transaction going on. For most
backends (well, any except PostgreSQL), most of the changes happening
during an upgrade like creating a new table, adding a column, etc. will
do an implicit commit and can't be rollbacked.
See http://trac.edgewall.org/changeset/9568 in particular.
> An example: updating the sequences in postgresql to contain the new
> correct value (after manually inserting rows with new ids). This is not
> necessary and invalid in sqllite but seemed necessary in Postgresql, so
> that there would not be duplicate ids.
>
Bitten has this problem as well, IIRC. Would be nice to have a
"standard" way to handle this, which is what
http://trac.edgewall.org/ticket/8575 is about. There's an
"update_sequence" function you could try, and if that works, this could
be turned into a patch which would add corresponding dummy functions for
the other backends...
> Another example is checking for table existence. Given there is not
> cross platform way to do this, the easiest I had been able to come up
> with was to select a row from it, and if that succeeds the table exists,
> otherwise it is false.
>
We have some code that does something like that (getting a list of
tables) in trac/test.py, in the reset_*_db methods. Feel free to propose
a patch moving this to trac/db/util.py.
> Is there anyway to get a second, out of transaction connection (yes I
> know all the inherent dangers of this)?
>
No, but as explained above, you don't really need this in the context of
an upgrade.
-- Christian
Bobbysmith007
> If you're talking about the upgrade process only, then you have to
> realize that there's anyway no global transaction going on. For most
> backends (well, any except PostgreSQL), most of the changes happening
> during an upgrade like creating a new table, adding a column, etc. will
> do an implicit commit and can't be rollbacked.
> Seehttp://trac.edgewall.org/changeset/9568in particular.
transactions errors though if this was not being transactioned.
(Perhaps autocommit is off by default on postgres, and so the missing
commit / close calls (that I just removed) are part of the problem).
If as stated all the other backends tend to autocommit for most
everything anyway, then I could see this behaviour (an error in a
select causing subsequent upgrade statements to fail) being missed.
The solution I came up with, but have not fully tested yet, was the
use of savepoints which seems to support a standard syntax in all of
the supported backends (NOT TESTED, but SQL99 standard). I also
rewrote and reorganized my upgrade code to not use table existence
checks in the couple places it was.
Once I spent a day thinking and refactoring my upgrade process so that
it was not relying on catching so many exceptions, I was no longer
getting upgrade errors. Thus I think that overall I made my upgrade
process more robust. Also if the main upgrade process is not in a
transaction then my try/catched'ed postgres sequence updates shouldn't
cause problems on other backends. (Though I still need to test more)
http://en.wikipedia.org/wiki/Savepoint
http://www.sqlite.org/lang_savepoint.html
http://www.postgresql.org/docs/current/interactive/sql-savepoint.html
http://dev.mysql.com/doc/refman/5.0/en/savepoint.html
#Savepoint example:
def db_table_exists(env, table):
db = env.get_read_db()
cur = db.cursor()
has_table = True;
try: # an exception can break a transaction if we are in one
cur.execute("SAVEPOINT db_table_exists;")
cur.execute("SELECT * FROM %s LIMIT 1" % table)
except Exception, e:
cur.execute("ROLLBACK TO SAVEPOINT db_table_exists;")
has_table = False
return has_table
> > An example: updating the sequences in postgresql to contain the new
> > correct value (after manually inserting rows with new ids). This is not
> > necessary and invalid in sqllite but seemed necessary in Postgresql, so
> > that there would not be duplicate ids.
>
> Bitten has this problem as well, IIRC. Would be nice to have a
> "standard" way to handle this, which is what http://trac.edgewall.org/ticket/8575 is about. There's an
> "update_sequence" function you could try, and if that works, this could
> be turned into a patch which would add corresponding dummy functions for
> the other backends...
on my plate at the moment.
Thanks again,
Russ
Bobbysmith007
> realize that there's anyway no global transaction going on. For most
> backends (well, any except PostgreSQL), most of the changes happening
> during an upgrade like creating a new table, adding a column, etc. will
> do an implicit commit and can't be rollbacked.
> > An example: updating the sequences in postgresql to contain the new
> > correct value (after manually inserting rows with new ids). This is not
> > necessary and invalid in sqllite but seemed necessary in Postgresql, so
> > that there would not be duplicate ids.
>
> Bitten has this problem as well, IIRC. Would be nice to have a
> be turned into a patch which would add corresponding dummy functions for
> the other backends...
happening for each upgrade participant. Which is what I was running
into (an exception during one part of my upgrade process prevents the
entire rest of the process from working (for this plugin)). I am
going to fix the problem temporarily with savepoints and then attempt
to submit a patch about http://trac.edgewall.org/ticket/8575.