From cbea45e392ae600eedf9e3c3efd087799295a767 Mon Sep 17 00:00:00 2001 From: Michail Nikolaev Date: Wed, 12 May 2021 22:56:11 +0300 Subject: [PATCH v2 3/4] test --- src/test/recovery/Makefile | 1 + .../recovery/t/022_standby_index_lp_dead.pl | 248 ++++++++++++++++++ 2 files changed, 249 insertions(+) create mode 100644 src/test/recovery/t/022_standby_index_lp_dead.pl diff --git a/src/test/recovery/Makefile b/src/test/recovery/Makefile index 96442ceb4e..6399184a8c 100644 --- a/src/test/recovery/Makefile +++ b/src/test/recovery/Makefile @@ -10,6 +10,7 @@ #------------------------------------------------------------------------- EXTRA_INSTALL=contrib/test_decoding +EXTRA_INSTALL+=contrib/pageinspect subdir = src/test/recovery top_builddir = ../../.. diff --git a/src/test/recovery/t/022_standby_index_lp_dead.pl b/src/test/recovery/t/022_standby_index_lp_dead.pl new file mode 100644 index 0000000000..0297eabd06 --- /dev/null +++ b/src/test/recovery/t/022_standby_index_lp_dead.pl @@ -0,0 +1,248 @@ +# Checks that index hints on standby work as excepted. +use strict; +use warnings; + +use PostgresNode; +use TestLib; +use Test::More tests => 16; +use Config; + +# Initialize primary node +my $node_primary = get_new_node('primary'); +$node_primary->init(allows_streaming => 1); +$node_primary->append_conf('postgresql.conf', qq{ + autovacuum = off + enable_seqscan = off + enable_indexonlyscan = off +}); +$node_primary->start; + +$node_primary->safe_psql('postgres', 'CREATE EXTENSION pageinspect'); +# Create test table with primary index +$node_primary->safe_psql( + 'postgres', 'CREATE TABLE test_table (id int, value int)'); +$node_primary->safe_psql( + 'postgres', 'CREATE INDEX test_index ON test_table (value, id)'); +# Fill some data to it, note to not put a lot of records to avoid +# heap_page_prune_opt call which cause conflict on recovery hiding conflict +# caused due index hint bits +$node_primary->safe_psql('postgres', + 'INSERT INTO test_table VALUES (generate_series(1, 30), 0)'); +# And vacuum to allow index hint bits to be set +$node_primary->safe_psql('postgres', 'VACUUM test_table'); +# For fail-fast in case FPW from primary +$node_primary->safe_psql('postgres', 'CHECKPOINT'); + +# Take backup +my $backup_name = 'my_backup'; +$node_primary->backup($backup_name); + +# Restore standby node from backup backup +my $node_standby_1 = get_new_node('standby_1'); +$node_standby_1->init_from_backup($node_primary, $backup_name, + has_streaming => 1); + +my $standby_settings = qq{ + max_standby_streaming_delay = 1 + wal_receiver_status_interval = 1 + hot_standby_feedback = off + enable_seqscan = off + enable_indexonlyscan = off +}; +$node_standby_1->append_conf('postgresql.conf', $standby_settings); +$node_standby_1->start; + +$node_standby_1->backup($backup_name); + +# Create second standby node linking to standby 1 +my $node_standby_2 = get_new_node('standby_2'); +$node_standby_2->init_from_backup($node_standby_1, $backup_name, + has_streaming => 1); +$node_standby_2->append_conf('postgresql.conf', $standby_settings); +$node_standby_2->start; + +# To avoid hanging while expecting some specific input from a psql +# instance being driven by us, add a timeout high enough that it +# should never trigger even on very slow machines, unless something +# is really wrong. +my $psql_timeout = IPC::Run::timer(10); + +# One psql to run command in repeatable read isolation level +my %psql_standby_repeatable_read = ('stdin' => '', 'stdout' => '', 'stderr' => ''); +$psql_standby_repeatable_read{run} = + IPC::Run::start( + [ 'psql', '-XAb', '-f', '-', '-d', $node_standby_1->connstr('postgres') ], + '<', \$psql_standby_repeatable_read{stdin}, + '>', \$psql_standby_repeatable_read{stdout}, + '2>', \$psql_standby_repeatable_read{stderr}, + $psql_timeout); + +# Another psql to run command in read committed isolation level +my %psql_standby_read_committed = ('stdin' => '', 'stdout' => '', 'stderr' => ''); +$psql_standby_read_committed{run} = + IPC::Run::start( + [ 'psql', '-XAb', '-f', '-', '-d', $node_standby_1->connstr('postgres') ], + '<', \$psql_standby_read_committed{stdin}, + '>', \$psql_standby_read_committed{stdout}, + '2>', \$psql_standby_read_committed{stderr}, + $psql_timeout); + +# Start RR transaction and read first row from index +ok(send_query_and_wait(\%psql_standby_repeatable_read, + q[ +BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ; +SELECT id FROM test_table WHERE value = 0 ORDER BY id LIMIT 1; +], + qr/1\n\(1 row\)/m), + 'row is visible in repeatable read'); + +# Start RC transaction and read first row from index +ok(send_query_and_wait(\%psql_standby_read_committed, + q[ +BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED; +SELECT id FROM test_table WHERE value = 0 ORDER BY id LIMIT 1; +], + qr/1\n\(1 row\)/m), + 'row is visible in read committed'); + +# Now delete first 10 rows in index +$node_primary->safe_psql('postgres', + 'UPDATE test_table SET value = 1 WHERE id <= 10'); + +# Make sure hint bits are not set on primary +is(hints_num($node_primary), qq(0), 'no index hint bits are set on primary yet'); + +# Make sure page is not processed by heap_page_prune_opt +is(non_normal_num($node_primary), qq(0), 'all items are normal in heap'); + +# Wait for standbys to catch up transaction +wait_for_catchup_all(); + +is(hints_num($node_standby_1), qq(0), 'no index hint bits are set on standby 1 yet'); +is(hints_num($node_standby_2), qq(0), 'no index hint bits are set on standby 2 yet'); + +# Try to set hint bits in index on standbys +try_to_set_hint_bits(); + +# Make sure previous queries not set the hints on standby because +# of RR snapshot +is(hints_num($node_standby_1), qq(0), 'no index hint bits are set on standby 1 yet'); +# At the same time hint bits are set on second standby +is(hints_num($node_standby_2), qq(10), 'index hint bits already set on second standby 2'); + +# Make sure read committed transaction is able to see correct data +ok(send_query_and_wait(\%psql_standby_read_committed, + q/SELECT id FROM test_table WHERE value = 0 ORDER BY id LIMIT 1;/, + qr/11\n\(1 row\)/m), + 'row is not visible in read committed'); + +ok(send_query_and_wait(\%psql_standby_repeatable_read, + q/SELECT id FROM test_table WHERE value = 0 ORDER BY id LIMIT 1;/, + qr/1\n\(1 row\)/m), + 'row is visible in repeatable read'); + +# Make checkpoint to cause FPI by LP_DEAD on primary +$node_primary->safe_psql('postgres', "CHECKPOINT"); + +# Set index hint bits and replicate to standby as FPI +$node_primary->safe_psql('postgres', + 'SELECT id FROM test_table WHERE value = 0 ORDER BY id LIMIT 1;'); + +# Make sure page is not processed by heap_page_prune_opt +is(non_normal_num($node_primary), qq(0), 'all items are normal in heap'); +# Make sure hint bits are set +is(hints_num($node_primary), qq(10), 'hint bits are set on primary already'); + +## Wait for standbys to catch up hint bits +wait_for_catchup_all(); + +is(hints_num($node_standby_1), qq(10), + 'hints are set on standby1 because FPI but marked as non-safe'); +is(hints_num($node_standby_2), qq(10), + 'hints are set on standby1 because FPI but masked as non-safe'); + +# Make sure read committed transaction is able to see correct data +ok(send_query_and_wait(\%psql_standby_read_committed, + q/SELECT id FROM test_table WHERE value = 0 ORDER BY id LIMIT 1;/, + qr/11\n\(1 row\)/m), + 'row is not visible in read committed'); + +# Make sure repeatable read transaction able to see correct data +# because hint bits are marked as non-safe +ok(send_query_and_wait(\%psql_standby_repeatable_read, + q/SELECT id FROM test_table WHERE value = 0 ORDER BY id LIMIT 1;/, + qr/1\n\(1 row\)/m), + 'row is visible in repeatable read'); + +$node_primary->stop(); +$node_standby_1->stop(); +$node_standby_2->stop(); + +# Send query, wait until string matches +sub send_query_and_wait { + my ($psql, $query, $untl) = @_; + + # send query + $$psql{stdin} .= $query; + $$psql{stdin} .= "\n"; + + # wait for query results + $$psql{run}->pump_nb(); + while (1) { + # See PostgresNode.pm's psql() + $$psql{stdout} =~ s/\r\n/\n/g if $Config{osname} eq 'msys'; + + # diag("\n" . $$psql{stdout}); # for debugging + # diag("\n" . $$psql{stderr}); # for debugging + + last if $$psql{stdout} =~ /$untl/; + last if $$psql{stderr} =~ /$untl/; + + if ($psql_timeout->is_expired) { + BAIL_OUT("aborting wait: program timed out \n" . + "stream contents: >>$$psql{stdout}<< \n" . + "pattern searched for: $untl"); + return 0; + } + if (not $$psql{run}->pumpable()) { + # This is fine for some tests, keep running + return 0; + } + $$psql{run}->pump(); + select(undef, undef, undef, 0.01); # sleep a little + + } + + $$psql{stdout} = ''; + + return 1; +} + +sub try_to_set_hint_bits { + # Try to set hint bits in index on standby + foreach (0 .. 3) { + $node_standby_1->safe_psql('postgres', + 'SELECT * FROM test_table WHERE value = 0 ORDER BY id LIMIT 1;'); + $node_standby_2->safe_psql('postgres', + 'SELECT * FROM test_table WHERE value = 0 ORDER BY id LIMIT 1;'); + } +} + +sub wait_for_catchup_all { + $node_primary->wait_for_catchup($node_standby_1, 'replay', + $node_primary->lsn('insert')); + $node_standby_1->wait_for_catchup($node_standby_2, 'replay', + $node_standby_1->lsn('replay')); +} + +sub hints_num { + my ($node) = @_; + return $node->safe_psql('postgres', + "SELECT count(*) FROM bt_page_items('test_index', 1) WHERE dead = true"); +} + +sub non_normal_num { + my ($node) = @_; + return $node->safe_psql('postgres', + "SELECT COUNT(*) FROM heap_page_items(get_raw_page('test_table', 0)) WHERE lp_flags != 1"); +} \ No newline at end of file -- 2.25.1