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: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>, Michael Paquier <michael@paquier.xyz>
Cc: pgsql-hackers@postgresql.org
Date: 2018-07-12T14:12:21Z
Lists: pgsql-hackers 
On 12/07/18 16:51, Andrew Dunstan wrote:
> 
> 
> On 07/10/2018 11:32 PM, Michael Paquier wrote:
>> On Tue, Jul 10, 2018 at 05:35:58PM +0300, Heikki Linnakangas wrote:
>>> 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.
>> Thanks for the pointer.  My tap test has been covering two out of the
>> three scenarios you have in your script.  I have been able to convert
>> the extra as the attached, and I have added as well an extra test with
>> TRUNCATE triggers.  So it seems to me that we want to disable the
>> optimization if any type of trigger are defined on the relation copied
>> to as it could be possible that these triggers work on the blocks copied
>> as well, for any BEFORE/AFTER and STATEMENT/ROW triggers.  What do you
>> think?
> 
> Yeah, this seems like the only sane approach.

Doesn't have to be a trigger, could be a CHECK constraint, datatype 
input function, etc. Admittedly, having a datatype input function that 
inserts to the table is worth a "huh?", but I'm feeling very confident 
that we can catch all such cases, and some of them might even be sensible.

- 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