Re: [GENERAL] AW: [HACKERS] TRANSACTIONS - Mailing list pgsql-general

Wim Ceulemans wrote:

> Zeugswetter Andreas SB wrote:
> >
> > > Jose Soares <jose@sferacarta.com> writes:
> > > > -------------------------------------------------------
> > > > Interbase, Oracle,Informix,Solid,Ms-Access,DB2:
> > > > -------------------------------------------------------
> > > > connect  hygea.gdb;
> > > > create table temp(a int);
> > > > insert into temp values (1);
> > > > insert into temp values (1000000000000000000000000000000000);
> > > > commit;
> > > > select * from temp;
> > >
> > > > arithmetic exception, numeric overflow, or string truncation
> > >
> > > >           A
> > > > ===========
> > > >           1
> > >
> > > > I would like to know what the Standard says and who is in the rigth path
> > > > PostgreSQL or the others, considering the two examples reported below.
> > >
> > > I think those other guys are unquestionably failing to
> > > conform to SQL92.
> > > 6.10 general rule 3.a says
> >
> > All others also throw an error for this statement, and thus conform.
> > As you can see from the select only the first row is inserted.
> > I think the numeric is only an example of an error, it could also be
> > any other error, like "duplicate key" or the like.
> >
> > > ......
> > >
> > > and 3.3.4.1 says
> > >
> > >          The phrase "an exception condition is raised:", followed by the
> > >          name of a condition, is used in General Rules and elsewhere to
> > >          indicate that the execution of a statement is unsuccessful, ap-
> > >          plication of General Rules, other than those of Subclause 12.3,
> > >          "<procedure>", and Subclause 20.1, "<direct SQL statement>", may
> > >          be terminated, diagnostic information is to be made available,
> > >          and execution of the statement is to have no effect on SQL-data
> > or
> >
> > Note here, that they say "the statement", which does not say anything about
> > other statements in the same transaction.
> >
> > >          schemas. The effect on <target specification>s and SQL descriptor
> > >          areas of an SQL-statement that terminates with an exception
> > condi-
> > >          tion, unless explicitly defined by this International Standard,
> > is
> > >          implementation-dependent.
> > >
> > > I see no way that allowing the transaction to commit after an overflow
> > > can be called consistent with the spec.
> >
> > Of course it can not commit this single statement that was in error.
> > All he wants is to commit all other statements, before and after the
> > error statement inside this same transaction.
> >
>
> Isn't the intention of a transaction that it is atomic, i.e. either all
> statements pass or none of them? (see section 5.4 in the standard).
>
>

There's another problem, in the following example the transaction il failed but
the transation it is not automatically rolledback, it remains instead in an "ABORT
STATE" waitting for an explicit ROLLBACK or COMMIT.
If I'm using transactions from a client program I don't know what's happened to
the back end.


first example:
^^^^^^^^^^
prova=> begin work;
BEGIN
prova=> create table tmp(a int);
ERROR:  Relation 'tmp' already exists
prova=> drop table tmp;
NOTICE:  (transaction aborted): all queries ignored until end of transaction block

*ABORT STATE*
prova=> commit;
END
-----------------------------------------------------------------------
What is happening ?
Why PostgreSQL doesn't make an implicit ROLLBACK instead of waitting for a
COMMIT/ROLLBACK ?
Why PostgreSQL allows a COMMIT in this case ?


second example:
^^^^^^^^

prova=> begin;
BEGIN
prova=> create table tmp(a int);
CREATE
prova=> create table tmp(a int);
ERROR:  Relation 'tmp' already exists
prova=> select * from tmp;
ERROR:  mdopen: couldn't open tmp: No such file or directory
prova=> commit;
END
prova=> select * from tmp;
ERROR:  tmp: Table does not exist.
-----------------------------------------------------------------------
What is happening ?
Apparently the transaction was successful but the TMP table doesn't exist  after a
successful COMMIT.
Why PostgreSQL allows a COMMIT in this case ?
Why in this case PostgreSQL doesn't show the:
        NOTICE:  (transaction aborted): all queries ignored until end of
transaction block
        *ABORT STATE*

--
Jose' Soares
Bologna, Italy                     Jose@sferacarta.com