test-wal-minimal-2-bash-script

#!/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"