Transactional DDL

On 8/14/07, Harpreet Dhaliwal <harpreet.dhaliwal01@gmail.com> wrote:

Hi,
I read a few lines about SP compilation in postgres

http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci1179016,00.html

1. stored procedure compilation is transactional.
"You can recompile a stored procedure on a live system, and only
transactions starting after that compilation will see the changes," he said.
"Transactions in process can complete with the old version. Oracle just
blocks on the busy procedure."

Is this what the Transactional DDL feature of postgresql talks about ?

That's just one of the DDLs that postgresql can handle in a
transaction. Basically, create / drop database and create / drop
tablespace aren't transactable. Anything else is fair game. Note
that wrapping alter table or reindex or truncate in a long running
transaction will likely lock the table for an unacceptable period of
time. But, putting a migration script that includes DDL and DML
together and wrapping it in begin; commit; pairs means that either it
all goes or none does, and the locks on alter table etc are only held
for the period it takes the migration script to run.

Oracle's lack of transactable DDL means you HAVE to take your system
down and have rollback scripts ready to go should your migration fail.
Having worked with both databases, I can honestly say this is one of
the areas PostgreSQL seriously beats Oracle in terms of usefulness.

"Harpreet Dhaliwal" <harpreet.dhaliwal01@gmail.com> writes:

I read a few lines about SP compilation in postgres
http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci1179016,00.html

Is this what the Transactional DDL feature of postgresql talks about ?

I'd say it's one very small aspect of what's involved in that.

Updates of stored procedures are a relatively trivial matter, because a
procedure is defined by just a single catalog entry (one row in
pg_proc). So either you see the new version or you see the old version,
not much to talk about. The DDL updates that are really interesting
... at least from an implementor's standpoint ... are the ones that
involve coordinated changes to multiple catalog entries and some
underlying filesystem files as well. In other words, ALTER TABLE.
There are not that many other systems that can choose to commit or roll
back an arbitrary collection of ALTER TABLE commands.

This doesn't come for free of course. What it mostly costs you in
Postgres-land is transient disk space requirements, since we have to
store both the "before" and "after" states until commit/rollback.

regards, tom lane

So you mean to say DDL statements can't be put in one single transaction in
Oracle ?

And this feature i.e. transactional DDL is not there in other major RDBMS
like sql server, oracle etc?

thanks
~Harpreet

On 8/14/07, Harpreet Dhaliwal <harpreet.dhaliwal01@gmail.com> wrote:

So you mean to say DDL statements can't be put in one single transaction in
Oracle ?

You can put them in, but then they will cause the previous DMK to be
silently committed

So you mean to say something like this as far as oracle is concerned:

BEGIN
DDL 1 (commits right after its execution)
DDL 2 (commits right after its execution)
END

That means there's no concept of putting DDL statements in a transaction in
oracle basically, right?

Thanks,
~Harpreet

Harpreet Dhaliwal wrote:

So you mean to say something like this as far as oracle is concerned:

BEGIN
DDL 1 (commits right after its execution)
DDL 2 (commits right after its execution)
END

That means there's no concept of putting DDL statements in a transaction
in oracle basically, right?

Yes.

//Magnus

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 08/15/07 00:05, Harpreet Dhaliwal wrote:

And this feature i.e. transactional DDL is not there in other major
RDBMS like sql server, oracle etc?

Define "major". Does it mean "popular" or "used on very large systems"?

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

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFGwp1GS9HxQb37XmcRAtdIAKC+7kG6K4WVxgGGSVT/AHcWCo6I8gCfZ9y5
bVcXkbWY4E9OzYss8g1i7Q4=
=/dqV
-----END PGP SIGNATURE-----

Harpreet Dhaliwal wrote:

And this feature i.e. transactional DDL is not there in other major
RDBMS like sql server, oracle etc?

You've had about 50 answers to that question already I think.

The answer is No.

--
Postgresql & php tutorials
http://www.designmagick.com/

Harpreet Dhaliwal wrote:

And this feature i.e. transactional DDL is not there in other major
RDBMS like sql server, oracle etc?

thanks
~Harpreet

<...snipped earlier postings...>

It surprised me when I saw Oracle's behavior. Informix supports DDL
within transactions quite happily:

create table foo22 (id int,myvalue varchar);
create table foo23 (id int);
begin;
alter table foo22 drop (myvalue);
alter table foo23 add (mynewvalue varchar);
rollback;

INFO - foo22: Columns Indexes Privileges References Status ...
Display column names and data types for a table.
----------------------- billing@arches_ip ------ Press CTRL-W for Help
--------
Column name Type Nulls
id integer yes
myvalue varchar(1,1) yes

AND
INFO - foo23: Columns Indexes Privileges References Status ...
Display column names and data types for a table.
----------------------- billing@arches_ip ------ Press CTRL-W for Help
--------
Column name Type Nulls
id integer yes

QED.

It's a strong point in PostgreSQL's favor that it behaves in what I
regard as a sane manner. That Oracle stuff makes me shudder -- it's unclean.

