1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
|
# Copyright (c) 2021-2023, PostgreSQL Global Development Group
# Test generated columns
use strict;
use warnings;
use PostgreSQL::Test::Cluster;
use PostgreSQL::Test::Utils;
use Test::More;
# setup
my $node_publisher = PostgreSQL::Test::Cluster->new('publisher');
$node_publisher->init(allows_streaming => 'logical');
$node_publisher->start;
my $node_subscriber = PostgreSQL::Test::Cluster->new('subscriber');
$node_subscriber->init(allows_streaming => 'logical');
$node_subscriber->start;
my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
$node_publisher->safe_psql('postgres',
"CREATE TABLE tab1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 2) STORED)"
);
$node_subscriber->safe_psql('postgres',
"CREATE TABLE tab1 (a int PRIMARY KEY, b int GENERATED ALWAYS AS (a * 22) STORED, c int)"
);
# data for initial sync
$node_publisher->safe_psql('postgres',
"INSERT INTO tab1 (a) VALUES (1), (2), (3)");
$node_publisher->safe_psql('postgres',
"CREATE PUBLICATION pub1 FOR ALL TABLES");
$node_subscriber->safe_psql('postgres',
"CREATE SUBSCRIPTION sub1 CONNECTION '$publisher_connstr' PUBLICATION pub1"
);
# Wait for initial sync of all subscriptions
$node_subscriber->wait_for_subscription_sync;
my $result = $node_subscriber->safe_psql('postgres', "SELECT a, b FROM tab1");
is( $result, qq(1|22
2|44
3|66), 'generated columns initial sync');
# data to replicate
$node_publisher->safe_psql('postgres', "INSERT INTO tab1 VALUES (4), (5)");
$node_publisher->safe_psql('postgres', "UPDATE tab1 SET a = 6 WHERE a = 5");
$node_publisher->wait_for_catchup('sub1');
$result = $node_subscriber->safe_psql('postgres', "SELECT * FROM tab1");
is( $result, qq(1|22|
2|44|
3|66|
4|88|
6|132|), 'generated columns replicated');
# try it with a subscriber-side trigger
$node_subscriber->safe_psql(
'postgres', q{
CREATE FUNCTION tab1_trigger_func() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
NEW.c := NEW.a + 10;
RETURN NEW;
END $$;
CREATE TRIGGER test1 BEFORE INSERT OR UPDATE ON tab1
FOR EACH ROW
EXECUTE PROCEDURE tab1_trigger_func();
ALTER TABLE tab1 ENABLE REPLICA TRIGGER test1;
});
$node_publisher->safe_psql('postgres', "INSERT INTO tab1 VALUES (7), (8)");
$node_publisher->safe_psql('postgres', "UPDATE tab1 SET a = 9 WHERE a = 7");
$node_publisher->wait_for_catchup('sub1');
$result =
$node_subscriber->safe_psql('postgres', "SELECT * FROM tab1 ORDER BY 1");
is( $result, qq(1|22|
2|44|
3|66|
4|88|
6|132|
8|176|18
9|198|19), 'generated columns replicated with trigger');
done_testing();
|
