Re: CREATE INDEX and HOT - revised design - Mailing list pgsql-hackers

From Hannu Krosing
Subject Re: CREATE INDEX and HOT - revised design
Date
Msg-id 1174638961.3826.31.camel@localhost.localdomain
Whole thread Raw
In response to Re: CREATE INDEX and HOT - revised design  ("Merlin Moncure" <mmoncure@gmail.com>)
List pgsql-hackers
Ühel kenal päeval, K, 2007-03-21 kell 14:06, kirjutas Merlin Moncure:
> On 3/21/07, Florian G. Pflug <fgp@phlo.org> wrote:
> > Pavan Deolasee wrote:
> > > On 3/21/07, Merlin Moncure <mmoncure@gmail.com> wrote:
> > >>
> > >> On 3/21/07, Pavan Deolasee <pavan.deolasee@gmail.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




pgsql-hackers by date:

Previous
From: Hannu Krosing
Date:
Subject: Re: CREATE INDEX and HOT - revised design
Next
From: "Pavan Deolasee"
Date:
Subject: Re: CREATE INDEX and HOT - revised design