Lists: | pgsql-general |
---|
From: | "Jasbinder Singh Bali" <jsbali(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Transactional DDL |
Date: | 2007-06-02 14:45:14 |
Message-ID: | a47902760706020745i4e0bf505s5007d8b256bc6bfa@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | "Alexander Staubo" <alex(at)purefiction(dot)net> |
---|---|
To: | "Jasbinder Singh Bali" <jsbali(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transactional DDL |
Date: | 2007-06-02 14:51:20 |
Message-ID: | 88daf38c0706020751h49a99f7bifae3fce84ffebaed@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 6/2/07, Jasbinder Singh Bali <jsbali(at)gmail(dot)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.
From: | "Jasbinder Singh Bali" <jsbali(at)gmail(dot)com> |
---|---|
To: | "Alexander Staubo" <alex(at)purefiction(dot)net> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transactional DDL |
Date: | 2007-06-02 15:12:44 |
Message-ID: | a47902760706020812q7867948cv37dbcf228a5627c4@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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 6/2/07, Alexander Staubo <alex(at)purefiction(dot)net> wrote:
>
> On 6/2/07, Jasbinder Singh Bali <jsbali(at)gmail(dot)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.
>
From: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
---|---|
To: | Jasbinder Singh Bali <jsbali(at)gmail(dot)com> |
Cc: | "Alexander Staubo" <alex(at)purefiction(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transactional DDL |
Date: | 2007-06-02 15:50:26 |
Message-ID: | 80F4E980-C713-4E80-BFA4-C5D870E00FC6@seespotcode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | "Harpreet Dhaliwal" <harpreet(dot)dhaliwal01(at)gmail(dot)com> |
---|---|
To: | "Michael Glaesemann" <grzm(at)seespotcode(dot)net> |
Cc: | "Jasbinder Singh Bali" <jsbali(at)gmail(dot)com>, "Alexander Staubo" <alex(at)purefiction(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transactional DDL |
Date: | 2007-06-02 16:08:31 |
Message-ID: | d86a77ef0706020908n17b1b72ej8ed53b2e26bbda3c@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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, Michael Glaesemann <grzm(at)seespotcode(dot)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
>
From: | "Dawid Kuroczko" <qnex42(at)gmail(dot)com> |
---|---|
To: | "Jasbinder Singh Bali" <jsbali(at)gmail(dot)com> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transactional DDL |
Date: | 2007-06-02 16:31:19 |
Message-ID: | 758d5e7f0706020931i3aaadb35h225fbc274ef7b878@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 6/2/07, Jasbinder Singh Bali <jsbali(at)gmail(dot)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
From: | "Harpreet Dhaliwal" <harpreet(dot)dhaliwal01(at)gmail(dot)com> |
---|---|
To: | "Dawid Kuroczko" <qnex42(at)gmail(dot)com> |
Cc: | "Jasbinder Singh Bali" <jsbali(at)gmail(dot)com>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transactional DDL |
Date: | 2007-06-02 16:46:54 |
Message-ID: | d86a77ef0706020946l3c334982u8dd80ad00fc15ec1@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
So, while writing any technical document, would it be wrong to mention
stored procedures in postgresql?
what is the general convention?
On 6/2/07, Dawid Kuroczko <qnex42(at)gmail(dot)com> wrote:
>
> On 6/2/07, Jasbinder Singh Bali <jsbali(at)gmail(dot)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/
>
From: | Michael Glaesemann <grzm(at)seespotcode(dot)net> |
---|---|
To: | "Harpreet Dhaliwal" <harpreet(dot)dhaliwal01(at)gmail(dot)com> |
Cc: | "Jasbinder Singh Bali" <jsbali(at)gmail(dot)com>, "Alexander Staubo" <alex(at)purefiction(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transactional DDL |
Date: | 2007-06-02 17:51:25 |
Message-ID: | E13618D4-93C4-4CB6-B877-9850678A4271@seespotcode.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | "Jasbinder Singh Bali" <jsbali(at)gmail(dot)com> |
---|---|
To: | "Michael Glaesemann" <grzm(at)seespotcode(dot)net> |
Cc: | "Harpreet Dhaliwal" <harpreet(dot)dhaliwal01(at)gmail(dot)com>, "Alexander Staubo" <alex(at)purefiction(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transactional DDL |
Date: | 2007-06-02 18:35:58 |
Message-ID: | a47902760706021135w45934546mdf4d583f3980a946@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 6/2/07, Michael Glaesemann <grzm(at)seespotcode(dot)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?
From: | "Harpreet Dhaliwal" <harpreet(dot)dhaliwal01(at)gmail(dot)com> |
---|---|
To: | "Jasbinder Singh Bali" <jsbali(at)gmail(dot)com> |
Cc: | "Michael Glaesemann" <grzm(at)seespotcode(dot)net>, "Alexander Staubo" <alex(at)purefiction(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transactional DDL |
Date: | 2007-06-02 18:37:32 |
Message-ID: | d86a77ef0706021137u7d730195v5bf09b393b3e0385@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 6/2/07, Jasbinder Singh Bali <jsbali(at)gmail(dot)com> wrote:
>
>
>
> On 6/2/07, Michael Glaesemann <grzm(at)seespotcode(dot)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?
From: | Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transactional DDL |
Date: | 2007-06-02 18:39:10 |
Message-ID: | 4661B94E.7050904@cox.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)gmail(dot)com> wrote:
>>
>> On 6/2/07, Jasbinder Singh Bali <jsbali(at)gmail(dot)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!
From: | Russ Brown <pickscrape(at)gmail(dot)com> |
---|---|
To: | Postgres general mailing list <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Transactional DDL |
Date: | 2007-06-02 18:52:54 |
Message-ID: | 4661BC86.1000000@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Harpreet Dhaliwal wrote:
>
>
> On 6/2/07, *Jasbinder Singh Bali* <jsbali(at)gmail(dot)com
> <mailto:jsbali(at)gmail(dot)com>> wrote:
>
>
>
> On 6/2/07, *Michael Glaesemann* < grzm(at)seespotcode(dot)net
> <mailto:grzm(at)seespotcode(dot)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.
From: | Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transactional DDL |
Date: | 2007-06-02 18:55:27 |
Message-ID: | 4661BD1F.5090005@cox.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 06/02/07 13:35, Jasbinder Singh Bali wrote:
> On 6/2/07, Michael Glaesemann <grzm(at)seespotcode(dot)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!
From: | PFC <lists(at)peufeu(dot)com> |
---|---|
To: | "Harpreet Dhaliwal" <harpreet(dot)dhaliwal01(at)gmail(dot)com>, "Jasbinder Singh Bali" <jsbali(at)gmail(dot)com> |
Cc: | "Michael Glaesemann" <grzm(at)seespotcode(dot)net>, "Alexander Staubo" <alex(at)purefiction(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transactional DDL |
Date: | 2007-06-02 19:17:49 |
Message-ID: | op.tta6jzljcigqcu@apollo13 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
>> 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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | Russ Brown <pickscrape(at)gmail(dot)com> |
Cc: | Postgres general mailing list <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Transactional DDL |
Date: | 2007-06-02 19:18:25 |
Message-ID: | 26227.1180811905@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Russ Brown <pickscrape(at)gmail(dot)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
From: | "Leif B(dot) Kristensen" <leif(at)solumslekt(dot)org> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transactional DDL |
Date: | 2007-06-02 19:52:13 |
Message-ID: | 200706022152.14013.leif@solumslekt.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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/
From: | "Harpreet Dhaliwal" <harpreet(dot)dhaliwal01(at)gmail(dot)com> |
---|---|
To: | "Leif B(dot) Kristensen" <leif(at)solumslekt(dot)org> |
Cc: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transactional DDL |
Date: | 2007-06-02 20:51:13 |
Message-ID: | d86a77ef0706021351h7512e701k6d503de479cb81f@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
my bad.. i replied to that in a wrong thread. sorry
On 6/2/07, Leif B. Kristensen <leif(at)solumslekt(dot)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
>
From: | "Jaime Casanova" <systemguards(at)gmail(dot)com> |
---|---|
To: | PFC <lists(at)peufeu(dot)com> |
Cc: | "Harpreet Dhaliwal" <harpreet(dot)dhaliwal01(at)gmail(dot)com>, "Jasbinder Singh Bali" <jsbali(at)gmail(dot)com>, "Michael Glaesemann" <grzm(at)seespotcode(dot)net>, "Alexander Staubo" <alex(at)purefiction(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transactional DDL |
Date: | 2007-06-02 22:46:43 |
Message-ID: | c2d9e70e0706021546h6881345doa2c2d57e62b27801@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 6/2/07, PFC <lists(at)peufeu(dot)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
From: | "Jaime Casanova" <systemguards(at)gmail(dot)com> |
---|---|
To: | "Russ Brown" <pickscrape(at)gmail(dot)com> |
Cc: | "Postgres general mailing list" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Transactional DDL |
Date: | 2007-06-02 22:51:15 |
Message-ID: | c2d9e70e0706021551u294480advf446fece78e41962@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
> > On 6/2/07, *Jasbinder Singh Bali* <jsbali(at)gmail(dot)com
> > <mailto:jsbali(at)gmail(dot)com>> wrote:
> >
> > On 6/2/07, *Michael Glaesemann* < grzm(at)seespotcode(dot)net
> > <mailto:grzm(at)seespotcode(dot)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
From: | Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> |
---|---|
To: | "Jaime Casanova" <systemguards(at)gmail(dot)com> |
Cc: | PFC <lists(at)peufeu(dot)com>, "Harpreet Dhaliwal" <harpreet(dot)dhaliwal01(at)gmail(dot)com>, "Jasbinder Singh Bali" <jsbali(at)gmail(dot)com>, "Michael Glaesemann" <grzm(at)seespotcode(dot)net>, "Alexander Staubo" <alex(at)purefiction(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transactional DDL |
Date: | 2007-06-02 23:39:11 |
Message-ID: | 28530.1180827551@sss.pgh.pa.us |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
"Jaime Casanova" <systemguards(at)gmail(dot)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
From: | PFC <lists(at)peufeu(dot)com> |
---|---|
To: | "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "Jaime Casanova" <systemguards(at)gmail(dot)com> |
Cc: | "Harpreet Dhaliwal" <harpreet(dot)dhaliwal01(at)gmail(dot)com>, "Jasbinder Singh Bali" <jsbali(at)gmail(dot)com>, "Michael Glaesemann" <grzm(at)seespotcode(dot)net>, "Alexander Staubo" <alex(at)purefiction(dot)net>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transactional DDL |
Date: | 2007-06-03 07:41:58 |
Message-ID: | op.ttb4z80gcigqcu@apollo13 |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Sun, 03 Jun 2007 01:39:11 +0200, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> "Jaime Casanova" <systemguards(at)gmail(dot)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
From: | David Fetter <david(at)fetter(dot)org> |
---|---|
To: | Harpreet Dhaliwal <harpreet(dot)dhaliwal01(at)gmail(dot)com> |
Cc: | "Leif B(dot) Kristensen" <leif(at)solumslekt(dot)org>, pgsql-general(at)postgresql(dot)org |
Subject: | Re: Transactional DDL |
Date: | 2007-06-04 21:15:19 |
Message-ID: | 20070604211519.GD20215@fetter.org |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)solumslekt(dot)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(at)fetter(dot)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