diff options
Diffstat (limited to 'src/test/regress/expected/publication.out')
| -rw-r--r-- | src/test/regress/expected/publication.out | 344 |
1 files changed, 344 insertions, 0 deletions
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out new file mode 100644 index 0000000..7c7e022 --- /dev/null +++ b/src/test/regress/expected/publication.out @@ -0,0 +1,344 @@ +-- +-- PUBLICATION +-- +CREATE ROLE regress_publication_user LOGIN SUPERUSER; +CREATE ROLE regress_publication_user2; +CREATE ROLE regress_publication_user_dummy LOGIN NOSUPERUSER; +SET SESSION AUTHORIZATION 'regress_publication_user'; +-- suppress warning that depends on wal_level +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION testpub_default; +RESET client_min_messages; +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) + +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION testpib_ins_trunct WITH (publish = insert); +RESET client_min_messages; +ALTER PUBLICATION testpub_default SET (publish = update); +-- error cases +CREATE PUBLICATION testpub_xxx WITH (foo); +ERROR: unrecognized publication parameter: "foo" +CREATE PUBLICATION testpub_xxx WITH (publish = 'cluster, vacuum'); +ERROR: unrecognized "publish" value: "cluster" +CREATE PUBLICATION testpub_xxx WITH (publish_via_partition_root = 'true', publish_via_partition_root = '0'); +ERROR: conflicting or redundant options +\dRp + 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 +(2 rows) + +ALTER PUBLICATION testpub_default SET (publish = 'insert, update, delete'); +\dRp + 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 +(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 TABLE testpub_parted (a int) PARTITION BY LIST (a); +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION testpub_foralltables FOR ALL TABLES WITH (publish = 'insert'); +RESET client_min_messages; +ALTER PUBLICATION testpub_foralltables SET (publish = 'insert, 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" + +\dRp+ testpub_foralltables + Publication testpub_foralltables + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root +--------------------------+------------+---------+---------+---------+-----------+---------- + regress_publication_user | t | t | t | f | f | f +(1 row) + +DROP TABLE testpub_tbl2; +DROP PUBLICATION testpub_foralltables; +CREATE TABLE testpub_tbl3 (a int); +CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3); +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION testpub3 FOR TABLE testpub_tbl3; +CREATE PUBLICATION testpub4 FOR TABLE ONLY testpub_tbl3; +RESET client_min_messages; +\dRp+ testpub3 + Publication testpub3 + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root +--------------------------+------------+---------+---------+---------+-----------+---------- + regress_publication_user | f | t | t | t | t | f +Tables: + "public.testpub_tbl3" + "public.testpub_tbl3a" + +\dRp+ testpub4 + Publication testpub4 + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root +--------------------------+------------+---------+---------+---------+-----------+---------- + regress_publication_user | f | t | t | t | t | f +Tables: + "public.testpub_tbl3" + +DROP TABLE testpub_tbl3, testpub_tbl3a; +DROP PUBLICATION testpub3, testpub4; +-- 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); +CREATE TABLE testpub_parted2 (LIKE testpub_parted); +ALTER PUBLICATION testpub_forparted1 SET (publish='insert'); +ALTER TABLE testpub_parted ATTACH PARTITION testpub_parted1 FOR VALUES IN (1); +ALTER TABLE testpub_parted ATTACH PARTITION testpub_parted2 FOR VALUES IN (2); +-- works despite missing REPLICA IDENTITY, because updates are not replicated +UPDATE testpub_parted1 SET a = 1; +-- only parent is listed as being in publication, not the partition +ALTER PUBLICATION testpub_forparted ADD TABLE testpub_parted; +\dRp+ testpub_forparted + Publication testpub_forparted + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root +--------------------------+------------+---------+---------+---------+-----------+---------- + regress_publication_user | f | t | t | t | t | f +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; +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" + +-- still fail, because parent's publication replicates updates +UPDATE testpub_parted2 SET a = 2; +ERROR: cannot update table "testpub_parted2" because it does not have a replica identity and publishes updates +HINT: To enable updating the table, set REPLICA IDENTITY using ALTER TABLE. +ALTER PUBLICATION testpub_forparted DROP TABLE testpub_parted; +-- works again, because update is no longer replicated +UPDATE testpub_parted2 SET a = 2; +-- publication includes both the parent table and the child table +ALTER PUBLICATION testpub_forparted ADD TABLE testpub_parted, testpub_parted2; +-- only parent is listed as being in publication, not the partition +SELECT * FROM pg_publication_tables; + pubname | schemaname | tablename +-------------------+------------+---------------- + testpub_forparted | public | testpub_parted +(1 row) + +DROP TABLE testpub_parted1, testpub_parted2; +DROP PUBLICATION testpub_forparted, testpub_forparted1; +-- 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; +-- 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. +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION testpub_fortbl FOR TABLE testpub_tbl1, pub_test.testpub_nopk; +RESET client_min_messages; +-- 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 + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root +--------------------------+------------+---------+---------+---------+-----------+---------- + regress_publication_user | f | t | t | t | t | f +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 + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root +--------------------------+------------+---------+---------+---------+-----------+---------- + regress_publication_user | f | t | t | t | f | f +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" + +-- verify relation cache invalidation when a primary key is added using +-- an existing index +CREATE TABLE pub_test.testpub_addpk (id int not null, data int); +ALTER PUBLICATION testpub_default ADD TABLE pub_test.testpub_addpk; +INSERT INTO pub_test.testpub_addpk VALUES(1, 11); +CREATE UNIQUE INDEX testpub_addpk_id_idx ON pub_test.testpub_addpk(id); +-- fail: +UPDATE pub_test.testpub_addpk SET id = 2; +ERROR: cannot update table "testpub_addpk" 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 pub_test.testpub_addpk ADD PRIMARY KEY USING INDEX testpub_addpk_id_idx; +-- now it should work: +UPDATE pub_test.testpub_addpk SET id = 2; +DROP TABLE pub_test.testpub_addpk; +-- 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; +SET client_min_messages = 'ERROR'; +CREATE PUBLICATION testpub2; -- ok +RESET client_min_messages; +ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- fail +ERROR: must be owner of table testpub_tbl1 +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; +DROP TABLE testpub_parted; +DROP VIEW testpub_view; +DROP TABLE testpub_tbl1; +\dRp+ testpub_default + Publication testpub_default + Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root +--------------------------+------------+---------+---------+---------+-----------+---------- + regress_publication_user | f | t | t | t | f | f +(1 row) + +-- fail - must be owner of publication +SET ROLE regress_publication_user_dummy; +ALTER PUBLICATION testpub_default RENAME TO testpub_dummy; +ERROR: must be owner of publication testpub_default +RESET ROLE; +ALTER PUBLICATION testpub_default RENAME TO testpub_foo; +\dRp testpub_foo + List of publications + Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root +-------------+--------------------------+------------+---------+---------+---------+-----------+---------- + testpub_foo | regress_publication_user | f | t | t | t | f | f +(1 row) + +-- rename back to keep the rest simple +ALTER PUBLICATION testpub_foo RENAME TO testpub_default; +ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2; +\dRp testpub_default + List of publications + Name | Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root +-----------------+---------------------------+------------+---------+---------+---------+-----------+---------- + testpub_default | regress_publication_user2 | f | t | t | t | f | f +(1 row) + +DROP PUBLICATION testpub_default; +DROP PUBLICATION testpib_ins_trunct; +DROP PUBLICATION testpub_fortbl; +DROP SCHEMA pub_test CASCADE; +NOTICE: drop cascades to table pub_test.testpub_nopk +RESET SESSION AUTHORIZATION; +DROP ROLE regress_publication_user, regress_publication_user2; +DROP ROLE regress_publication_user_dummy; |