Re: CREATE INDEX and HOT - revised design

On 3/22/07, Pavan Deolasee <pavan.deolasee@gmail.com> wrote:
> On 3/22/07, Csaba Nagy <nagy@ecircle-ag.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.

The objections to 'create index' forced to being non-transactional
come from what I can see two general cases:
* update scripts
* functions that create tables, etc

ISTM that in both cases nobody would complain too much if the lock was
escalated to exclusive lock in those cases.  So, maybe an alternative
solution is this:

* Make create index/create index concurrently extra-transactional as
Pavan suggested.
* Introduce index creation ability to alter/create table.  If as Pavan
suggests this integrates well with HOT due to excl lock, alter table
can be left transactional.  Now, there is at least alternative path to
take in use cases previously covered by create index + transaction.
now, there is a clean break between classic DDL (alter table, etc) and
non-transactional maintenance commands, like vacuum which create index
becomes part of.

merlin