So you mean to say DDL statements can't be put in one single transaction in Oracle ?
On 8/15/07, Scott Marlowe <[EMAIL PROTECTED]> wrote: > > On 8/14/07, Harpreet Dhaliwal <[EMAIL PROTECTED]> 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. >