Re: CREATE INDEX and HOT - revised design

Ühel kenal päeval, K, 2007-03-21 kell 14:06, kirjutas Merlin Moncure:
> 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.

What I would really like is an official way to promote an UNIQUE index
to Primary Key, so that PK could also be added without locking the table
for long periods.

> > 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.

we don't have stored procedures :( only functions.

Maybe your problem will be fixed once we get proper stored procedures,
which can begin and commit/rollback transactions inside their code.

> 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.

I don't think that any of the HOT restrictions apply when creating an
index on an empty table.

--
----------------
Hannu Krosing
Database Architect
Skype Technologies OÜ
Akadeemia tee 21 F, Tallinn, 12618, Estonia

Skype me: callto:hkrosing
Get Skype for free: http://www.skype.com