Transactional DDL

On 6/2/07, Jasbinder Singh Bali <jsbali@gmail.com> wrote:
[snip]

I believe that if a database supports transactional ddl then ddl1 and ddl2
would commit together as a batch
And
If a Db doesn't support this transactional DDL feature then ddl1 executes
and commits without even caring about ddl2. Right?

Exactly right -- Oracle, for example, implicitly commits the
transaction when you execute a DDL statement such as "create table".

Alexander.

But its said that transactions in any RDBMS follow ACID properties.
So if i put a create table and an Insert statement in the same begin end
block as one single transactioin, won't both create and insert follow acid
property, being in one single trasaction, and either both get committed or
none, talking about oracle lets say

Show quoted text

On 6/2/07, Alexander Staubo <alex@purefiction.net> wrote:

On 6/2/07, Jasbinder Singh Bali <jsbali@gmail.com> wrote:
[snip]

I believe that if a database supports transactional ddl then ddl1 and

ddl2

would commit together as a batch
And
If a Db doesn't support this transactional DDL feature then ddl1

executes

and commits without even caring about ddl2. Right?

Exactly right -- Oracle, for example, implicitly commits the
transaction when you execute a DDL statement such as "create table".

Alexander.

On Jun 2, 2007, at 10:12 , Jasbinder Singh Bali wrote:

But its said that transactions in any RDBMS follow ACID properties.
So if i put a create table and an Insert statement in the same
begin end block as one single transactioin, won't both create and
insert follow acid property, being in one single trasaction, and
either both get committed or none, talking about oracle lets say

test=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | a | table | postgres
public | b | table | postgres
(2 rows)

test=# begin;
BEGIN
test=# create table foo (a integer);
CREATE TABLE
test=# insert into foo (a) values (1);
INSERT 0 1
test=# commit;
COMMIT
test=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | a | table | postgres
public | b | table | postgres
public | foo | table | postgres
(3 rows)

test=# select * from foo;
a
---
1
(1 row)

test=# begin;
BEGIN
test=# create table bar (a integer);
CREATE TABLE
test=# insert into bar (a) values (1);
INSERT 0 1
test=# select * from bar;
a
---
1
(1 row)

test=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | a | table | postgres
public | b | table | postgres
public | bar | table | postgres
public | foo | table | postgres
(4 rows)

test=# select 1/0;
ERROR: division by zero
test=# commit;
ROLLBACK
test=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | a | table | postgres
public | b | table | postgres
public | foo | table | postgres
(3 rows)

Michael Glaesemann
grzm seespotcode net

Whats so novel about postgresql here?
This would happen in any RDBMS. right?
You induced divide by zero exception that crashed the whole transaction and
it did not create the table bar?
I can't see any Transactional DDL philosophy here.
Could you please throw some more light on it to point out the transactional
DDL fundamental as
pointed out by Jas?

Thanks,
~Harpreet

Show quoted text

On 6/2/07, Michael Glaesemann <grzm@seespotcode.net> wrote:

On Jun 2, 2007, at 10:12 , Jasbinder Singh Bali wrote:

But its said that transactions in any RDBMS follow ACID properties.
So if i put a create table and an Insert statement in the same
begin end block as one single transactioin, won't both create and
insert follow acid property, being in one single trasaction, and
either both get committed or none, talking about oracle lets say

test=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | a | table | postgres
public | b | table | postgres
(2 rows)

test=# begin;
BEGIN
test=# create table foo (a integer);
CREATE TABLE
test=# insert into foo (a) values (1);
INSERT 0 1
test=# commit;
COMMIT
test=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | a | table | postgres
public | b | table | postgres
public | foo | table | postgres
(3 rows)

test=# select * from foo;
a
---
1
(1 row)

test=# begin;
BEGIN
test=# create table bar (a integer);
CREATE TABLE
test=# insert into bar (a) values (1);
INSERT 0 1
test=# select * from bar;
a
---
1
(1 row)

test=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | a | table | postgres
public | b | table | postgres
public | bar | table | postgres
public | foo | table | postgres
(4 rows)

test=# select 1/0;
ERROR: division by zero
test=# commit;
ROLLBACK
test=# \d
List of relations
Schema | Name | Type | Owner
--------+------+-------+----------
public | a | table | postgres
public | b | table | postgres
public | foo | table | postgres
(3 rows)

