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=yetxvmuu1yk4umxqy2g...@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.
cheers
andrew
--
Andrew Dunstan https://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services
