Re: [LUG] postgresql inserts

[Pete Hatton <pete@xxxxxxxxxxxxx>]

> Hi, thanks for this idea.
> As this reqiures 2 SQL commands, I am worried that there may be some
> problems if this procedure should be run by a different user inbetween 
> these commands. I would consider transaction locking, however sequences 
> values are not locked. Any ideas on how to secure this?
> 
Not used Postgresql myself, but my thoughts are make to make the field 
that usess the sequence number a primary key or a index with no duplicates.  

Then witb a commit and rollback it shouldn't cause any problems.

eg

begin transaction
sql to get next sequence number
insert data into table
if insert fails due to a primary key constraint or index constraint
  rollback
else
  commit

Stored procedures are you friend here :)

Pete Hatton
---------------------------------
E-mail:  pete@xxxxxxxxxxxxx
Webpage: http://www.monolight.org
---------------------------------
All men have the right to wait in line.


--
The Mailing List for the Devon & Cornwall LUG
Mail majordomo@xxxxxxxxxxxx with "unsubscribe list" in the
message body to unsubscribe.