Re: BUG #6258: Lock Sequence

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: Laerson keler <laerson(dot)keler(at)lkmc(dot)com(dot)br>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #6258: Lock Sequence
Date: 2011-10-17 19:43:09
Message-ID: 18368.1318880589@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

Laerson keler <laerson(dot)keler(at)lkmc(dot)com(dot)br> writes:
> 2011/10/17 Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
>> "Laerson Keler" <laerson(dot)keler(at)lkmc(dot)com(dot)br> writes:
>> Why did you do that, that is what were you trying to accomplish? It
>> never did block nextval() on the sequence, for example.

> Tom Lane, good afternoon, I block the sequence not to miss the sequel, for
> it not to be skipped if the insert to fail. My logic involves two triggers,
> one before and one after. I give the first one in last_value select for
> update in the sequence and insert after I run a select next_val ('sequence')
> to place in the next issue, so the sequence in my table is no failure.

Well, that's a cute idea, but the fact is that it was always quite
unsafe because it had no interlock against nextval(). Moreover, you
still did not have a guarantee of no holes in the assigned ID values,
because the transaction could still fail after the AFTER trigger runs.

There really is not any way to generate guaranteed-hole-free sequences
using sequence objects. If you have to have that, I'd suggest locking
the table against other writes and then fetching MAX(id) + 1. It's not
very fast, and it's not at all concurrent, but that's the price of
ensuring no holes. Personally I'd rethink how badly you need that
property.

regards, tom lane

In response to

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Jaime Casanova 2011-10-17 20:55:11 Re: BUG #6258: Lock Sequence
Previous Message Euler Taveira de Oliveira 2011-10-17 19:13:13 Re: BUG #6258: Lock Sequence