Re: INSERT WHERE NOT EXISTS

Reuben D. Budiardja wrote:

>>Reuben must be prepared for unique key violation, I'm afraid. And,
>>despite the optimism in the link, we still don't have savepoints. :-(
>
> Interesting reading of the archive. In my particular case here, I don't have
> to worry too much about the race thing. But the inherent problem is still
> there.
>
> Where can I read / learn more about the so-called "savepoints"? This is the
> first time I've heard it. Granted, I've only been using postgresql recently.

Here's a link to Oracle's implementation:

http://www.engin.umich.edu/caen/wls/software/oracle/server.901/a90125/statements_102.htm#2091263

If PostgreSQL had SAVEPOINTs or even nested transactions, one could
handle a unique key violation without having to resubmit the whole
transaction. Eg:

BEGIN; <- Outer Tx

.. do a lot of work ..

LOOP:

status = ABORTED

WHILE (status == ABORTED) {
BEGIN; <- Nested Tx
UPDATE foo SET val = 1 WHERE key = 0;
IF zero rows updated {
INSERT INTO foo...
^- Causes unique key violation, but only Nested Tx in ABORT state
}
status = TX state;
END;
}

END;

I believe there are hackers working on nested transactions for 7.5?

Mike Mascari
mascarm(at)mascari(dot)com

>
> Thanks.
> RDB
>