On 3/22/07, Csaba Nagy <[EMAIL PROTECTED]> wrote:

> speaking with pavan off list he seems to think that only 'create
> index' is outside transaction, not the other ddl flavors of it because
> they are generally acquiring a excl lock.  so, in that sense it is
> possibly acceptable to me although still a pretty tough pill to
> swallow (thinking, guc time).  It would also preclude ever integrating
> vanilla 'create index' to create table command, fwiw.

Just to signal that it is in use: we did use create index in
transactions occasionally when we had to do DB schema upgrade on
production systems for application upgrades which span multiple versions
of our application (normally we upgrade versions one by one, but we have
some systems which are upgraded rarely). In these occasions it was
riskier than usually to run the cumulated upgrade scripts outside a
transaction block.

But that was mostly a convenience feature, we could always rearrange our
upgrade scripts to do all the rest first and then all the index creation
at the end if all the rest succeeded... but if implicit index creation
fails (e.g. when adding a new field to a table which happens also to be
a primary key) inside the transaction, that would hurt... mostly in more
work/more risks of extended downtime, but it will have a factor of

What I am hearing from many users is that its probably not such
a nice thing to put such restriction. Thats fair. It really helps to think
about a solution once you know what is acceptable and what is not.

I am back to the drawing board.



EnterpriseDB     http://www.enterprisedb.com

Reply via email to