Horton

Re: [HACKERS] WAL logging problem in 9.4.3?

Heikki Linnakangas <hlinnaka@iki.fi>

View thread View on postgr.es
From: Heikki Linnakangas <hlinnaka@iki.fi>
To: Michael Paquier <michael@paquier.xyz>
Cc: pgsql-hackers@postgresql.org
Date: 2018-07-10T14:35:58Z
Lists: pgsql-hackers

Attachments

Thanks for picking this up!

(I hope this gets through the email filters this time, sending a shell 
script seems to be difficult. I also trimmed the CC list, if that helps.)

On 04/07/18 07:59, Michael Paquier wrote:
> Hence I propose the patch attached which disables the TRUNCATE and COPY
> optimizations for two cases, which are the ones actually causing
> problems.  One solution has been presented by Simon here for COPY, which
> is to disable the optimization when there are no blocks on a relation
> with wal_level = minimal:
> https://www.postgresql.org/message-id/CANP8+jKN4V4MJEzFN_iEtdZ+1oM=YETxvmuu1YK4UMXQY2gaGw@mail.gmail.com
> For back-patching, I find that really appealing.

This fails in the case that there are any WAL-logged changes to the 
table while the COPY is running. That can happen at least if the table 
has an INSERT trigger, that performs operations on the same table, and 
the COPY fires the trigger. That scenario is covered by the little bash 
script I posted earlier in this thread 
(https://www.postgresql.org/message-id/55AFC302.1060805%40iki.fi). 
Attached is a new version of that script, updated to make it work with v11.

> The second thing that the patch attached does is to tweak
> ExecuteTruncateGuts so as the TRUNCATE optimization never runs for
> wal_level = minimal.

If we go down that route, let's at least keep the TRUNCATE optimization 
for temporary and unlogged tables.

- Heikki

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