Greg Williamson
Senior DBA
GlobeXplorer LLC, a DigitalGlobe company

Confidentiality Notice: This e-mail message, including any attachments,
is for the sole use of the intended recipient(s) and may contain
confidential and privileged information and must be protected in
accordance with those provisions. Any unauthorized review, use,
disclosure or distribution is prohibited. If you are not the intended
recipient, please contact the sender by reply e-mail and destroy all
copies of the original message.

(My corporate masters made me say this.)

On Wednesday 15 August 2007 1:58:07 am Harpreet Dhaliwal wrote:

So you mean to say something like this as far as oracle is concerned:

BEGIN
DDL 1 (commits right after its execution)
DDL 2 (commits right after its execution)
END

That means there's no concept of putting DDL statements in a transaction in
oracle basically, right?

Even worse: In certain transaction isolation levels (READ COMMITTED and
SERIALIZABLE IIRC) it yells at you and doesn't perform the DDL command.

Which makes it virtually impossible to do dynamic DDL in any serious J2EE
application.

<rant>
God, how I hate Oracle.
</rant>

Thanks,
~Harpreet

jan

On 8/15/07, Harpreet Dhaliwal <harpreet.dhaliwal01@gmail.com> wrote:

And this feature i.e. transactional DDL is not there in other major RDBMS
like sql server, oracle etc?

The subject of transactional DDL and its prevalence was discussed in a
May thread, "why postgresql over other RDBMS"
(http://archives.postgresql.org/pgsql-general/2007-05/msg01151.php).

It was pointed out that Ingres, Firebird, InterBase and NonStop SQL
also supported transactional DDL, as did a couple of legacy database
systems now sold by Oracle -- no other products support transactional
DDL.

(You keep asking the list about novel features that supposedly make
PostgreSQL unique or superior. Still haven't found a thesis topic?)

Alexander.

you can use SET TRANSACTION LEVEL READ UNCOMMITTED to acquire the dirty
reads

From your perspective how *should* the DB handle this?

M
This email message and any files transmitted with it contain confidential
information intended only for the person(s) to whom this email message is
addressed. If you have received this email message in error, please notify
the sender immediately by telephone or email and destroy the original
message without making a copy. Thank you.

----- Original Message -----
From: "Jan de Visser" <jdevisser@digitalfairway.com>
To: <pgsql-general@postgresql.org>
Cc: "Harpreet Dhaliwal" <harpreet.dhaliwal01@gmail.com>; "Scott Marlowe"
<scott.marlowe@gmail.com>
Sent: Wednesday, August 15, 2007 5:15 AM
Subject: Re: [GENERAL] Transactional DDL

Scott Marlowe wrote:

On 8/14/07, Harpreet Dhaliwal <harpreet.dhaliwal01@gmail.com> wrote:

Hi,
I read a few lines about SP compilation in postgres

http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci1179016,00.html

1. stored procedure compilation is transactional.
"You can recompile a stored procedure on a live system, and only
transactions starting after that compilation will see the changes," he said.
"Transactions in process can complete with the old version. Oracle just
blocks on the busy procedure."

Really?

When I tried it [1]http://archives.postgresql.org/pgsql-bugs/2005-04/msg00163.php - changing a function definition during the
middle of a long-running-query that used the function gave
me the surprising result that some rows were processed using
the old definition of the function and some with the new one.

The explanation from Tom [2]http://archives.postgresql.org/pgsql-bugs/2005-04/msg00179.php was that there was some good
reason function lookups used SnapshotNow.

Ron

[1]: http://archives.postgresql.org/pgsql-bugs/2005-04/msg00163.php

I have a long query something like

select slow_function(col) from large_table;

and half way through the query, in a separate connection, I

CREATE OR REPLACE slow_function ....

I was surprised to see that some of the rows in my select
were processed by the old definition and some by the new.

[2]: http://archives.postgresql.org/pgsql-bugs/2005-04/msg00179.php

So is there really any version control mechanism of functions in postgresql
or not ?

~Harpreet

Ron Mayer schrieb:

Scott Marlowe wrote:

On 8/14/07, Harpreet Dhaliwal <harpreet.dhaliwal01@gmail.com> wrote:

Hi,
I read a few lines about SP compilation in postgres

http://searchoracle.techtarget.com/originalContent/0,289142,sid41_gci1179016,00.html

1. stored procedure compilation is transactional.
"You can recompile a stored procedure on a live system, and only
transactions starting after that compilation will see the changes," he said.
"Transactions in process can complete with the old version. Oracle just
blocks on the busy procedure."

Really?

When I tried it [1] - changing a function definition during the
middle of a long-running-query that used the function gave
me the surprising result that some rows were processed using
the old definition of the function and some with the new one.

The explanation from Tom [2] was that there was some good
reason function lookups used SnapshotNow.

Yes - if you want to see transactional DDL, put your
function change in a transaction. If you do that, you
will see your long running other transaction is seeing
the old definition the whole time (or shortly after
you commit the function changing transaction). This is
basically "read committed".

Regards
Tino