Re: Revisited: Transactions, insert unique.

On Mon, Apr 24, 2000 at 09:23:48PM -0700, Joachim Achtzehnter wrote:
>
> 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.

Hmm, sounds like your moving the goal posts: now you want checkpoints,
no? The problem under discussion is allowing the tranaction to procede
when an error occurs that both the programmer and the DBMS knows leaves
the system in a consistent state: a simple SQL parse error, for example,
or an attempt to violate a 'unique' constraint on an INSERT. Peter
Eisentraut already offered a very high level, potential dangerous one
line patch, to deal with the 'table not found' case. Digging into the
code a little, I think this will also handle the SQL parse error case
correctly, but may have a problem with constaint case: I've recompiling
code right now to test it. ;-) (Later) Hmm, not good: the bad tuple ends
up going live, not immediately, but after the next select. I think I
better find that old message and follow up with this info, so it's in the
mailing list archives, at least.

>
> > 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.

Right, but often one is in a situation where technical requirements are
being enforced by beaurocracies for non-technical reasons: if I _know_
that the product is good enough for the task at hand, and there exists no
mechanism to get an exception to the requirement, 'technical compliance'
serves my purposes. Basically, if management is going to play games, I'm
going to outplay them, if I have to.

>
> > 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.
>

The difference between the Oracle case, and the Postgresql case is
that the corporation tried to give you that twisting of the standard
as the _only_ answer: What I'm saying is, well, technically, this _is_
compliant, but yes, it's not the right way to do it, and we're working
on it, and by the way, how do you want it to work, and here's the code,
if we can't get to it fast enough, maybe you can?

I'm just asking you not to make Postgresql jump a higher _political_
bar than its competitors. Hmm, I find it odd to be on the 'practical,
business' side of one of these arguments: I'm usually the one pushing
the 'theoretical, academic' side.

One thing I should mention: PostgreSQL's overall transaction semantics
_are not_ SQL92 standard compliant, in that the client must explicitly
start the first transaction. the 'autocommit' mode is a non-SQL92
extension, which is also commonly available in other systems. Having it
be the default is non-standard, however.

>
> 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.
>

Doesn't this quote mearly bemoan the fact that nesting of transactions is
_not_ a general requirement of "even today's SQL systems"? I can't follow
the example, which seems to move back and forth between an UPDATE and an
INSERT statement. Are they suggesting that the UPDATE could be implemented
as a nested transaction containing a DELETE and an INSERT, and if the
INSERT fails, the DELETE must rollback? More likely, I suppose, is that
the context is of an overall transaction, and by 'SQL update statements'
they mean any SQL statement that alters the data in the tables. With that
interpretation, I agree it supports your argument that "even today's SQL
systems" (I can hear their academic disdain in that, can't you?) support
an "ad hoc" form of transaction nesting, which postgresql does not.

Hmm, on further reflection, I'm going to come over to your side of this
one: while I reserve the right to twist the standards for those who play
standards compliance games, I will agree that much of the verbage in the
standard make the _most_ sense when statement level aborts are assumed
to be present.

Ross
P.S. Thanks for having the patience to bring me around on this one.

--
Ross J. Reedstrom, Ph.D., <reedstrm(at)rice(dot)edu>
NSBRI Research Scientist/Programmer
Computer and Information Technology Institute
Rice University, 6100 S. Main St., Houston, TX 77005