From: Markus Bertheau <mbertheau.pg@googlemail.com>
To: Scott Marlowe <scott.marlowe@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Joshua D. Drake <jd@commandprompt.com>
Cc: Peter Koczan <pjkoczan@gmail.com>
Cc: pgsql-performance <pgsql-performance@postgresql.org>
Subject: Re: disabling an index without deleting it?
Date: Wed, 27 Feb 2008 11:29:55 +0600
Message-ID: <684362e10802262129w4fe41264p1eb75cadd5f4d259@mail.gmail.com> (raw)
In-Reply-To: <dcc563d10802262116q54b0541fj1aa10821e2255419@mail.gmail.com>
References: <4544e0330802261246t574167fdpc47629d1bbe64fed@mail.gmail.com>
<dcc563d10802261257h6abfbc92x62a7569b8610ecc4@mail.gmail.com>
<20080226130339.64bd4ec3@commandprompt.com>
<9050.1204064560@sss.pgh.pa.us>
<684362e10802261848j79686752k6dfbe032e985b131@mail.gmail.com>
<13604.1204087729@sss.pgh.pa.us>
<dcc563d10802262116q54b0541fj1aa10821e2255419@mail.gmail.com>
2008/2/27, Scott Marlowe <scott.marlowe@gmail.com>:
> On Tue, Feb 26, 2008 at 10:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "Markus Bertheau" <mbertheau.pg@googlemail.com> writes:
> > > 2008/2/27, Tom Lane <tgl@sss.pgh.pa.us>:
> >
> > >> No, what makes you think that? The index won't change at all in the
> > >> above example. The major problem is, as Scott says, that DROP INDEX
> > >> takes exclusive lock on the table so any other sessions will be locked
> > >> out of it for the duration of your test query.
> >
> > > Why is the exclusive lock not taken later, so that this method can be
> > > used reasonably risk-free on production systems?
> >
> > Er, later than what? Once the DROP is pending, other transactions can
> > hardly safely use the index for lookups, and what should they do about
> > insertions?
>
>
> I see what you're saying. Sadly, my dreams of drop index concurrently
> appear dashed.
Maybe a different syntax: DROP INDEX DEFERRED, which will make the
current transaction behave as if the index was dropped but not
actually drop it until the end of the transaction. Inserts and updates
of this and other transactions behave as if the index existed.
On the other hand, if the only reason to have that feature is to plan
and execute queries pretending that one index doesn't exist, then DROP
INDEX DEFERRED is not the most straightforward syntax.
Markus
--
Markus Bertheau
Blog: http://www.bluetwanger.de/blog/
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: mbertheau.pg@googlemail.com, scott.marlowe@gmail.com, tgl@sss.pgh.pa.us, jd@commandprompt.com, pjkoczan@gmail.com
Subject: Re: disabling an index without deleting it?
In-Reply-To: <684362e10802262129w4fe41264p1eb75cadd5f4d259@mail.gmail.com>
* 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