Re: Protecting against unexpected zero-pages: proposal - Mailing list pgsql-hackers

From Robert Haas
Subject Re: Protecting against unexpected zero-pages: proposal
Date
Msg-id AANLkTikx2Lm7i4_pWFV3h9bZed8Thv7Jq6_eSAsXOz8-@mail.gmail.com
Whole thread Raw
In response to Re: Protecting against unexpected zero-pages: proposal  (Josh Berkus <josh@agliodbs.com>)
Responses Re: Protecting against unexpected zero-pages: proposal
List pgsql-hackers
On Tue, Nov 9, 2010 at 5:45 PM, Josh Berkus <josh@agliodbs.com> wrote:
> Robert,
>
>> Uh, no it doesn't.  It only requires you to be more aggressive about
>> vacuuming the transactions that are in the aborted-XIDs array.  It
>> doesn't affect transaction wraparound vacuuming at all, either
>> positively or negatively.  You still have to freeze xmins before they
>> flip from being in the past to being in the future, but that's it.
>
> Sorry, I was trying to say that it's similar to the freeze issue, not
> that it affects freeze.  Sorry for the lack of clarity.
>
> What I was getting at is that this could cause us to vacuum
> relations/pages which would otherwise never be vaccuumed (or at least,
> not until freeze).  Imagine a very large DW table which is normally
> insert-only and seldom queried, but once a month or so the insert aborts
> and rolls back.

Oh, I see.  In that case, under the proposed scheme, you'd get an
immediate vacuum of everything inserted into the table since the last
failed insert.  Everything prior to the last failed insert would be
OK, since the visibility map bits would already be set for those
pages.  Yeah, that would be annoying.

There's a related problem with index-only scans.  If a large DW table
which is normally insert-only, but which IS queried regularly, it
won't be able to use index-only scans effectively because without
regularly vacuuming, the visibility map bits won't be set.  We've
previously discussed the possibility of having the background writer
set hint bits before writing the pages, and maybe it could even set
the all-visible bit and update the visibility map, too.  But that
won't help if the transaction inserts a large enough quantity of data
that it starts spilling buffers to disk before it commits.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company


pgsql-hackers by date:

Previous
From: Tom Lane
Date:
Subject: Re: Protecting against unexpected zero-pages: proposal
Next
From: Gurjeet Singh
Date:
Subject: Re: Protecting against unexpected zero-pages: proposal