This has been saved for the 8.4 release:
http://momjian.postgresql.org/cgi-bin/pgpatches_hold
---
Tom Lane wrote:
> "Steven Flatt" <[EMAIL PROTECTED]> writes:
> > So, can we simply trust what's in pg_class.relpages
Gregory Stark <[EMAIL PROTECTED]> writes:
> Should reindex be doing an in-place update?
Not if you'd like it to be crash-safe.
> Alternatively, why does the planner need access to the pg_class entry and not
> just the pg_index record?
For one thing, to find out how big the index is ... though if
"Tom Lane" <[EMAIL PROTECTED]> writes:
> "Steven Flatt" <[EMAIL PROTECTED]> writes:
>> So, can we simply trust what's in pg_class.relpages and ignore looking
>> directly at the index?
>
> No, we can't. In the light of morning I remember more about the reason
> for the aforesaid patch: it's actua
On 8/24/07, Tom Lane <[EMAIL PROTECTED]> wrote:
> You might be able to work around it for now by faking such a reindex
> "by hand"; that is, create a duplicate new index under a different
> name using CREATE INDEX CONCURRENTLY, then exclusive-lock the table
> for just long enough to drop the old i
"Steven Flatt" <[EMAIL PROTECTED]> writes:
> So, can we simply trust what's in pg_class.relpages and ignore looking
> directly at the index?
No, we can't. In the light of morning I remember more about the reason
for the aforesaid patch: it's actually unsafe to read the pg_class row
at all if you
On 8/24/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>
> "Steven Flatt" <[EMAIL PROTECTED]> writes:
> > Why do we even need to consider calling RelationGetNumberOfBlocks or
> looking
> > at the pg_class.relpages entry? My understanding of the expected
> behaviour
> > is that while a reindex is happenin
"Steven Flatt" <[EMAIL PROTECTED]> writes:
>> 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.
> Why do we even need to consider calling RelationGetNumberOfBlocks or looki
On 8/24/07, Mark Kirkwood <[EMAIL PROTECTED]> wrote:
>
> Tom Lane wrote:
> >
> > 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
>
"Steven Flatt" <[EMAIL PROTECTED]> writes:
> On 8/22/07, Gregory Stark <[EMAIL PROTECTED]> wrote:
>
> 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?
I was using CV
Tom Lane wrote:
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
"Steven Flatt" <[EMAIL PROTECTED]> 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
On 8/22/07, Gregory Stark <[EMAIL PROTECTED]> wrote:
> postgres=# create table test (i integer);
> CREATE TABLE
> postgres=# insert into test select generate_series(1,1000);
> INSERT 0 1000
> postgres=# create or replace function slow(integer) returns integer as
> 'begin perform pg_sleep(0); retur
"Steven Flatt" <[EMAIL PROTECTED]> writes:
> However I'm seeing that all readers of that table are blocked until the
> reindex finishes, even reads that do not attempt to use the index. Is this
> a problem with the docs or a bug?
You'll have to describe in more detail what you're doing so we can
>
> It makes more sense for us to have ~1 hour's worth of reindexing
> afterwards during which read performance on that partition is "compromised".
>
So, based on the docs, I was expecting read performance to be compromised
during a reindex, specifically reads would not be allowed to use the inde
On 8/8/07, Tom Lane <[EMAIL PROTECTED]> wrote:
>
> I'd make the same remark about Steven's case: if possible, don't create
> the indexes at all until you've loaded the table fully.
We considered this, however in some of our 12-hour partitions, there are
upwards of 50 or 60 million rows near the e
In response to "Decibel!" <[EMAIL PROTECTED]>:
> On Wed, Aug 08, 2007 at 03:27:57PM -0400, Bill Moran wrote:
> > I've had similar experience. One thing you didn't mention that I've noticed
> > is that VACUUM FULL often bloats indexes. I've made it SOP that
> > after application upgrades (which u
Bill Moran <[EMAIL PROTECTED]> writes:
> In response to "Steven Flatt" <[EMAIL PROTECTED]>:
>> What's interesting is that an insert-only table can benefit significantly
>> from reindexing after the table is fully loaded.
> I've had similar experience. One thing you didn't mention that I've notice
On Wed, Aug 08, 2007 at 03:27:57PM -0400, Bill Moran wrote:
> I've had similar experience. One thing you didn't mention that I've noticed
> is that VACUUM FULL often bloats indexes. I've made it SOP that
> after application upgrades (which usually includes lots of ALTER TABLES and
> other massive
In response to "Steven Flatt" <[EMAIL PROTECTED]>:
> On 8/8/07, Vivek Khera <[EMAIL PROTECTED]> wrote:
> >
> > If all you ever did was insert into that table, then you probably
> > don't need to reindex. If you did mass updates/deletes mixed with
> > your inserts, then perhaps you do.
> >
> > Do
On 8/8/07, Vivek Khera <[EMAIL PROTECTED]> wrote:
>
> If all you ever did was insert into that table, then you probably
> don't need to reindex. If you did mass updates/deletes mixed with
> your inserts, then perhaps you do.
>
> Do some experiments comparing pg_class.relpages for your table and
>
On Jul 18, 2007, at 1:08 PM, Steven Flatt wrote:
Some background: we make extensive use of partitioned tables. In
fact, I'm
really only considering reindexing partitions that have "just
closed". In
our simplest/most general case, we have a table partitioned by a
timestamp
column, each pa
On Wed, Jul 18, 2007 at 01:08:30PM -0400, Steven Flatt wrote:
> We're using Postgres 8.2.4.
>
> I'm trying to decide whether it's worthwhile to implement a process that
> does periodic reindexing. In a few ad hoc tests, where I've tried to set up
> data similar to how our application does it, I'v
22 matches
Mail list logo
