[lug] (OT) postgresql

[rm at fabula.de]

On Sun, Nov 10, 2002 at 03:25:44PM -0700, Evelyn Mitchell wrote:
> * On 2002-11-10 22:22 j davis <davis_compz at hotmail.com> wrote:
> > 
> > Hello,
> >  is there a postgresql select query i can enter to specify
> > the last row of a table...i.e. i want to make a new invoice number based
> > on the inv_num field of the last customer entry into a table.the only thing
> > i can think off is to run a loop on the data base using perl to increment
> > a var + 1 for every row in the table...then use this var to get a query
> > to the last row...hope this is coherent :)
> 
> If you have a increment count field, then I do:
> select max(key) from table;
> 
> In the program I do:
> newkey = cursor.key + 1
> 
> And in sql I do:
> insert into table (key, entry) values (newkey, 'this is the entry);

Hmmm (hmmmm!), this is a nice quick fix but it _will_ fail in a multi-
user environment. Let's assume that two clients (A and B) will issue
the above mentioned statements. Both will retrieve the same value 
for 'max(key)', increment it and then insert a new invoice with the
same key, thanks to transaction level isolation ;-/ Not really what
one wants. The easiest solution (IMHO) in this situationwould be
to create a sequence in the database and give the 'key' column a
default value of 'next(sequence_name)' -- this _will_ guarantee a
unique value in 'key' for each invoice (and making 'key' unique in
the table definition wouldn't hurt :-) More on all this can be found
in Postgresql's fine documentation (sorry, i'm behind a 2400 dialin
right now. Nostalgic feelings ...)


  Ralf Mattes
> -- 
> Regards,                    tummy.com, ltd 
> Evelyn Mitchell             Linux Consulting since 1995
> efm at tummy.com               Senior System and Network Administrators
>                             http://www.tummy.com/
> _______________________________________________
> Web Page:  http://lug.boulder.co.us
> Mailing List: http://lists.lug.boulder.co.us/mailman/listinfo/lug
> Join us on IRC: lug.boulder.co.us port=6667 channel=#colug