Skip to content

Commit b31fbe8

Browse files
macdicemacdice
committed
Warn if wal_level is too low when creating a publication.
Provide a hint to users that they need to increase wal_level before subscriptions can work. Author: Lucas Viecelli, with some adjustments by Thomas Munro Reviewed-by: Tom Lane Discussion: https://postgr.es/m/CAPjy-57rn5Y9g4e5u--eSOP-7P4QrE9uOZmT2ZcUebF8qxsYhg%40mail.gmail.com
1 parent d3751ad commit b31fbe8

File tree

5 files changed

+40
-0
lines changed

5 files changed

+40
-0
lines changed

‎src/backend/commands/publicationcmds.c

Lines changed: 8 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -232,6 +232,14 @@ CreatePublication(CreatePublicationStmt *stmt)
232232

233233
InvokeObjectPostCreateHook(PublicationRelationId, puboid, 0);
234234

235+
if (wal_level != WAL_LEVEL_LOGICAL)
236+
{
237+
ereport(WARNING,
238+
(errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
239+
errmsg("wal_level is insufficient to publish logical changes"),
240+
errhint("Set wal_level to logical before creating subscriptions.")));
241+
}
242+
235243
return myself;
236244
}
237245

‎src/test/regress/expected/object_address.out

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -42,7 +42,10 @@ ALTER DEFAULT PRIVILEGES FOR ROLE regress_addr_user REVOKE DELETE ON TABLES FROM
4242
CREATE TRANSFORM FOR int LANGUAGE SQL (
4343
FROM SQL WITH FUNCTION prsd_lextype(internal),
4444
TO SQL WITH FUNCTION int4recv(internal));
45+
-- suppress warning that depends on wal_level
46+
SET client_min_messages = 'ERROR';
4547
CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
48+
RESET client_min_messages;
4649
CREATE SUBSCRIPTION regress_addr_sub CONNECTION '' PUBLICATION bar WITH (connect = false, slot_name = NONE);
4750
WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
4851
CREATE STATISTICS addr_nsp.gentable_stat ON a, b FROM addr_nsp.gentable;

‎src/test/regress/expected/publication.out

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -5,15 +5,20 @@ CREATE ROLE regress_publication_user LOGIN SUPERUSER;
55
CREATE ROLE regress_publication_user2;
66
CREATE ROLE regress_publication_user_dummy LOGIN NOSUPERUSER;
77
SET SESSION AUTHORIZATION 'regress_publication_user';
8+
-- suppress warning that depends on wal_level
9+
SET client_min_messages = 'ERROR';
810
CREATE PUBLICATION testpub_default;
11+
RESET client_min_messages;
912
COMMENT ON PUBLICATION testpub_default IS 'test publication';
1013
SELECT obj_description(p.oid, 'pg_publication') FROM pg_publication p;
1114
obj_description
1215
------------------
1316
test publication
1417
(1 row)
1518

19+
SET client_min_messages = 'ERROR';
1620
CREATE PUBLICATION testpib_ins_trunct WITH (publish = insert);
21+
RESET client_min_messages;
1722
ALTER PUBLICATION testpub_default SET (publish = update);
1823
-- error cases
1924
CREATE PUBLICATION testpub_xxx WITH (foo);
@@ -43,7 +48,9 @@ CREATE TABLE testpub_tbl1 (id serial primary key, data text);
4348
CREATE TABLE pub_test.testpub_nopk (foo int, bar int);
4449
CREATE VIEW testpub_view AS SELECT 1;
4550
CREATE TABLE testpub_parted (a int) PARTITION BY LIST (a);
51+
SET client_min_messages = 'ERROR';
4652
CREATE PUBLICATION testpub_foralltables FOR ALL TABLES WITH (publish = 'insert');
53+
RESET client_min_messages;
4754
ALTER PUBLICATION testpub_foralltables SET (publish = 'insert, update');
4855
CREATE TABLE testpub_tbl2 (id serial primary key, data text);
4956
-- fail - can't add to for all tables publication
@@ -86,8 +93,10 @@ DROP TABLE testpub_tbl2;
8693
DROP PUBLICATION testpub_foralltables;
8794
CREATE TABLE testpub_tbl3 (a int);
8895
CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3);
96+
SET client_min_messages = 'ERROR';
8997
CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3;
9098
CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3;
99+
RESET client_min_messages;
91100
\dRp+ testpub3
92101
Publication testpub3
93102
Owner | All tables | Inserts | Updates | Deletes | Truncates
@@ -111,7 +120,9 @@ DROP PUBLICATION testpub3, testpub4;
111120
CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_view;
112121
ERROR: "testpub_view" is not a table
113122
DETAIL: Only tables can be added to publications.
123+
SET client_min_messages = 'ERROR';
114124
CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1, pub_test.testpub_nopk;
125+
RESET client_min_messages;
115126
-- fail - already added
116127
ALTER PUBLICATION testpub_fortbl ADD TABLE testpub_tbl1;
117128
ERROR: relation "testpub_tbl1" is already member of publication "testpub_fortbl"
@@ -196,7 +207,9 @@ ERROR: permission denied for database regression
196207
SET ROLE regress_publication_user;
197208
GRANT CREATE ON DATABASE regression TO regress_publication_user2;
198209
SET ROLE regress_publication_user2;
210+
SET client_min_messages = 'ERROR';
199211
CREATE PUBLICATION testpub2; -- ok
212+
RESET client_min_messages;
200213
ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- fail
201214
ERROR: must be owner of table testpub_tbl1
202215
SET ROLE regress_publication_user;

