Thread: AW: [HACKERS] TRANSACTIONS

AW: [HACKERS] TRANSACTIONS

From

Zeugswetter Andreas SB

Date:

23 February 2000, 06:54:25

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

Andreas

Re: [GENERAL] AW: [HACKERS] TRANSACTIONS

From

Wim Ceulemans

Date:

23 February 2000, 08:04:33

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

Wim

Re: [GENERAL] AW: [HACKERS] TRANSACTIONS

From

Jose Soares

Date:

24 February 2000, 06:49:56

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

Re: [GENERAL] AW: [HACKERS] TRANSACTIONS

From

Peter Eisentraut

Date:

24 February 2000, 14:19:01

On Thu, 24 Feb 2000, Jose Soares wrote:

> NOTICE:  (transaction aborted): all queries ignored until end of transaction block
>
> *ABORT STATE*

> Why PostgreSQL doesn't make an implicit ROLLBACK instead of waitting for a
> COMMIT/ROLLBACK ?

The PostgreSQL transaction paradigm seems to be that if you explicitly
start a transaction, you get to explicitly end it. This is of course at
odds with SQL, but it seems internally consistent to me. I hope that one
of these days we can offer the other behaviour as well.

> Why PostgreSQL allows a COMMIT in this case ?

Good question. I assume it doesn't actually commit though, does it? I
think a CHECK_IF_ABORTED (sp?) before calling the commit utility routine
would be appropriate. Anyone?

--
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Re: [GENERAL] AW: [HACKERS] TRANSACTIONS

From

Jose Soares

Date:

28 February 2000, 07:44:01

Peter Eisentraut wrote:

On Thu, 24 Feb 2000, Jose Soares wrote:
> NOTICE: (transaction aborted): all queries ignored until end of transaction block
>
> *ABORT STATE*
> Why PostgreSQL doesn't make an implicit ROLLBACK instead of waitting for a
> COMMIT/ROLLBACK ?
The PostgreSQL transaction paradigm seems to be that if you explicitly
start a transaction, you get to explicitly end it. This is of course at
odds with SQL, but it seems internally consistent to me. I hope that one
of these days we can offer the other behaviour as well.
> Why PostgreSQL allows a COMMIT in this case ?
Good question. I assume it doesn't actually commit though, does it? I
think a CHECK_IF_ABORTED (sp?) before calling the commit utility routine
would be appropriate. Anyone?

Seems that PostgreSQL has a basically difference from other databases, it has two operation modes
"transaction mode" and "non-transaction mode".
If you want initialize a transaction in PostgreSQL you must declare it by using the BEGIN WORK
statement and an END/ABORT/ROLLBACK/COMMIT statement to terminate the transaction and switch from "transaction mode" to "non-transaction mode".
The SQL92 doesn't have such statement like BEGIN WORK because when you initialize a connection to a database you are all the time in transaction mode.
Should it be the real problem with transactions ?

--
Peter Eisentraut                  Sernanders vaeg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden
************

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