agora inbox for pgsql-hackers@postgresql.org
help / color / mirror / Atom feedFrom: Andrew Dunstan <andrew.dunstan@2ndquadrant.com>
To: Michael Paquier <michael@paquier.xyz>
To: Heikki Linnakangas <hlinnaka@iki.fi>
Cc: pgsql-hackers@postgresql.org
Subject: Re: [HACKERS] WAL logging problem in 9.4.3?
Date: Thu, 12 Jul 2018 09:51:33 -0400
Message-ID: <aac8e19b-9159-e473-77be-53f80b658190@2ndQuadrant.com> (raw)
In-Reply-To: <20180711033241.GQ1661@paquier.xyz>
References: <20171211.175424.09346818.horiguchi.kyotaro@lab.ntt.co.jp>
<20180105041040.GI2416@tamriel.snowman.net>
<20180111.170355.123986255.horiguchi.kyotaro@lab.ntt.co.jp>
<20180330.100646.86008470.horiguchi.kyotaro@lab.ntt.co.jp>
<20180704045912.GG1672@paquier.xyz>
<df32e286-ae2b-f45a-8f2e-4fa02684300b@iki.fi>
<20180711033241.GQ1661@paquier.xyz>
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...
>>> 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.
cheers
andrew
--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
view thread (244+ messages) latest in thread
Message-ID: <aac8e19b-9159-e473-77be-53f80b658190@2ndQuadrant.com>
Permalink: ../aac8e19b-9159-e473-77be-53f80b658190@2ndQuadrant.com/
Also on: postgresql.org/message-id/aac8e19b-9159-e473-77be-53f80b658190@2ndQuadrant.com
reply
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-hackers@postgresql.org
Cc: andrew.dunstan@2ndquadrant.com, michael@paquier.xyz, hlinnaka@iki.fi
Subject: Re: [HACKERS] WAL logging problem in 9.4.3?
In-Reply-To: <aac8e19b-9159-e473-77be-53f80b658190@2ndQuadrant.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox