Hi dear all,
Now, we need to know if it is possible from the ODBC interface to access to
diagnostic registers like "GET DIAGNOSTICS rc =ROW_COUNT". It seems not to
work from odbc, maybe it need some changes to work. Can anybody help?,
thanks.
"Henshall, Stuart" wrote:
> I believe LOCK 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.
>
