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 inconvenience.
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. Thanks, Pavan -- EnterpriseDB http://www.enterprisedb.com