1-- 2-- PUBLICATION 3-- 4CREATE ROLE regress_publication_user LOGIN SUPERUSER; 5CREATE ROLE regress_publication_user2; 6CREATE ROLE regress_publication_user_dummy LOGIN NOSUPERUSER; 7SET SESSION AUTHORIZATION 'regress_publication_user'; 8CREATE PUBLICATION testpub_default; 9COMMENT ON PUBLICATION testpub_default IS 'test publication'; 10SELECT obj_description(p.oid, 'pg_publication') FROM pg_publication p; 11 obj_description 12------------------ 13 test publication 14(1 row) 15 16CREATE PUBLICATION testpib_ins_trunct WITH (publish = insert); 17ALTER PUBLICATION testpub_default SET (publish = update); 18-- error cases 19CREATE PUBLICATION testpub_xxx WITH (foo); 20ERROR: unrecognized publication parameter: foo 21CREATE PUBLICATION testpub_xxx WITH (publish = 'cluster, vacuum'); 22ERROR: unrecognized "publish" value: "cluster" 23\dRp 24 List of publications 25 Name | Owner | All tables | Inserts | Updates | Deletes 26--------------------+--------------------------+------------+---------+---------+--------- 27 testpib_ins_trunct | regress_publication_user | f | t | f | f 28 testpub_default | regress_publication_user | f | f | t | f 29(2 rows) 30 31ALTER PUBLICATION testpub_default SET (publish = 'insert, update, delete'); 32\dRp 33 List of publications 34 Name | Owner | All tables | Inserts | Updates | Deletes 35--------------------+--------------------------+------------+---------+---------+--------- 36 testpib_ins_trunct | regress_publication_user | f | t | f | f 37 testpub_default | regress_publication_user | f | t | t | t 38(2 rows) 39 40--- adding tables 41CREATE SCHEMA pub_test; 42CREATE TABLE testpub_tbl1 (id serial primary key, data text); 43CREATE TABLE pub_test.testpub_nopk (foo int, bar int); 44CREATE VIEW testpub_view AS SELECT 1; 45CREATE TABLE testpub_parted (a int) PARTITION BY LIST (a); 46CREATE PUBLICATION testpub_foralltables FOR ALL TABLES WITH (publish = 'insert'); 47ALTER PUBLICATION testpub_foralltables SET (publish = 'insert, update'); 48CREATE TABLE testpub_tbl2 (id serial primary key, data text); 49-- fail - can't add to for all tables publication 50ALTER PUBLICATION testpub_foralltables ADD TABLE testpub_tbl2; 51ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES 52DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications. 53-- fail - can't drop from all tables publication 54ALTER PUBLICATION testpub_foralltables DROP TABLE testpub_tbl2; 55ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES 56DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications. 57-- fail - can't add to for all tables publication 58ALTER PUBLICATION testpub_foralltables SET TABLE pub_test.testpub_nopk; 59ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES 60DETAIL: Tables cannot be added to or dropped from FOR ALL TABLES publications. 61SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_foralltables'; 62 pubname | puballtables 63----------------------+-------------- 64 testpub_foralltables | t 65(1 row) 66 67\d+ testpub_tbl2 68 Table "public.testpub_tbl2" 69 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 70--------+---------+-----------+----------+------------------------------------------+----------+--------------+------------- 71 id | integer | | not null | nextval('testpub_tbl2_id_seq'::regclass) | plain | | 72 data | text | | | | extended | | 73Indexes: 74 "testpub_tbl2_pkey" PRIMARY KEY, btree (id) 75Publications: 76 "testpub_foralltables" 77 78\dRp+ testpub_foralltables 79 Publication testpub_foralltables 80 Owner | All tables | Inserts | Updates | Deletes 81--------------------------+------------+---------+---------+--------- 82 regress_publication_user | t | t | t | f 83(1 row) 84 85DROP TABLE testpub_tbl2; 86DROP PUBLICATION testpub_foralltables; 87CREATE TABLE testpub_tbl3 (a int); 88CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3); 89CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3; 90CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3; 91\dRp+ testpub3 92 Publication testpub3 93 Owner | All tables | Inserts | Updates | Deletes 94--------------------------+------------+---------+---------+--------- 95 regress_publication_user | f | t | t | t 96Tables: 97 "public.testpub_tbl3" 98 "public.testpub_tbl3a" 99 100\dRp+ testpub4 101 Publication testpub4 102 Owner | All tables | Inserts | Updates | Deletes 103--------------------------+------------+---------+---------+--------- 104 regress_publication_user | f | t | t | t 105Tables: 106 "public.testpub_tbl3" 107 108DROP TABLE testpub_tbl3, testpub_tbl3a; 109DROP PUBLICATION testpub3, testpub4; 110-- Test cache invalidation FOR ALL TABLES publication 111SET client_min_messages = 'ERROR'; 112CREATE TABLE testpub_tbl4(a int); 113INSERT INTO testpub_tbl4 values(1); 114UPDATE testpub_tbl4 set a = 2; 115CREATE PUBLICATION testpub_foralltables FOR ALL TABLES; 116RESET client_min_messages; 117-- fail missing REPLICA IDENTITY 118UPDATE testpub_tbl4 set a = 3; 119ERROR: cannot update table "testpub_tbl4" because it does not have a replica identity and publishes updates 120HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. 121DROP PUBLICATION testpub_foralltables; 122-- should pass after dropping the publication 123UPDATE testpub_tbl4 set a = 3; 124DROP TABLE testpub_tbl4; 125-- fail - view 126CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_view; 127ERROR: "testpub_view" is not a table 128DETAIL: Only tables can be added to publications. 129CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1, pub_test.testpub_nopk; 130-- fail - already added 131ALTER PUBLICATION testpub_fortbl ADD TABLE testpub_tbl1; 132ERROR: relation "testpub_tbl1" is already member of publication "testpub_fortbl" 133-- fail - already added 134CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1; 135ERROR: publication "testpub_fortbl" already exists 136\dRp+ testpub_fortbl 137 Publication testpub_fortbl 138 Owner | All tables | Inserts | Updates | Deletes 139--------------------------+------------+---------+---------+--------- 140 regress_publication_user | f | t | t | t 141Tables: 142 "pub_test.testpub_nopk" 143 "public.testpub_tbl1" 144 145-- fail - view 146ALTER PUBLICATION testpub_default ADD TABLE testpub_view; 147ERROR: "testpub_view" is not a table 148DETAIL: Only tables can be added to publications. 149-- fail - partitioned table 150ALTER PUBLICATION testpub_fortbl ADD TABLE testpub_parted; 151ERROR: "testpub_parted" is a partitioned table 152DETAIL: Adding partitioned tables to publications is not supported. 153HINT: You can add the table partitions individually. 154ALTER PUBLICATION testpub_default ADD TABLE testpub_tbl1; 155ALTER PUBLICATION testpub_default SET TABLE testpub_tbl1; 156ALTER PUBLICATION testpub_default ADD TABLE pub_test.testpub_nopk; 157ALTER PUBLICATION testpib_ins_trunct ADD TABLE pub_test.testpub_nopk, testpub_tbl1; 158\d+ pub_test.testpub_nopk 159 Table "pub_test.testpub_nopk" 160 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 161--------+---------+-----------+----------+---------+---------+--------------+------------- 162 foo | integer | | | | plain | | 163 bar | integer | | | | plain | | 164Publications: 165 "testpib_ins_trunct" 166 "testpub_default" 167 "testpub_fortbl" 168 169\d+ testpub_tbl1 170 Table "public.testpub_tbl1" 171 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 172--------+---------+-----------+----------+------------------------------------------+----------+--------------+------------- 173 id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | plain | | 174 data | text | | | | extended | | 175Indexes: 176 "testpub_tbl1_pkey" PRIMARY KEY, btree (id) 177Publications: 178 "testpib_ins_trunct" 179 "testpub_default" 180 "testpub_fortbl" 181 182\dRp+ testpub_default 183 Publication testpub_default 184 Owner | All tables | Inserts | Updates | Deletes 185--------------------------+------------+---------+---------+--------- 186 regress_publication_user | f | t | t | t 187Tables: 188 "pub_test.testpub_nopk" 189 "public.testpub_tbl1" 190 191ALTER PUBLICATION testpub_default DROP TABLE testpub_tbl1, pub_test.testpub_nopk; 192-- fail - nonexistent 193ALTER PUBLICATION testpub_default DROP TABLE pub_test.testpub_nopk; 194ERROR: relation "testpub_nopk" is not part of the publication 195\d+ testpub_tbl1 196 Table "public.testpub_tbl1" 197 Column | Type | Collation | Nullable | Default | Storage | Stats target | Description 198--------+---------+-----------+----------+------------------------------------------+----------+--------------+------------- 199 id | integer | | not null | nextval('testpub_tbl1_id_seq'::regclass) | plain | | 200 data | text | | | | extended | | 201Indexes: 202 "testpub_tbl1_pkey" PRIMARY KEY, btree (id) 203Publications: 204 "testpib_ins_trunct" 205 "testpub_fortbl" 206 207-- permissions 208SET ROLE regress_publication_user2; 209CREATE PUBLICATION testpub2; -- fail 210ERROR: permission denied for database regression 211SET ROLE regress_publication_user; 212GRANT CREATE ON DATABASE regression TO regress_publication_user2; 213SET ROLE regress_publication_user2; 214CREATE PUBLICATION testpub2; -- ok 215ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- fail 216ERROR: must be owner of relation testpub_tbl1 217SET ROLE regress_publication_user; 218GRANT regress_publication_user TO regress_publication_user2; 219SET ROLE regress_publication_user2; 220ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- ok 221DROP PUBLICATION testpub2; 222SET ROLE regress_publication_user; 223REVOKE CREATE ON DATABASE regression FROM regress_publication_user2; 224DROP TABLE testpub_parted; 225DROP VIEW testpub_view; 226DROP TABLE testpub_tbl1; 227\dRp+ testpub_default 228 Publication testpub_default 229 Owner | All tables | Inserts | Updates | Deletes 230--------------------------+------------+---------+---------+--------- 231 regress_publication_user | f | t | t | t 232(1 row) 233 234-- fail - must be owner of publication 235SET ROLE regress_publication_user_dummy; 236ALTER PUBLICATION testpub_default RENAME TO testpub_dummy; 237ERROR: must be owner of publication testpub_default 238RESET ROLE; 239ALTER PUBLICATION testpub_default RENAME TO testpub_foo; 240\dRp testpub_foo 241 List of publications 242 Name | Owner | All tables | Inserts | Updates | Deletes 243-------------+--------------------------+------------+---------+---------+--------- 244 testpub_foo | regress_publication_user | f | t | t | t 245(1 row) 246 247-- rename back to keep the rest simple 248ALTER PUBLICATION testpub_foo RENAME TO testpub_default; 249ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2; 250\dRp testpub_default 251 List of publications 252 Name | Owner | All tables | Inserts | Updates | Deletes 253-----------------+---------------------------+------------+---------+---------+--------- 254 testpub_default | regress_publication_user2 | f | t | t | t 255(1 row) 256 257DROP PUBLICATION testpub_default; 258DROP PUBLICATION testpib_ins_trunct; 259DROP PUBLICATION testpub_fortbl; 260DROP SCHEMA pub_test CASCADE; 261NOTICE: drop cascades to table pub_test.testpub_nopk 262RESET SESSION AUTHORIZATION; 263DROP ROLE regress_publication_user, regress_publication_user2; 264DROP ROLE regress_publication_user_dummy; 265