Re: [HACKERS] WAL logging problem in 9.4.3?

Hello.

The attached is back-patches from 9.5 through master.

At Mon, 02 Mar 2020 16:53:53 +0900 (JST), Kyotaro Horiguchi <horikyota(dot)ntt(at)gmail(dot)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