Re: [GENERAL] TRANSACTIONS

At 12:47 PM 22-02-2000 +0100, Jose Soares wrote:
>begin transaction;
>create table tmp(a int);
>insert into tmp values (1);
>insert into tmp values (1000000000000000000000000000000000);
>ERROR:  pg_atoi: error reading "1000000000000000000000000000000000":
>Numerical result out of range
>commit;
>select * from tmp;
>ERROR:  tmp: Table does not exist.
>-------------------------------------------------------
>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

Stuff done in a transaction cannot be committed if there is an error. So
looks like Postgres is right and the rest are wrong ;).

Also I believe Oracle does a commit behind your back whenever you do a
create table or stuff like that.

However I did have problems rolling back a create table in Postgres before-
after rolling back I could not recreate a table of the same name. I had to
manually unlink the table at filesystem level. Not sure if that has been
fixed.

On a different note I wonder if there could be layers of transactions
(without having to create two separate connections)..

Begin transaction A
Try to do transaction B
Depending on whether B succeeds or fails we do the following stuff differently
blahblahblah
If blahblablah fails then rollback the whole thingy, including nested
transaction B (even if "committed")
commit transaction A

Sounds like a headache to implement tho (performance hits etc), and
probably more an academic feature than anything. So I'm just wondering just
for the sake of wondering ;). If we go that way lots of people will have a
new toy to play with (to sell as well) and things will get even more
complex.. <grin>.

Cheerio,

Link.