Re: Automatic rollback on error, duplicate key?

* [email protected] <[email protected]> [2002-11-11 17:04 -0800]:
> [...] I've just started a project using python and pypgsql and I've
> run into something I consider odd.
> 
> I have a table defined as:
> 
> create table items (
>   gid        int8 UNIQUE,
>   name       varchar(256),
>   descr      varchar(1024)
> );
> 
> Over time I'll be inserting rows into the items table, and I may have
> duplicates.  I expect the insert to fail, and I don't care, I just
> want to continue inserting other items, and more importantly, I want
> previous inserts to this table and other tables to not disappear.
> 
> When I code this in a way that I thought should work, it appears that
> the pypgsql module is doing an automatic rollback when I insert a
> duplicate:
> 
>             try:
>                 cur.execute(sqlStmt)
>             except PgSQL.Error, msg:
>                 print "Insert from pg_database failed\n%s" % msg
>                 print sqlStmt
> 
> It seems to me that, since I am trapping the error, it should be up to
> me to decide if a rollback() should be performed. Why is the module
> deciding for me? Or have I misunderstood what is going on?

No, that is indeed what's currently going on. I agree it's odd and I
think it should be changed. The only question is: how exactly should the
behaviour be? Hmm. Maybe I should look how psycopg implement it.

> I took a look at the DB API 2.0 spec at
> http://www.python.org/peps/pep-0249.html but I can't see where this
> behavior is defined (or any detailed error handling behavior,
> actually).
> 
> Can someone point me in the right direction here? I basically want to
> do:
> 
>             open a new transaction
> 
>             insert row into table 1
> 
>             insert multiple rows into table 2
>                these inserts may be duplicates
> 
>             insert multiple rows into table 3, each of which is tied
>             back to table 1 by an ID.
> 
>             commit the transaction
> 
> For all statements except the inserts into table 2, if I get an error
> I would like to rollback the transaction.

ISTM that to achieve the above goal using pyPgSQL, you currently will
have to set the the autocommit property of the connection to 1 and issue
the BEGIN/ROLLBACK/COMMIT statements yourself using cursor.execute,
catching exceptions appropriately.

> I'd might as well also ask how to detect different error codes. The
> inserts into table 2 may fail for other reasons, and in those cases I
> should do a rollback. I only want to continue if the error is trying
> to insert a duplicate key. How am I supposed to do that, perform a
> regex on the error message string?

Yes.

> Yuck.

Indeed.

> Are there error codes in the cursor or connection to look at?

Nope. PostgreSQL doesn't have any error codes. With a little chance,
this will be worked on for version 7.4, if I read pgsql-hackers
correctly.

-- Gerhard


-------------------------------------------------------
This sf.net email is sponsored by:ThinkGeek
Welcome to geek heaven.
http://thinkgeek.com/sf