Skip site navigation (1) Skip section navigation (2)

Re: CREATE INDEX and HOT - revised design

From: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
To: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: "Pavan Deolasee" <pavan(dot)deolasee(at)gmail(dot)com>,"Florian G(dot) Pflug" <fgp(at)phlo(dot)org>,"Bruce Momjian" <bruce(at)momjian(dot)us>,"PostgreSQL-development" <pgsql-hackers(at)postgresql(dot)org>,"Pavan Deolasee" <pavan(dot)deolasee(at)enterprisedb(dot)com>
Subject: Re: CREATE INDEX and HOT - revised design
Date: 2007-03-29 22:01:47
Message-ID: (view raw, whole thread or download thread mbox)
Lists: pgsql-hackers
On Thu, 2007-03-29 at 17:27 -0400, Tom Lane wrote:
> "Simon Riggs" <simon(at)2ndquadrant(dot)com> writes:
> > ISTM that the run-another-transaction-afterwards idea is the only one
> > that does everything I think we need. I really do wish we could put in a
> > wait, like CIC, but I just think it will break existing programs.
> Actually, there's a showstopper objection to that: plain CREATE INDEX
> has to be able to run within a larger transaction.  (To do otherwise
> breaks "pg_dump --single-transaction", just for starters.)  This means
> it can *not* commit partway through.

I agree with most of that, but thats why we-are-where-we-are and I don't
think its a showstopper at all.

The idea is to make note that the transaction has created an index
within a transaction block, so that after the top level transaction
commits we sneak in an extra hidden transaction to update the pg_index
tuple with the xcreate of the first transaction. 

We don't do this after the CREATE INDEX statement ends, only at the end
of the transaction in which it ran. We only do this if we are creating
an index on a table that is not a temporary table and was not created
during the transaction (so --single-transaction isn't broken and doesn't
require this additional action).

i.e. MyTransactionCreatedVisibleIndex, with special processing in

The only other alternative is to forcibly throw a relcache inval event
in the same circumstances without running the additional transaction,
but the solution is mostly the same.

I agree this is weird, but no more weird a solution as CIC was when that
first came out. I don't like it, or think its clever; I just think its
the only thing on the table. 

  Simon Riggs             

In response to


pgsql-hackers by date

Next:From: Bruce MomjianDate: 2007-03-29 22:03:07
Subject: Re: List of uncompleted patches for 8.3
Previous:From: Bruce MomjianDate: 2007-03-29 22:00:52
Subject: Re: List of uncompleted patches for 8.2

Privacy Policy | About PostgreSQL
Copyright © 1996-2017 The PostgreSQL Global Development Group