Re: COPY with hints, rebirth

On Sat, Feb 25, 2012 at 6:24 PM, Kevin Grittner
<Kevin(dot)Grittner(at)wicourts(dot)gov> wrote:
> Simon Riggs <simon(at)2ndQuadrant(dot)com> wrote:
>
>> This patch extends that and actually sets the tuple header flag as
>> HEAP_XMIN_COMMITTED during the load.
>
> Fantastic!
>
> So, without bulk-load conditions, a long-lived tuple in PostgreSQL
> is written to disk at least five times[1]:
>
> (1) The WAL record for the inserted tuple is written.
> (2) The inserted tuple is written.
> (3) The HEAP_XMIN_COMMITTED bit is set and the tuple is re-written
>    in place some time after the inserting transaction's COMMIT.
> (4) The WAL record for the "freeze" in write 5 is written.
> (5) The xmin is set to frozen and the tuple is rewritten in place
>    some time after every other connection can see it.
>
> Prior to your patch, bulk load omitted write 1.  With your patch we
> will also omit write 3.

Yes, well explained.

> Since you've just been looking at this area, do you have any
> thoughts about writes 4 and 5 being rendered unnecessary by writing
> bulk-loaded tuples with a frozen xmin, and having transactions with
> a snapshot which doesn't include the bulk load's transaction just
> not seeing the table?  (Or am I just dreaming about those?)

Setting straight to frozen breaks MVCC, unless/until we use MVCC for
catalog access because we can see the table immediately and then read
the contents as if they had always been there.

I think we could add that as an option on COPY, since "breaking MVCC"
in that way is only a bad thing if it happens accidentally without the
user's permission and knowledge - which they may wish to give in many
cases, such as reloading a database from a dump.

--
 Simon Riggs                   http://www.2ndQuadrant.com/
 PostgreSQL Development, 24x7 Support, Training & Services