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.
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
#!/bin/bash
set -e
export PGDATABASE=postgres
PATH=./bin:$PATH
cat > /tmp/test-copydata <<EOF
copied row 1
copied row 2
copied row 3
EOF
initdb -D data-minimal
echo "max_wal_senders=0" >> data-minimal/postgresql.conf
echo "wal_level=minimal" >> data-minimal/postgresql.conf
# CREATE, INSERT, COPY, crash.
#
# If COPY inserts to the existing block, and is not WAL-logged, replaying
# the implicit FPW of the INSERT record will destroy the COPY data.
pg_ctl -D data-minimal -w start
psql <<EOF
BEGIN;
CREATE TABLE test1(t text primary key);
SELECT relname, relfilenode from pg_class where relfilenode >= 16384;
INSERT INTO test1 VALUES ('inserted row');
\copy test1 FROM '/tmp/test-copydata'
COMMIT;
EOF
pg_ctl -D data-minimal stop -m immediate
sleep 1
pg_ctl -D data-minimal -w start
echo "Should have 4 rows:"
psql -c "SELECT * FROM test1"
psql -c "DROP TABLE test1" > /dev/null # cleanup
# CREATE, COPY, crash. Trigger in COPY that inserts more to same table.
#
# If the INSERTS from the trigger go to the same block we're copying to,
# and the INSERTs are WAL-logged, WAL replay will fail when it tries to
# replay the WAL record but the "before" image doesn't match, because not
# all changes were WAL-logged.
#pg_ctl -D data-minimal -w start
psql <<EOF
BEGIN;
CREATE TABLE test1(t text primary key);
CREATE FUNCTION test1_beforetrig() returns trigger language plpgsql as \$\$
BEGIN
IF new.t NOT LIKE 'triggered%' THEN
INSERT INTO test1 VALUES ('triggered ' || NEW.t);
END IF;
RETURN NEW;
END;
\$\$;
CREATE TRIGGER test1_beforeinsert BEFORE INSERT ON test1 FOR EACH ROW EXECUTE
PROCEDURE test1_beforetrig();
SELECT relname, relfilenode from pg_class where relfilenode >= 16384;
\copy test1 FROM '/tmp/test-copydata'
COMMIT;
EOF
pg_ctl -D data-minimal stop -m immediate
sleep 1
pg_ctl -D data-minimal -w start
echo "Should have 6 rows (3 original and 3 inserted by trigger):"
psql -c "SELECT * FROM test1"
psql -c "DROP TABLE test1" > /dev/null # cleanup
psql -c "DROP FUNCTION test1_beforetrig()" > /dev/null # cleanup
# CREATE, TRUNCATE, COPY, crash.
#
# If we skip WAL-logging of the COPY, replaying the TRUNCATE record destroy
# the newly inserted data.
#pg_ctl -D data-minimal -w start
psql <<EOF
BEGIN;
CREATE TABLE test1(t text primary key);
SELECT relname, relfilenode from pg_class where relfilenode >= 16384;
TRUNCATE test1;
SELECT relname, relfilenode from pg_class where relfilenode >= 16384;
\copy test1 FROM '/tmp/test-copydata'
COMMIT;
EOF
pg_ctl -D data-minimal stop -m immediate
sleep 1
pg_ctl -D data-minimal -w start
echo "Should have 3 rows:"
psql -c "SELECT * FROM test1"
