publication.out 15.1 KB
Newer Older
Peter Eisentraut's avatar
Peter Eisentraut committed
1 2 3 4
--
-- PUBLICATION
--
CREATE ROLE regress_publication_user LOGIN SUPERUSER;
5
CREATE ROLE regress_publication_user2;
6
CREATE ROLE regress_publication_user_dummy LOGIN NOSUPERUSER;
Peter Eisentraut's avatar
Peter Eisentraut committed
7
SET SESSION AUTHORIZATION 'regress_publication_user';
8 9
-- suppress warning that depends on wal_level
SET client_min_messages = 'ERROR';
Peter Eisentraut's avatar
Peter Eisentraut committed
10
CREATE PUBLICATION testpub_default;
11
RESET client_min_messages;
12 13 14 15 16 17 18
COMMENT ON PUBLICATION testpub_default IS 'test publication';
SELECT obj_description(p.oid, 'pg_publication') FROM pg_publication p;
 obj_description  
------------------
 test publication
(1 row)

19
SET client_min_messages = 'ERROR';
20
CREATE PUBLICATION testpib_ins_trunct WITH (publish = insert);
21
RESET client_min_messages;
22 23 24
ALTER PUBLICATION testpub_default SET (publish = update);
-- error cases
CREATE PUBLICATION testpub_xxx WITH (foo);
25
ERROR:  unrecognized publication parameter: "foo"
26 27
CREATE PUBLICATION testpub_xxx WITH (publish = 'cluster, vacuum');
ERROR:  unrecognized "publish" value: "cluster"
28 29
CREATE PUBLICATION testpub_xxx WITH (publish_via_partition_root = 'true', publish_via_partition_root = '0');
ERROR:  conflicting or redundant options
Peter Eisentraut's avatar
Peter Eisentraut committed
30
\dRp
31 32 33 34 35
                                              List of publications
        Name        |          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root 
--------------------+--------------------------+------------+---------+---------+---------+-----------+----------
 testpib_ins_trunct | regress_publication_user | f          | t       | f       | f       | f         | f
 testpub_default    | regress_publication_user | f          | f       | t       | f       | f         | f
Peter Eisentraut's avatar
Peter Eisentraut committed
36 37
(2 rows)

38
ALTER PUBLICATION testpub_default SET (publish = 'insert, update, delete');
Peter Eisentraut's avatar
Peter Eisentraut committed
39
\dRp
40 41 42 43 44
                                              List of publications
        Name        |          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root 
--------------------+--------------------------+------------+---------+---------+---------+-----------+----------
 testpib_ins_trunct | regress_publication_user | f          | t       | f       | f       | f         | f
 testpub_default    | regress_publication_user | f          | t       | t       | t       | f         | f
Peter Eisentraut's avatar
Peter Eisentraut committed
45 46 47 48 49 50 51
(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;
52
CREATE TABLE testpub_parted (a int) PARTITION BY LIST (a);
53
SET client_min_messages = 'ERROR';
54
CREATE PUBLICATION testpub_foralltables FOR ALL TABLES WITH (publish = 'insert');
55
RESET client_min_messages;
56
ALTER PUBLICATION testpub_foralltables SET (publish = 'insert, update');
Peter Eisentraut's avatar
Peter Eisentraut committed
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
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"

87
\dRp+ testpub_foralltables
88 89 90 91
                              Publication testpub_foralltables
          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root 
--------------------------+------------+---------+---------+---------+-----------+----------
 regress_publication_user | t          | t       | t       | f       | f         | f
92 93
(1 row)

Peter Eisentraut's avatar
Peter Eisentraut committed
94 95
DROP TABLE testpub_tbl2;
DROP PUBLICATION testpub_foralltables;
96 97
CREATE TABLE testpub_tbl3 (a int);
CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3);
98
SET client_min_messages = 'ERROR';
99 100
CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3;
CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3;
101
RESET client_min_messages;
102
\dRp+ testpub3
103 104 105 106
                                    Publication testpub3
          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root 
