Re: Daily Digest V1 #1428

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/