Adding upstream version 15.5.upstream/15.5

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

6 files changed, 542 insertions, 0 deletions

diff --git a/src/test/modules/test_misc/.gitignore b/src/test/modules/test_misc/.gitignore
new file mode 100644
index 0000000..5dcb3ff
--- /dev/null
+++ b/src/test/modules/test_misc/.gitignore
@@ -0,0 +1,4 @@
+# Generated subdirectories
+/log/
+/results/
+/tmp_check/
diff --git a/src/test/modules/test_misc/Makefile b/src/test/modules/test_misc/Makefile
new file mode 100644
index 0000000..39c6c20
--- /dev/null
+++ b/src/test/modules/test_misc/Makefile
@@ -0,0 +1,14 @@
+# src/test/modules/test_misc/Makefile
+
+TAP_TESTS = 1
+
+ifdef USE_PGXS
+PG_CONFIG = pg_config
+PGXS := $(shell $(PG_CONFIG) --pgxs)
+include $(PGXS)
+else
+subdir = src/test/modules/test_misc
+top_builddir = ../../../..
+include $(top_builddir)/src/Makefile.global
+include $(top_srcdir)/contrib/contrib-global.mk
+endif
diff --git a/src/test/modules/test_misc/README b/src/test/modules/test_misc/README
new file mode 100644
index 0000000..4876733
--- /dev/null
+++ b/src/test/modules/test_misc/README
@@ -0,0 +1,4 @@
+This directory doesn't actually contain any extension module.
+
+What it is is a home for otherwise-unclassified TAP tests that exercise core
+server features.  We might equally well have called it, say, src/test/misc.
diff --git a/src/test/modules/test_misc/t/001_constraint_validation.pl b/src/test/modules/test_misc/t/001_constraint_validation.pl
new file mode 100644
index 0000000..3b9fc66
--- /dev/null
+++ b/src/test/modules/test_misc/t/001_constraint_validation.pl
@@ -0,0 +1,315 @@
+
+# Copyright (c) 2021-2022, PostgreSQL Global Development Group
+
+# Verify that ALTER TABLE optimizes certain operations as expected
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+# Initialize a test cluster
+my $node = PostgreSQL::Test::Cluster->new('primary');
+$node->init();
+# Turn message level up to DEBUG1 so that we get the messages we want to see
+$node->append_conf('postgresql.conf', 'client_min_messages = DEBUG1');
+$node->start;
+
+# Run a SQL command and return psql's stderr (including debug messages)
+sub run_sql_command
+{
+	my $sql = shift;
+	my $stderr;
+
+	$node->psql(
+		'postgres',
+		$sql,
+		stderr        => \$stderr,
+		on_error_die  => 1,
+		on_error_stop => 1);
+	return $stderr;
+}
+
+# Check whether result of run_sql_command shows that we did a verify pass
+sub is_table_verified
+{
+	my $output = shift;
+	return index($output, 'DEBUG:  verifying table') != -1;
+}
+
+my $output;
+
+note "test alter table set not null";
+
+run_sql_command(
+	'create table atacc1 (test_a int, test_b int);
+	 insert into atacc1 values (1, 2);');
+
+$output = run_sql_command('alter table atacc1 alter test_a set not null;');
+ok(is_table_verified($output),
+	'column test_a without constraint will scan table');
+
+run_sql_command(
+	'alter table atacc1 alter test_a drop not null;
+	 alter table atacc1 add constraint atacc1_constr_a_valid
+	 check(test_a is not null);');
+
+# normal run will verify table data
+$output = run_sql_command('alter table atacc1 alter test_a set not null;');
+ok(!is_table_verified($output), 'with constraint will not scan table');
+ok( $output =~
+	  m/existing constraints on column "atacc1.test_a" are sufficient to prove that it does not contain nulls/,
+	'test_a proved by constraints');
+
+run_sql_command('alter table atacc1 alter test_a drop not null;');
+
+# we have check only for test_a column, so we need verify table for test_b
+$output = run_sql_command(
+	'alter table atacc1 alter test_b set not null, alter test_a set not null;'
+);
+ok(is_table_verified($output), 'table was scanned');
+# we may miss debug message for test_a constraint because we need verify table due test_b
+ok( !(  $output =~
+		m/existing constraints on column "atacc1.test_b" are sufficient to prove that it does not contain nulls/
+	),
+	'test_b not proved by wrong constraints');
+run_sql_command(
+	'alter table atacc1 alter test_a drop not null, alter test_b drop not null;'
+);
+
+# test with both columns having check constraints
+run_sql_command(
+	'alter table atacc1 add constraint atacc1_constr_b_valid check(test_b is not null);'
+);
+$output = run_sql_command(
+	'alter table atacc1 alter test_b set not null, alter test_a set not null;'
+);
+ok(!is_table_verified($output), 'table was not scanned for both columns');
+ok( $output =~
+	  m/existing constraints on column "atacc1.test_a" are sufficient to prove that it does not contain nulls/,
+	'test_a proved by constraints');
+ok( $output =~
+	  m/existing constraints on column "atacc1.test_b" are sufficient to prove that it does not contain nulls/,
+	'test_b proved by constraints');
+run_sql_command('drop table atacc1;');
+
+note "test alter table attach partition";
+
+run_sql_command(
+	'CREATE TABLE list_parted2 (
+	a int,
+	b char
+	) PARTITION BY LIST (a);
+	CREATE TABLE part_3_4 (
+	LIKE list_parted2,
+	CONSTRAINT check_a CHECK (a IN (3)));');
+
+# need NOT NULL to skip table scan
+$output = run_sql_command(
+	'ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4);'
+);
+ok(is_table_verified($output), 'table part_3_4 scanned');
+
+run_sql_command(
+	'ALTER TABLE list_parted2 DETACH PARTITION part_3_4;
+	 ALTER TABLE part_3_4 ALTER a SET NOT NULL;');
+
+$output = run_sql_command(
+	'ALTER TABLE list_parted2 ATTACH PARTITION part_3_4 FOR VALUES IN (3, 4);'
+);
+ok(!is_table_verified($output), 'table part_3_4 not scanned');
+ok( $output =~
+	  m/partition constraint for table "part_3_4" is implied by existing constraints/,
+	'part_3_4 verified by existing constraints');
+
+# test attach default partition
+run_sql_command(
+	'CREATE TABLE list_parted2_def (
+	LIKE list_parted2,
+	CONSTRAINT check_a CHECK (a IN (5, 6)));');
+$output = run_sql_command(
+	'ALTER TABLE list_parted2 ATTACH PARTITION list_parted2_def default;');
+ok(!is_table_verified($output), 'table list_parted2_def not scanned');
+ok( $output =~
+	  m/partition constraint for table "list_parted2_def" is implied by existing constraints/,
+	'list_parted2_def verified by existing constraints');
+
+$output = run_sql_command(
+	'CREATE TABLE part_55_66 PARTITION OF list_parted2 FOR VALUES IN (55, 66);'
+);
+ok(!is_table_verified($output), 'table list_parted2_def not scanned');
+ok( $output =~
+	  m/updated partition constraint for default partition "list_parted2_def" is implied by existing constraints/,
+	'updated partition constraint for default partition list_parted2_def');
+
+# test attach another partitioned table
+run_sql_command(
+	'CREATE TABLE part_5 (
+	LIKE list_parted2
+	) PARTITION BY LIST (b);
+	CREATE TABLE part_5_a PARTITION OF part_5 FOR VALUES IN (\'a\');
+	ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 5);'
+);
+$output = run_sql_command(
+	'ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5);');
+ok(!($output =~ m/verifying table "part_5"/), 'table part_5 not scanned');
+ok($output =~ m/verifying table "list_parted2_def"/,
+	'list_parted2_def scanned');
+ok( $output =~
+	  m/partition constraint for table "part_5" is implied by existing constraints/,
+	'part_5 verified by existing constraints');
+
+run_sql_command(
+	'ALTER TABLE list_parted2 DETACH PARTITION part_5;
+	 ALTER TABLE part_5 DROP CONSTRAINT check_a;');
+
+# scan should again be skipped, even though NOT NULL is now a column property
+run_sql_command(
+	'ALTER TABLE part_5 ADD CONSTRAINT check_a CHECK (a IN (5)),
+	 ALTER a SET NOT NULL;'
+);
+$output = run_sql_command(
+	'ALTER TABLE list_parted2 ATTACH PARTITION part_5 FOR VALUES IN (5);');
+ok(!($output =~ m/verifying table "part_5"/), 'table part_5 not scanned');
+ok($output =~ m/verifying table "list_parted2_def"/,
+	'list_parted2_def scanned');
+ok( $output =~
+	  m/partition constraint for table "part_5" is implied by existing constraints/,
+	'part_5 verified by existing constraints');
+
+# Check the case where attnos of the partitioning columns in the table being
+# attached differs from the parent.  It should not affect the constraint-
+# checking logic that allows to skip the scan.
+run_sql_command(
+	'CREATE TABLE part_6 (
+	c int,
+	LIKE list_parted2,
+	CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 6)
+	);
+	ALTER TABLE part_6 DROP c;');
+$output = run_sql_command(
+	'ALTER TABLE list_parted2 ATTACH PARTITION part_6 FOR VALUES IN (6);');
+ok(!($output =~ m/verifying table "part_6"/), 'table part_6 not scanned');
+ok($output =~ m/verifying table "list_parted2_def"/,
+	'list_parted2_def scanned');
+ok( $output =~
+	  m/partition constraint for table "part_6" is implied by existing constraints/,
+	'part_6 verified by existing constraints');
+
+# Similar to above, but the table being attached is a partitioned table
+# whose partition has still different attnos for the root partitioning
+# columns.
+run_sql_command(
+	'CREATE TABLE part_7 (
+	LIKE list_parted2,
+	CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 7)
+	) PARTITION BY LIST (b);
+	CREATE TABLE part_7_a_null (
+	c int,
+	d int,
+	e int,
+	LIKE list_parted2,  -- a will have attnum = 4
+	CONSTRAINT check_b CHECK (b IS NULL OR b = \'a\'),
+	CONSTRAINT check_a CHECK (a IS NOT NULL AND a = 7)
+	);
+	ALTER TABLE part_7_a_null DROP c, DROP d, DROP e;');
+
+$output = run_sql_command(
+	'ALTER TABLE part_7 ATTACH PARTITION part_7_a_null FOR VALUES IN (\'a\', null);'
+);
+ok(!is_table_verified($output), 'table not scanned');
+ok( $output =~
+	  m/partition constraint for table "part_7_a_null" is implied by existing constraints/,
+	'part_7_a_null verified by existing constraints');
+$output = run_sql_command(
+	'ALTER TABLE list_parted2 ATTACH PARTITION part_7 FOR VALUES IN (7);');
+ok(!is_table_verified($output), 'tables not scanned');
+ok( $output =~
+	  m/partition constraint for table "part_7" is implied by existing constraints/,
+	'part_7 verified by existing constraints');
+ok( $output =~
+	  m/updated partition constraint for default partition "list_parted2_def" is implied by existing constraints/,
+	'updated partition constraint for default partition list_parted2_def');
+
+run_sql_command(
+	'CREATE TABLE range_parted (
+	a int,
+	b int
+	) PARTITION BY RANGE (a, b);
+	CREATE TABLE range_part1 (
+	a int NOT NULL CHECK (a = 1),
+	b int NOT NULL);');
+
+$output = run_sql_command(
+	'ALTER TABLE range_parted ATTACH PARTITION range_part1 FOR VALUES FROM (1, 1) TO (1, 10);'
+);
+ok(is_table_verified($output), 'table range_part1 scanned');
+ok( !(  $output =~
+		m/partition constraint for table "range_part1" is implied by existing constraints/
+	),
+	'range_part1 not verified by existing constraints');
+
+run_sql_command(
+	'CREATE TABLE range_part2 (
+	a int NOT NULL CHECK (a = 1),
+	b int NOT NULL CHECK (b >= 10 and b < 18)
+);');
+$output = run_sql_command(
+	'ALTER TABLE range_parted ATTACH PARTITION range_part2 FOR VALUES FROM (1, 10) TO (1, 20);'
+);
+ok(!is_table_verified($output), 'table range_part2 not scanned');
+ok( $output =~
+	  m/partition constraint for table "range_part2" is implied by existing constraints/,
+	'range_part2 verified by existing constraints');
+
+# If a partitioned table being created or an existing table being attached
+# as a partition does not have a constraint that would allow validation scan
+# to be skipped, but an individual partition does, then the partition's
+# validation scan is skipped.
+run_sql_command(
+	'CREATE TABLE quuux (a int, b text) PARTITION BY LIST (a);
+	CREATE TABLE quuux_default PARTITION OF quuux DEFAULT PARTITION BY LIST (b);
+	CREATE TABLE quuux_default1 PARTITION OF quuux_default (
+	CONSTRAINT check_1 CHECK (a IS NOT NULL AND a = 1)
+	) FOR VALUES IN (\'b\');
+	CREATE TABLE quuux1 (a int, b text);');
+
+$output = run_sql_command(
+	'ALTER TABLE quuux ATTACH PARTITION quuux1 FOR VALUES IN (1);');
+ok(is_table_verified($output), 'quuux1 table scanned');
+ok( !(  $output =~
+		m/partition constraint for table "quuux1" is implied by existing constraints/
+	),
+	'quuux1 verified by existing constraints');
+
+run_sql_command('CREATE TABLE quuux2 (a int, b text);');
+$output = run_sql_command(
+	'ALTER TABLE quuux ATTACH PARTITION quuux2 FOR VALUES IN (2);');
+ok(!($output =~ m/verifying table "quuux_default1"/),
+	'quuux_default1 not scanned');
+ok($output =~ m/verifying table "quuux2"/, 'quuux2 scanned');
+ok( $output =~
+	  m/updated partition constraint for default partition "quuux_default1" is implied by existing constraints/,
+	'updated partition constraint for default partition quuux_default1');
+run_sql_command('DROP TABLE quuux1, quuux2;');
+
+# should validate for quuux1, but not for quuux2
+$output = run_sql_command(
+	'CREATE TABLE quuux1 PARTITION OF quuux FOR VALUES IN (1);');
+ok(!is_table_verified($output), 'tables not scanned');
+ok( !(  $output =~
+		m/partition constraint for table "quuux1" is implied by existing constraints/
+	),
+	'quuux1 verified by existing constraints');
+$output = run_sql_command(
+	'CREATE TABLE quuux2 PARTITION OF quuux FOR VALUES IN (2);');
+ok(!is_table_verified($output), 'tables not scanned');
+ok( $output =~
+	  m/updated partition constraint for default partition "quuux_default1" is implied by existing constraints/,
+	'updated partition constraint for default partition quuux_default1');
+run_sql_command('DROP TABLE quuux;');
+
+$node->stop('fast');
+
+done_testing();
diff --git a/src/test/modules/test_misc/t/002_tablespace.pl b/src/test/modules/test_misc/t/002_tablespace.pl
new file mode 100644
index 0000000..95cd2b7
--- /dev/null
+++ b/src/test/modules/test_misc/t/002_tablespace.pl
@@ -0,0 +1,95 @@
+# Simple tablespace tests that can't be replicated on the same host
+# due to the use of absolute paths, so we keep them out of the regular
+# regression tests.
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init;
+$node->start;
+
+# Create a couple of directories to use as tablespaces.
+my $basedir      = $node->basedir();
+my $TS1_LOCATION = "$basedir/ts1";
+$TS1_LOCATION =~ s/\/\.\//\//g;    # collapse foo/./bar to foo/bar
+mkdir($TS1_LOCATION);
+my $TS2_LOCATION = "$basedir/ts2";
+$TS2_LOCATION =~ s/\/\.\//\//g;
+mkdir($TS2_LOCATION);
+
+my $result;
+
+# Create a tablespace with an absolute path
+$result = $node->psql('postgres',
+	"CREATE TABLESPACE regress_ts1 LOCATION '$TS1_LOCATION'");
+ok($result == 0, 'create tablespace with absolute path');
+
+# Can't create a tablespace where there is one already
+$result = $node->psql('postgres',
+	"CREATE TABLESPACE regress_ts1 LOCATION '$TS1_LOCATION'");
+ok($result != 0, 'clobber tablespace with absolute path');
+
+# Create table in it
+$result = $node->psql('postgres', "CREATE TABLE t () TABLESPACE regress_ts1");
+ok($result == 0, 'create table in tablespace with absolute path');
+
+# Can't drop a tablespace that still has a table in it
+$result = $node->psql('postgres', "DROP TABLESPACE regress_ts1");
+ok($result != 0, 'drop tablespace with absolute path');
+
+# Drop the table
+$result = $node->psql('postgres', "DROP TABLE t");
+ok($result == 0, 'drop table in tablespace with absolute path');
+
+# Drop the tablespace
+$result = $node->psql('postgres', "DROP TABLESPACE regress_ts1");
+ok($result == 0, 'drop tablespace with absolute path');
+
+# Create two absolute tablespaces and two in-place tablespaces, so we can
+# testing various kinds of tablespace moves.
+$result = $node->psql('postgres',
+	"CREATE TABLESPACE regress_ts1 LOCATION '$TS1_LOCATION'");
+ok($result == 0, 'create tablespace 1 with absolute path');
+$result = $node->psql('postgres',
+	"CREATE TABLESPACE regress_ts2 LOCATION '$TS2_LOCATION'");
+ok($result == 0, 'create tablespace 2 with absolute path');
+$result = $node->psql('postgres',
+	"SET allow_in_place_tablespaces=on; CREATE TABLESPACE regress_ts3 LOCATION ''"
+);
+ok($result == 0, 'create tablespace 3 with in-place directory');
+$result = $node->psql('postgres',
+	"SET allow_in_place_tablespaces=on; CREATE TABLESPACE regress_ts4 LOCATION ''"
+);
+ok($result == 0, 'create tablespace 4 with in-place directory');
+
+# Create a table and test moving between absolute and in-place tablespaces
+$result = $node->psql('postgres', "CREATE TABLE t () TABLESPACE regress_ts1");
+ok($result == 0, 'create table in tablespace 1');
+$result = $node->psql('postgres', "ALTER TABLE t SET tablespace regress_ts2");
+ok($result == 0, 'move table abs->abs');
+$result = $node->psql('postgres', "ALTER TABLE t SET tablespace regress_ts3");
+ok($result == 0, 'move table abs->in-place');
+$result = $node->psql('postgres', "ALTER TABLE t SET tablespace regress_ts4");
+ok($result == 0, 'move table in-place->in-place');
+$result = $node->psql('postgres', "ALTER TABLE t SET tablespace regress_ts1");
+ok($result == 0, 'move table in-place->abs');
+
+# Drop everything
+$result = $node->psql('postgres', "DROP TABLE t");
+ok($result == 0, 'create table in tablespace 1');
+$result = $node->psql('postgres', "DROP TABLESPACE regress_ts1");
+ok($result == 0, 'drop tablespace 1');
+$result = $node->psql('postgres', "DROP TABLESPACE regress_ts2");
+ok($result == 0, 'drop tablespace 2');
+$result = $node->psql('postgres', "DROP TABLESPACE regress_ts3");
+ok($result == 0, 'drop tablespace 3');
+$result = $node->psql('postgres', "DROP TABLESPACE regress_ts4");
+ok($result == 0, 'drop tablespace 4');
+
+$node->stop;
+
+done_testing();
diff --git a/src/test/modules/test_misc/t/003_check_guc.pl b/src/test/modules/test_misc/t/003_check_guc.pl
new file mode 100644
index 0000000..20c2dc3
--- /dev/null
+++ b/src/test/modules/test_misc/t/003_check_guc.pl
@@ -0,0 +1,110 @@
+# Tests to cross-check the consistency of GUC parameters with
+# postgresql.conf.sample.
+
+use strict;
+use warnings;
+use PostgreSQL::Test::Cluster;
+use PostgreSQL::Test::Utils;
+use Test::More;
+
+my $node = PostgreSQL::Test::Cluster->new('main');
+$node->init;
+$node->start;
+
+# Grab the names of all the parameters that can be listed in the
+# configuration sample file.  config_file is an exception, it is not
+# in postgresql.conf.sample but is part of the lists from guc.c.
+# Custom GUCs loaded by extensions are excluded.
+my $all_params = $node->safe_psql(
+	'postgres',
+	"SELECT name
+     FROM pg_settings
+   WHERE NOT 'NOT_IN_SAMPLE' = ANY (pg_settings_get_flags(name)) AND
+       name <> 'config_file' AND category <> 'Customized Options'
+     ORDER BY 1");
+# Note the lower-case conversion, for consistency.
+my @all_params_array = split("\n", lc($all_params));
+
+# Grab the names of all parameters marked as NOT_IN_SAMPLE.
+my $not_in_sample = $node->safe_psql(
+	'postgres',
+	"SELECT name
+     FROM pg_settings
+   WHERE 'NOT_IN_SAMPLE' = ANY (pg_settings_get_flags(name))
+     ORDER BY 1");
+my @not_in_sample_array = split("\n", lc($not_in_sample));
+
+# use the sample file from the temp install
+my $share_dir = $node->config_data('--sharedir');
+my $sample_file = "$share_dir/postgresql.conf.sample";
+
+# List of all the GUCs found in the sample file.
+my @gucs_in_file;
+
+# Read the sample file line-by-line, checking its contents to build a list
+# of everything known as a GUC.
+my $num_tests = 0;
+open(my $contents, '<', $sample_file)
+  || die "Could not open $sample_file: $!";
+while (my $line = <$contents>)
+{
+	# Check if this line matches a GUC parameter:
+	# - Each parameter is preceded by "#", but not "# " in the sample
+	# file.
+	# - Valid configuration options are followed immediately by " = ",
+	# with one space before and after the equal sign.
+	if ($line =~ m/^#?([_[:alpha:]]+) = .*/)
+	{
+		# Lower-case conversion matters for some of the GUCs.
+		my $param_name = lc($1);
+
+		# Ignore some exceptions.
+		next if $param_name eq "include";
+		next if $param_name eq "include_dir";
+		next if $param_name eq "include_if_exists";
+
+		# Update the list of GUCs found in the sample file, for the
+		# follow-up tests.
+		push @gucs_in_file, $param_name;
+	}
+}
+
+close $contents;
+
+# Cross-check that all the GUCs found in the sample file match the ones
+# fetched above.  This maps the arrays to a hash, making the creation of
+# each exclude and intersection list easier.
+my %gucs_in_file_hash  = map { $_ => 1 } @gucs_in_file;
+my %all_params_hash    = map { $_ => 1 } @all_params_array;
+my %not_in_sample_hash = map { $_ => 1 } @not_in_sample_array;
+
+my @missing_from_file = grep(!$gucs_in_file_hash{$_}, @all_params_array);
+is(scalar(@missing_from_file),
+	0, "no parameters missing from postgresql.conf.sample");
+
+my @missing_from_list = grep(!$all_params_hash{$_}, @gucs_in_file);
+is(scalar(@missing_from_list), 0, "no parameters missing from guc.c");
+
+my @sample_intersect = grep($not_in_sample_hash{$_}, @gucs_in_file);
+is(scalar(@sample_intersect),
+	0, "no parameters marked as NOT_IN_SAMPLE in postgresql.conf.sample");
+
+# These would log some information only on errors.
+foreach my $param (@missing_from_file)
+{
+	print("found GUC $param in guc.c, missing from postgresql.conf.sample\n");
+}
+foreach my $param (@missing_from_list)
+{
+	print(
+		"found GUC $param in postgresql.conf.sample, with incorrect info in guc.c\n"
+	);
+}
+foreach my $param (@sample_intersect)
+{
+	print(
+		"found GUC $param in postgresql.conf.sample, marked as NOT_IN_SAMPLE\n"
+	);
+}
+
+done_testing();