--------------------------+------------+---------+---------+---------+-----------+----------
 regress_publication_user | f          | t       | t       | t       | t         | f
107 108 109 110 111
Tables:
    "public.testpub_tbl3"
    "public.testpub_tbl3a"

\dRp+ testpub4
112 113 114 115
                                    Publication testpub4
          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root 
--------------------------+------------+---------+---------+---------+-----------+----------
 regress_publication_user | f          | t       | t       | t       | t         | f
116 117 118 119 120
Tables:
    "public.testpub_tbl3"

DROP TABLE testpub_tbl3, testpub_tbl3a;
DROP PUBLICATION testpub3, testpub4;
121 122 123 124 125 126 127 128 129 130 131 132 133
-- Tests for partitioned tables
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub_forparted;
CREATE PUBLICATION testpub_forparted1;
RESET client_min_messages;
CREATE TABLE testpub_parted1 (LIKE testpub_parted);
ALTER PUBLICATION testpub_forparted1 SET (publish='insert');
-- works despite missing REPLICA IDENTITY, because updates are not replicated
UPDATE testpub_parted1 SET a = 1;
ALTER TABLE testpub_parted ATTACH PARTITION testpub_parted1 FOR VALUES IN (1);
-- only parent is listed as being in publication, not the partition
ALTER PUBLICATION testpub_forparted ADD TABLE testpub_parted;
\dRp+ testpub_forparted
134 135 136 137
                               Publication testpub_forparted
          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root 
--------------------------+------------+---------+---------+---------+-----------+----------
 regress_publication_user | f          | t       | t       | t       | t         | f
138 139 140 141 142 143 144 145 146 147
Tables:
    "public.testpub_parted"

-- should now fail, because parent's publication replicates updates
UPDATE testpub_parted1 SET a = 1;
ERROR:  cannot update table "testpub_parted1" because it does not have a replica identity and publishes updates
HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
ALTER TABLE testpub_parted DETACH PARTITION testpub_parted1;
-- works again, because parent's publication is no longer considered
UPDATE testpub_parted1 SET a = 1;
148 149 150 151 152 153 154 155 156
ALTER PUBLICATION testpub_forparted SET (publish_via_partition_root = true);
\dRp+ testpub_forparted
                               Publication testpub_forparted
          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root 
--------------------------+------------+---------+---------+---------+-----------+----------
 regress_publication_user | f          | t       | t       | t       | t         | t
Tables:
    "public.testpub_parted"

157 158
DROP TABLE testpub_parted1;
DROP PUBLICATION testpub_forparted, testpub_forparted1;
159 160 161 162 163 164 165 166 167 168 169 170 171 172 173
-- Test cache invalidation FOR ALL TABLES publication
SET client_min_messages = 'ERROR';
CREATE TABLE testpub_tbl4(a int);
INSERT INTO testpub_tbl4 values(1);
UPDATE testpub_tbl4 set a = 2;
CREATE PUBLICATION testpub_foralltables FOR ALL TABLES;
RESET client_min_messages;
-- fail missing REPLICA IDENTITY
UPDATE testpub_tbl4 set a = 3;
ERROR:  cannot update table "testpub_tbl4" because it does not have a replica identity and publishes updates
HINT:  To enable updating the table, set REPLICA IDENTITY using ALTER TABLE.
DROP PUBLICATION testpub_foralltables;
-- should pass after dropping the publication
UPDATE testpub_tbl4 set a = 3;
DROP TABLE testpub_tbl4;
Peter Eisentraut's avatar
Peter Eisentraut committed
174 175 176 177
-- 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.
178
SET client_min_messages = 'ERROR';
Peter Eisentraut's avatar
Peter Eisentraut committed
179
CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1, pub_test.testpub_nopk;
180
RESET client_min_messages;
Peter Eisentraut's avatar
Peter Eisentraut committed
181 182 183 184 185 186 187
-- 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
188 189 190 191
                                 Publication testpub_fortbl
          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root 
