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 9CREATE PUBLICATION testpub_default; 10 11COMMENT ON PUBLICATION testpub_default IS 'test publication'; 12SELECT obj_description(p.oid, 'pg_publication') FROM pg_publication p; 13 14CREATE PUBLICATION testpib_ins_trunct WITH (publish = insert); 15 16ALTER PUBLICATION testpub_default SET (publish = update); 17 18-- error cases 19CREATE PUBLICATION testpub_xxx WITH (foo); 20CREATE PUBLICATION testpub_xxx WITH (publish = 'cluster, vacuum'); 21 22\dRp 23 24ALTER PUBLICATION testpub_default SET (publish = 'insert, update, delete'); 25 26\dRp 27 28--- adding tables 29CREATE SCHEMA pub_test; 30CREATE TABLE testpub_tbl1 (id serial primary key, data text); 31CREATE TABLE pub_test.testpub_nopk (foo int, bar int); 32CREATE VIEW testpub_view AS SELECT 1; 33CREATE TABLE testpub_parted (a int) PARTITION BY LIST (a); 34 35CREATE PUBLICATION testpub_foralltables FOR ALL TABLES WITH (publish = 'insert'); 36ALTER PUBLICATION testpub_foralltables SET (publish = 'insert, update'); 37 38CREATE TABLE testpub_tbl2 (id serial primary key, data text); 39-- fail - can't add to for all tables publication 40ALTER PUBLICATION testpub_foralltables ADD TABLE testpub_tbl2; 41-- fail - can't drop from all tables publication 42ALTER PUBLICATION testpub_foralltables DROP TABLE testpub_tbl2; 43-- fail - can't add to for all tables publication 44ALTER PUBLICATION testpub_foralltables SET TABLE pub_test.testpub_nopk; 45 46SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_foralltables'; 47\d+ testpub_tbl2 48\dRp+ testpub_foralltables 49 50DROP TABLE testpub_tbl2; 51DROP PUBLICATION testpub_foralltables; 52 53CREATE TABLE testpub_tbl3 (a int); 54CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3); 55CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3; 56CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3; 57\dRp+ testpub3 58\dRp+ testpub4 59 60DROP TABLE testpub_tbl3, testpub_tbl3a; 61DROP PUBLICATION testpub3, testpub4; 62 63-- Test cache invalidation FOR ALL TABLES publication 64SET client_min_messages = 'ERROR'; 65CREATE TABLE testpub_tbl4(a int); 66INSERT INTO testpub_tbl4 values(1); 67UPDATE testpub_tbl4 set a = 2; 68CREATE PUBLICATION testpub_foralltables FOR ALL TABLES; 69RESET client_min_messages; 70-- fail missing REPLICA IDENTITY 71UPDATE testpub_tbl4 set a = 3; 72DROP PUBLICATION testpub_foralltables; 73-- should pass after dropping the publication 74UPDATE testpub_tbl4 set a = 3; 75DROP TABLE testpub_tbl4; 76 77-- fail - view 78CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_view; 79CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1, pub_test.testpub_nopk; 80-- fail - already added 81ALTER PUBLICATION testpub_fortbl ADD TABLE testpub_tbl1; 82-- fail - already added 83CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1; 84 85\dRp+ testpub_fortbl 86 87-- fail - view 88ALTER PUBLICATION testpub_default ADD TABLE testpub_view; 89-- fail - partitioned table 90ALTER PUBLICATION testpub_fortbl ADD TABLE testpub_parted; 91 92ALTER PUBLICATION testpub_default ADD TABLE testpub_tbl1; 93ALTER PUBLICATION testpub_default SET TABLE testpub_tbl1; 94ALTER PUBLICATION testpub_default ADD TABLE pub_test.testpub_nopk; 95 96ALTER PUBLICATION testpib_ins_trunct ADD TABLE pub_test.testpub_nopk, testpub_tbl1; 97 98\d+ pub_test.testpub_nopk 99\d+ testpub_tbl1 100\dRp+ testpub_default 101 102ALTER PUBLICATION testpub_default DROP TABLE testpub_tbl1, pub_test.testpub_nopk; 103-- fail - nonexistent 104ALTER PUBLICATION testpub_default DROP TABLE pub_test.testpub_nopk; 105 106\d+ testpub_tbl1 107 108-- permissions 109SET ROLE regress_publication_user2; 110CREATE PUBLICATION testpub2; -- fail 111 112SET ROLE regress_publication_user; 113GRANT CREATE ON DATABASE regression TO regress_publication_user2; 114SET ROLE regress_publication_user2; 115CREATE PUBLICATION testpub2; -- ok 116 117ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- fail 118 119SET ROLE regress_publication_user; 120GRANT regress_publication_user TO regress_publication_user2; 121SET ROLE regress_publication_user2; 122ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- ok 123 124DROP PUBLICATION testpub2; 125 126SET ROLE regress_publication_user; 127REVOKE CREATE ON DATABASE regression FROM regress_publication_user2; 128 129DROP TABLE testpub_parted; 130DROP VIEW testpub_view; 131DROP TABLE testpub_tbl1; 132 133\dRp+ testpub_default 134 135-- fail - must be owner of publication 136SET ROLE regress_publication_user_dummy; 137ALTER PUBLICATION testpub_default RENAME TO testpub_dummy; 138RESET ROLE; 139 140ALTER PUBLICATION testpub_default RENAME TO testpub_foo; 141 142\dRp testpub_foo 143 144-- rename back to keep the rest simple 145ALTER PUBLICATION testpub_foo RENAME TO testpub_default; 146 147ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2; 148 149\dRp testpub_default 150 151DROP PUBLICATION testpub_default; 152DROP PUBLICATION testpib_ins_trunct; 153DROP PUBLICATION testpub_fortbl; 154 155DROP SCHEMA pub_test CASCADE; 156 157RESET SESSION AUTHORIZATION; 158DROP ROLE regress_publication_user, regress_publication_user2; 159DROP ROLE regress_publication_user_dummy; 160