From: Tom Lane <tgl@sss.pgh.pa.us>
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>
Cc: Markus Bertheau <mbertheau.pg@googlemail.com>
Cc: Joshua D. Drake <jd@commandprompt.com>
Cc: Peter Koczan <pjkoczan@gmail.com>
Cc: Scott Marlowe <scott.marlowe@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: disabling an index without deleting it?
Date: Wed, 27 Feb 2008 18:00:17 -0500
Message-ID: <10385.1204153217@sss.pgh.pa.us> (raw)
In-Reply-To: <47C578A1.EE98.0025.0@wicourts.gov>
References: <4544e0330802261246t574167fdpc47629d1bbe64fed@mail.gmail.com>
<dcc563d10802261257h6abfbc92x62a7569b8610ecc4@mail.gmail.com>
<20080226130339.64bd4ec3@commandprompt.com>
<9050.1204064560@sss.pgh.pa.us>
<684362e10802261848j79686752k6dfbe032e985b131@mail.gmail.com>
<684362e10802261848j79686752k6dfbe032e985b131@mail.gmail.com>
<13604.1204087729@sss.pgh.pa.us>
<47C578A1.EE98.0025.0@wicourts.gov>
"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:
> Out of curiosity, couldn't any transaction using a snapshot prior to
> the commit of the DROP continue to use it (just like an uncommited
> DELETE of a row)? The transaction doing the DROP wouldn't maintain
> it for modifications, which is fine whether it is committed or
> rolled back. There would just be the matter of "vacuuming" the
> index out of physical existence once all transactions which could
> see it are gone.
You can't just lazily remove the index after the last xact stops using
it; there has to be an agreed synchronization point among all the
transactions. Otherwise you could have xact A expecting the index to
contain entries from the already-committed xact B, but B thought the
index was dead and didn't bother updating it.
We might be able to do something that would shorten the length of time
that the exclusive lock is held, but AFAICS we couldn't eliminate it
altogether; and I'm unconvinced that merely shortening the interval
is worth much extra complexity.
In the particular case at hand, a planner hook to make it ignore the
index is a far better solution anyway...
regards, tom lane
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, Kevin.Grittner@wicourts.gov, mbertheau.pg@googlemail.com, jd@commandprompt.com, pjkoczan@gmail.com, scott.marlowe@gmail.com
Subject: Re: disabling an index without deleting it?
In-Reply-To: <10385.1204153217@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox