Horton

Re: [HACKERS] WAL logging problem in 9.4.3?

Michael Paquier <michael@paquier.xyz>

View thread View on postgr.es
From: Michael Paquier <michael@paquier.xyz>
To: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
Cc: Kyotaro HORIGUCHI <horiguchi.kyotaro@lab.ntt.co.jp>, Stephen Frost <sfrost@snowman.net>, Michael Paquier <michael.paquier@gmail.com>, Thomas Munro <thomas.munro@enterprisedb.com>, Tom Lane <tgl@sss.pgh.pa.us>, Alvaro Herrera <alvherre@2ndquadrant.com>, David Steele <david@pgmasters.net>, Heikki Linnakangas <hlinnaka@iki.fi>, Simon Riggs <simon@2ndquadrant.com>, Andres Freund <andres@anarazel.de>, Fujii Masao <masao.fujii@gmail.com>, kleptog@svana.org, PostgreSQL Hackers <pgsql-hackers@postgresql.org>
Date: 2018-07-05T07:11:53Z
Lists: pgsql-hackers 
On Wed, Jul 04, 2018 at 07:55:53AM -0400, Andrew Dunstan wrote:
> Many thanks for working on this.

No problem.  Thanks for the lookup.

> +1 for these changes, even though the TRUNCATE fix looks perverse. If
> anyone wants to propose further optimizations in this area this would
> at least give us a startpoint which is correct.

Yes, that's exactly what I am coming at.  The optimizations which are
currently broken just cannot and should not be used.  If anybody wishes
to improve the current set of optimizations in place for wal_level =
minimal, let's also consider the other patch.  Based on the tests I sent
in the previous patch, I have compiled five scenarios by the way: 
1) BEGIN -> CREATE TABLE -> TRUNCATE -> COMMIT.
With wal_level = minimal, this fails hard with "could not read block 0
blah" when trying to read the data after commit..
2) BEGIN -> CREATE -> INSERT -> TRUNCATE -> INSERT -> COMMIT, and this
one reports an empty table, without failing, but there should be tuples
from the INSERT.
3) BEGIN -> CREATE -> INSERT -> TRUNCATE -> COPY -> COMMIT, which also
reports an empty table while there should be tuples from the COPY.
4) BEGIN -> CREATE -> INSERT -> TRUNCATE -> INSERT -> COPY -> INSERT ->
COMMIT, which fails at WAL replay with a PANIC: invalid max offset
number.
5) BEGIN -> CREATE -> INSERT -> COPY -> COMMIT, which sees only the
tuple inserted, causing an incorrect number of tuples.  If you reverse
the COPY and INSERT, then this is able to pass.

This stuff really generates a good number of different failures.  There
have been so many people participating on this thread that discussing
more this approach would be surely a good step forward, and this
summarizes quite nicely the set of failures discussed until now here.  I
would be happy to push forward with this patch to close all the holes
mentioned.
--
Michael

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