Re: Autoincremental value - Mailing list pgsql-general

From Scott Marlowe
Subject Re: Autoincremental value
Date
Msg-id 1092848739.17763.43.camel@localhost.localdomain
Whole thread Raw
In response to Re: Autoincremental value  (Pierre-Frédéric Caillaud<lists@boutiquenumerique.com>)
List pgsql-general
On Tue, 2004-08-17 at 12:22, Pierre-Frédéric Caillaud wrote:
>     Suppose your table is like :
>
>     key1    key2
>     1    1
>     1    2
>     2    1
>
>     To get the next value to insert for key1=1 you can do this :
>
>     SELECT key2 FROM ... WHERE key1=1 ORDER BY key2 DESC LIMIT 1
>
>     Of course a UNIQUE INDEX on key1, key2 helps.
>
>     You won't be protected from two transactions adding the same value at the
> same time, though. The unique index will catch them and one of them will
> fail (constraint violation etc). Just retry the transaction until it
> works... or, be a warrior and lock the table... but if you do that, please
> do it in a function/trigger so that it's not kept locked for long !

Actually, it should be transactionally safe to put this in a trigger and
use select ... for update ...
which should lock the key1/key2 combo you're operating on for the short
period of the trigger running, and block other triggers from running at
the same time on those data.


pgsql-general by date:

Previous
From: Tino Wildenhain
Date:
Subject: Re: Installing FullTextSearchTool tsearch2
Next
From: Steve Crawford
Date:
Subject: FATAL: invalid frontend message type 8