Horton

Re: WAL logging problem in 9.4.3?

Tom Lane <tgl@sss.pgh.pa.us>

View thread View on postgr.es
From: Tom Lane <tgl@sss.pgh.pa.us>
To: Andres Freund <andres@anarazel.de>
Cc: Fujii Masao <masao.fujii@gmail.com>, Martijn van Oosterhout <kleptog@svana.org>, PostgreSQL-development <pgsql-hackers@postgresql.org>
Date: 2015-07-09T23:06:11Z
Lists: pgsql-hackers 
Andres Freund <andres@anarazel.de> writes:
> On 2015-07-06 11:49:54 -0400, Tom Lane wrote:
>> Rather than reverting cab9a0656c36739f, which would re-introduce a
>> different performance problem, perhaps we could have COPY create a new
>> relfilenode when it does this.  That should be safe if the table was
>> previously empty.

> I'm not convinced that cab9a0656c36739f needs to survive in that
> form. To me only allowing one COPY to benefit from the wal_level =
> minimal optimization has a significantly higher cost than
> cab9a0656c36739f.

What evidence have you got to base that value judgement on?

cab9a0656c36739f was based on an actual user complaint, so we have good
evidence that there are people out there who care about the cost of
truncating a table many times in one transaction.  On the other hand,
I know of no evidence that anyone's depending on multiple sequential
COPYs, nor intermixed COPY and INSERT, to be fast.  The original argument
for having this COPY optimization at all was to make restoring pg_dump
scripts in a single transaction fast; and that use-case doesn't care
about anything but a single COPY into a virgin table.

I think you're worrying about exactly the wrong case.

> My tentative guess is that the best course is to
> a) Make heap_truncate_one_rel() create a new relfeilnode. That fixes the
>    truncation replay issue.
> b) Force new pages to be used when using the heap_sync mode in
>    COPY. That avoids the INIT danger you found. It seems rather
>    reasonable to avoid using pages that have already been the target of
>    WAL logging here in general.

And what reason is there to think that this would fix all the problems?
We know of those two, but we've not exactly looked hard for other cases.
Again, the only known field usage for the COPY optimization is the pg_dump
scenario; were that not so, we'd have noticed the problem long since.
So I don't have any faith that this is a well-tested area.

			regards, tom lane

Commits

  1. Add perl2host call missing from a new test file.

  2. Skip WAL for new relfilenodes, under wal_level=minimal.

  3. Revert "Skip WAL for new relfilenodes, under wal_level=minimal."

  4. Back-patch log_newpage_range().

  5. During heap rebuild, lock any TOAST index until end of transaction.

  6. In log_newpage_range(), heed forkNum and page_std arguments.

  7. Back-patch src/test/recovery and PostgresNode from 9.6 to 9.5.

  8. Reduce pg_ctl's reaction time when waiting for postmaster start/stop.

  9. Accelerate end-of-transaction dropping of relations

  10. Redesign the planner's handling of index-descent cost estimation.

  11. Make TRUNCATE do truncate-in-place when processing a relation that was created