Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly
Appearance settings

Commit 8a86618

Browse filesBrowse files
michaelpqmichaelpq
committed
Fix corruption of templates after CREATE DATABASE .. STRATEGY WAL_LOG
WAL_LOG does a scan of the template's pg_class to determine the set of relations that need to be copied from a template database to the new one. However, as coded in 9c08aea, this copy strategy would load the pages of pg_class without considering it as a permanent relation, causing the loaded pages to never be flushed when they should. Any modification of the template's pg_class, mostly through DDLs, would then be missed, causing corruptions. STRATEGY = WAL_LOG is the default over FILE_COPY since it has been introduced, so any changes done to pg_class on a database template would be gone. Updates of database templates should be a rare thing, so the impact of this bug should be hopefully limited. The pre-14 default strategy FILE_COPY is safe, and can be used as a workaround. Ryo Matsumura has found and analyzed the issue, and Nathan has written a test able to reproduce the failure (with few tweaks from me). Backpatch down to 15, where STRATEGY = WAL_LOG has been introduced. Author: Nathan Bossart, Ryo Matsumura Reviewed-by: Dilip Kumar, Michael Paquier Discussion: https://postgr.es/m/TYCPR01MB6868677E499C9AD5123084B5E8A39@TYCPR01MB6868.jpnprd01.prod.outlook.com Backpatch-through: 15
1 parent b3e184a commit 8a86618
Copy full SHA for 8a86618

File tree

3 files changed

+47
-1
lines changed
Filter options

3 files changed

+47
-1
lines changed

‎src/backend/commands/dbcommands.c

Copy file name to clipboardExpand all lines: src/backend/commands/dbcommands.c
+1-1Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -296,7 +296,7 @@ ScanSourceDatabasePgClass(Oid tbid, Oid dbid, char *srcpath)
296296
CHECK_FOR_INTERRUPTS();
297297

298298
buf = ReadBufferWithoutRelcache(rlocator, MAIN_FORKNUM, blkno,
299-
RBM_NORMAL, bstrategy, false);
299+
RBM_NORMAL, bstrategy, true);
300300

301301
LockBuffer(buf, BUFFER_LOCK_SHARE);
302302
page = BufferGetPage(buf);

‎src/test/recovery/meson.build

Copy file name to clipboardExpand all lines: src/test/recovery/meson.build
+1Lines changed: 1 addition & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -39,6 +39,7 @@ tests += {
3939
't/031_recovery_conflict.pl',
4040
't/032_relfilenode_reuse.pl',
4141
't/033_replay_tsp_drops.pl',
42+
't/034_create_database.pl',
4243
],
4344
},
4445
}

‎src/test/recovery/t/034_create_database.pl

Copy file name to clipboard
+45Lines changed: 45 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -0,0 +1,45 @@
1+
2+
# Copyright (c) 2023, PostgreSQL Global Development Group
3+
4+
# Test WAL replay for CREATE DATABASE .. STRATEGY WAL_LOG.
5+
6+
use strict;
7+
use warnings;
8+
use PostgreSQL::Test::Cluster;
9+
use PostgreSQL::Test::Utils;
10+
use Test::More;
11+
12+
my $node = PostgreSQL::Test::Cluster->new('node');
13+
$node->init;
14+
$node->start;
15+
16+
# This checks that any DDLs run on the template database that modify pg_class
17+
# are persisted after creating a database from it using the WAL_LOG strategy,
18+
# as a direct copy of the template database's pg_class is used in this case.
19+
my $db_template = "template1";
20+
my $db_new = "test_db_1";
21+
22+
# Create table. It should persist on the template database.
23+
$node->safe_psql("postgres",
24+
"CREATE DATABASE $db_new STRATEGY WAL_LOG TEMPLATE $db_template;");
25+
26+
$node->safe_psql($db_template, "CREATE TABLE tab_db_after_create_1 (a INT);");
27+
28+
# Flush the changes affecting the template database, then replay them.
29+
$node->safe_psql("postgres", "CHECKPOINT;");
30+
31+
$node->stop('immediate');
32+
$node->start;
33+
my $result = $node->safe_psql($db_template,
34+
"SELECT count(*) FROM pg_class WHERE relname LIKE 'tab_db_%';");
35+
is($result, "1",
36+
"check that table exists on template after crash, with checkpoint");
37+
38+
# The new database should have no tables.
39+
$result = $node->safe_psql($db_new,
40+
"SELECT count(*) FROM pg_class WHERE relname LIKE 'tab_db_%';");
41+
is($result, "0",
42+
"check that there are no tables from template on new database after crash"
43+
);
44+
45+
done_testing();

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.