Jon,
One thing to keep in mind is that sequences will commit right away. In
other words if one transaction does a select nextval('sequence') and
get's back the value 1, and another transaction does a select
nextval('sequence') it will get the value 2 back regardless of what the
other transaction does, yes even if it rolls back.
Dave
On Fri, 2002-06-07 at 12:43, Jon Swinth wrote:
> 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
>
>