Horton

Re: [HACKERS] WAL logging problem in 9.4.3?

Kyotaro Horiguchi <horikyota.ntt@gmail.com>

View thread View on postgr.es
From: Kyotaro Horiguchi <horikyota.ntt@gmail.com>
To: noah@leadboat.com
Cc: robertmhaas@gmail.com, pgsql-hackers@postgresql.org, 9erthalion6@gmail.com, andrew.dunstan@2ndquadrant.com, hlinnaka@iki.fi, michael@paquier.xyz
Date: 2020-03-04T07:29:19Z
Lists: pgsql-hackers

Attachments

Hello.

The attached is back-patches from 9.5 through master.

At Mon, 02 Mar 2020 16:53:53 +0900 (JST), Kyotaro Horiguchi <horikyota.ntt@gmail.com> wrote in 
> > Would you translate this into back-patch versions for v9.5 through v12?
> 
> The explicit list of commands that initiate the WAL-skipping mode
> works for me. I'm going to work on the tranlation right now.

At first I fixed several ssues in 018_wal_optimize.pl:

- TRUNCATE INSERT, TRUNCATE INSERT PREPARE

 It wrongly passes if finally we see the value only from the first
 INSERT. I changed it so that it checks the value, not the number of
 values.

- TRUNCATE with end-of-xact WAL => lengty end-of-xact WAL

 TRUNCATE inhibits end-of-xact WAL so I removed the TRUNCATE.  It uses
 only 1 page so it fails to excercise multi-page behavior of
 log_newpage_range.  At least 33 pages is needed to check if it is
 working correctly.  10000 rows is sufficient but I choosed 20000 rows
 including margin.

- COPY with INSERT tirggers
 It wrongly referes to OLD in AFTER-INSERT trigger. It yeilds NULL
 for 11 and later, or ends in ERROR otherwise.  Addition to that
 AFTER-INSERT ROW-level trigger is fired after *stagtement* (but
 before AFTER-INSERT statement level triggers).  That being said, it
 doesn't affect the result of the test so I leave it with modifying it
 not to refer to OLD.

log_newpage_range has been introduced at PG12.  Fortunately the
required infrastructure is introduced at PG9.5 so what I need to do
for PG95-PG11 is back-patching the function and its counter part in
xlog_redo. It doen't WAL format itself but XLOG_FPI gets to have 2 or
more backup pages so the compatibility is forward only. That is, newer
minor versions read WAL from older minor versions, but not vise
versea.  I'm not sure it is back-patchable so in the attached the
end-of-xact WAL feature is separated for PG9.5-PG11.
(000x-Add-end-of-xact-WAL-feature-of-WAL-skipping.patch)

====

In the patchset for 12, I let the functions heap_sync,
heapam_methods.finish_bulk_insert and table_finish_bulk_insert left
as-is.  As the result heapam_finish_bulk_insert becomes no-op.
begin_heap_rewrite is a public function but the last parameter is
useless and rather harmful as it looks as if it works. So I removed
the parameter.
  
For 11 and 10, heap_sync and begin_heap_rewrite is treated the same
way to 12.

For 9.6, mdexists() creates the specified file while bootstrap mode
and that leads to assertion failure of smgrDoPendingSyncs. So I made
CreateStorage not register pending sync while bootstrap mode.
gistbuild generates the LSN for root page of a newly created index
using gistGetFakeLSN(heap), which fires assertion failure in
gistGetFakeLSN.  I think we should use index instead of heap there,
but it doesn't matter if we don't have the new pending sync mechanism,
so I didn't split it as a separate patch.  pg_visibility doesn't have
regression test but I added the files conatining only the test for
this feature.

For 9.5, pg_visibility does not exist so I dropped the test for the
module.  It lacks a part of TAP infrastructure nowadays we have, but I
want to have the test (and it actually found a bug I made during this
work). So I added a patch to back-patch TestLib.pm, PostgresNode.pm
and RecursiveCopy.pm along with 018_wal_optimize.pl.
(0004-Add-TAP-test-for-WAL-skipping-feature.patch)

regards.

-- 
Kyotaro Horiguchi
NTT Open Source Software Center

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