Re: Best approach for a "gap-less" sequence

Поиск
Список
Период
Сортировка
От Berend Tober
Тема Re: Best approach for a "gap-less" sequence
Дата
Msg-id 44E0D827.1040004@seaworthysys.com
обсуждение исходный текст
Ответ на Re: Best approach for a "gap-less" sequence  (Jorge Godoy <jgodoy@gmail.com>)
Ответы Re: Best approach for a "gap-less" sequence
Список pgsql-general
Jorge Godoy wrote:

> Chris <dmagick@gmail.com> writes:
>
>
>>I'm not sure what type of lock you'd need to make sure no other transactions
>>updated the table (see
>>http://www.postgresql.org/docs/8.1/interactive/sql-lock.html) but "in theory"
>>something like this should work:
>>
>>begin;
>>select id from table order by id desc limit 1;
>>insert into table (id, blah) values (id+1, 'blah');
>>commit;
>
>
> This is part of the solution, yes.  But I would still need locking this table
> so that no other concurrent transaction gets another "id".  I don't want to
> lock the main table --

Wouldn't SELECT ... FOR UPDATE give you the row lock you need without
locking the table?

 From "http://www.postgresql.org/docs/8.1/interactive/sql-select.html":

"FOR UPDATE/FOR SHARE Clause

...FOR UPDATE causes the rows retrieved by the SELECT statement to be
locked as though for update. This prevents them from being modified or
deleted by other transactions until the current transaction ends. That
is, other transactions that attempt UPDATE, DELETE, or SELECT FOR UPDATE
of these rows will be blocked until the current transaction ends. Also,
if an UPDATE, DELETE, or SELECT FOR UPDATE from another transaction has
already locked a selected row or rows, SELECT FOR UPDATE will wait for
the other transaction to complete, and will then lock and return the
updated row (or no row, if the row was deleted). ..."

Regards,
Berend Tober

В списке pgsql-general по дате отправления:

Предыдущее
От: Tom Lane
Дата:
Сообщение: Re: text datum VARDATA and strings
Следующее
От: Harald Fuchs
Дата:
Сообщение: Re: Best approach for a "gap-less" sequence