Re: Abort state on duplicated PKey in transactions

I believe LOCK TABLE <table> IN EXCLUSIVE MODE should block everything but
selects, but it locks for the entire transaction I think. Maybe in tcl you
could create your own locking using global variables. If the spin lock code
is available to user functions you might be able to use that.
Alternativley, inside a plpgsql function, could you use something like this:

INSERT INTO ex_tbl (a,b,pk) SELECT var1 AS a,var2 AS b,var3 AS pk WHERE NOT
EXISTS (SELECT * FROM ex_tbl WHERE pk=var3) LIMIT 1;
GET DIAGNOSTICS rc =ROW_COUNT;

where pk is the primary key is the primary key of ex_tbl.
if rc=0 then you'd know the primary key already existed and if rc=1 then it
would have inserted succesfully
- Stuart

"Haoldo Stenger" wrote:

> "Matthew T. O'Connor" wrote:
> >
> > > A solution, could be to query for the existance of the PK, just before
> the
> > > insertion. But there is a little span between the test and the
> > > insertion, where another insertion from another transaction could void
> > > the existance test. Any clever ideas on how to solve this? Using
> > > triggers maybe? Other solutions?
> > >
> >
> > All you need to do is use a sequence. If you set the sequence to be the
> > primary key with a default value of nextval(seq_name) then you will
> never
> > have a collision. Alternatly if you need to know that number before you
> > start inserting you can select next_val(seq_name) before you inser and
> use
> > that. By the way the datatype serial automates exactly what I
> described.
>
> Yes, but there are situations where a sequenced PK isn't what is needed.
> Imagine a DW app, where composed PKs such as (ClientNum, Year, Month,
> ArticleNum) in a table which has ArticleQty as a secondary field are
> used, in order to consolidate detail record from other tables. There,
> the processing cycle goes like checking for the existance of the PK, if
> it exists, add ArticleQtyDetail to ArticleQty, and update; and if it
> doesn't exist, insert the record with ArticleQtyDetail as the starting
> value of ArticleQty. See it? Then, if between the "select from" and the
> "insert into", other process in the system (due to parallel processing
> for instance) inserts a record with the same key, then the first
> transaction would cancel, forcing redoing of all the processing. So,
> sort of atomicity of the check?update:insert operation is needed. How
> can that be easily implemented using locks and triggers for example?
>
> Regards,
> Haroldo.