pgsql-hackers-owner@postgresql.org ,in message <200106062214.f56ME2E51506@postg
	resql.org>, wrote: 

>     First  it's  not  standard  SQL, so chances aren't that good.
>     Second, how do you think the  system  should  behave  in  the
>     following case:
> 
>     * Table  A  has  one  trigger BEFORE INSERT doing some checks
>       plus inserting a row into table newA and updating a row  in
>       table  balanceA.   It  also  has triggers BEFORE UPDATE and
>       BEFORE DELETE that update balanceA.
> 
>     * Now we do your REPLACE INTO
> 
>     The problem is that in a concurrent multiuser environment you
>     cannot  know  if  that  row  exists until you actually do the
>     insert (except you lock the  entire  table  and  check  for).
>     Since  there's  a  BEFORE  trigger  which  potentially  could
>     suppress the INSERT, you can't do the insert  before  fireing
>     it. Now it has been run, did it's inserts and updates and the
>     statement must be converted into an UPDATE  because  the  row
>     exists - how do you undo the trigger work?

  How about doing things the other way around ? :

  Attempt the update.  If it succeeds and affects zero rows (and therefore
launches no triggers?), then do an insert.

  Any failure during the update should probably constitute failure of the
replace and preclude attempting the insert.

  I have to weigh in and say that I REALLY wish SQL had a standard REPLACE
(or I'd call it "SET") operator.  Since it does not I have to do the UPDATE
|| INSERT thing in many places.  It doesn't help either when your library has
bugs that prevent returning an accurate count of rows affected either.

-- 
Bob Forsman                                   thoth@gainesville.fl.us
           http://www.gainesville.fl.us/~thoth/

Wed, 06 Jun 2001 18:45:34 -0400
Robert Forsman <thoth@purplefrog.com>
Re: Daily Digest V1 #1428 
Robert Forsman wrote:
> 
>  It doesn't help either when your library has
> bugs that prevent returning an accurate count of rows affected either.

So does it have such bugs ?

---------------
Hannu

Thu, 07 Jun 2001 08:05:44 +0500
Hannu Krosing <hannu@tm.ee>
Re: Re: Daily Digest V1 #1428
Hannu Krosing <hannu@tm.ee> ,in message <3B1EEF88.7E086190@tm.ee>, wrote: 

> Robert Forsman wrote:
> > 
> >  It doesn't help either when your library has
> > bugs that prevent returning an accurate count of rows affected either.
> 
> So does it have such bugs ?

  Postgres 7 fixed that JDBC bug.  Now we just need NOC to upgrade our
field servers.

-- 
Bob Forsman                                   thoth@gainesville.fl.us
           http://www.gainesville.fl.us/~thoth/

Thu, 07 Jun 2001 10:37:36 -0400
Robert Forsman <thoth@purplefrog.com>
Re: Re: Daily Digest V1 #1428 