Re: COPY with hints, rebirth

On Fri, Mar 2, 2012 at 8:58 PM, Noah Misch <noah(at)leadboat(dot)com> wrote:
> On Fri, Mar 02, 2012 at 08:46:45AM +0000, Simon Riggs wrote:
>> On Thu, Mar 1, 2012 at 8:49 PM, Heikki Linnakangas <heikki(dot)linnakangas(at)enterprisedb(dot)com> wrote:
>> > It's still broken:
>
> [BEGIN;TRUNCATE;SAVEPOINT;COPY;ROLLBACK TO]
>
>> So this approach isn't the one...
>>
>> The COPY FREEZE patch provides a way for the user to say explicitly
>> that they don't really care about these MVCC corner cases and as a
>> result allows us to avoid touching XidInMVCCSnapshot() at all. So
>> there is still a patch on the table.
>
> You can salvage the optimization by tightening its prerequisite: use it when
> the current subtransaction or a child thereof created or truncated the table.
> A parent subtransaction having done so is acceptable for the WAL avoidance
> optimization but not for this.

That's already what it does. The problem is what happens after the COPY.

If we said "you can't see the rows you just loaded" it would be
problem over, but in my opinion that needs an explicit request from
the user to show they accept that.

> A COPY FREEZE ignoring that stronger restriction would be an interesting
> feature in its own right, but it brings up other problems.  For example,
> suppose you write a tuple, then fail while writing its index entries.  The
> tuple is already frozen and visible, but it lacks a full set of live index
> entries.  The subtransaction aborts, but the top transaction commits and makes
> the situation permanent.

The optimisation only works in the subtransaction that created the
relfilenode so that isn't an issue.

Thanks for your input.

> Incidentally, I contend that we should write frozen tuples to new/truncated
> tables unconditionally.  The current behavior of making old snapshots see the
> table as empty violates atomicity at least as badly as letting those snapshots
> see the future-snapshot contents.  But Marti has a sound proposal that would
> interact with your efforts here to avoid violating atomicity at all:
> http://archives.postgresql.org/message-id/CABRT9RBRMdsoz8KxgeHfb4LG-ev9u67-6DLqvoiibpkKhTLQfw@mail.gmail.com

Personally, that seems a pretty reasonable thing.

I like Marti's idea. At present, making his idea work could easily
mean checksums sink, so not sure whether to attempt to make that work
in detail.

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