Re: Re: Failed Statements within Transactions

Mike Mascari <mascarm(at)mascari(dot)com> writes:
> How about:
> INSERT INTO foo SELECT 1, 'Hello' WHERE NOT EXISTS (SELECT
> key FROM foo WHERE key = 1);

Cute idea, but it doesn't eliminate the race-condition problem.
If two backends execute this code concurrently, both will find
that the subselect returns no rows, and both will try to do the
INSERT. If you have a unique index on the table, then one will
succeed and the other will get an error (after waiting to see
if the first commits or not).

There's no doubt that savepoints within transactions would be a
good improvement to Postgres, and I believe Vadim has hopes of
making that happen in 7.2 or 7.3. But in the meantime, transactions
without savepoints are a lot better than "transactions" that fail to
notice errors at all. Any MySQL partisans who contend otherwise
just plain Don't Get It.

regards, tom lane