Re: Transactional DDL

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.