Re: Autoincremental value

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.