agora inbox for pgsql-hackers@postgresql.orghelp / color / mirror / Atom feed
[PATCH v68 19/31] pgstat: test: test stats interactions with streaming replication. 4+ messages / 2 participants [nested] [flat]
* [PATCH v68 19/31] pgstat: test: test stats interactions with streaming replication. @ 2022-03-21 19:58 Andres Freund <andres@anarazel.de> 0 siblings, 0 replies; 4+ messages in thread From: Andres Freund @ 2022-03-21 19:58 UTC (permalink / raw) Author: Melanie Plageman <melanieplageman@gmail.com> Discussion: https://postgr.es/m/20220303021600.hs34ghqcw6zcokdh@alap3.anarazel.de --- .../recovery/t/030_stats_cleanup_replica.pl | 228 ++++++++++++++++++ 1 file changed, 228 insertions(+) create mode 100644 src/test/recovery/t/030_stats_cleanup_replica.pl diff --git a/src/test/recovery/t/030_stats_cleanup_replica.pl b/src/test/recovery/t/030_stats_cleanup_replica.pl new file mode 100644 index 00000000000..ea79e990395 --- /dev/null +++ b/src/test/recovery/t/030_stats_cleanup_replica.pl @@ -0,0 +1,228 @@ +# Copyright (c) 2021-2022, PostgreSQL Global Development Group + +# Tests that statistics are removed from a physical replica after being dropped +# on the primary + +use strict; +use warnings; +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +# Initialize primary node +my $node_primary = PostgreSQL::Test::Cluster->new('primary'); +# A specific role is created to perform some tests related to replication, +# and it needs proper authentication configuration. +$node_primary->init( + allows_streaming => 1, + auth_extra => [ '--create-role', 'repl_role' ]); +$node_primary->start; +my $backup_name = 'my_backup'; + +# Set track_functions to all on primary +$node_primary->append_conf('postgresql.conf', "track_functions = 'all'"); +$node_primary->reload; + +# Take backup +$node_primary->backup($backup_name); + +# Create streaming standby linking to primary +my $node_standby = PostgreSQL::Test::Cluster->new('standby'); +$node_standby->init_from_backup($node_primary, $backup_name, + has_streaming => 1); +$node_standby->start; + +sub populate_standby_stats +{ + my ($node_primary, $node_standby, $connect_db, $schema) = @_; + # Create table on primary + $node_primary->safe_psql($connect_db, + "CREATE TABLE $schema.drop_tab_test1 AS SELECT generate_series(1,100) AS a" + ); + + # Create function on primary + $node_primary->safe_psql($connect_db, + "CREATE FUNCTION $schema.drop_func_test1() RETURNS VOID AS 'select 2;' LANGUAGE SQL IMMUTABLE" + ); + + # Wait for catchup + my $primary_lsn = $node_primary->lsn('write'); + $node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn); + + # Get database oid + my $dboid = $node_standby->safe_psql($connect_db, + "SELECT oid FROM pg_database WHERE datname = '$connect_db'"); + + # Get table oid + my $tableoid = $node_standby->safe_psql($connect_db, + "SELECT '$schema.drop_tab_test1'::regclass::oid"); + + # Do scan on standby + $node_standby->safe_psql($connect_db, + "SELECT * FROM $schema.drop_tab_test1"); + + # Get function oid + my $funcoid = $node_standby->safe_psql($connect_db, + "SELECT '$schema.drop_func_test1()'::regprocedure::oid"); + + # Call function on standby + $node_standby->safe_psql($connect_db, "SELECT $schema.drop_func_test1()"); + + # Force flush of stats on standby + $node_standby->safe_psql($connect_db, + "SELECT pg_stat_force_next_flush()"); + + return ($dboid, $tableoid, $funcoid); +} + +sub drop_function_by_oid +{ + my ($node_primary, $connect_db, $funcoid) = @_; + + # Get function name from returned oid + my $func_name = $node_primary->safe_psql($connect_db, + "SELECT '$funcoid'::regprocedure"); + + # Drop function on primary + $node_primary->safe_psql($connect_db, "DROP FUNCTION $func_name"); +} + +sub drop_table_by_oid +{ + my ($node_primary, $connect_db, $tableoid) = @_; + + # Get table name from returned oid + my $table_name = + $node_primary->safe_psql($connect_db, "SELECT '$tableoid'::regclass"); + + # Drop table on primary + $node_primary->safe_psql($connect_db, "DROP TABLE $table_name"); +} + +sub test_standby_func_tab_stats_status +{ + local $Test::Builder::Level = $Test::Builder::Level + 1; + my ($node_standby, $connect_db, $dboid, $tableoid, $funcoid, $present) = + @_; + + is( $node_standby->safe_psql( + $connect_db, + "SELECT pg_stat_exists_stat('relation', $dboid, $tableoid)"), + $present, + "Check that table stats exist is '$present' on standby"); + + is( $node_standby->safe_psql( + $connect_db, + "SELECT pg_stat_exists_stat('function', $dboid, $funcoid)"), + $present, + "Check that function stats exist is '$present' on standby"); + + return; +} + +sub test_standby_db_stats_status +{ + local $Test::Builder::Level = $Test::Builder::Level + 1; + my ($node_standby, $connect_db, $dboid, $present) = @_; + + is( $node_standby->safe_psql( + $connect_db, "SELECT pg_stat_exists_stat('database', $dboid, 0)"), + $present, + "Check that db stats exist is '$present' on standby"); +} + +# Test that stats are cleaned up on standby after dropping table or function + +# Populate test objects +my ($dboid, $tableoid, $funcoid) = + populate_standby_stats($node_primary, $node_standby, 'postgres', 'public'); + +# Test that the stats are present +test_standby_func_tab_stats_status($node_standby, 'postgres', + $dboid, $tableoid, $funcoid, 't'); + +# Drop test objects +drop_table_by_oid($node_primary, 'postgres', $tableoid); + +drop_function_by_oid($node_primary, 'postgres', $funcoid); + +# Wait for catchup +my $primary_lsn = $node_primary->lsn('write'); +$node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn); + +# Force flush of stats on standby +$node_standby->safe_psql('postgres', "SELECT pg_stat_force_next_flush()"); + +# Check table and function stats removed from standby +test_standby_func_tab_stats_status($node_standby, 'postgres', + $dboid, $tableoid, $funcoid, 'f'); + +# Check that stats are cleaned up on standby after dropping schema + +# Create schema +$node_primary->safe_psql('postgres', "CREATE SCHEMA drop_schema_test1"); + +# Wait for catchup +$primary_lsn = $node_primary->lsn('write'); +$node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn); + +# Populate test objects +($dboid, $tableoid, $funcoid) = populate_standby_stats($node_primary, + $node_standby, 'postgres', 'drop_schema_test1'); + +# Test that the stats are present +test_standby_func_tab_stats_status($node_standby, 'postgres', + $dboid, $tableoid, $funcoid, 't'); + +# Drop schema +$node_primary->safe_psql('postgres', "DROP SCHEMA drop_schema_test1 CASCADE"); + +# Wait for catchup +$primary_lsn = $node_primary->lsn('write'); +$node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn); + +# Force flush of stats on standby +$node_standby->safe_psql('postgres', "SELECT pg_stat_force_next_flush()"); + +# Check table and function stats removed from standby +test_standby_func_tab_stats_status($node_standby, 'postgres', + $dboid, $tableoid, $funcoid, 'f'); + +# Test that stats are cleaned up on standby after dropping database + +# Create the database +$node_primary->safe_psql('postgres', "CREATE DATABASE test"); + +# Wait for catchup +$primary_lsn = $node_primary->lsn('write'); +$node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn); + +# Populate test objects +($dboid, $tableoid, $funcoid) = + populate_standby_stats($node_primary, $node_standby, 'test', 'public'); + +# Test that the stats are present +test_standby_func_tab_stats_status($node_standby, 'test', + $dboid, $tableoid, $funcoid, 't'); + +test_standby_db_stats_status($node_standby, 'test', $dboid, 't'); + +# Drop db 'test' on primary +$node_primary->safe_psql('postgres', "DROP DATABASE test"); + +# Wait for catchup +$primary_lsn = $node_primary->lsn('write'); +$node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn); + +# Force flush of stats on standby +$node_standby->safe_psql('postgres', "SELECT pg_stat_force_next_flush()"); + +# Test that the stats were cleaned up on standby +# Note that this connects to 'postgres' but provides the dboid of dropped db +# 'test' which was returned by previous routine +test_standby_func_tab_stats_status($node_standby, 'postgres', + $dboid, $tableoid, $funcoid, 'f'); + +test_standby_db_stats_status($node_standby, 'postgres', $dboid, 'f'); + +done_testing(); -- 2.35.1.677.gabf474a5dd --c4n45bccvxafocvc Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v68-0020-pgstat-test-stats-handling-of-restarts-including.patch" ^ permalink raw reply [nested|flat] 4+ messages in thread
* [PATCH v69 15/28] pgstat: test: test stats interactions with streaming replication. @ 2022-03-21 19:58 Andres Freund <andres@anarazel.de> 0 siblings, 0 replies; 4+ messages in thread From: Andres Freund @ 2022-03-21 19:58 UTC (permalink / raw) Author: Melanie Plageman <melanieplageman@gmail.com> Discussion: https://postgr.es/m/20220303021600.hs34ghqcw6zcokdh@alap3.anarazel.de --- .../recovery/t/030_stats_cleanup_replica.pl | 218 ++++++++++++++++++ 1 file changed, 218 insertions(+) create mode 100644 src/test/recovery/t/030_stats_cleanup_replica.pl diff --git a/src/test/recovery/t/030_stats_cleanup_replica.pl b/src/test/recovery/t/030_stats_cleanup_replica.pl new file mode 100644 index 00000000000..be7e62a9cb4 --- /dev/null +++ b/src/test/recovery/t/030_stats_cleanup_replica.pl @@ -0,0 +1,218 @@ +# Copyright (c) 2021-2022, PostgreSQL Global Development Group + +# Tests that statistics are removed from a physical replica after being dropped +# on the primary + +use strict; +use warnings; +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +# Initialize primary node +my $node_primary = PostgreSQL::Test::Cluster->new('primary'); +# A specific role is created to perform some tests related to replication, +# and it needs proper authentication configuration. +$node_primary->init(allows_streaming => 1); + +# Set track_functions to all on primary +$node_primary->append_conf('postgresql.conf', "track_functions = 'all'"); +$node_primary->start; + +# Take backup +my $backup_name = 'my_backup'; +$node_primary->backup($backup_name); + +# Create streaming standby linking to primary +my $node_standby = PostgreSQL::Test::Cluster->new('standby'); +$node_standby->init_from_backup($node_primary, $backup_name, + has_streaming => 1); +$node_standby->start; + +my $sect = 'initial'; + +sub populate_standby_stats +{ + my ($connect_db, $schema) = @_; + # Create table on primary + $node_primary->safe_psql($connect_db, + "CREATE TABLE $schema.drop_tab_test1 AS SELECT generate_series(1,100) AS a" + ); + + # Create function on primary + $node_primary->safe_psql($connect_db, + "CREATE FUNCTION $schema.drop_func_test1() RETURNS VOID AS 'select 2;' LANGUAGE SQL IMMUTABLE" + ); + + # Wait for catchup + my $primary_lsn = $node_primary->lsn('write'); + $node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn); + + # Get database oid + my $dboid = $node_standby->safe_psql($connect_db, + "SELECT oid FROM pg_database WHERE datname = '$connect_db'"); + + # Get table oid + my $tableoid = $node_standby->safe_psql($connect_db, + "SELECT '$schema.drop_tab_test1'::regclass::oid"); + + # Do scan on standby + $node_standby->safe_psql($connect_db, + "SELECT * FROM $schema.drop_tab_test1"); + + # Get function oid + my $funcoid = $node_standby->safe_psql($connect_db, + "SELECT '$schema.drop_func_test1()'::regprocedure::oid"); + + # Call function on standby + $node_standby->safe_psql($connect_db, "SELECT $schema.drop_func_test1()"); + + return ($dboid, $tableoid, $funcoid); +} + +sub drop_function_by_oid +{ + my ($connect_db, $funcoid) = @_; + + # Get function name from returned oid + my $func_name = $node_primary->safe_psql($connect_db, + "SELECT '$funcoid'::regprocedure"); + + # Drop function on primary + $node_primary->safe_psql($connect_db, "DROP FUNCTION $func_name"); +} + +sub drop_table_by_oid +{ + my ($connect_db, $tableoid) = @_; + + # Get table name from returned oid + my $table_name = + $node_primary->safe_psql($connect_db, "SELECT '$tableoid'::regclass"); + + # Drop table on primary + $node_primary->safe_psql($connect_db, "DROP TABLE $table_name"); +} + +sub test_standby_func_tab_stats_status +{ + local $Test::Builder::Level = $Test::Builder::Level + 1; + my ($connect_db, $dboid, $tableoid, $funcoid, $present) = @_; + + my %expected = (rel => $present, func => $present); + my %stats; + + $stats{rel} = $node_standby->safe_psql($connect_db, + "SELECT pg_stat_exists_stat('relation', $dboid, $tableoid)"); + $stats{func} = $node_standby->safe_psql($connect_db, + "SELECT pg_stat_exists_stat('function', $dboid, $funcoid)"); + + is_deeply(\%stats, \%expected, "$sect: standby stats as expected"); + + return; +} + +sub test_standby_db_stats_status +{ + local $Test::Builder::Level = $Test::Builder::Level + 1; + my ($connect_db, $dboid, $present) = @_; + + is( $node_standby->safe_psql( + $connect_db, "SELECT pg_stat_exists_stat('database', $dboid, 0)"), + $present, + "$sect: standby db stats as expected"); +} + +# Test that stats are cleaned up on standby after dropping table or function + +# Populate test objects +my ($dboid, $tableoid, $funcoid) = + populate_standby_stats('postgres', 'public'); + +# Test that the stats are present +test_standby_func_tab_stats_status('postgres', + $dboid, $tableoid, $funcoid, 't'); + +# Drop test objects +drop_table_by_oid('postgres', $tableoid); + +drop_function_by_oid('postgres', $funcoid); + +$sect = 'post drop'; + +# Wait for catchup +my $primary_lsn = $node_primary->lsn('write'); +$node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn); + +# Check table and function stats removed from standby +test_standby_func_tab_stats_status('postgres', + $dboid, $tableoid, $funcoid, 'f'); + +# Check that stats are cleaned up on standby after dropping schema + +$sect = "schema creation"; + +# Create schema +$node_primary->safe_psql('postgres', "CREATE SCHEMA drop_schema_test1"); + +# Wait for catchup +$primary_lsn = $node_primary->lsn('write'); +$node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn); + +# Populate test objects +($dboid, $tableoid, $funcoid) = + populate_standby_stats('postgres', 'drop_schema_test1'); + +# Test that the stats are present +test_standby_func_tab_stats_status('postgres', + $dboid, $tableoid, $funcoid, 't'); + +# Drop schema +$node_primary->safe_psql('postgres', "DROP SCHEMA drop_schema_test1 CASCADE"); + +$sect = "post schema drop"; + +# Wait for catchup +$primary_lsn = $node_primary->lsn('write'); +$node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn); + +# Check table and function stats removed from standby +test_standby_func_tab_stats_status('postgres', + $dboid, $tableoid, $funcoid, 'f'); + +# Test that stats are cleaned up on standby after dropping database + +# Create the database +$node_primary->safe_psql('postgres', "CREATE DATABASE test"); + +$sect = "createdb"; + +# Wait for catchup +$primary_lsn = $node_primary->lsn('write'); +$node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn); + +# Populate test objects +($dboid, $tableoid, $funcoid) = populate_standby_stats('test', 'public'); + +# Test that the stats are present +test_standby_func_tab_stats_status('test', $dboid, $tableoid, $funcoid, 't'); + +test_standby_db_stats_status('test', $dboid, 't'); + +# Drop db 'test' on primary +$node_primary->safe_psql('postgres', "DROP DATABASE test"); +$sect = "post dropdb"; + +# Wait for catchup +$primary_lsn = $node_primary->lsn('write'); +$node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn); + +# Test that the stats were cleaned up on standby +# Note that this connects to 'postgres' but provides the dboid of dropped db +# 'test' which was returned by previous routine +test_standby_func_tab_stats_status('postgres', + $dboid, $tableoid, $funcoid, 'f'); + +test_standby_db_stats_status('postgres', $dboid, 'f'); + +done_testing(); -- 2.35.1.677.gabf474a5dd --be3jiks7ge4r32o3 Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v69-0016-pgstat-test-stats-handling-of-restarts-including.patch" ^ permalink raw reply [nested|flat] 4+ messages in thread
* [PATCH v70 20/27] pgstat: test: test stats interactions with streaming replication. @ 2022-03-21 19:58 Andres Freund <andres@anarazel.de> 0 siblings, 0 replies; 4+ messages in thread From: Andres Freund @ 2022-03-21 19:58 UTC (permalink / raw) Author: Melanie Plageman <melanieplageman@gmail.com> Discussion: https://postgr.es/m/20220303021600.hs34ghqcw6zcokdh@alap3.anarazel.de --- .../recovery/t/030_stats_cleanup_replica.pl | 243 ++++++++++++++++++ 1 file changed, 243 insertions(+) create mode 100644 src/test/recovery/t/030_stats_cleanup_replica.pl diff --git a/src/test/recovery/t/030_stats_cleanup_replica.pl b/src/test/recovery/t/030_stats_cleanup_replica.pl new file mode 100644 index 00000000000..6b8998e5da9 --- /dev/null +++ b/src/test/recovery/t/030_stats_cleanup_replica.pl @@ -0,0 +1,243 @@ +# Copyright (c) 2021-2022, PostgreSQL Global Development Group + +# Tests that statistics are removed from a physical replica after being dropped +# on the primary + +use strict; +use warnings; +use PostgreSQL::Test::Cluster; +use PostgreSQL::Test::Utils; +use Test::More; + +# Initialize primary node +my $node_primary = PostgreSQL::Test::Cluster->new('primary'); +# A specific role is created to perform some tests related to replication, +# and it needs proper authentication configuration. +$node_primary->init(allows_streaming => 1); + +# Set track_functions to all on primary +$node_primary->append_conf('postgresql.conf', "track_functions = 'all'"); +$node_primary->start; + +# Take backup +my $backup_name = 'my_backup'; +$node_primary->backup($backup_name); + +# Create streaming standby linking to primary +my $node_standby = PostgreSQL::Test::Cluster->new('standby'); +$node_standby->init_from_backup($node_primary, $backup_name, + has_streaming => 1); +$node_standby->start; + +my $sect = 'initial'; + +sub populate_standby_stats +{ + my ($connect_db, $schema) = @_; + # Create table on primary + $node_primary->safe_psql($connect_db, + "CREATE TABLE $schema.drop_tab_test1 AS SELECT generate_series(1,100) AS a" + ); + + # Create function on primary + $node_primary->safe_psql($connect_db, + "CREATE FUNCTION $schema.drop_func_test1() RETURNS VOID AS 'select 2;' LANGUAGE SQL IMMUTABLE" + ); + + # Wait for catchup + my $primary_lsn = $node_primary->lsn('write'); + $node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn); + + # Get database oid + my $dboid = $node_standby->safe_psql($connect_db, + "SELECT oid FROM pg_database WHERE datname = '$connect_db'"); + + # Get table oid + my $tableoid = $node_standby->safe_psql($connect_db, + "SELECT '$schema.drop_tab_test1'::regclass::oid"); + + # Do scan on standby + $node_standby->safe_psql($connect_db, + "SELECT * FROM $schema.drop_tab_test1"); + + # Get function oid + my $funcoid = $node_standby->safe_psql($connect_db, + "SELECT '$schema.drop_func_test1()'::regprocedure::oid"); + + # Call function on standby + $node_standby->safe_psql($connect_db, "SELECT $schema.drop_func_test1()"); + + return ($dboid, $tableoid, $funcoid); +} + +sub drop_function_by_oid +{ + my ($connect_db, $funcoid) = @_; + + # Get function name from returned oid + my $func_name = $node_primary->safe_psql($connect_db, + "SELECT '$funcoid'::regprocedure"); + + # Drop function on primary + $node_primary->safe_psql($connect_db, "DROP FUNCTION $func_name"); +} + +sub drop_table_by_oid +{ + my ($connect_db, $tableoid) = @_; + + # Get table name from returned oid + my $table_name = + $node_primary->safe_psql($connect_db, "SELECT '$tableoid'::regclass"); + + # Drop table on primary + $node_primary->safe_psql($connect_db, "DROP TABLE $table_name"); +} + +sub test_standby_func_tab_stats_status +{ + local $Test::Builder::Level = $Test::Builder::Level + 1; + my ($connect_db, $dboid, $tableoid, $funcoid, $present) = @_; + + my %expected = (rel => $present, func => $present); + my %stats; + + $stats{rel} = $node_standby->safe_psql($connect_db, + "SELECT pg_stat_exists_stat('relation', $dboid, $tableoid)"); + $stats{func} = $node_standby->safe_psql($connect_db, + "SELECT pg_stat_exists_stat('function', $dboid, $funcoid)"); + + is_deeply(\%stats, \%expected, "$sect: standby stats as expected"); + + return; +} + +sub test_standby_db_stats_status +{ + local $Test::Builder::Level = $Test::Builder::Level + 1; + my ($connect_db, $dboid, $present) = @_; + + is( $node_standby->safe_psql( + $connect_db, "SELECT pg_stat_exists_stat('database', $dboid, 0)"), + $present, + "$sect: standby db stats as expected"); +} + +# Test that stats are cleaned up on standby after dropping table or function + +# Populate test objects +my ($dboid, $tableoid, $funcoid) = + populate_standby_stats('postgres', 'public'); + +# Test that the stats are present +test_standby_func_tab_stats_status('postgres', + $dboid, $tableoid, $funcoid, 't'); + +# Drop test objects +drop_table_by_oid('postgres', $tableoid); + +drop_function_by_oid('postgres', $funcoid); + +$sect = 'post drop'; + +# Wait for catchup +my $primary_lsn = $node_primary->lsn('write'); +$node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn); + +# Check table and function stats removed from standby +test_standby_func_tab_stats_status('postgres', + $dboid, $tableoid, $funcoid, 'f'); + +# Check that stats are cleaned up on standby after dropping schema + +$sect = "schema creation"; + +# Create schema +$node_primary->safe_psql('postgres', "CREATE SCHEMA drop_schema_test1"); + +# Wait for catchup +$primary_lsn = $node_primary->lsn('write'); +$node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn); + +# Populate test objects +($dboid, $tableoid, $funcoid) = + populate_standby_stats('postgres', 'drop_schema_test1'); + +# Test that the stats are present +test_standby_func_tab_stats_status('postgres', + $dboid, $tableoid, $funcoid, 't'); + +# Drop schema +$node_primary->safe_psql('postgres', "DROP SCHEMA drop_schema_test1 CASCADE"); + +$sect = "post schema drop"; + +# Wait for catchup +$primary_lsn = $node_primary->lsn('write'); +$node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn); + +# Check table and function stats removed from standby +test_standby_func_tab_stats_status('postgres', + $dboid, $tableoid, $funcoid, 'f'); + +# Test that stats are cleaned up on standby after dropping database + +# Create the database +$node_primary->safe_psql('postgres', "CREATE DATABASE test"); + +$sect = "createdb"; + +# Wait for catchup +$primary_lsn = $node_primary->lsn('write'); +$node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn); + +# Populate test objects +($dboid, $tableoid, $funcoid) = populate_standby_stats('test', 'public'); + +# Test that the stats are present +test_standby_func_tab_stats_status('test', $dboid, $tableoid, $funcoid, 't'); + +test_standby_db_stats_status('test', $dboid, 't'); + +# Drop db 'test' on primary +$node_primary->safe_psql('postgres', "DROP DATABASE test"); +$sect = "post dropdb"; + +# Wait for catchup +$primary_lsn = $node_primary->lsn('write'); +$node_primary->wait_for_catchup($node_standby, 'replay', $primary_lsn); + +# Test that the stats were cleaned up on standby +# Note that this connects to 'postgres' but provides the dboid of dropped db +# 'test' which was returned by previous routine +test_standby_func_tab_stats_status('postgres', + $dboid, $tableoid, $funcoid, 'f'); + +test_standby_db_stats_status('postgres', $dboid, 'f'); + +# verify that stats persist across graceful restarts on a replica + +# NB: Can't test database stats, they're immediately repopulated when +# reconnecting... +$sect = "pre restart"; +($dboid, $tableoid, $funcoid) = populate_standby_stats('postgres', 'public'); +test_standby_func_tab_stats_status('postgres', + $dboid, $tableoid, $funcoid, 't'); + +$node_standby->restart(); + +$sect = "post non-immediate"; + +test_standby_func_tab_stats_status('postgres', + $dboid, $tableoid, $funcoid, 't'); + +# but gone after an immediate restart +$node_standby->stop('immediate'); +$node_standby->start(); + +$sect = "post immediate restart"; + +test_standby_func_tab_stats_status('postgres', + $dboid, $tableoid, $funcoid, 'f'); + +done_testing(); -- 2.35.1.677.gabf474a5dd --vl7on5vfmrsyxmos Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v70-0021-pgstat-test-subscriber-stats-reset-and-drop.patch" ^ permalink raw reply [nested|flat] 4+ messages in thread
* [PATCH v4 2/7] Row pattern recognition patch (parse/analysis). @ 2023-08-09 07:56 Tatsuo Ishii <ishii@postgresql.org> 0 siblings, 0 replies; 4+ messages in thread From: Tatsuo Ishii @ 2023-08-09 07:56 UTC (permalink / raw) --- src/backend/parser/parse_agg.c | 7 + src/backend/parser/parse_clause.c | 292 +++++++++++++++++++++++++++++- src/backend/parser/parse_expr.c | 4 + src/backend/parser/parse_func.c | 3 + 4 files changed, 305 insertions(+), 1 deletion(-) diff --git a/src/backend/parser/parse_agg.c b/src/backend/parser/parse_agg.c index 85cd47b7ae..aa7a1cee80 100644 --- a/src/backend/parser/parse_agg.c +++ b/src/backend/parser/parse_agg.c @@ -564,6 +564,10 @@ check_agglevels_and_constraints(ParseState *pstate, Node *expr) errkind = true; break; + case EXPR_KIND_RPR_DEFINE: + errkind = true; + break; + /* * There is intentionally no default: case here, so that the * compiler will warn if we add a new ParseExprKind without @@ -953,6 +957,9 @@ transformWindowFuncCall(ParseState *pstate, WindowFunc *wfunc, case EXPR_KIND_CYCLE_MARK: errkind = true; break; + case EXPR_KIND_RPR_DEFINE: + errkind = true; + break; /* * There is intentionally no default: case here, so that the diff --git a/src/backend/parser/parse_clause.c b/src/backend/parser/parse_clause.c index 334b9b42bd..60020a7025 100644 --- a/src/backend/parser/parse_clause.c +++ b/src/backend/parser/parse_clause.c @@ -100,7 +100,10 @@ static WindowClause *findWindowClause(List *wclist, const char *name); static Node *transformFrameOffset(ParseState *pstate, int frameOptions, Oid rangeopfamily, Oid rangeopcintype, Oid *inRangeFunc, Node *clause); - +static void transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef, List **targetlist); +static List *transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef, List **targetlist); +static void transformPatternClause(ParseState *pstate, WindowClause *wc, WindowDef *windef); +static List *transformMeasureClause(ParseState *pstate, WindowClause *wc, WindowDef *windef); /* * transformFromClause - @@ -2950,6 +2953,10 @@ transformWindowDefinitions(ParseState *pstate, rangeopfamily, rangeopcintype, &wc->endInRangeFunc, windef->endOffset); + + /* Process Row Pattern Recognition related clauses */ + transformRPR(pstate, wc, windef, targetlist); + wc->runCondition = NIL; wc->winref = winref; @@ -3815,3 +3822,286 @@ transformFrameOffset(ParseState *pstate, int frameOptions, return node; } + +/* + * transformRPR + * Process Row Pattern Recognition related clauses + */ +static void +transformRPR(ParseState *pstate, WindowClause *wc, WindowDef *windef, List **targetlist) +{ + /* + * Window definition exists? + */ + if (windef == NULL) + return; + + /* + * Row Pattern Common Syntax clause exists? + */ + if (windef->rpCommonSyntax == NULL) + return; + + /* Check Frame option. Frame must start at current row */ + if ((wc->frameOptions & FRAMEOPTION_START_CURRENT_ROW) == 0) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("FRAME must start at current row when row patttern recognition is used"))); + + /* Transform AFTER MACH SKIP TO clause */ + wc->rpSkipTo = windef->rpCommonSyntax->rpSkipTo; + + /* Transform SEEK or INITIAL clause */ + wc->initial = windef->rpCommonSyntax->initial; + + /* Transform DEFINE clause into list of TargetEntry's */ + wc->defineClause = transformDefineClause(pstate, wc, windef, targetlist); + + /* Check PATTERN clause and copy to patternClause */ + transformPatternClause(pstate, wc, windef); + + /* Transform MEASURE clause */ + transformMeasureClause(pstate, wc, windef); +} + +/* + * transformDefineClause Process DEFINE clause and transform ResTarget into + * list of TargetEntry. + * + * XXX we only support column reference in row pattern definition search + * condition, e.g. "price". <row pattern definition variable name>.<column + * reference> is not supported, e.g. "A.price". + */ +static List * +transformDefineClause(ParseState *pstate, WindowClause *wc, WindowDef *windef, List **targetlist) +{ + /* DEFINE variable name initials */ + static char *defineVariableInitials = "abcdefghijklmnopqrstuvwxyz"; + + ListCell *lc, *l; + ResTarget *restarget, *r; + List *restargets; + char *name; + int initialLen; + int i; + + /* + * If Row Definition Common Syntax exists, DEFINE clause must exist. + * (the raw parser should have already checked it.) + */ + Assert(windef->rpCommonSyntax->rpDefs != NULL); + + /* + * Check and add "A AS A IS TRUE" if pattern variable is missing in DEFINE + * per the SQL standard. + */ + restargets = NIL; + foreach(lc, windef->rpCommonSyntax->rpPatterns) + { + A_Expr *a; + bool found = false; + + if (!IsA(lfirst(lc), A_Expr)) + ereport(ERROR, + errmsg("node type is not A_Expr")); + + a = (A_Expr *)lfirst(lc); + name = strVal(a->lexpr); + + foreach(l, windef->rpCommonSyntax->rpDefs) + { + restarget = (ResTarget *)lfirst(l); + + if (!strcmp(restarget->name, name)) + { + found = true; + break; + } + } + + if (!found) + { + /* + * "name" is missing. So create "name AS name IS TRUE" ResTarget + * node and add it to the temporary list. + */ + A_Const *n; + + restarget = makeNode(ResTarget); + n = makeNode(A_Const); + n->val.boolval.type = T_Boolean; + n->val.boolval.boolval = true; + n->location = -1; + restarget->name = pstrdup(name); + restarget->indirection = NIL; + restarget->val = (Node *)n; + restarget->location = -1; + restargets = lappend((List *)restargets, restarget); + } + } + + if (list_length(restargets) >= 1) + { + /* add missing DEFINEs */ + windef->rpCommonSyntax->rpDefs = list_concat(windef->rpCommonSyntax->rpDefs, + restargets); + list_free(restargets); + } + + /* + * Check for duplicate row pattern definition variables. The standard + * requires that no two row pattern definition variable names shall be + * equivalent. + */ + restargets = NIL; + foreach(lc, windef->rpCommonSyntax->rpDefs) + { + restarget = (ResTarget *)lfirst(lc); + name = restarget->name; + + /* + * Add DEFINE expression (Restarget->val) to the targetlist as a + * TargetEntry if it does not exist yet. Planner will add the column + * ref var node to the outer plan's target list later on. This makes + * DEFINE expression could access the outer tuple while evaluating + * PATTERN. + * + * XXX: adding whole expressions of DEFINE to the plan.targetlist is + * not so good, because it's not necessary to evalute the expression + * in the target list while running the plan. We should extract the + * var nodes only then add them to the plan.targetlist. + */ + findTargetlistEntrySQL99(pstate, (Node *)restarget->val, targetlist, EXPR_KIND_RPR_DEFINE); + + /* + * Make sure that the row pattern definition search condition is a + * boolean expression. + */ + transformWhereClause(pstate, restarget->val, + EXPR_KIND_RPR_DEFINE, "DEFINE"); + + foreach(l, restargets) + { + char *n; + + r = (ResTarget *) lfirst(l); + n = r->name; + + if (!strcmp(n, name)) + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("row pattern definition variable name \"%s\" appears more than once in DEFINE clause", + name), + parser_errposition(pstate, exprLocation((Node *)r)))); + } + restargets = lappend(restargets, restarget); + } + list_free(restargets); + + /* + * Create list of row pattern DEFINE variable name's initial. + * We assign [a-z] to them (up to 26 variable names are allowed). + */ + restargets = NIL; + i = 0; + initialLen = strlen(defineVariableInitials); + + foreach(lc, windef->rpCommonSyntax->rpDefs) + { + char initial[2]; + + restarget = (ResTarget *)lfirst(lc); + name = restarget->name; + + if (i >= initialLen) + { + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("number of row pattern definition variable names exceeds %d", initialLen), + parser_errposition(pstate, exprLocation((Node *)restarget)))); + } + initial[0] = defineVariableInitials[i++]; + initial[1] = '\0'; + wc->defineInitial = lappend(wc->defineInitial, makeString(pstrdup(initial))); + } + + return transformTargetList(pstate, windef->rpCommonSyntax->rpDefs, + EXPR_KIND_RPR_DEFINE); +} + +/* + * transformPatternClause + * Process PATTERN clause and return PATTERN clause in the raw parse tree + */ +static void +transformPatternClause(ParseState *pstate, WindowClause *wc, WindowDef *windef) +{ + ListCell *lc, *l; + + /* + * Row Pattern Common Syntax clause exists? + */ + if (windef->rpCommonSyntax == NULL) + return; + + /* + * Primary row pattern variable names in PATTERN clause must appear in + * DEFINE clause as row pattern definition variable names. + */ + wc->patternVariable = NIL; + wc->patternRegexp = NIL; + foreach(lc, windef->rpCommonSyntax->rpPatterns) + { + A_Expr *a; + char *name; + char *regexp; + bool found = false; + + if (!IsA(lfirst(lc), A_Expr)) + ereport(ERROR, + errmsg("node type is not A_Expr")); + + a = (A_Expr *)lfirst(lc); + name = strVal(a->lexpr); + + foreach(l, windef->rpCommonSyntax->rpDefs) + { + ResTarget *restarget = (ResTarget *)lfirst(l); + + if (!strcmp(restarget->name, name)) + { + found = true; + break; + } + } + + if (!found) + { + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("primary row pattern variable name \"%s\" does not appear in DEFINE clause", + name), + parser_errposition(pstate, exprLocation((Node *)a)))); + } + wc->patternVariable = lappend(wc->patternVariable, makeString(pstrdup(name))); + regexp = strVal(lfirst(list_head(a->name))); + wc->patternRegexp = lappend(wc->patternRegexp, makeString(pstrdup(regexp))); + } +} + +/* + * transformMeasureClause + * Process MEASURE clause + * XXX MEASURE clause is not supported yet + */ +static List * +transformMeasureClause(ParseState *pstate, WindowClause *wc, WindowDef *windef) +{ + if (windef->rowPatternMeasures == NIL) + return NIL; + + ereport(ERROR, + (errcode(ERRCODE_SYNTAX_ERROR), + errmsg("%s","MEASURE clause is not supported yet"), + parser_errposition(pstate, exprLocation((Node *)windef->rowPatternMeasures)))); +} diff --git a/src/backend/parser/parse_expr.c b/src/backend/parser/parse_expr.c index fed8e4d089..8921b7ae01 100644 --- a/src/backend/parser/parse_expr.c +++ b/src/backend/parser/parse_expr.c @@ -557,6 +557,7 @@ transformColumnRef(ParseState *pstate, ColumnRef *cref) case EXPR_KIND_COPY_WHERE: case EXPR_KIND_GENERATED_COLUMN: case EXPR_KIND_CYCLE_MARK: + case EXPR_KIND_RPR_DEFINE: /* okay */ break; @@ -1770,6 +1771,7 @@ transformSubLink(ParseState *pstate, SubLink *sublink) case EXPR_KIND_VALUES: case EXPR_KIND_VALUES_SINGLE: case EXPR_KIND_CYCLE_MARK: + case EXPR_KIND_RPR_DEFINE: /* okay */ break; case EXPR_KIND_CHECK_CONSTRAINT: @@ -3149,6 +3151,8 @@ ParseExprKindName(ParseExprKind exprKind) return "GENERATED AS"; case EXPR_KIND_CYCLE_MARK: return "CYCLE"; + case EXPR_KIND_RPR_DEFINE: + return "DEFINE"; /* * There is intentionally no default: case here, so that the diff --git a/src/backend/parser/parse_func.c b/src/backend/parser/parse_func.c index b3f0b6a137..2ff3699538 100644 --- a/src/backend/parser/parse_func.c +++ b/src/backend/parser/parse_func.c @@ -2656,6 +2656,9 @@ check_srf_call_placement(ParseState *pstate, Node *last_srf, int location) case EXPR_KIND_CYCLE_MARK: errkind = true; break; + case EXPR_KIND_RPR_DEFINE: + errkind = true; + break; /* * There is intentionally no default: case here, so that the -- 2.25.1 ----Next_Part(Wed_Aug__9_17_41_12_2023_134)-- Content-Type: Text/X-Patch; charset=us-ascii Content-Transfer-Encoding: 7bit Content-Disposition: inline; filename="v4-0003-Row-pattern-recognition-patch-planner.patch" ^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2023-08-09 07:56 UTC | newest] Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed) -- links below jump to the message on this page -- 2022-03-21 19:58 [PATCH v68 19/31] pgstat: test: test stats interactions with streaming replication. Andres Freund <andres@anarazel.de> 2022-03-21 19:58 [PATCH v69 15/28] pgstat: test: test stats interactions with streaming replication. Andres Freund <andres@anarazel.de> 2022-03-21 19:58 [PATCH v70 20/27] pgstat: test: test stats interactions with streaming replication. Andres Freund <andres@anarazel.de> 2023-08-09 07:56 [PATCH v4 2/7] Row pattern recognition patch (parse/analysis). Tatsuo Ishii <ishii@postgresql.org>
This inbox is served by agora; see mirroring instructions for how to clone and mirror all data and code used for this inbox