Re: Revisited: Transactions, insert unique.

Today, in a message to pgsql-general, Ross J. Reedstrom wrote:
>
> the developers have already agreed that the error system needs an
> overhaul, ... Inclusion of the ability to ignore some errors will
> happen.

This is good to hear. I suspect, though, that to support statement-level
aborts more is needed than an overhaul of the error system. Statement
level aborts are essentially a form of nested transaction, i.e. the
ability to rollback a precisely defined subset of the top-level
transaction.

> I was _very_ specific that the _only_ thing I disagree with in this
> is being careful to not provide the enemy with ammunition, as it were,
> and over interpret the standard to PostgreSQL's detriment.

The fact that some vendors of proprietary systems think in these terms is
no reason for the postgresql community to do the same. Unlike them we
don't HAVE to sell the system at all cost to be successful.

> This is why _not_ having this discussion can hurt.

Don't agree.

> being able to play the 'technically SQL92 compliant' card, without
> having to cross your fingers behind your back, is very important.

But you are essentially doing that, crossing your fingers I mean, by
relying on a shaky interpretation solely to be able to claim compliance,
while you know full well that the alternative interpretation is the better
one.

> Heck, I'd be wrestling with Oracle right now,

Well, since you mention Oracle: A few years ago I had a similar argument
with them about their peculiar interpretation of what SERIALIZABLE in
SQL92 means. The early versions of Oracle's multi-version concurrancy
mechanism didn't provide truely serializable transactions, yet they
claimed it did by resorting to rather twisted interpretations of the
standard.

I didn't think this was acceptable then, and I certainly don't condone
similar window dressing today. Postgresql doesn't need this kind of PR.

> P.S. I think we're in (quasi) violent agreement, don't you?

Well, we do seem to agree on some important points, like the fact that
statement-level aborts are important to have. We seem to be talking past
each other in other respects.

Let me get back to my contention that statement-level aborts, or atomicity
on the level of SQL statements, are considered an obvious requirement in
the database community. Because everybody assumes this to be the case, it
is hard to find it explicitly mentioned. Let me quote from Gray and
Reuter's "Transaction Processing", considered the bible on the subject by
some:

Even current database systems ... do not rely on nested transactions for
their own implementation. This is quite surprising, because nesting the
scope of commitment and backout is commonplace even in today's SQL
systems (although the user cannot influence it). This is particularly
true for SQL update statements that are executed as the subtransactions.
Think of this transaction as the root transaction and the SQL statements
as the subtransactions. Obviously, an SQL update statement commits its
modifications to the top-level transaction. If it fails (assume an
INSERT being rejected because a unique value condition is violated), it
is implicitly undone and so appears to be atomic even in the case of a
failure. In other words, update SQL statements have all the properties
of subtransactions, but since the implementation techniques are
typically ad hoc rather than guided by the idea of transaction nesting,
the general mechanism is not available for the application programmer.

Joachim

--
private: joachim(at)kraut(dot)bc(dot)ca (http://www.kraut.bc.ca)
work: joachim(at)mercury(dot)bc(dot)ca (http://www.mercury.bc.ca)