Michael Glaesemann
grzm seespotcode net

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On 6/2/07, Jasbinder Singh Bali <jsbali@gmail.com> wrote:

But its said that transactions in any RDBMS follow ACID properties.
So if i put a create table and an Insert statement in the same begin end
block as one single transactioin, won't both create and insert follow acid
property, being in one single trasaction, and either both get committed or
none, talking about oracle lets say

Actually, Oracle inserts implicit COMMIT after each DDL.

So, if you have:

BEGIN;
INSERT INTO foo (bar) VALUES (1);
CREATE INDEX foo_bar ON foo (bar);
-- Here Oracle will insert implicit COMMIT, thus your foo table will
have value 1 commited.
-- And here Oracle will BEGIN a new trasaction.
INSERT INTO foo (bar) VALUES (2);
ROLLBACK;
-- And you will rollback the insert of value 2. Value 1 remains in the table,
-- because it is already committed.

Regards,
Dawid

So, while writing any technical document, would it be wrong to mention
stored procedures in postgresql?
what is the general convention?

Show quoted text

On 6/2/07, Dawid Kuroczko <qnex42@gmail.com> wrote:

On 6/2/07, Jasbinder Singh Bali <jsbali@gmail.com> wrote:

But its said that transactions in any RDBMS follow ACID properties.
So if i put a create table and an Insert statement in the same begin end
block as one single transactioin, won't both create and insert follow

acid

property, being in one single trasaction, and either both get committed

or

none, talking about oracle lets say

Actually, Oracle inserts implicit COMMIT after each DDL.

So, if you have:

BEGIN;
INSERT INTO foo (bar) VALUES (1);
CREATE INDEX foo_bar ON foo (bar);
-- Here Oracle will insert implicit COMMIT, thus your foo table will
have value 1 commited.
-- And here Oracle will BEGIN a new trasaction.
INSERT INTO foo (bar) VALUES (2);
ROLLBACK;
-- And you will rollback the insert of value 2. Value 1 remains in the
table,
-- because it is already committed.

Regards,
Dawid

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote:

Whats so novel about postgresql here?
This would happen in any RDBMS. right?
You induced divide by zero exception that crashed the whole
transaction and it did not create the table bar?

