SV: Unanswered questions about Postgre - Mailing list pgsql-general

Hi,

...
> > > That is what transactions are for. If any errors occur, then the
> > > transacction is aborted. You are supposed to use transactions when you
want
> > > either everything to occur (the whole transaction), or nothing, if an
> > > error occurs.

And thats wrong!

The caller should have a change to handle the error. Like if a "insert"
fails, you might want to use "update" instead. It should be the caller who
decides if the transaction should be aborted ("rollback") or not.

As it is now transactions are _totally_ useless with dba:s that serves more
than one client.

...
> > There is obviously no
> > reason why a transaction needs to be aborted for syntax errors.

Absolutely correct. It should be the caller who decides what he wants to do
with the transaction (rollback, or just continue as nothing happened).

...
> A bank is transferring money from one acount to another. Say the money
> leaves the first account (first update query), and then an error occurs
> when inserting the money into the second account (second update query). If
...

Schematic code snipped:

BEGIN;
update table account set credit = credit + 100;
if( error )
{
    insert into account (credit,debet) VALUES( 100,0 );
    if( error )
    {
        ROLLBACK;
        return FAILED;
    }
}
update table account set debet = debet + 100;
if( error )
{
    insert into account (credit, debet) VALUES( 0, 100 );
    if( error )
    {
        ROLLBACK;
        return FAILED;
    }
}
COMMIT;

That is the _correct_ way to do a bank transaction. And that is how
transactions should work.

...
> That is the whole point of transactions - they are used for an
> "all-or-nothing" approach.

Correct, but it should be the caller who decides what to do. Not the dba.

...
> The transaction succeeds, and you end up with two phones with the same
> number. BAD thing.

Your still wrong about the correct dba behaviour. It should be the callers
decision, not the dba.

> > nessasary.   If you don't believe me, here's two fully SQL-92 compliant
> > databases, Oracle and interbase, which do not exhibit this behavior:

I do not give a sh** about SQL9_. There are nothing that forbids a dba to be
better than something.

...
> So, what would you like to be the criteria for aborting or proceeding with
> a transaction?

dba should not try to guess what I want to do with a transaction. It should
repport all errors to me (the caller) and let me decide what to do with the
transaction, period.

...
> > > If you don't like this behaviour, then use auto-commit, and make every

And thats stupid.

...
> > grouping a set of statements and commiting them or rolling them back as
> > a whole.  I do not, however, want the transaction aborted by the server

Thats how it should be.

...
> > when it does not need to be.  Clearly in the above case, neither
> > interbase nor oracle decided that the transaction had to be aborted.

Neither does Sybase or MSSQL.

// Jarmo