Re: CREATE INDEX and HOT - revised design

On 3/22/07, Csaba Nagy <nagy(at)ecircle-ag(dot)com> 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