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.
