MaRcElO PeReIrA wrote:
> Dave,
>
> I actually use just the sequence, as you wrote!
>
> The biggest problem it that I *can't* have holes in
> that column, so it was because I used id (serial) and
> forn_id (integer).
You could maintain some sort of systemnumber table yourself
create table sysnum (
first int not null,
next int not null,
last int not null
latest_updater text not null,
the_time timestamp? not null);
and get your serial number from the next column.
However, this strategy demands the same logic from
all programs using the table:
pseudo Ada code
loop
begin transaction
select * from sysnum into some Adarecord;
update sysnum
set next=next+1
latest_updater = The_pid_or_name_of_your_process_or_thread
the_time=now (with good enough acurracy)
where
latest_updater = Adarecord.latest_updater and
The_time = Adarecord.The_time;
if Rows_Affected = 0 then
Rollback transaction;
else
commit transaction:
exit
end if;
(perhaps a small delay, say 0.05 sec?)
end loop;
you can get Rows_affected from PQ_Cmd_Tuples
if Rows_affected is 0 then you have a transaction conflict,
and must start all over again, to get a unique value.
What this does to performance, I don't know, but I do know it works,
IF AND ONLY IF all processes follow the same rule.
There should proberly be some code to handle when
you fall over the edge, ie next > last => next = first
/Björn
