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'; 8 9-- suppress warning that depends on wal_level 10SET client_min_messages = 'ERROR'; 11CREATE PUBLICATION testpub_default; 12RESET client_min_messages; 13 14COMMENT ON PUBLICATION testpub_default IS 'test publication'; 15SELECT obj_description(p.oid, 'pg_publication') FROM pg_publication p; 16 17SET client_min_messages = 'ERROR'; 18CREATE PUBLICATION testpib_ins_trunct WITH (publish = insert); 19RESET client_min_messages; 20 21ALTER PUBLICATION testpub_default SET (publish = update); 22 23-- error cases 24CREATE PUBLICATION testpub_xxx WITH (foo); 25CREATE PUBLICATION testpub_xxx WITH (publish = 'cluster, vacuum'); 26CREATE PUBLICATION testpub_xxx WITH (publish_via_partition_root = 'true', publish_via_partition_root = '0'); 27 28\dRp 29 30ALTER PUBLICATION testpub_default SET (publish = 'insert, update, delete'); 31 32\dRp 33 34--- adding tables 35CREATE SCHEMA pub_test; 36CREATE TABLE testpub_tbl1 (id serial primary key, data text); 37CREATE TABLE pub_test.testpub_nopk (foo int, bar int); 38CREATE VIEW testpub_view AS SELECT 1; 39CREATE TABLE testpub_parted (a int) PARTITION BY LIST (a); 40 41SET client_min_messages = 'ERROR'; 42CREATE PUBLICATION testpub_foralltables FOR ALL TABLES WITH (publish = 'insert'); 43RESET client_min_messages; 44ALTER PUBLICATION testpub_foralltables SET (publish = 'insert, update'); 45 46CREATE TABLE testpub_tbl2 (id serial primary key, data text); 47-- fail - can't add to for all tables publication 48ALTER PUBLICATION testpub_foralltables ADD TABLE testpub_tbl2; 49-- fail - can't drop from all tables publication 50ALTER PUBLICATION testpub_foralltables DROP TABLE testpub_tbl2; 51-- fail - can't add to for all tables publication 52ALTER PUBLICATION testpub_foralltables SET TABLE pub_test.testpub_nopk; 53 54SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_foralltables'; 55\d+ testpub_tbl2 56\dRp+ testpub_foralltables 57 58DROP TABLE testpub_tbl2; 59DROP PUBLICATION testpub_foralltables; 60 61CREATE TABLE testpub_tbl3 (a int); 62CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3); 63SET client_min_messages = 'ERROR'; 64CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3; 65CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3; 66RESET client_min_messages; 67\dRp+ testpub3 68\dRp+ testpub4 69 70DROP TABLE testpub_tbl3, testpub_tbl3a; 71DROP PUBLICATION testpub3, testpub4; 72 73-- Tests for partitioned tables 74SET client_min_messages = 'ERROR'; 75CREATE PUBLICATION testpub_forparted; 76CREATE PUBLICATION testpub_forparted1; 77RESET client_min_messages; 78CREATE TABLE testpub_parted1 (LIKE testpub_parted); 79CREATE TABLE testpub_parted2 (LIKE testpub_parted); 80ALTER PUBLICATION testpub_forparted1 SET (publish='insert'); 81ALTER TABLE testpub_parted ATTACH PARTITION testpub_parted1 FOR VALUES IN (1); 82ALTER TABLE testpub_parted ATTACH PARTITION testpub_parted2 FOR VALUES IN (2); 83-- works despite missing REPLICA IDENTITY, because updates are not replicated 84UPDATE testpub_parted1 SET a = 1; 85-- only parent is listed as being in publication, not the partition 86ALTER PUBLICATION testpub_forparted ADD TABLE testpub_parted; 87\dRp+ testpub_forparted 88-- should now fail, because parent's publication replicates updates 89UPDATE testpub_parted1 SET a = 1; 90ALTER TABLE testpub_parted DETACH PARTITION testpub_parted1; 91-- works again, because parent's publication is no longer considered 92UPDATE testpub_parted1 SET a = 1; 93ALTER PUBLICATION testpub_forparted SET (publish_via_partition_root = true); 94\dRp+ testpub_forparted 95-- still fail, because parent's publication replicates updates 96UPDATE testpub_parted2 SET a = 2; 97ALTER PUBLICATION testpub_forparted DROP TABLE testpub_parted; 98-- works again, because update is no longer replicated 99UPDATE testpub_parted2 SET a = 2; 100DROP TABLE testpub_parted1, testpub_parted2; 101DROP PUBLICATION testpub_forparted, testpub_forparted1; 102 103-- Test cache invalidation FOR ALL TABLES publication 104SET client_min_messages = 'ERROR'; 105CREATE TABLE testpub_tbl4(a int); 106INSERT INTO testpub_tbl4 values(1); 107UPDATE testpub_tbl4 set a = 2; 108CREATE PUBLICATION testpub_foralltables FOR ALL TABLES; 109RESET client_min_messages; 110-- fail missing REPLICA IDENTITY 111UPDATE testpub_tbl4 set a = 3; 112DROP PUBLICATION testpub_foralltables; 113-- should pass after dropping the publication 114UPDATE testpub_tbl4 set a = 3; 115DROP TABLE testpub_tbl4; 116 117-- fail - view 118CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_view; 119SET client_min_messages = 'ERROR'; 120CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1, pub_test.testpub_nopk; 121RESET client_min_messages; 122-- fail - already added 123ALTER PUBLICATION testpub_fortbl ADD TABLE testpub_tbl1; 124-- fail - already added 125CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1; 126 127\dRp+ testpub_fortbl 128 129-- fail - view 130ALTER PUBLICATION testpub_default ADD TABLE testpub_view; 131 132ALTER PUBLICATION testpub_default ADD TABLE testpub_tbl1; 133ALTER PUBLICATION testpub_default SET TABLE testpub_tbl1; 134ALTER PUBLICATION testpub_default ADD TABLE pub_test.testpub_nopk; 135 136ALTER PUBLICATION testpib_ins_trunct ADD TABLE pub_test.testpub_nopk, testpub_tbl1; 137 138\d+ pub_test.testpub_nopk 139\d+ testpub_tbl1 140\dRp+ testpub_default 141 142ALTER PUBLICATION testpub_default DROP TABLE testpub_tbl1, pub_test.testpub_nopk; 143-- fail - nonexistent 144ALTER PUBLICATION testpub_default DROP TABLE pub_test.testpub_nopk; 145 146\d+ testpub_tbl1 147 148-- permissions 149SET ROLE regress_publication_user2; 150CREATE PUBLICATION testpub2; -- fail 151 152SET ROLE regress_publication_user; 153GRANT CREATE ON DATABASE regression TO regress_publication_user2; 154SET ROLE regress_publication_user2; 155SET client_min_messages = 'ERROR'; 156CREATE PUBLICATION testpub2; -- ok 157RESET client_min_messages; 158 159ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- fail 160 161SET ROLE regress_publication_user; 162GRANT regress_publication_user TO regress_publication_user2; 163SET ROLE regress_publication_user2; 164ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- ok 165 166DROP PUBLICATION testpub2; 167 168SET ROLE regress_publication_user; 169REVOKE CREATE ON DATABASE regression FROM regress_publication_user2; 170 171DROP TABLE testpub_parted; 172DROP VIEW testpub_view; 173DROP TABLE testpub_tbl1; 174 175\dRp+ testpub_default 176 177-- fail - must be owner of publication 178SET ROLE regress_publication_user_dummy; 179ALTER PUBLICATION testpub_default RENAME TO testpub_dummy; 180RESET ROLE; 181 182ALTER PUBLICATION testpub_default RENAME TO testpub_foo; 183 184\dRp testpub_foo 185 186-- rename back to keep the rest simple 187ALTER PUBLICATION testpub_foo RENAME TO testpub_default; 188 189ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2; 190 191\dRp testpub_default 192 193DROP PUBLICATION testpub_default; 194DROP PUBLICATION testpib_ins_trunct; 195DROP PUBLICATION testpub_fortbl; 196 197DROP SCHEMA pub_test CASCADE; 198 199RESET SESSION AUTHORIZATION; 200DROP ROLE regress_publication_user, regress_publication_user2; 201DROP ROLE regress_publication_user_dummy; 202