Re: Query inside transaction

On Fri, Jun 25, 2004 at 12:48:43 +0200,
Ago <ago(at)nmb(dot)it> wrote:
> OK, thanks Michal, I did not know this issue. I thought I should use LOCK table inside the transaction to pick up the correct id value from SELECT MAX(id) FROM e_catalog.

It depends on what you want. Sequences should be used to produce unique
values. If you want to get consecutively numbered rows then they shouldn't
be used.

>
>
> On 25/06/2004 12.38, Michal Táborský <michal(at)taborsky(dot)cz> wrote:
> >NMB Webmaster wrote:
> >
> >> But if someone else runs the same transaction in the same time
> >what
> > > value does "currval('sequence')" return? That one of the first
> > > transaction or that one of the other transaction? Moreover, field
> > > id is a unique primary key, it does not accept duplicates.
> >
> >That's the beauty of sequences. They are transaction-safe. Co
> >"currval('sequence')" will always return the same value of the previous
> >
> >nextval call within that transaction, no matter how many other
> >transactions picked the numbers in between.

It is actually a bit stronger promise than that. Currval returns that last
assigned value from the current session, which may span multiple transactions.