Re: Automatic rollback on error, duplicate key?
"Billy G. Allie" <[email protected]>
| Newsgroups | gmane.comp.python.db.pypgsql.user |
|---|---|
| Message-ID | <[email protected]> |
[email protected] wrote: > [...] > 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. > > 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? Yuck. Are there error codes in the > cursor or connection to look at? You could do a search on table 2 for the record you are about to insert to see if it exists. You can then add it if it wasn't found. That way you avoid the automatic rollback cause by the duplicate key error. Alternatively, is the problem with duplicate int8 values in table 2. Can you use a serial field to assign the values when they are inserted? This will guarantee uniqueness. Later versions of PostgreSQL allow for 8 byte serial fields. -- ____ | Billy G. Allie | Domain....: [email protected] | /| | 7436 Hartwell | MSN.......: [email protected] |-/-|----- | Dearborn, MI 48126| |/ |LLIE | (313) 582-1540 |
signature.asc
(application/pgp-signature, 2 KB)
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Content-Type: text/plain; charset=us-ascii [email protected] wrote: > [...] > 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. > > 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? Yuck. Are there error codes in the > cursor or connection to look at? You could do a search on table 2 for the record you are about to insert to see if it exists. You can then add it if it wasn't found. That way you avoid the automatic rollback cause by the duplicate key error. Alternatively, is the problem with duplicate int8 values in table 2. Can you use a serial field to assign the values when they are inserted? This will guarantee uniqueness. Later versions of PostgreSQL allow for 8 byte serial fields. - -- ____ | Billy G. Allie | Domain....: [email protected] | /| | 7436 Hartwell | MSN.......: [email protected] |-/-|----- | Dearborn, MI 48126| |/ |LLIE | (313) 582-1540 | -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.0.2 (UnixWare) Comment: Exmh version 2.2 06/23/2000 iD8DBQE90JQKnmIkMXoVVdURAgXIAKDxZ7/USBP9Y8cCot41kM1e98qv0gCdEw0v +VWX8t1anN3zKZGvaNS672g= =bJ7S -----END PGP SIGNATURE-----