Thanks. You saved me work by pointing me to the FOR UPDATE detail, and
the aggregate non-locking restriction. Can anyone comment on why this is
so? Reposting in HACKERS was a good idea :)
Side note: GeneXus (http://www.genexus.com) support, will be formally
announced in 10 days in an important international event
(http://www.artech.com.uy/cgi-bin/webartech/HEvver01.exe?S,131,0,10).
This will leverage PostgreSQL in the business environments which GeneXus
deals with. Is anyone interested in receiving more information on what
GeneXus is and does?
Regards,
Haroldo.
Lincoln Yeoh wrote:
>
> I had a similar issue.
>
> I needed to make sure I had a unique row- insert if not there, update if
> there.
>
> So I resorted to locking the whole table, then select, then insert/update.
>
> What Tom told me to do was to use lock table tablename in exclusive mode
> for my case.
>
> This blocks select for updates, but doesn't block selects.
>
> So you must check with a select for update, then only do the insert if it's
> ok.
>
> If you don't check with a select for update it will not block, and bad
> things could happen :).
>
> However I couldn't do a "for update" with an aggregate, so in my
> generalised "putrow" routine I can't use "in exclusive mode".
>
> I basically wanted to do a select count(*) from datable where whereclause
> for update.
>
> If the count was 0 then only insert, else if 1 update, else make some noise
> :).
>
> The alternative is to actually fetch the rows which can be slower.
>
> Regards,
> Link.
>
> At 12:20 PM 08-09-2001 -0500, Haroldo Stenger wrote:
> >transaction should have to be redone if the insertion failed. 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?
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://www.postgresql.org/search.mpl