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
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
|
--
-- PUBLICATION
--
CREATE ROLE regress_publication_user LOGIN SUPERUSER;
SET SESSION AUTHORIZATION 'regress_publication_user';
CREATE PUBLICATION testpub_default;
CREATE PUBLICATION testpib_ins_trunct WITH (nopublish delete, nopublish update);
ALTER PUBLICATION testpub_default WITH (nopublish insert, nopublish delete);
\dRp
List of publications
Name | Owner | Inserts | Updates | Deletes
--------------------+--------------------------+---------+---------+---------
testpib_ins_trunct | regress_publication_user | t | f | f
testpub_default | regress_publication_user | f | t | f
(2 rows)
ALTER PUBLICATION testpub_default WITH (publish insert, publish delete);
\dRp
List of publications
Name | Owner | Inserts | Updates | Deletes
--------------------+--------------------------+---------+---------+---------
testpib_ins_trunct | regress_publication_user | t | f | f
testpub_default | regress_publication_user | t | t | t
(2 rows)
--- adding tables
CREATE SCHEMA pub_test;
CREATE TABLE testpub_tbl1 (id serial primary key, data text);
CREATE TABLE pub_test.testpub_nopk (foo int, bar int);
CREATE VIEW testpub_view AS SELECT 1;
CREATE PUBLICATION testpub_foralltables FOR ALL TABLES WITH (nopublish delete, nopublish update);
ALTER PUBLICATION testpub_foralltables WITH (publish update);
CREATE TABLE testpub_tbl2 (id serial primary key, data text);
-- fail - can't add to for all tables publication
ALTER PUBLICATION testpub_foralltables ADD TABLE testpub_tbl2;
ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES
DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications.
-- fail - can't drop from all tables publication
ALTER PUBLICATION testpub_foralltables DROP TABLE testpub_tbl2;
ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES
DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications.
-- fail - can't add to for all tables publication
ALTER PUBLICATION testpub_foralltables SET TABLE pub_test.testpub_nopk;
ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES
DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications.
SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_foralltables';
pubname | puballtables
----------------------+--------------
testpub_foralltables | t
(1 row)
\d+ testpub_tbl2
Table "public.testpub_tbl2"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+------------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('testpub_tbl2_id_seq'::regclass) | plain | |
data | text | | | | extended | |
Indexes:
"testpub_tbl2_pkey" PRIMARY KEY, btree (id)
Publications:
"testpub_foralltables"
DROP TABLE testpub_tbl2;
DROP PUBLICATION testpub_foralltables;
-- fail - view
CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_view;
ERROR: "testpub_view" is not a table
DETAIL: Only tables can be added to publications.
CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1, pub_test.testpub_nopk;
-- fail - already added
ALTER PUBLICATION testpub_fortbl ADD TABLE testpub_tbl1;
ERROR: relation "testpub_tbl1" is already member of publication "testpub_fortbl"
-- fail - already added
CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1;
ERROR: publication "testpub_fortbl" already exists
\dRp+ testpub_fortbl
Publication testpub_fortbl
Inserts | Updates | Deletes
---------+---------+---------
t | t | t
Tables:
"pub_test.testpub_nopk"
"public.testpub_tbl1"
-- fail - view
ALTER PUBLICATION testpub_default ADD TABLE testpub_view;
ERROR: "testpub_view" is not a table
DETAIL: Only tables can be added to publications.
ALTER PUBLICATION testpub_default ADD TABLE testpub_tbl1;
ALTER PUBLICATION testpub_default SET TABLE testpub_tbl1;
ALTER PUBLICATION testpub_default ADD TABLE pub_test.testpub_nopk;
ALTER PUBLICATION testpib_ins_trunct ADD TABLE pub_test.testpub_nopk, testpub_tbl1;
\d+ pub_test.testpub_nopk
Table "pub_test.testpub_nopk"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+---------+---------+--------------+-------------
foo | integer | | | | plain | |
bar | integer | | | | plain | |
Publications:
"testpib_ins_trunct"
"testpub_default"
"testpub_fortbl"
\d+ testpub_tbl1
Table "public.testpub_tbl1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+------------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | plain | |
data | text | | | | extended | |
Indexes:
"testpub_tbl1_pkey" PRIMARY KEY, btree (id)
Publications:
"testpib_ins_trunct"
"testpub_default"
"testpub_fortbl"
\dRp+ testpub_default
Publication testpub_default
Inserts | Updates | Deletes
---------+---------+---------
t | t | t
Tables:
"pub_test.testpub_nopk"
"public.testpub_tbl1"
ALTER PUBLICATION testpub_default DROP TABLE testpub_tbl1, pub_test.testpub_nopk;
-- fail - nonexistent
ALTER PUBLICATION testpub_default DROP TABLE pub_test.testpub_nopk;
ERROR: relation "testpub_nopk" is not part of the publication
\d+ testpub_tbl1
Table "public.testpub_tbl1"
Column | Type | Collation | Nullable | Default | Storage | Stats target | Description
--------+---------+-----------+----------+------------------------------------------+----------+--------------+-------------
id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | plain | |
data | text | | | | extended | |
Indexes:
"testpub_tbl1_pkey" PRIMARY KEY, btree (id)
Publications:
"testpib_ins_trunct"
"testpub_fortbl"
DROP VIEW testpub_view;
DROP TABLE testpub_tbl1;
\dRp+ testpub_default
Publication testpub_default
Inserts | Updates | Deletes
---------+---------+---------
t | t | t
(1 row)
DROP PUBLICATION testpub_default;
DROP PUBLICATION testpib_ins_trunct;
DROP SCHEMA pub_test CASCADE;
NOTICE: drop cascades to table pub_test.testpub_nopk
RESET SESSION AUTHORIZATION;
DROP ROLE regress_publication_user;
|
