Thread: Transactional DDL

Hi,

A few days back, it was commented by someone in the community that Postgres has
this Transactional DDL feature.
What I understand about Transactional DDL is something like this:

begin
       --ddl 1
       --ddl 2
end;

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?

~Jas

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

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

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?

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

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

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

On Sun, 03 Jun 2007 01:39:11 +0200, Tom Lane <tgl@sss.pgh.pa.us> wrote:

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

    Yes, their manual explains this and warns against it. The full list is
here :

http://www.mysql.org/doc/refman/5.1/en/implicit-commit.html

On Sat, Jun 02, 2007 at 04:51:13PM -0400, Harpreet Dhaliwal wrote:
> my bad.. i replied to that in a wrong thread. sorry

That is one of many reasons that smart people don't top post.  Had you
decided not to top post, you would have realized instantly that you
were in the wrong thread.

If there is a word or phrase in the above that you do not understand,
please feel free to ask, but blithely continuing to top post will get
you a reputation you don't want.

Regards,
David.
>
> 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
> >

--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!
Consider donating to PostgreSQL: http://www.postgresql.org/about/donate