On Sat, Feb 11, 2012 at 12:05 PM, Kjell Rilbe <kjell.rilbe@...> wrote:

>
> If you want to avoid sequences that perhaps have different syntax for
> different servers, may I suggest that you have a separate table:
>
> create table "PK" (
>   "TableName" varchar(30) not null primary key,
>   "LastUsedId" int not null default 0
> )
>
> Enter one row in this table for each table you need a PK "generator"
> for. To aquire a key, start your transaction with:
>
> update "PK"
> set "LastUsedId" = "LastUsedId" + 1
> where "TableName" = :TableName
>
> Then
>
> select "LastUsedId"
> from "PK"
> where "TableName" = :TableName
>

Just be sure to do that in a separate transaction with a retry on
deadlock/update conflict. That's exactly the strategy used to create
a gateway record that prevents concurrent updates to a table.

Good luck,

Ann