Re: Abort state on duplicated PKey in transactions

From: Haroldo Stenger <hstenger(at)adinet(dot)com(dot)uy>
To: Lincoln Yeoh <lyeoh(at)pop(dot)jaring(dot)my>, pgsql-hackers(at)postgresql(dot)org
Cc: jnj(at)artech(dot)com(dot)uy
Subject: Re: Abort state on duplicated PKey in transactions
Date: 2001-09-10 14:28:39
Message-ID: 3B9CCE17.5BDD9E89@adinet.com.uy
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

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

In response to

Browse pgsql-hackers by date

  From Date Subject
Next Message Bruce Momjian 2001-09-10 14:55:16 Re: [HACKERS] JDBC pg_description update needed for CVS tip
Previous Message Peter Eisentraut 2001-09-10 14:24:20 x = NULL