Re: Protecting against unexpected zero-pages: proposal

On Tue, Nov 9, 2010 at 5:45 PM, Josh Berkus <josh(at)agliodbs(dot)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