pg.ddx.io pgsql-performance@postgresql.org mailing list archivehelp / color / mirror / Atom feed
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steven Flatt <steven.flatt@gmail.com>
Cc: Gregory Stark <stark@enterprisedb.com>
Cc: Bill Moran <wmoran@collaborativefusion.com>
Cc: Vivek Khera <vivek@khera.org>
Cc: Pgsql performance <pgsql-performance@postgresql.org>
Subject: Re: When/if to Reindex
Date: Thu, 23 Aug 2007 23:59:02 -0400
Message-ID: <17370.1187927942@sss.pgh.pa.us> (raw)
In-Reply-To: <357fa7590708231525h2f48ce4dw3e48ecca5ccddf72@mail.gmail.com>
References: <357fa7590707181008k65ab2f20l55bebb3c14f52770@mail.gmail.com>
<38118C09-FC0A-4CCB-9461-FC6D29D81EE4@khera.org>
<357fa7590708081212p7b19cbbbn39dbadae8603edd6@mail.gmail.com>
<20070808152757.9eb9bbcc.wmoran@collaborativefusion.com>
<11801.1186624308@sss.pgh.pa.us>
<357fa7590708090651p71b9bc47n66739f8dbbb4ff57@mail.gmail.com>
<357fa7590708220955h441cd75ap4c0f0342a1c30a14@mail.gmail.com>
<873aybw2le.fsf@oxford.xeocode.com>
<357fa7590708231525h2f48ce4dw3e48ecca5ccddf72@mail.gmail.com>
"Steven Flatt" <steven.flatt@gmail.com> writes:
> Interestingly enough, the example you've given does not work for me either.
> The select count(*) from test blocks until the reindex completes. Are we
> using the same pg version?
Seems like a fair question, because Greg's example blocks for me too,
in plancat.c where the planner is trying to acquire information on each
index. This seems to be an unwanted side effect of this 8.2-era patch
http://archives.postgresql.org/pgsql-committers/2006-07/msg00356.php
specifically, note here
http://developer.postgresql.org/cvsweb.cgi/pgsql/src/backend/optimizer/util/plancat.c.diff?r1=1.121;...
how the new planner coding takes at least AccessShareLock on each index,
where the old coding took no lock at all.
I think that the new coding rule of "you *must* take some lock when
opening the relation" is essential for tables, but it might not be
necessary for indexes if you've got a lock on the parent table.
We don't allow any schema changes on an index to be made without holding
exclusive lock on the parent, so plancat.c's basic purpose of finding
out the properties of the index could be done safely without any index
lock.
The fly in the ointment is that after collecting the pg_index definition
of the index, plancat.c also wants to know how big it is --- it calls
RelationGetNumberOfBlocks. And that absolutely does look at the
physical storage, which means it absolutely is unsafe to do in parallel
with a REINDEX that will be dropping the old physical storage at some
point.
So maybe we are stuck and we have to say "that doesn't work anymore".
But it feels like we might not be too far away from letting it still
work. Thoughts, ideas?
regards, tom lane
view thread (23+ messages) latest in thread
Message-ID: <17370.1187927942@sss.pgh.pa.us> Permalink: ../17370.1187927942@sss.pgh.pa.us/ Also on: postgresql.org/message-id/17370.1187927942@sss.pgh.pa.us ·
reply
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, steven.flatt@gmail.com, stark@enterprisedb.com, wmoran@collaborativefusion.com, vivek@khera.org Subject: Re: When/if to Reindex In-Reply-To: <17370.1187927942@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