BUG #6258: Lock Sequence
The following bug has been logged online:
Bug reference: 6258
Logged by: Laerson Keler
Email address: laerson.keler@lkmc.com.br
PostgreSQL version: 8.4.9
Operating system: Ubuntu 10.04 - Kernel 2.6.32-34
Description: Lock Sequence
Details:
Dear Srs, good morning, I did the automatic update postgresql 8.4.9 and
a function stopped working, I used the option select for update
in a sequence and is now giving the following error "can not lock rows in
sequence ...", how can I enable the blocking of the sequence?
Already grateful for the attention.
Thank you.
"Laerson Keler" <laerson.keler@lkmc.com.br> writes:
Dear Srs, good morning, I did the automatic update postgresql 8.4.9 and
a function stopped working, I used the option select for update
in a sequence and is now giving the following error "can not lock rows in
sequence ...", how can I enable the blocking of the sequence?
Why did you do that, that is what were you trying to accomplish? It
never did block nextval() on the sequence, for example.
regards, tom lane
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.
Already grateful for the attention.
Thank you.
[]Laerson Keler
laerson.keler@lkmc.com.br
Cel: 11 9914-4030
Res: 11 3404-4632
2011/10/17 Tom Lane <tgl@sss.pgh.pa.us>
Show quoted text
"Laerson Keler" <laerson.keler@lkmc.com.br> writes:
Dear Srs, good morning, I did the automatic update postgresql 8.4.9 and
a function stopped working, I used the option select for update
in a sequence and is now giving the following error "can not lock rows in
sequence ...", how can I enable the blocking of the sequence?Why did you do that, that is what were you trying to accomplish? It
never did block nextval() on the sequence, for example.regards, tom lane
On 17-10-2011 12:52, Tom Lane wrote:
"Laerson Keler"<laerson.keler@lkmc.com.br> writes:
Dear Srs, good morning, I did the automatic update postgresql 8.4.9 and
a function stopped working, I used the option select for update
in a sequence and is now giving the following error "can not lock rows in
sequence ...", how can I enable the blocking of the sequence?
You can't. It was disabled [1]http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=21538377ee6a0ee91f756726bd8b3de6d19fd20a.
Why did you do that, that is what were you trying to accomplish? It
never did block nextval() on the sequence, for example.
Maybe he is using an old pgpool-II version?
[1]: http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=21538377ee6a0ee91f756726bd8b3de6d19fd20a
http://git.postgresql.org/gitweb/?p=postgresql.git;a=commit;h=21538377ee6a0ee91f756726bd8b3de6d19fd20a
--
Euler Taveira de Oliveira - Timbira http://www.timbira.com.br/
PostgreSQL: Consultoria, Desenvolvimento, Suporte 24x7 e Treinamento
Laerson keler <laerson.keler@lkmc.com.br> writes:
2011/10/17 Tom Lane <tgl@sss.pgh.pa.us>
"Laerson Keler" <laerson.keler@lkmc.com.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
On Mon, Oct 17, 2011 at 2:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.
another option is to create a table to use as a sequence, and lock
that table everytime you need a new value... is not concurrent also,
but at least faster... unless i'm missing something
--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
good idea. I will try.
Thank you.
[]Laerson Keler
laerson.keler@lkmc.com.br
Cel: 11 9914-4030
Res: 11 3404-4632
2011/10/17 Jaime Casanova <jaime@2ndquadrant.com>
Show quoted text
On Mon, Oct 17, 2011 at 2:43 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
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.another option is to create a table to use as a sequence, and lock
that table everytime you need a new value... is not concurrent also,
but at least faster... unless i'm missing something--
Jaime Casanova www.2ndQuadrant.com
Professional PostgreSQL: Soporte 24x7 y capacitación
I changed the logic of the trigger, I'm using before and taking the next_val
().
Thank you.
[]Laerson Keler
laerson.keler@lkmc.com.br
Cel: 11 9914-4030
Res: 11 3404-4632
2011/10/17 Tom Lane <tgl@sss.pgh.pa.us>
Show quoted text
Laerson keler <laerson.keler@lkmc.com.br> writes:
2011/10/17 Tom Lane <tgl@sss.pgh.pa.us>
"Laerson Keler" <laerson.keler@lkmc.com.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