Re: Transactional DDL

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

On 8/15/07, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> wrote:
>
> On 8/14/07, Harpreet Dhaliwal <harpreet(dot)dhaliwal01(at)gmail(dot)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.
>