First off, thank you Dave and Barry for getting to the bottom of this. I now understand why it is happening.
Now, I realize that I am probably in the wrong e-mail list, but why in the world would it work that way?
Why is the DB telling me when to commit or rollback my transaction?
This is going to make high volume operations not work very well. No matter how well I select first before
trying to update or insert, I am going to run into issues of uncommited data from other clients. This means
that when I try to insert a record because I don't see one already there (i.e. unique key) but there is already
an uncommited one there, I will receive an exception and be forced to rollback all my work. The DB doesn't
allow me to do the simple step of handling the insert exception, finding the existing record, and simply updating
it and going on. After all, the uncommited record that was in the way should be commited already because
otherwise the DB would not have known wither to throw an exception (other client commited) or allow the insert
(other client rolled back).
Thanks again for the help.
On Thursday 06 June 2002 09:22 pm, Barry Lind wrote:
> Jon,
>
> Jon Swinth wrote:
> > The code actually calls a select
> >
> > from currval() first and then on exception calls nextval() to see what
> > the sequence is currently at.
>
> The description here sounds like you are doing something you cannot do
> in Postgres. If you get an exception/error in postgres you cannot just
> catch the exception and continue processing. Any error in postgres will
> abort the transaction, you must first do an explicit rollback() before
> you can continue issuing additional sql statements. So when you say
> 'and then on excpetion calls ...' it sounds to me like you are trapping
> an exception and trying to do additional work.
>
> thanks,
> --Barry