Autoincrementation cost

on

Most of DBA (Data Base administrator) uses a sequentiel counter that incremnents their Primarykey automaticly, it’s a field managed by the DBMS (Data Base Mangement Sytem), while you can relax. Originally this is a DBA or developper field only, the user won’t see that garbage, this kind of usesfull tricks makes sometimes the hell of DB administrators when dealing with indexing, or when a transaction is rolled back leaving a big hole between two records ! So some promgrammers prefer to manager their counter from the client application wich is a painfull task !
So how do you do it there ? never care ! let the machine wash your clothes and just answer your mobile ! it’s so easy, a serial field is not so bad, you have just to make you won triger that makes the job !

Create or Replace Trigger MakeItWork() Returns As Opaque
Language plpgsql AS’
Declare
MaxFiels,NextCounterVal int8;
BEGIN
MaxFiels= Select count(*) from my Table;
NextCounterVal = Select nextval(‘My_Counter’)
If Maxfiels NextCounterVal Then
select setval(‘My_Counter’,||MaxFiels||);
End If;
END
‘;

That’s so easy that you you a coherant serial even rolledback transactions, you can also write an other triggers while a record is deleted, by reorgnaizing the whole records forward. And remeber your Primary Key field could never be a real data jsut a unsignifinat fileld.

Advertisements

One Comment Add yours

  1. hari says:

    I am using vb as front end and postgresql as back end .i want to insert a bitmap image in front end.when pressing on a controll the image have to be saved into postgresql database ,also i have retrieve the picture.how can i do this

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s