Re: CREATE INDEX and HOT - revised design

From: "Simon Riggs" <simon(at)2ndquadrant(dot)com>
To: "Pavan Deolasee" <pavan(dot)deolasee(at)gmail(dot)com>
Cc: "Tom Lane" <tgl(at)sss(dot)pgh(dot)pa(dot)us>, "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 20:04:11
Message-ID: 1175198652.4386.599.camel@silverbirch.site
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-hackers

On Thu, 2007-03-29 at 22:08 +0530, Pavan Deolasee wrote:
>
>
> On 3/29/07, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> It will replan at the first use of the plan after seeing the
> relcache
> inval sent by commit of the index-creating transaction. If
> you have
> two separate transactions to create an index and then mark it
> valid
> later, everything's fine because there are two inval events.
> However, if you design something where an index becomes usable
> due
> to the passage of time rather than an explicit mark-valid
> step,
> there's gonna be a problem. I'd suggest trying to stick to
> the
> way CREATE INDEX CONCURRENTLY does it...
>
>
> I had earlier proposed to do things CIC way. But there were objections
> to the additional wait introduced in CREATE INDEX, and I don't
> think they were unreasonable. May be if we can avoid waits if there
> are no HOT-chains in the table, but still we need agreement on that.
>
> OTOH ISTM that the pg_index:xcreate solution may work fine if
> we can keep index unusable to those transactions which started
> before CREATE INDEX could commit.

Pavan, ISTM you have misunderstood Tom slightly.

Having the index invisible to all current transactions is acceptable.

However, the other backends will not receive an invalidation event,
which means even when they start new transactions they will still not
see it, which is not acceptable.

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.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

In response to

Responses

Browse pgsql-hackers by date

  From Date Subject
Next Message Joshua D. Drake 2007-03-29 20:25:12 Feature thought: idle in transaction timeout
Previous Message Simon Riggs 2007-03-29 19:56:06 Re: CREATE INDEX and HOT - revised design