Re: CREATE INDEX and HOT - revised design

On 3/21/07, Florian G. Pflug <fgp(at)phlo(dot)org> wrote:
> Pavan Deolasee wrote:
> > On 3/21/07, Merlin Moncure <mmoncure(at)gmail(dot)com> wrote:
> >>
> >> On 3/21/07, Pavan Deolasee <pavan(dot)deolasee(at)gmail(dot)com> wrote:
> >> > It seems much simpler to me do something like this. But important
> >> > question is whether the restriction that CREATE INDEX can not
> >> > be run in a transaction block is acceptable ?
> >>
> >> yikes -- this is huge, huge price to pay, IMHO. Think about DDL that
> >> implies index creation such as adding unique constraint to a table,
> >> many postgresql users (including me) take advantage of that in update
> >> systems to production databases.
> >>
> > I didn't understand that quite well. How does it help to run CREATE
> > INDEX inside a transaction ?
> The problem is not so much CREATE INDEX per se, but other DDL commands
> that implicitly create an index, for example adding a PRIMARY KEY to a
> table.
>
> Some applications that I have written would fail badly if CREATE INDEX
> was disallowed inside a transaction - mostly, because they use plpgsql
> functions to manipulate database objects, and disallowing CREATE INDEX
> inside a transaction presumably means disallowing it from inside
> stored procedures.

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.

merlin