Thread: Re: Efficient Insert statement for one record into multiple
>> If I got you right I cannot link this two insert statements together. >> So I need two times to open a prepared statement and execute it. >> Would I need to do this in a transaction or is it safe to do this >> without it? > > It's safe to do it without a transaction, what you have to make sure > is that you do it with the same connection object. Safe as far as currval() is concerned, yes, but then the second INSERT could fail while the first INSERT is committed, right? Yours, Laurenz Albe
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Albe Laurenz wrote: >>> If I got you right I cannot link this two insert statements together. >>> So I need two times to open a prepared statement and execute it. >>> Would I need to do this in a transaction or is it safe to do this >>> without it? >> It's safe to do it without a transaction, what you have to make sure >> is that you do it with the same connection object. > > Safe as far as currval() is concerned, yes, but then the second > INSERT could fail while the first INSERT is committed, right? Yes, but as I said, it depends of the application if you do the two insertions in one transaction. Regards, Roland. - -- Dipl.-Phys. Roland Walter mailto: roland (dot) walter (dot) rwa (at) gmx (dot) net -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.2 (GNU/Linux) Comment: Using GnuPG with SUSE - http://enigmail.mozdev.org iD8DBQFFhsBMxoOEAkary1ERAlp2AJ97Sw/pKIPr7Lm4QPKBYg1wD4oQZwCdG/0r xmvivi1ApSuI8viTpcKbIS0= =A8ZT -----END PGP SIGNATURE-----
Hello, the insertion within the same connection objects works just fine. ;o) One thing which still keeps me thinking is how should I handle duplicate key exceptions when it happens in the first insert? Should I just close the connection object in the catch clause when this happens in the first statement so the second statement hits an AlreadyClosedConnectionException? Cheers, Pete
Hi, Peter, "Peter Neu" <peter.neu@gmx.net> wrote: > the insertion within the same connection objects works just fine. ;o) > One thing which still keeps me thinking is how should I handle duplicate > key exceptions when it happens in the first insert? Should I just close > the connection object in the catch clause when this happens in the first > statement so the second statement hits an AlreadyClosedConnectionException? Why execute the second statement at all? Maybe you should rework the control flow of your application. Regards, Markus -- Markus Schaber | Logical Tracking&Tracing International AG Dipl. Inf. | Software Development GIS Fight against software patents in Europe! www.ffii.org www.nosoftwarepatents.org
If you create the key using nextval you will never get duplicate key exceptions. However, you should never close connections in the catch clause. Always close connections in a finally clause. Dave On 19-Dec-06, at 5:18 AM, Peter Neu wrote: > Hello, > > the insertion within the same connection objects works just fine. ;o) > One thing which still keeps me thinking is how should I handle > duplicate > key exceptions when it happens in the first insert? Should I just > close > the connection object in the catch clause when this happens in the > first > statement so the second statement hits an > AlreadyClosedConnectionException? > > Cheers, > Pete > > > > ---------------------------(end of > broadcast)--------------------------- > TIP 1: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that > your > message can get through to the mailing list cleanly >