‎src/test/regress/sql/object_address.sql

Lines changed: 3 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -45,7 +45,10 @@ ALTER DEFAULT PRIVILEGES FOR ROLE regress_addr_user REVOKE DELETE ON TABLES FROM
4545
CREATE TRANSFORM FOR int LANGUAGE SQL (
4646
FROM SQL WITH FUNCTION prsd_lextype(internal),
4747
TO SQL WITH FUNCTION int4recv(internal));
48+
-- suppress warning that depends on wal_level
49+
SET client_min_messages = 'ERROR';
4850
CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
51+
RESET client_min_messages;
4952
CREATE SUBSCRIPTION regress_addr_sub CONNECTION '' PUBLICATION bar WITH (connect = false, slot_name = NONE);
5053
CREATE STATISTICS addr_nsp.gentable_stat ON a, b FROM addr_nsp.gentable;
5154

‎src/test/regress/sql/publication.sql

Lines changed: 13 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -6,12 +6,17 @@ CREATE ROLE regress_publication_user2;
66
CREATE ROLE regress_publication_user_dummy LOGIN NOSUPERUSER;
77
SET SESSION AUTHORIZATION 'regress_publication_user';
88

9+
-- suppress warning that depends on wal_level
10+
SET client_min_messages = 'ERROR';
911
CREATE PUBLICATION testpub_default;
12+
RESET client_min_messages;
1013

1114
COMMENT ON PUBLICATION testpub_default IS 'test publication';
1215
SELECT obj_description(p.oid, 'pg_publication') FROM pg_publication p;
1316

17+
SET client_min_messages = 'ERROR';
1418
CREATE PUBLICATION testpib_ins_trunct WITH (publish = insert);
19+
RESET client_min_messages;
1520

1621
ALTER PUBLICATION testpub_default SET (publish = update);
1722

@@ -32,7 +37,9 @@ CREATE TABLE pub_test.testpub_nopk (foo int, bar int);
3237
CREATE VIEW testpub_view AS SELECT 1;
3338
CREATE TABLE testpub_parted (a int) PARTITION BY LIST (a);
3439

40+
SET client_min_messages = 'ERROR';
3541
CREATE PUBLICATION testpub_foralltables FOR ALL TABLES WITH (publish = 'insert');
42+
RESET client_min_messages;
3643
ALTER PUBLICATION testpub_foralltables SET (publish = 'insert, update');
3744

3845
CREATE TABLE testpub_tbl2 (id serial primary key, data text);
@@ -52,8 +59,10 @@ DROP PUBLICATION testpub_foralltables;
5259

5360
CREATE TABLE testpub_tbl3 (a int);
5461
CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3);
62+
SET client_min_messages = 'ERROR';
5563
CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3;
5664
CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3;
65+
RESET client_min_messages;
5766
\dRp+ testpub3
5867
\dRp+ testpub4
5968

@@ -62,7 +71,9 @@ DROP PUBLICATION testpub3, testpub4;
6271

6372
-- fail - view
6473
CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_view;
74+
SET client_min_messages = 'ERROR';
6575
CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1, pub_test.testpub_nopk;
76+
RESET client_min_messages;
6677
-- fail - already added
6778
ALTER PUBLICATION testpub_fortbl ADD TABLE testpub_tbl1;
6879
-- fail - already added
@@ -98,7 +109,9 @@ CREATE PUBLICATION testpub2; -- fail
98109
SET ROLE regress_publication_user;
99110
GRANT CREATE ON DATABASE regression TO regress_publication_user2;
100111
SET ROLE regress_publication_user2;
112+
SET client_min_messages = 'ERROR';
101113
CREATE PUBLICATION testpub2; -- ok
114+
RESET client_min_messages;
102115

103116
ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- fail
104117

0 commit comments

Comments
 (0)