[Please don't top-post. It makes the discussion hard to follow.]

I used the divide by zero to raise an error to show that both the
CREATE TABLE and the INSERT were rolled back when the transaction
failed. If there's another definition of transactional DDL, I'd like
to know what it is.

Michael Glaesemann
grzm seespotcode net

On 6/2/07, Michael Glaesemann <grzm@seespotcode.net> wrote:

On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote:

Whats so novel about postgresql here?
This would happen in any RDBMS. right?
You induced divide by zero exception that crashed the whole
transaction and it did not create the table bar?

[Please don't top-post. It makes the discussion hard to follow.]

I used the divide by zero to raise an error to show that both the
CREATE TABLE and the INSERT were rolled back when the transaction
failed. If there's another definition of transactional DDL, I'd like
to know what it is.

Michael Glaesemann
grzm seespotcode net

This is what happens in every RDBMS. Whats so special about postgres then?

On 6/2/07, Jasbinder Singh Bali <jsbali@gmail.com> wrote:

On 6/2/07, Michael Glaesemann <grzm@seespotcode.net> wrote:

On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote:

Whats so novel about postgresql here?
This would happen in any RDBMS. right?
You induced divide by zero exception that crashed the whole
transaction and it did not create the table bar?

[Please don't top-post. It makes the discussion hard to follow.]

I used the divide by zero to raise an error to show that both the
CREATE TABLE and the INSERT were rolled back when the transaction
failed. If there's another definition of transactional DDL, I'd like
to know what it is.

Michael Glaesemann
grzm seespotcode net

This is what happens in every RDBMS. Whats so special about postgres then?

Exactly. this seems like proving the ACIC property of a database thats true
for every RDBMS.
Whats so different in postgresql then?

You were politely asked not to top-post.

On 06/02/07 11:46, Harpreet Dhaliwal wrote:

So, while writing any technical document, would it be wrong to mention
stored procedures in postgresql?
what is the general convention?

Did I miss something? What does "stored procedures" have to do with
"Transactional DDL"?

On 6/2/07, Dawid Kuroczko <qnex42@gmail.com> wrote:

On 6/2/07, Jasbinder Singh Bali <jsbali@gmail.com> wrote:

But its said that transactions in any RDBMS follow ACID properties.
So if i put a create table and an Insert statement in the same begin

end

block as one single transactioin, won't both create and insert follow

acid

property, being in one single trasaction, and either both get committed

or

none, talking about oracle lets say

Actually, Oracle inserts implicit COMMIT after each DDL.

So, if you have:

BEGIN;
INSERT INTO foo (bar) VALUES (1);
CREATE INDEX foo_bar ON foo (bar);
-- Here Oracle will insert implicit COMMIT, thus your foo table will
have value 1 commited.
-- And here Oracle will BEGIN a new trasaction.
INSERT INTO foo (bar) VALUES (2);
ROLLBACK;
-- And you will rollback the insert of value 2. Value 1 remains in the
table,
-- because it is already committed.

Regards,
Dawid

--
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

Harpreet Dhaliwal wrote:

On 6/2/07, *Jasbinder Singh Bali* <jsbali@gmail.com
<mailto:jsbali@gmail.com>> wrote:

On 6/2/07, *Michael Glaesemann* < grzm@seespotcode.net
<mailto:grzm@seespotcode.net>> wrote:

On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote:

Whats so novel about postgresql here?
This would happen in any RDBMS. right?
You induced divide by zero exception that crashed the whole
transaction and it did not create the table bar?

[Please don't top-post. It makes the discussion hard to follow.]

I used the divide by zero to raise an error to show that both the
CREATE TABLE and the INSERT were rolled back when the transaction
failed. If there's another definition of transactional DDL, I'd like
to know what it is.

Michael Glaesemann
grzm seespotcode net

This is what happens in every RDBMS.

No, it doesn't

Whats so special about postgres
then?

Exactly. this seems like proving the ACIC property of a database thats
true for every RDBMS.
Whats so different in postgresql then?

Try doing the same test in MySQL (using InnoDB so you get a supposedly
ACID compliant table type).

Or even in Oracle.

You'll find that the table create gets committed *implicitly*, and the
rollback will only rollback the insert, not the table create.

The point is that most RDBMS systems treat DDL a little differently and
force transaction commit when they are executed. Postgres does not.

On 06/02/07 13:35, Jasbinder Singh Bali wrote:

On 6/2/07, Michael Glaesemann <grzm@seespotcode.net> wrote:

On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote:

Whats so novel about postgresql here?
This would happen in any RDBMS. right?
You induced divide by zero exception that crashed the whole
transaction and it did not create the table bar?

[Please don't top-post. It makes the discussion hard to follow.]

I used the divide by zero to raise an error to show that both the
CREATE TABLE and the INSERT were rolled back when the transaction
failed. If there's another definition of transactional DDL, I'd like
to know what it is.

Michael Glaesemann
grzm seespotcode net

This is what happens in every RDBMS. Whats so special about postgres then?

But it's NOT what happens in every RDBMS. Oracle implicitly
executes a COMMIT after every DDL statement.

--
Ron Johnson, Jr.
Jefferson LA USA

Give a man a fish, and he eats for a day.
Hit him with a fish, and he goes away for good!

This is what happens in every RDBMS. Whats so special about postgres
then?

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE blehhhh ( id INTEGER ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO blehhhh VALUES (1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM blehhhh;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)

psql=> BEGIN;
BEGIN
psql=> CREATE TABLE blehhhh ( id INTEGER );
CREATE TABLE
psql=> INSERT INTO blehhhh VALUES (1),(2),(3);
INSERT 0 3
psql=> ROLLBACK;
ROLLBACK
psql=> SELECT * FROM blehhhh;
ERREUR: la relation «blehhhh» n'existe pas

Russ Brown <pickscrape@gmail.com> writes:

Harpreet Dhaliwal wrote:

Whats so different in postgresql then?

Try doing the same test in MySQL (using InnoDB so you get a supposedly
ACID compliant table type).

Or even in Oracle.

Examples (using mysql 5.0.40, reasonably current):

mysql> create table t1 (f1 int) engine = innodb;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+
| f1 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)

mysql> select * from t1;
+------+
| f1 |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
+------+
| f1 |
+------+
| 1 |
+------+
1 row in set (0.00 sec)

[ okay, so we can roll back an INSERT properly ]

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> drop table t1;
Query OK, 0 rows affected (0.01 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t1;
ERROR 1146 (42S02): Table 'test.t1' doesn't exist

[ oops, DROP TABLE isn't transactional ]

mysql> create table t1 (f1 int) engine = innodb;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(1);
Query OK, 1 row affected (0.00 sec)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into t1 values(2);
Query OK, 1 row affected (0.00 sec)

mysql> create table t2 (f2 int) engine = innodb;
Query OK, 0 rows affected (0.01 sec)

mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from t2;
Empty set (0.00 sec)

[ so CREATE TABLE isn't transactional, and what's more, now
the INSERT wasn't either: ]

mysql> select * from t1;
+------+
| f1 |
+------+
| 1 |
| 2 |
+------+
2 rows in set (0.00 sec)

So it appears that mysql works just like Oracle on this point:
a DDL operation forces an immediate COMMIT.

regards, tom lane

On Saturday 2. June 2007 20:39, Ron Johnson wrote:

You were politely asked not to top-post.

On 06/02/07 11:46, Harpreet Dhaliwal wrote:

So, while writing any technical document, would it be wrong to
mention stored procedures in postgresql?
what is the general convention?

Did I miss something? What does "stored procedures" have to do with
"Transactional DDL"?

I believe that he posted this in reply to the "Stored procedures and
functions" thread. It kind of fits in there.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

my bad.. i replied to that in a wrong thread. sorry

Show quoted text

On 6/2/07, Leif B. Kristensen <leif@solumslekt.org> wrote:

On Saturday 2. June 2007 20:39, Ron Johnson wrote:

You were politely asked not to top-post.

On 06/02/07 11:46, Harpreet Dhaliwal wrote:

So, while writing any technical document, would it be wrong to
mention stored procedures in postgresql?
what is the general convention?

Did I miss something? What does "stored procedures" have to do with
"Transactional DDL"?

I believe that he posted this in reply to the "Stored procedures and
functions" thread. It kind of fits in there.
--
Leif Biberg Kristensen | Registered Linux User #338009
http://solumslekt.org/ | Cruising with Gentoo/KDE
My Jazz Jukebox: http://www.last.fm/user/leifbk/

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

On 6/2/07, PFC <lists@peufeu.com> wrote:

This is what happens in every RDBMS. Whats so special about postgres
then?

mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)

mysql> CREATE TABLE blehhhh ( id INTEGER ) ENGINE=InnoDB;
Query OK, 0 rows affected (0.09 sec)

mysql> INSERT INTO blehhhh VALUES (1),(2),(3);
Query OK, 3 rows affected (0.02 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> ROLLBACK;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT * FROM blehhhh;
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
+------+
3 rows in set (0.00 sec)

Tom's example seems to show that mysql inserts a commit immidiatelly
after a DDL but this one example shows the thing is worse than that.
if that is the case this 3 rows should have been gone with the
rollback.

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook

On 6/2/07, *Jasbinder Singh Bali* <jsbali@gmail.com
<mailto:jsbali@gmail.com>> wrote:

On 6/2/07, *Michael Glaesemann* < grzm@seespotcode.net
<mailto:grzm@seespotcode.net>> wrote:

On Jun 2, 2007, at 11:08 , Harpreet Dhaliwal wrote:

Whats so novel about postgresql here?
This would happen in any RDBMS. right?
You induced divide by zero exception that crashed the whole
transaction and it did not create the table bar?

No, it doesn't

then informix is better than oracle in this point. last time i try
this on informix it did the right thing...

sadly enough, i don't have an informix database at hand to confirm if
my memory has no corrupted indexes ;)

--
regards,
Jaime Casanova

"Programming today is a race between software engineers striving to
build bigger and better idiot-proof programs and the universe trying
to produce bigger and better idiots.
So far, the universe is winning."
Richard Cook

"Jaime Casanova" <systemguards@gmail.com> writes:

Tom's example seems to show that mysql inserts a commit immidiatelly
after a DDL but this one example shows the thing is worse than that.

Actually, I think their behavior is just "DDL issues a COMMIT", so that
after that you are out of the transaction and the INSERT commits
immediately. Some experimentation shows that mysql doesn't issue a
warning for rollback-outside-a-transaction, so the lack of any complaint
at the rollback step is just standard mysql-ism.

regards, tom lane