Ühel kenal päeval, K, 2007-03-21 kell 14:06, kirjutas Merlin Moncure:
> On 3/21/07, Florian G. Pflug <[EMAIL PROTECTED]> wrote:
> > Pavan Deolasee wrote:
> > > On 3/21/07, Merlin Moncure <[EMAIL PROTECTED]> wrote:
> > >>
> > >> On 3/21/07, Pavan Deolasee <[EMAIL PROTECTED]> 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

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?


Reply via email to