Обсуждение: Continue sequence
Hi,
Which is the correct way to obtain a continue sequence without fail (hole/gap) for a composite-id ?
Example:
CREATE TABLE nf
(
number INTERGER NOT NULL,
serie VARCHAR(2) NOT NULL,
CONSTRAINT pky_nf PRIMARY KEY (number,serie)
)
number serie
1 A
2 A
1 B
3 A
4 A
2 B
I think that I will need to use LOCK TABLE or SELECT FOR UPDATE, but in both cases if the client terminal shutdown suddenly the tuple/table stays locked on the server for a indeterminate time.
I did a test with the following commands using pgadmin III:
BEGIN;
LOCK TABLE nf IN EXCLUSIVE MODE;
I restarted my terminal and checked in another terminal that the LOCK condition was there. After 10 minutes, to unlock the table, I send a KILL command for the process that was locking the table/tuple.
Best regards,
Rogério Augusto Bassete - BRAZIL/SP
Which is the correct way to obtain a continue sequence without fail (hole/gap) for a composite-id ?
Example:
CREATE TABLE nf
(
number INTERGER NOT NULL,
serie VARCHAR(2) NOT NULL,
CONSTRAINT pky_nf PRIMARY KEY (number,serie)
)
number serie
1 A
2 A
1 B
3 A
4 A
2 B
I think that I will need to use LOCK TABLE or SELECT FOR UPDATE, but in both cases if the client terminal shutdown suddenly the tuple/table stays locked on the server for a indeterminate time.
I did a test with the following commands using pgadmin III:
BEGIN;
LOCK TABLE nf IN EXCLUSIVE MODE;
I restarted my terminal and checked in another terminal that the LOCK condition was there. After 10 minutes, to unlock the table, I send a KILL command for the process that was locking the table/tuple.
Best regards,
Rogério Augusto Bassete - BRAZIL/SP
__________________________________________________
Fale com seus amigos de graça com o novo Yahoo! Messenger
http://br.messenger.yahoo.com/
On Fri, Jan 05, 2007 at 09:30:46 -0800, Wilton Wonrath <wrwonrath@yahoo.com.br> wrote: > > I think that I will need to use LOCK TABLE or SELECT FOR UPDATE, but in > both cases if the client terminal shutdown suddenly the tuple/table > stays locked on the server for a indeterminate time. You need to use LOCK TABLE to block other sessions from concurrently inserting the same item. What is the purpose of the number column that requires there to be no gaps for a particular serie? If they could just be unique, you could use a single sequence for the table and you wouldn't have to do locking.