Subject: Re: [Proposal] Fully WAL logged CREATE DATABASE - No Checkpoints

From: Dilip Kumar <[email-masked]>
Date: Fri, 1 Apr 2022 13:51:25 +0530

On Thu, Mar 31, 2022 at 9:52 PM Andres Freund <[email-masked]> wrote:

> > +     "ALTER DATABASE testdb SET TABLESPACE regress_ts2");
> > +ok($result == 0, 'move database to tablespace 2');
>
> This just tests the command doesn't fail, but not whether it actually did
> something useful. Seem we should at least insert a row or two into the the
> table, and verify they can be accessed?

Now, added some tuples and verified them.

> >  # Check that only READ-only queries can run on standbys
> >  is($node_standby_1->psql('postgres', 'INSERT INTO tab_int VALUES (1)'),
> >       3, 'read-only queries on standby 1');
>
> I'd probably add a function for creating database / table and then testing it,
> with a strategy parameter. That way we can afterwards add more tests verifying
> that everything worked.

I have created a function to create a database and table and verify
the content in it.  Another option is we can just keep the database
and table creation inside the function and the verification part
outside it so that if some future test case wants to create some extra
content and verify it then they can do so.   But with the current
tests in mind the way I got it in the attached patch has less
duplicate code so I preferred it this way.

-- 
Regards,
Dilip Kumar
EnterpriseDB: http://www.enterprisedb.com
From a672a27a9e502331a7c4ca2a16f3f660c8ed3fcd Mon Sep 17 00:00:00 2001
From: Dilip Kumar <[email-masked]>
Date: Fri, 1 Apr 2022 13:15:57 +0530
Subject: [PATCH v2] Create database test coverage

Test create database strategy wal replay and alter database
set tablespace.
---
 src/test/modules/test_misc/t/002_tablespace.pl | 14 ++++++++++++++
 src/test/recovery/t/001_stream_rep.pl          | 25 +++++++++++++++++++++++++
 2 files changed, 39 insertions(+)

diff --git a/src/test/modules/test_misc/t/002_tablespace.pl b/src/test/modules/test_misc/t/002_tablespace.pl
index 04e5439..6ac6f79 100644
--- a/src/test/modules/test_misc/t/002_tablespace.pl
+++ b/src/test/modules/test_misc/t/002_tablespace.pl
@@ -83,7 +83,21 @@ $result = $node->psql('postgres',
 	"ALTER TABLE t SET tablespace regress_ts1");
 ok($result == 0, 'move table in-place->abs');
 
+# Test ALTER DATABASE SET TABLESPACE
+$result = $node->psql('postgres',
+	"CREATE DATABASE testdb TABLESPACE regress_ts1");
+ok($result == 0, 'create database in tablespace 1');
+$result = $node->psql('testdb',
+	"CREATE TABLE tab_int AS SELECT generate_series(1,10) AS a");
+ok($result == 0, 'create table in testdb database');
+$result = $node->psql('postgres',
+	"ALTER DATABASE testdb SET TABLESPACE regress_ts2");
+ok($result == 0, 'move database to tablespace 2');
+$result = $node->safe_psql('testdb', "SELECT count(*) FROM tab_int");
+is($result, qq(10), 'check contents after moving to a different tablespace');
+
 # Drop everything
+$result = $node->psql('postgres', "DROP DATABASE testdb");
 $result = $node->psql('postgres',
 	"DROP TABLE t");
 ok($result == 0, 'create table in tablespace 1');
diff --git a/src/test/recovery/t/001_stream_rep.pl b/src/test/recovery/t/001_stream_rep.pl
index 583ee87..cf4041a 100644
--- a/src/test/recovery/t/001_stream_rep.pl
+++ b/src/test/recovery/t/001_stream_rep.pl
@@ -78,6 +78,31 @@ $result = $node_standby_2->safe_psql('postgres', "SELECT * FROM seq1");
 print "standby 2: $result\n";
 is($result, qq(33|0|t), 'check streamed sequence content on standby 2');
 
+# Create database with some contents for a given strategy and check on standby
+sub test_createdb_strategy_and_check
+{
+	my $node1       = shift;
+	my $node2       = shift;
+	my $dbname      = shift;
+	my $strategy    = shift;
+
+	$node1->safe_psql('postgres',
+		"CREATE DATABASE $dbname STRATEGY = $strategy; ");
+	$node1->safe_psql($dbname,
+		"CREATE TABLE tab_int AS SELECT generate_series(1,10) AS a");
+	my $lsn = $node1->lsn('write');
+	$node1->wait_for_catchup($node2, 'replay', $lsn);
+
+	my $result = $node2->safe_psql($dbname, "SELECT count(*) FROM tab_int");
+	is($result, qq(10), 'check streamed content on standby');
+}
+# Test replication of file_copy strategy
+test_createdb_strategy_and_check($node_primary, $node_standby_1, "testdb1",
+	"file_copy");
+# Test replication of wal_log strategy
+test_createdb_strategy_and_check($node_primary, $node_standby_1, "testdb2",
+	"wal_log");
+
 # Check that only READ-only queries can run on standbys
 is($node_standby_1->psql('postgres', 'INSERT INTO tab_int VALUES (1)'),
 	3, 'read-only queries on standby 1');
-- 
1.8.3.1