--------------------------+------------+---------+---------+---------+-----------+----------
 regress_publication_user | f          | t       | t       | t       | t         | f
Peter Eisentraut's avatar
Peter Eisentraut committed
192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228
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
229 230 231 232
                                Publication testpub_default
          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root 
--------------------------+------------+---------+---------+---------+-----------+----------
 regress_publication_user | f          | t       | t       | t       | f         | f
Peter Eisentraut's avatar
Peter Eisentraut committed
233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252
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"

253 254 255 256 257 258 259
-- permissions
SET ROLE regress_publication_user2;
CREATE PUBLICATION testpub2;  -- fail
ERROR:  permission denied for database regression
SET ROLE regress_publication_user;
GRANT CREATE ON DATABASE regression TO regress_publication_user2;
SET ROLE regress_publication_user2;
260
SET client_min_messages = 'ERROR';
261
CREATE PUBLICATION testpub2;  -- ok
262
RESET client_min_messages;
263
ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1;  -- fail
264
ERROR:  must be owner of table testpub_tbl1
265 266 267 268 269 270 271
SET ROLE regress_publication_user;
GRANT regress_publication_user TO regress_publication_user2;
SET ROLE regress_publication_user2;
ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1;  -- ok
DROP PUBLICATION testpub2;
SET ROLE regress_publication_user;
REVOKE CREATE ON DATABASE regression FROM regress_publication_user2;
272
DROP TABLE testpub_parted;
Peter Eisentraut's avatar
Peter Eisentraut committed
273 274 275
DROP VIEW testpub_view;
DROP TABLE testpub_tbl1;
\dRp+ testpub_default
276 277 278 279
                                Publication testpub_default
          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root 
--------------------------+------------+---------+---------+---------+-----------+----------
 regress_publication_user | f          | t       | t       | t       | f         | f
Peter Eisentraut's avatar
Peter Eisentraut committed
280 281
(1 row)

Peter Eisentraut's avatar
Peter Eisentraut committed
282
-- fail - must be owner of publication
283 284 285 286
SET ROLE regress_publication_user_dummy;
ALTER PUBLICATION testpub_default RENAME TO testpub_dummy;
ERROR:  must be owner of publication testpub_default
RESET ROLE;
287 288
ALTER PUBLICATION testpub_default RENAME TO testpub_foo;
\dRp testpub_foo
289 290 291 292
                                           List of publications
    Name     |          Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root 
-------------+--------------------------+------------+---------+---------+---------+-----------+----------
 testpub_foo | regress_publication_user | f          | t       | t       | t       | f         | f
293 294
(1 row)

295 296
-- rename back to keep the rest simple
ALTER PUBLICATION testpub_foo RENAME TO testpub_default;
297 298
ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2;
\dRp testpub_default
299 300 301 302
                                             List of publications
      Name       |           Owner           | All tables | Inserts | Updates | Deletes | Truncates | Via root 
-----------------+---------------------------+------------+---------+---------+---------+-----------+----------
 testpub_default | regress_publication_user2 | f          | t       | t       | t       | f         | f
303 304
(1 row)

305
DROP PUBLICATION testpub_default;
Peter Eisentraut's avatar
Peter Eisentraut committed
306
DROP PUBLICATION testpib_ins_trunct;
307
DROP PUBLICATION testpub_fortbl;
Peter Eisentraut's avatar
Peter Eisentraut committed
308 309 310
DROP SCHEMA pub_test CASCADE;
NOTICE:  drop cascades to table pub_test.testpub_nopk
RESET SESSION AUTHORIZATION;
311
DROP ROLE regress_publication_user, regress_publication_user2;
312
DROP ROLE regress_publication_user_dummy;