Ü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? http://archives.postgresql.org