v23-0003-Tests-and-documentation-for-schema-level-support.patch
text/x-patch
Filename: v23-0003-Tests-and-documentation-for-schema-level-support.patch
Type: text/x-patch
Part: 2
Patch
Format: format-patch
Series: patch v23-0003
Subject: Tests and documentation for schema level support for publication.
| File | + | − |
|---|---|---|
| doc/src/sgml/catalogs.sgml | 69 | 3 |
| doc/src/sgml/ref/alter_publication.sgml | 46 | 1 |
| doc/src/sgml/ref/create_publication.sgml | 46 | 8 |
| src/bin/pg_dump/t/002_pg_dump.pl | 30 | 0 |
| src/test/regress/expected/object_address.out | 5 | 1 |
| src/test/regress/expected/publication.out | 466 | 2 |
| src/test/regress/sql/object_address.sql | 3 | 0 |
| src/test/regress/sql/publication.sql | 209 | 2 |
| src/test/subscription/t/025_rep_changes_for_schema.pl | 168 | 0 |
From 423db53f5d8ca3873b873cfdcbb2e54f67e64f94 Mon Sep 17 00:00:00 2001
From: Vigneshwaran C <vignesh21@gmail.com>
Date: Mon, 30 Aug 2021 16:31:14 +0530
Subject: [PATCH v23 3/4] Tests and documentation for schema level support for
publication.
Tests and documentation for schema level support for publication.
---
doc/src/sgml/catalogs.sgml | 72 ++-
doc/src/sgml/ref/alter_publication.sgml | 47 +-
doc/src/sgml/ref/create_publication.sgml | 54 +-
src/bin/pg_dump/t/002_pg_dump.pl | 30 ++
src/test/regress/expected/object_address.out | 6 +-
src/test/regress/expected/publication.out | 468 +++++++++++++++++-
src/test/regress/sql/object_address.sql | 3 +
src/test/regress/sql/publication.sql | 211 +++++++-
.../t/025_rep_changes_for_schema.pl | 168 +++++++
9 files changed, 1042 insertions(+), 17 deletions(-)
create mode 100644 src/test/subscription/t/025_rep_changes_for_schema.pl
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 2b2c70a26e..2186a27e27 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -245,6 +245,11 @@
<entry>relation to publication mapping</entry>
</row>
+ <row>
+ <entry><link linkend="catalog-pg-publication-schema"><structname>pg_publication_schema</structname></link></entry>
+ <entry>schema to publication mapping</entry>
+ </row>
+
<row>
<entry><link linkend="catalog-pg-range"><structname>pg_range</structname></link></entry>
<entry>information about range types</entry>
@@ -6236,6 +6241,67 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
</table>
</sect1>
+ <sect1 id="catalog-pg-publication-schema">
+ <title><structname>pg_publication_schema</structname></title>
+
+ <indexterm zone="catalog-pg-publication-schema">
+ <primary>pg_publication_schema</primary>
+ </indexterm>
+
+ <para>
+ The catalog <structname>pg_publication_schema</structname> contains the
+ mapping between schemas and publications in the database. This is a
+ many-to-many mapping.
+ </para>
+
+ <table>
+ <title><structname>pg_publication_schema</structname> Columns</title>
+ <tgroup cols="1">
+ <thead>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ Column Type
+ </para>
+ <para>
+ Description
+ </para></entry>
+ </row>
+ </thead>
+
+ <tbody>
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>oid</structfield> <type>oid</type>
+ </para>
+ <para>
+ Row identifier
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>pspubid</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-publication"><structname>pg_publication</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ Reference to publication
+ </para></entry>
+ </row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>psnspcid</structfield> <type>oid</type>
+ (references <link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.<structfield>oid</structfield>)
+ </para>
+ <para>
+ Reference to schema
+ </para></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+ </sect1>
+
<sect1 id="catalog-pg-range">
<title><structname>pg_range</structname></title>
@@ -11276,9 +11342,9 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
information about the mapping between publications and the tables they
contain. Unlike the underlying catalog
<link linkend="catalog-pg-publication-rel"><structname>pg_publication_rel</structname></link>,
- this view expands
- publications defined as <literal>FOR ALL TABLES</literal>, so for such
- publications there will be a row for each eligible table.
+ this view expands publications defined as <literal>FOR ALL TABLES</literal>
+ and <literal>FOR ALL TABLES IN SCHEMA</literal>, so for such publications
+ there will be a row for each eligible table.
</para>
<table>
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index faa114b2c6..21788f7176 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -24,6 +24,9 @@ PostgreSQL documentation
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> ADD TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ...]
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ...]
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> DROP TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ...]
+ALTER PUBLICATION <replaceable class="parameter">name</replaceable> ADD ALL TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ...]
+ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET ALL TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ...]
+ALTER PUBLICATION <replaceable class="parameter">name</replaceable> DROP ALL TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ...]
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
@@ -50,7 +53,18 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
</para>
<para>
- The fourth variant of this command listed in the synopsis can change
+ The fourth, fifth and sixth variants of this command change which schemas
+ are part of the publication. The <literal>SET SCHEMA</literal> clause will
+ replace the list of schemas in the publication with the specified one.
+ The <literal>ADD SCHEMA</literal> and <literal>DROP SCHEMA</literal> clauses
+ will add and remove one or more schemas from the publication. Note that
+ adding schemas to a publication that is already subscribed to will require
+ a <literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal> action on
+ the subscribing side in order to become effective.
+ </para>
+
+ <para>
+ The seventh variant of this command listed in the synopsis can change
all of the publication properties specified in
<xref linkend="sql-createpublication"/>. Properties not mentioned in the
command retain their previous settings.
@@ -63,6 +77,8 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
<para>
You must own the publication to use <command>ALTER PUBLICATION</command>.
Adding a table to a publication additionally requires owning that table.
+ The <literal>ADD SCHEMA</literal> and <literal>SET SCHEMA</literal> to a
+ publication requires the invoking user to be a superuser.
To alter the owner, you must also be a direct or indirect member of the new
owning role. The new owner must have <literal>CREATE</literal> privilege on
the database. Also, the new owner of a <literal>FOR ALL TABLES</literal>
@@ -97,6 +113,15 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><replaceable class="parameter">schema_name</replaceable></term>
+ <listitem>
+ <para>
+ Name of an existing schema.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
<listitem>
@@ -141,6 +166,26 @@ ALTER PUBLICATION noinsert SET (publish = 'update, delete');
Add some tables to the publication:
<programlisting>
ALTER PUBLICATION mypublication ADD TABLE users, departments;
+</programlisting></para>
+
+ <para>
+ Add some schemas to the publication:
+<programlisting>
+ALTER PUBLICATION sales_publication ADD ALL TABLES IN SCHEMA marketing_june, sales_june;
+</programlisting>
+ </para>
+
+ <para>
+ Drop some schema from the publication:
+<programlisting>
+ALTER PUBLICATION production_quarterly_publication DROP ALL TABLES IN SCHEMA production_july;
+</programlisting>
+ </para>
+
+ <para>
+ Set schema to the publication:
+<programlisting>
+ALTER PUBLICATION production_publication SET ALL TABLES IN SCHEMA production_july;
</programlisting></para>
</refsect1>
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index ff82fbca55..db8d90cd1d 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -22,8 +22,9 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
- [ FOR TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ...]
- | FOR ALL TABLES ]
+ [ FOR ALL TABLES
+ | [ FOR { TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ... ], }
+ | { ALL TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ] } ] ]
[ WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
</synopsis>
</refsynopsisdiv>
@@ -99,6 +100,16 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>FOR ALL TABLES IN SCHEMA</literal></term>
+ <listitem>
+ <para>
+ Marks the publication as one that replicates changes for the all tables in
+ the specified list of schemas, including tables created in the future.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
<listitem>
@@ -153,9 +164,10 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<title>Notes</title>
<para>
- If neither <literal>FOR TABLE</literal> nor <literal>FOR ALL
- TABLES</literal> is specified, then the publication starts out with an
- empty set of tables. That is useful if tables are to be added later.
+ If <literal>FOR TABLE</literal>, <literal>FOR ALL TABLES</literal> or
+ <literal>FOR ALL TABLES IN SCHEMA</literal> is not specified, then the
+ publication starts out with an empty set of tables. That is useful if
+ tables or schemas are to be added later.
</para>
<para>
@@ -170,9 +182,10 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
</para>
<para>
- To add a table to a publication, the invoking user must have ownership
- rights on the table. The <command>FOR ALL TABLES</command> clause requires
- the invoking user to be a superuser.
+ To add a table/schema to a publication, the invoking user must have
+ ownership rights on the table/schema. The <command>FOR ALL TABLES</command>
+ and <command>FOR ALL TABLES IN SCHEMA</command> clause requires the invoking
+ user to be a superuser.
</para>
<para>
@@ -222,6 +235,31 @@ CREATE PUBLICATION alltables FOR ALL TABLES;
<programlisting>
CREATE PUBLICATION insert_only FOR TABLE mydata
WITH (publish = 'insert');
+</programlisting>
+ </para>
+
+ <para>
+ Create a publication that publishes all changes for all the tables present in
+ the schema "production":
+<programlisting>
+CREATE PUBLICATION production_publication FOR ALL TABLES IN SCHEMA production;
+</programlisting>
+ </para>
+
+ <para>
+ Create a publication that publishes all changes for tables "users" and
+ "departments" and that publishes all changes for all the tables present in
+ the schema "production":
+<programlisting>
+CREATE PUBLICATION production_publication FOR TABLE users, departments, ALL TABLES IN SCHEMA production;
+</programlisting>
+ </para>
+
+ <para>
+ Create a publication that publishes all changes for all the tables present in
+ the schemas "marketing" and "sales":
+<programlisting>
+CREATE PUBLICATION sales_publication FOR ALL TABLES IN SCHEMA marketing, sales;
</programlisting></para>
</refsect1>
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index a4ee54d516..f19f279d55 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -2301,6 +2301,15 @@ my %tests = (
like => { %full_runs, section_post_data => 1, },
},
+ 'CREATE PUBLICATION pub3' => {
+ create_order => 50,
+ create_sql => 'CREATE PUBLICATION pub3;',
+ regexp => qr/^
+ \QCREATE PUBLICATION pub3 WITH (publish = 'insert, update, delete, truncate');\E
+ /xm,
+ like => { %full_runs, section_post_data => 1, },
+ },
+
'CREATE SUBSCRIPTION sub1' => {
create_order => 50,
create_sql => 'CREATE SUBSCRIPTION sub1
@@ -2337,6 +2346,27 @@ my %tests = (
unlike => { exclude_dump_test_schema => 1, },
},
+ 'ALTER PUBLICATION pub3 ADD ALL TABLES IN SCHEMA dump_test' => {
+ create_order => 51,
+ create_sql =>
+ 'ALTER PUBLICATION pub3 ADD ALL TABLES IN SCHEMA dump_test;',
+ regexp => qr/^
+ \QALTER PUBLICATION pub3 ADD ALL TABLES IN SCHEMA dump_test;\E
+ /xm,
+ like => { %full_runs, section_post_data => 1, },
+ unlike => { exclude_dump_test_schema => 1, },
+ },
+
+ 'ALTER PUBLICATION pub3 ADD ALL TABLES IN SCHEMA public' => {
+ create_order => 52,
+ create_sql =>
+ 'ALTER PUBLICATION pub3 ADD ALL TABLES IN SCHEMA public;',
+ regexp => qr/^
+ \QALTER PUBLICATION pub3 ADD ALL TABLES IN SCHEMA public;\E
+ /xm,
+ like => { %full_runs, section_post_data => 1, },
+ },
+
'CREATE SCHEMA public' => {
regexp => qr/^CREATE SCHEMA public;/m,
diff --git a/src/test/regress/expected/object_address.out b/src/test/regress/expected/object_address.out
index 388097a695..bd60f72f78 100644
--- a/src/test/regress/expected/object_address.out
+++ b/src/test/regress/expected/object_address.out
@@ -45,6 +45,7 @@ CREATE TRANSFORM FOR int LANGUAGE SQL (
-- suppress warning that depends on wal_level
SET client_min_messages = 'ERROR';
CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
+CREATE PUBLICATION addr_pub_schema FOR ALL TABLES IN SCHEMA addr_nsp;
RESET client_min_messages;
CREATE SUBSCRIPTION regress_addr_sub CONNECTION '' PUBLICATION bar WITH (connect = false, slot_name = NONE);
WARNING: tables were not subscribed, you will have to run ALTER SUBSCRIPTION ... REFRESH PUBLICATION to subscribe the tables
@@ -428,6 +429,7 @@ WITH objects (type, name, args) AS (VALUES
('access method', '{btree}', '{}'),
('publication', '{addr_pub}', '{}'),
('publication relation', '{addr_nsp, gentable}', '{addr_pub}'),
+ ('publication schema', '{addr_nsp}', '{addr_pub_schema}'),
('subscription', '{regress_addr_sub}', '{}'),
('statistics object', '{addr_nsp, gentable_stat}', '{}')
)
@@ -490,7 +492,8 @@ SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*,
subscription | | regress_addr_sub | regress_addr_sub | t
publication | | addr_pub | addr_pub | t
publication relation | | | addr_nsp.gentable in publication addr_pub | t
-(49 rows)
+ publication schema | | | addr_nsp in publication addr_pub_schema | t
+(50 rows)
---
--- Cleanup resources
@@ -502,6 +505,7 @@ drop cascades to foreign table genftable
drop cascades to server integer
drop cascades to user mapping for regress_addr_user on server integer
DROP PUBLICATION addr_pub;
+DROP PUBLICATION addr_pub_schema;
DROP SUBSCRIPTION regress_addr_sub;
DROP SCHEMA addr_nsp CASCADE;
NOTICE: drop cascades to 14 other objects
diff --git a/src/test/regress/expected/publication.out b/src/test/regress/expected/publication.out
index 23131de23f..17a42d5ea7 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -69,6 +69,92 @@ DETAIL: Tables cannot be added to, dropped from, or set on FOR ALL TABLES publi
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, dropped from, or set on FOR ALL TABLES publications.
+-- fail - can't add schema to for all tables publication
+ALTER PUBLICATION testpub_foralltables ADD ALL TABLES IN SCHEMA pub_test;
+ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES
+DETAIL: Schemas cannot be added to, dropped from, or set on FOR ALL TABLES publications.
+-- fail - can't drop schema from all tables publication
+ALTER PUBLICATION testpub_foralltables DROP ALL TABLES IN SCHEMA pub_test;
+ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES
+DETAIL: Schemas cannot be added to, dropped from, or set on FOR ALL TABLES publications.
+-- fail - can't set schema to for all tables publication
+ALTER PUBLICATION testpub_foralltables SET ALL TABLES IN SCHEMA pub_test;
+ERROR: publication "testpub_foralltables" is defined as FOR ALL TABLES
+DETAIL: Schemas cannot be added to, dropped from, or set on FOR ALL TABLES publications.
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub_fortable FOR TABLE testpub_tbl1;
+RESET client_min_messages;
+-- should be able to add schema to for table publication
+ALTER PUBLICATION testpub_fortable ADD ALL TABLES IN SCHEMA pub_test;
+\dRp+ testpub_fortable
+ Publication testpub_fortable
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Tables:
+ "public.testpub_tbl1"
+Schemas:
+ "pub_test"
+
+-- should be able to drop schema from for table publication
+ALTER PUBLICATION testpub_fortable DROP ALL TABLES IN SCHEMA pub_test;
+\dRp+ testpub_fortable
+ Publication testpub_fortable
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Tables:
+ "public.testpub_tbl1"
+
+-- should be able to set schema to for table publication
+ALTER PUBLICATION testpub_fortable SET ALL TABLES IN SCHEMA pub_test;
+\dRp+ testpub_fortable
+ Publication testpub_fortable
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Tables:
+ "public.testpub_tbl1"
+Schemas:
+ "pub_test"
+
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub_forschema FOR ALL TABLES IN SCHEMA pub_test;
+RESET client_min_messages;
+-- should be able to add table to schema publication
+ALTER PUBLICATION testpub_forschema ADD TABLE pub_test.testpub_nopk;
+\dRp+ testpub_forschema
+ Publication testpub_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Tables:
+ "pub_test.testpub_nopk"
+Schemas:
+ "pub_test"
+
+-- should be able to drop table from schema publication
+ALTER PUBLICATION testpub_forschema DROP TABLE pub_test.testpub_nopk;
+\dRp+ testpub_forschema
+ Publication testpub_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "pub_test"
+
+-- should be able to set table to schema publication
+ALTER PUBLICATION testpub_forschema SET TABLE pub_test.testpub_nopk;
+\dRp+ testpub_forschema
+ Publication testpub_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Tables:
+ "pub_test.testpub_nopk"
+Schemas:
+ "pub_test"
+
SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_foralltables';
pubname | puballtables
----------------------+--------------
@@ -94,7 +180,7 @@ Publications:
(1 row)
DROP TABLE testpub_tbl2;
-DROP PUBLICATION testpub_foralltables;
+DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema;
CREATE TABLE testpub_tbl3 (a int);
CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3);
SET client_min_messages = 'ERROR';
@@ -246,18 +332,21 @@ GRANT CREATE ON DATABASE regression TO regress_publication_user2;
SET ROLE regress_publication_user2;
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub2; -- ok
+CREATE PUBLICATION testpub3; -- ok
RESET client_min_messages;
ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- fail
ERROR: must be owner of table testpub_tbl1
+ALTER PUBLICATION testpub3 ADD ALL TABLES IN SCHEMA pub_test; -- fail
+ERROR: must be superuser to add or set schemas
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;
+DROP PUBLICATION testpub3;
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
@@ -289,11 +378,386 @@ ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2;
testpub_default | regress_publication_user2 | f | t | t | t | f | f
(1 row)
+-- adding schemas and tables
+CREATE SCHEMA pub_test1;
+CREATE SCHEMA pub_test2;
+CREATE SCHEMA pub_test3;
+CREATE TABLE pub_test1.tbl (id int, data text);
+CREATE TABLE pub_test1.tbl1 (id serial primary key, data text);
+CREATE TABLE pub_test2.tbl1 (id serial primary key, data text);
+-- suppress warning that depends on wal_level
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub1_forschema FOR ALL TABLES IN SCHEMA pub_test1;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "pub_test1"
+
+SELECT p.pubname FROM pg_catalog.pg_publication p, pg_catalog.pg_namespace n, pg_catalog.pg_publication_schema ps WHERE n.oid = ps.psnspcid AND p.oid = ps.pspubid AND n.nspname = 'pub_test1' ORDER BY 1;
+ pubname
+--------------------
+ testpub1_forschema
+(1 row)
+
+CREATE PUBLICATION testpub2_forschema FOR ALL TABLES IN SCHEMA pub_test1, pub_test2, pub_test3;
+\dRp+ testpub2_forschema
+ Publication testpub2_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "pub_test1"
+ "pub_test2"
+ "pub_test3"
+
+SELECT p.pubname FROM pg_catalog.pg_publication p, pg_catalog.pg_namespace n, pg_catalog.pg_publication_schema ps WHERE n.oid = ps.psnspcid AND p.oid = ps.pspubid AND n.nspname = 'pub_test1' ORDER BY 1;
+ pubname
+--------------------
+ testpub1_forschema
+ testpub2_forschema
+(2 rows)
+
+SELECT p.pubname FROM pg_catalog.pg_publication p, pg_catalog.pg_namespace n, pg_catalog.pg_publication_schema ps WHERE n.oid = ps.psnspcid AND p.oid = ps.pspubid AND n.nspname = 'pub_test2' ORDER BY 1;
+ pubname
+--------------------
+ testpub2_forschema
+(1 row)
+
+SELECT p.pubname FROM pg_catalog.pg_publication p, pg_catalog.pg_namespace n, pg_catalog.pg_publication_schema ps WHERE n.oid = ps.psnspcid AND p.oid = ps.pspubid AND n.nspname = 'pub_test3' ORDER BY 1;
+ pubname
+--------------------
+ testpub2_forschema
+(1 row)
+
+-- check create publication on CURRENT_SCHEMA
+CREATE PUBLICATION testpub3_forschema FOR ALL TABLES IN SCHEMA CURRENT_SCHEMA;
+RESET client_min_messages;
+\dRp+ testpub3_forschema
+ Publication testpub3_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "public"
+
+SELECT p.pubname FROM pg_catalog.pg_publication p, pg_catalog.pg_namespace n, pg_catalog.pg_publication_schema ps WHERE n.oid = ps.psnspcid AND p.oid = ps.pspubid AND n.nspname = 'public' ORDER BY 1;
+ pubname
+--------------------
+ testpub3_forschema
+(1 row)
+
+-- check create publication on CURRENT_SCHEMA where search_path is not set
+SET SEARCH_PATH='';
+CREATE PUBLICATION testpub_forschema FOR ALL TABLES IN SCHEMA CURRENT_SCHEMA;
+ERROR: no schema has been selected
+RESET SEARCH_PATH;
+-- check create publication on a schema that does not exist
+CREATE PUBLICATION testpub_forschema FOR ALL TABLES IN SCHEMA non_existent_schema;
+ERROR: schema "non_existent_schema" does not exist
+-- check create publication on a system schema
+CREATE PUBLICATION testpub_forschema FOR ALL TABLES IN SCHEMA pg_catalog;
+ERROR: "pg_catalog" is a system schema
+DETAIL: This operation is not supported for system schemas.
+-- check create publication on an object which is not schema
+CREATE PUBLICATION testpub1_forschema1 FOR ALL TABLES IN SCHEMA testpub_view;
+ERROR: schema "testpub_view" does not exist
+-- dropping the schema should reflect the change in publication
+DROP SCHEMA pub_test3;
+\dRp+ testpub2_forschema
+ Publication testpub2_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "pub_test1"
+ "pub_test2"
+
+-- renaming the schema should reflect the change in publication
+ALTER SCHEMA pub_test1 RENAME to pub_test1_renamed;
+\dRp+ testpub2_forschema
+ Publication testpub2_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "pub_test1_renamed"
+ "pub_test2"
+
+ALTER SCHEMA pub_test1_renamed RENAME to pub_test1;
+\dRp+ testpub2_forschema
+ Publication testpub2_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "pub_test1"
+ "pub_test2"
+
+-- alter publication add schema
+ALTER PUBLICATION testpub1_forschema ADD ALL TABLES IN SCHEMA pub_test2;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "pub_test1"
+ "pub_test2"
+
+-- alter publication add CURRENT_SCHEMA
+ALTER PUBLICATION testpub1_forschema ADD ALL TABLES IN SCHEMA CURRENT_SCHEMA;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "pub_test1"
+ "pub_test2"
+ "public"
+
+-- add non existent schema
+ALTER PUBLICATION testpub1_forschema ADD ALL TABLES IN SCHEMA non_existent_schema;
+ERROR: schema "non_existent_schema" does not exist
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "pub_test1"
+ "pub_test2"
+ "public"
+
+-- add a schema which is already added to the publication
+ALTER PUBLICATION testpub1_forschema ADD ALL TABLES IN SCHEMA pub_test1;
+ERROR: schema "pub_test1" is already member of publication "testpub1_forschema"
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "pub_test1"
+ "pub_test2"
+ "public"
+
+-- alter publication drop CURRENT_SCHEMA
+ALTER PUBLICATION testpub1_forschema DROP ALL TABLES IN SCHEMA CURRENT_SCHEMA;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "pub_test1"
+ "pub_test2"
+
+-- alter publication drop schema
+ALTER PUBLICATION testpub1_forschema DROP ALL TABLES IN SCHEMA pub_test2;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "pub_test1"
+
+-- drop schema that is not present in the publication
+ALTER PUBLICATION testpub1_forschema DROP ALL TABLES IN SCHEMA pub_test2;
+ERROR: schema "pub_test2" is not part of the publication
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "pub_test1"
+
+-- drop a schema that does not exist in the system
+ALTER PUBLICATION testpub1_forschema DROP ALL TABLES IN SCHEMA non_existent_schema;
+ERROR: schema "non_existent_schema" does not exist
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "pub_test1"
+
+-- drop all schemas
+ALTER PUBLICATION testpub1_forschema DROP ALL TABLES IN SCHEMA pub_test1;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+(1 row)
+
+-- alter publication set schema
+ALTER PUBLICATION testpub1_forschema SET ALL TABLES IN SCHEMA pub_test1;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "pub_test1"
+
+-- alter publication set multiple schema
+ALTER PUBLICATION testpub1_forschema SET ALL TABLES IN SCHEMA pub_test1, pub_test2;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "pub_test1"
+ "pub_test2"
+
+-- alter publication set non-existent schema
+ALTER PUBLICATION testpub1_forschema SET ALL TABLES IN SCHEMA non_existent_schema;
+ERROR: schema "non_existent_schema" does not exist
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "pub_test1"
+ "pub_test2"
+
+-- alter publication set it with the same schema
+ALTER PUBLICATION testpub1_forschema SET ALL TABLES IN SCHEMA pub_test1, pub_test2;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "pub_test1"
+ "pub_test2"
+
+-- alter publication set it duplicate schemas should set the schemas after
+-- removing the duplicate schemas
+ALTER PUBLICATION testpub1_forschema SET ALL TABLES IN SCHEMA pub_test1, pub_test1;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "pub_test1"
+
+-- cleanup pub_test1 schema for invalidation tests
+ALTER PUBLICATION testpub2_forschema DROP ALL TABLES IN SCHEMA pub_test1;
+-- verify relation cache invalidations through update statement for the
+-- default REPLICA IDENTITY on the relation, if schema is part of the
+-- publication then update will fail because relation's relreplident
+-- option will be set, if schema is not part of the publication then update
+-- will be successful.
+INSERT INTO pub_test1.tbl VALUES(1, 'test');
+-- fail
+UPDATE pub_test1.tbl SET id = 2;
+ERROR: cannot update table "tbl" 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 testpub1_forschema DROP ALL TABLES IN SCHEMA pub_test1;
+-- success
+UPDATE pub_test1.tbl SET id = 2;
+ALTER PUBLICATION testpub1_forschema SET ALL TABLES IN SCHEMA pub_test1;
+-- fail
+UPDATE pub_test1.tbl SET id = 2;
+ERROR: cannot update table "tbl" 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 testpub1_forschema DROP ALL TABLES IN SCHEMA pub_test1;
+-- success
+UPDATE pub_test1.tbl SET id = 2;
+ALTER PUBLICATION testpub1_forschema ADD ALL TABLES IN SCHEMA pub_test1;
+-- fail
+UPDATE pub_test1.tbl SET id = 2;
+ERROR: cannot update table "tbl" 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 set it with CURRENT_SCHEMA
+ALTER PUBLICATION testpub1_forschema SET ALL TABLES IN SCHEMA CURRENT_SCHEMA;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "public"
+
+-- alter publication set all tables in schema on an empty publication.
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub4_forschema;
+RESET client_min_messages;
+\dRp+ testpub4_forschema
+ Publication testpub4_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+(1 row)
+
+ALTER PUBLICATION testpub4_forschema SET ALL TABLES IN SCHEMA pub_test1;
+\dRp+ testpub4_forschema
+ Publication testpub4_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Schemas:
+ "pub_test1"
+
+-- create publication including both for table and for all tables in schema.
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub_forschema_fortable FOR ALL TABLES IN SCHEMA pub_test1, TABLE pub_test2.tbl1;
+CREATE PUBLICATION testpub_fortable_forschema FOR TABLE pub_test2.tbl1, ALL TABLES IN SCHEMA pub_test1;
+RESET client_min_messages;
+\dRp+ testpub_forschema_fortable
+ Publication testpub_forschema_fortable
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Tables:
+ "pub_test2.tbl1"
+Schemas:
+ "pub_test1"
+
+\dRp+ testpub_fortable_forschema
+ Publication testpub_fortable_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root
+--------------------------+------------+---------+---------+---------+-----------+----------
+ regress_publication_user | f | t | t | t | t | f
+Tables:
+ "pub_test2.tbl1"
+Schemas:
+ "pub_test1"
+
+-- fail specifying table without any of FOR ALL TABLES IN SCHEMA or FOR TABLE or FOR ALL TABLES
+CREATE PUBLICATION testpub_error FOR pub_test2.tbl1;
+ERROR: FOR TABLE/FOR ALL TABLES IN SCHEMA should be specified before the table/schema name(s)
+LINE 1: CREATE PUBLICATION testpub_error FOR pub_test2.tbl1;
+ ^
+DROP VIEW testpub_view;
DROP PUBLICATION testpub_default;
DROP PUBLICATION testpib_ins_trunct;
DROP PUBLICATION testpub_fortbl;
+DROP PUBLICATION testpub1_forschema;
+DROP PUBLICATION testpub2_forschema;
+DROP PUBLICATION testpub3_forschema;
+DROP PUBLICATION testpub4_forschema;
+DROP PUBLICATION testpub_forschema_fortable;
+DROP PUBLICATION testpub_fortable_forschema;
DROP SCHEMA pub_test CASCADE;
NOTICE: drop cascades to table pub_test.testpub_nopk
+DROP SCHEMA pub_test1 CASCADE;
+NOTICE: drop cascades to 2 other objects
+DETAIL: drop cascades to table pub_test1.tbl
+drop cascades to table pub_test1.tbl1
+DROP SCHEMA pub_test2 CASCADE;
+NOTICE: drop cascades to table pub_test2.tbl1
RESET SESSION AUTHORIZATION;
DROP ROLE regress_publication_user, regress_publication_user2;
DROP ROLE regress_publication_user_dummy;
diff --git a/src/test/regress/sql/object_address.sql b/src/test/regress/sql/object_address.sql
index 2f4f66e3e1..49822d7ed4 100644
--- a/src/test/regress/sql/object_address.sql
+++ b/src/test/regress/sql/object_address.sql
@@ -48,6 +48,7 @@ CREATE TRANSFORM FOR int LANGUAGE SQL (
-- suppress warning that depends on wal_level
SET client_min_messages = 'ERROR';
CREATE PUBLICATION addr_pub FOR TABLE addr_nsp.gentable;
+CREATE PUBLICATION addr_pub_schema FOR ALL TABLES IN SCHEMA addr_nsp;
RESET client_min_messages;
CREATE SUBSCRIPTION regress_addr_sub CONNECTION '' PUBLICATION bar WITH (connect = false, slot_name = NONE);
CREATE STATISTICS addr_nsp.gentable_stat ON a, b FROM addr_nsp.gentable;
@@ -198,6 +199,7 @@ WITH objects (type, name, args) AS (VALUES
('access method', '{btree}', '{}'),
('publication', '{addr_pub}', '{}'),
('publication relation', '{addr_nsp, gentable}', '{addr_pub}'),
+ ('publication schema', '{addr_nsp}', '{addr_pub_schema}'),
('subscription', '{regress_addr_sub}', '{}'),
('statistics object', '{addr_nsp, gentable_stat}', '{}')
)
@@ -215,6 +217,7 @@ SELECT (pg_identify_object(addr1.classid, addr1.objid, addr1.objsubid)).*,
---
DROP FOREIGN DATA WRAPPER addr_fdw CASCADE;
DROP PUBLICATION addr_pub;
+DROP PUBLICATION addr_pub_schema;
DROP SUBSCRIPTION regress_addr_sub;
DROP SCHEMA addr_nsp CASCADE;
diff --git a/src/test/regress/sql/publication.sql b/src/test/regress/sql/publication.sql
index d844075368..cae96e7662 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -51,12 +51,45 @@ ALTER PUBLICATION testpub_foralltables DROP TABLE testpub_tbl2;
-- fail - can't add to for all tables publication
ALTER PUBLICATION testpub_foralltables SET TABLE pub_test.testpub_nopk;
+-- fail - can't add schema to for all tables publication
+ALTER PUBLICATION testpub_foralltables ADD ALL TABLES IN SCHEMA pub_test;
+-- fail - can't drop schema from all tables publication
+ALTER PUBLICATION testpub_foralltables DROP ALL TABLES IN SCHEMA pub_test;
+-- fail - can't set schema to for all tables publication
+ALTER PUBLICATION testpub_foralltables SET ALL TABLES IN SCHEMA pub_test;
+
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub_fortable FOR TABLE testpub_tbl1;
+RESET client_min_messages;
+-- should be able to add schema to for table publication
+ALTER PUBLICATION testpub_fortable ADD ALL TABLES IN SCHEMA pub_test;
+\dRp+ testpub_fortable
+-- should be able to drop schema from for table publication
+ALTER PUBLICATION testpub_fortable DROP ALL TABLES IN SCHEMA pub_test;
+\dRp+ testpub_fortable
+-- should be able to set schema to for table publication
+ALTER PUBLICATION testpub_fortable SET ALL TABLES IN SCHEMA pub_test;
+\dRp+ testpub_fortable
+
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub_forschema FOR ALL TABLES IN SCHEMA pub_test;
+RESET client_min_messages;
+-- should be able to add table to schema publication
+ALTER PUBLICATION testpub_forschema ADD TABLE pub_test.testpub_nopk;
+\dRp+ testpub_forschema
+-- should be able to drop table from schema publication
+ALTER PUBLICATION testpub_forschema DROP TABLE pub_test.testpub_nopk;
+\dRp+ testpub_forschema
+-- should be able to set table to schema publication
+ALTER PUBLICATION testpub_forschema SET TABLE pub_test.testpub_nopk;
+\dRp+ testpub_forschema
+
SELECT pubname, puballtables FROM pg_publication WHERE pubname = 'testpub_foralltables';
\d+ testpub_tbl2
\dRp+ testpub_foralltables
DROP TABLE testpub_tbl2;
-DROP PUBLICATION testpub_foralltables;
+DROP PUBLICATION testpub_foralltables, testpub_fortable, testpub_forschema;
CREATE TABLE testpub_tbl3 (a int);
CREATE TABLE testpub_tbl3a (b text) INHERITS (testpub_tbl3);
@@ -133,9 +166,11 @@ GRANT CREATE ON DATABASE regression TO regress_publication_user2;
SET ROLE regress_publication_user2;
SET client_min_messages = 'ERROR';
CREATE PUBLICATION testpub2; -- ok
+CREATE PUBLICATION testpub3; -- ok
RESET client_min_messages;
ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- fail
+ALTER PUBLICATION testpub3 ADD ALL TABLES IN SCHEMA pub_test; -- fail
SET ROLE regress_publication_user;
GRANT regress_publication_user TO regress_publication_user2;
@@ -143,12 +178,12 @@ SET ROLE regress_publication_user2;
ALTER PUBLICATION testpub2 ADD TABLE testpub_tbl1; -- ok
DROP PUBLICATION testpub2;
+DROP PUBLICATION testpub3;
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
@@ -169,11 +204,183 @@ ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2;
\dRp testpub_default
+-- adding schemas and tables
+CREATE SCHEMA pub_test1;
+CREATE SCHEMA pub_test2;
+CREATE SCHEMA pub_test3;
+CREATE TABLE pub_test1.tbl (id int, data text);
+CREATE TABLE pub_test1.tbl1 (id serial primary key, data text);
+CREATE TABLE pub_test2.tbl1 (id serial primary key, data text);
+
+-- suppress warning that depends on wal_level
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub1_forschema FOR ALL TABLES IN SCHEMA pub_test1;
+\dRp+ testpub1_forschema
+SELECT p.pubname FROM pg_catalog.pg_publication p, pg_catalog.pg_namespace n, pg_catalog.pg_publication_schema ps WHERE n.oid = ps.psnspcid AND p.oid = ps.pspubid AND n.nspname = 'pub_test1' ORDER BY 1;
+
+CREATE PUBLICATION testpub2_forschema FOR ALL TABLES IN SCHEMA pub_test1, pub_test2, pub_test3;
+\dRp+ testpub2_forschema
+SELECT p.pubname FROM pg_catalog.pg_publication p, pg_catalog.pg_namespace n, pg_catalog.pg_publication_schema ps WHERE n.oid = ps.psnspcid AND p.oid = ps.pspubid AND n.nspname = 'pub_test1' ORDER BY 1;
+SELECT p.pubname FROM pg_catalog.pg_publication p, pg_catalog.pg_namespace n, pg_catalog.pg_publication_schema ps WHERE n.oid = ps.psnspcid AND p.oid = ps.pspubid AND n.nspname = 'pub_test2' ORDER BY 1;
+SELECT p.pubname FROM pg_catalog.pg_publication p, pg_catalog.pg_namespace n, pg_catalog.pg_publication_schema ps WHERE n.oid = ps.psnspcid AND p.oid = ps.pspubid AND n.nspname = 'pub_test3' ORDER BY 1;
+
+-- check create publication on CURRENT_SCHEMA
+CREATE PUBLICATION testpub3_forschema FOR ALL TABLES IN SCHEMA CURRENT_SCHEMA;
+RESET client_min_messages;
+
+\dRp+ testpub3_forschema
+SELECT p.pubname FROM pg_catalog.pg_publication p, pg_catalog.pg_namespace n, pg_catalog.pg_publication_schema ps WHERE n.oid = ps.psnspcid AND p.oid = ps.pspubid AND n.nspname = 'public' ORDER BY 1;
+
+-- check create publication on CURRENT_SCHEMA where search_path is not set
+SET SEARCH_PATH='';
+CREATE PUBLICATION testpub_forschema FOR ALL TABLES IN SCHEMA CURRENT_SCHEMA;
+RESET SEARCH_PATH;
+
+-- check create publication on a schema that does not exist
+CREATE PUBLICATION testpub_forschema FOR ALL TABLES IN SCHEMA non_existent_schema;
+
+-- check create publication on a system schema
+CREATE PUBLICATION testpub_forschema FOR ALL TABLES IN SCHEMA pg_catalog;
+
+-- check create publication on an object which is not schema
+CREATE PUBLICATION testpub1_forschema1 FOR ALL TABLES IN SCHEMA testpub_view;
+
+-- dropping the schema should reflect the change in publication
+DROP SCHEMA pub_test3;
+\dRp+ testpub2_forschema
+
+-- renaming the schema should reflect the change in publication
+ALTER SCHEMA pub_test1 RENAME to pub_test1_renamed;
+\dRp+ testpub2_forschema
+
+ALTER SCHEMA pub_test1_renamed RENAME to pub_test1;
+\dRp+ testpub2_forschema
+
+-- alter publication add schema
+ALTER PUBLICATION testpub1_forschema ADD ALL TABLES IN SCHEMA pub_test2;
+\dRp+ testpub1_forschema
+
+-- alter publication add CURRENT_SCHEMA
+ALTER PUBLICATION testpub1_forschema ADD ALL TABLES IN SCHEMA CURRENT_SCHEMA;
+\dRp+ testpub1_forschema
+
+-- add non existent schema
+ALTER PUBLICATION testpub1_forschema ADD ALL TABLES IN SCHEMA non_existent_schema;
+\dRp+ testpub1_forschema
+
+-- add a schema which is already added to the publication
+ALTER PUBLICATION testpub1_forschema ADD ALL TABLES IN SCHEMA pub_test1;
+\dRp+ testpub1_forschema
+
+-- alter publication drop CURRENT_SCHEMA
+ALTER PUBLICATION testpub1_forschema DROP ALL TABLES IN SCHEMA CURRENT_SCHEMA;
+\dRp+ testpub1_forschema
+
+-- alter publication drop schema
+ALTER PUBLICATION testpub1_forschema DROP ALL TABLES IN SCHEMA pub_test2;
+\dRp+ testpub1_forschema
+
+-- drop schema that is not present in the publication
+ALTER PUBLICATION testpub1_forschema DROP ALL TABLES IN SCHEMA pub_test2;
+\dRp+ testpub1_forschema
+
+-- drop a schema that does not exist in the system
+ALTER PUBLICATION testpub1_forschema DROP ALL TABLES IN SCHEMA non_existent_schema;
+\dRp+ testpub1_forschema
+
+-- drop all schemas
+ALTER PUBLICATION testpub1_forschema DROP ALL TABLES IN SCHEMA pub_test1;
+\dRp+ testpub1_forschema
+
+-- alter publication set schema
+ALTER PUBLICATION testpub1_forschema SET ALL TABLES IN SCHEMA pub_test1;
+\dRp+ testpub1_forschema
+
+-- alter publication set multiple schema
+ALTER PUBLICATION testpub1_forschema SET ALL TABLES IN SCHEMA pub_test1, pub_test2;
+\dRp+ testpub1_forschema
+
+-- alter publication set non-existent schema
+ALTER PUBLICATION testpub1_forschema SET ALL TABLES IN SCHEMA non_existent_schema;
+\dRp+ testpub1_forschema
+
+-- alter publication set it with the same schema
+ALTER PUBLICATION testpub1_forschema SET ALL TABLES IN SCHEMA pub_test1, pub_test2;
+\dRp+ testpub1_forschema
+
+-- alter publication set it duplicate schemas should set the schemas after
+-- removing the duplicate schemas
+ALTER PUBLICATION testpub1_forschema SET ALL TABLES IN SCHEMA pub_test1, pub_test1;
+\dRp+ testpub1_forschema
+
+-- cleanup pub_test1 schema for invalidation tests
+ALTER PUBLICATION testpub2_forschema DROP ALL TABLES IN SCHEMA pub_test1;
+
+-- verify relation cache invalidations through update statement for the
+-- default REPLICA IDENTITY on the relation, if schema is part of the
+-- publication then update will fail because relation's relreplident
+-- option will be set, if schema is not part of the publication then update
+-- will be successful.
+INSERT INTO pub_test1.tbl VALUES(1, 'test');
+
+-- fail
+UPDATE pub_test1.tbl SET id = 2;
+ALTER PUBLICATION testpub1_forschema DROP ALL TABLES IN SCHEMA pub_test1;
+
+-- success
+UPDATE pub_test1.tbl SET id = 2;
+ALTER PUBLICATION testpub1_forschema SET ALL TABLES IN SCHEMA pub_test1;
+
+-- fail
+UPDATE pub_test1.tbl SET id = 2;
+ALTER PUBLICATION testpub1_forschema DROP ALL TABLES IN SCHEMA pub_test1;
+
+-- success
+UPDATE pub_test1.tbl SET id = 2;
+ALTER PUBLICATION testpub1_forschema ADD ALL TABLES IN SCHEMA pub_test1;
+
+-- fail
+UPDATE pub_test1.tbl SET id = 2;
+
+-- alter publication set it with CURRENT_SCHEMA
+ALTER PUBLICATION testpub1_forschema SET ALL TABLES IN SCHEMA CURRENT_SCHEMA;
+\dRp+ testpub1_forschema
+
+-- alter publication set all tables in schema on an empty publication.
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub4_forschema;
+RESET client_min_messages;
+\dRp+ testpub4_forschema
+ALTER PUBLICATION testpub4_forschema SET ALL TABLES IN SCHEMA pub_test1;
+\dRp+ testpub4_forschema
+
+-- create publication including both for table and for all tables in schema.
+SET client_min_messages = 'ERROR';
+CREATE PUBLICATION testpub_forschema_fortable FOR ALL TABLES IN SCHEMA pub_test1, TABLE pub_test2.tbl1;
+CREATE PUBLICATION testpub_fortable_forschema FOR TABLE pub_test2.tbl1, ALL TABLES IN SCHEMA pub_test1;
+RESET client_min_messages;
+
+\dRp+ testpub_forschema_fortable
+\dRp+ testpub_fortable_forschema
+
+-- fail specifying table without any of FOR ALL TABLES IN SCHEMA or FOR TABLE or FOR ALL TABLES
+CREATE PUBLICATION testpub_error FOR pub_test2.tbl1;
+
+DROP VIEW testpub_view;
+
DROP PUBLICATION testpub_default;
DROP PUBLICATION testpib_ins_trunct;
DROP PUBLICATION testpub_fortbl;
+DROP PUBLICATION testpub1_forschema;
+DROP PUBLICATION testpub2_forschema;
+DROP PUBLICATION testpub3_forschema;
+DROP PUBLICATION testpub4_forschema;
+DROP PUBLICATION testpub_forschema_fortable;
+DROP PUBLICATION testpub_fortable_forschema;
DROP SCHEMA pub_test CASCADE;
+DROP SCHEMA pub_test1 CASCADE;
+DROP SCHEMA pub_test2 CASCADE;
RESET SESSION AUTHORIZATION;
DROP ROLE regress_publication_user, regress_publication_user2;
diff --git a/src/test/subscription/t/025_rep_changes_for_schema.pl b/src/test/subscription/t/025_rep_changes_for_schema.pl
new file mode 100644
index 0000000000..a3e17f20f1
--- /dev/null
+++ b/src/test/subscription/t/025_rep_changes_for_schema.pl
@@ -0,0 +1,168 @@
+
+# Copyright (c) 2021, PostgreSQL Global Development Group
+
+# Basic logical replication test
+use strict;
+use warnings;
+use PostgresNode;
+use TestLib;
+use Test::More tests => 14;
+
+# Initialize publisher node
+my $node_publisher = PostgresNode->new('publisher');
+$node_publisher->init(allows_streaming => 'logical');
+$node_publisher->start;
+
+# Create subscriber node
+my $node_subscriber = PostgresNode->new('subscriber');
+$node_subscriber->init(allows_streaming => 'logical');
+$node_subscriber->start;
+
+# Test replication with publications created using FOR ALL TABLES IN SCHEMA
+# option.
+# Create schemas and tables on publisher
+$node_publisher->safe_psql('postgres', "CREATE SCHEMA sch1");
+$node_publisher->safe_psql('postgres', "CREATE SCHEMA sch2");
+$node_publisher->safe_psql('postgres', "CREATE SCHEMA sch3");
+$node_publisher->safe_psql('postgres', "CREATE TABLE sch1.tab1 AS SELECT generate_series(1,10) AS a");
+$node_publisher->safe_psql('postgres', "CREATE TABLE sch1.tab2 AS SELECT generate_series(1,10) AS a");
+$node_publisher->safe_psql('postgres', "CREATE TABLE sch2.tab1 AS SELECT generate_series(1,10) AS a");
+$node_publisher->safe_psql('postgres', "CREATE TABLE sch2.tab2 AS SELECT generate_series(1,10) AS a");
+
+# Create schemas and tables on subscriber
+$node_subscriber->safe_psql('postgres', "CREATE SCHEMA sch1");
+$node_subscriber->safe_psql('postgres', "CREATE SCHEMA sch2");
+$node_subscriber->safe_psql('postgres', "CREATE SCHEMA sch3");
+$node_subscriber->safe_psql('postgres', "CREATE TABLE sch1.tab1 (a int)");
+$node_subscriber->safe_psql('postgres', "CREATE TABLE sch1.tab2 (a int)");
+$node_subscriber->safe_psql('postgres', "CREATE TABLE sch2.tab1 (a int)");
+$node_subscriber->safe_psql('postgres', "CREATE TABLE sch2.tab2 (a int)");
+
+# Setup logical replication
+my $publisher_connstr = $node_publisher->connstr . ' dbname=postgres';
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION tap_pub_schema FOR ALL TABLES IN SCHEMA sch1,sch2");
+$node_subscriber->safe_psql('postgres',
+ "CREATE SUBSCRIPTION tap_sub_schema CONNECTION '$publisher_connstr' PUBLICATION tap_pub_schema"
+ );
+
+$node_publisher->wait_for_catchup('tap_sub_schema');
+
+# Also wait for initial table sync to finish
+my $synced_query =
+ "SELECT count(1) = 0 FROM pg_subscription_rel WHERE srsubstate NOT IN ('r', 's');";
+$node_subscriber->poll_query_until('postgres', $synced_query)
+ or die "Timed out while waiting for subscriber to synchronize data";
+
+# Check the schema table data is synced up.
+my $result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*), min(a), max(a) FROM sch1.tab1");
+is($result, qq(10|1|10), 'check rows on subscriber catchup');
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*), min(a), max(a) FROM sch1.tab2");
+is($result, qq(10|1|10), 'check rows on subscriber catchup');
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*), min(a), max(a) FROM sch2.tab1");
+is($result, qq(10|1|10), 'check rows on subscriber catchup');
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*), min(a), max(a) FROM sch2.tab2");
+is($result, qq(10|1|10), 'check rows on subscriber catchup');
+
+# Insert some data into few tables and verify that inserted data is replicated.
+$node_publisher->safe_psql('postgres', "INSERT INTO sch1.tab1 VALUES(generate_series(11,20))");
+$node_publisher->safe_psql('postgres', "INSERT INTO sch2.tab1 VALUES(generate_series(11,20))");
+
+$node_publisher->wait_for_catchup('tap_sub_schema');
+
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*), min(a), max(a) FROM sch1.tab1");
+is($result, qq(20|1|20), 'check rows on subscriber catchup');
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*), min(a), max(a) FROM sch2.tab1");
+is($result, qq(20|1|20), 'check rows on subscriber catchup');
+
+# Create new table in the publication schema, verify that subscriber does not get
+# the new table data in the subscriber before refresh.
+$node_publisher->safe_psql('postgres', "CREATE TABLE sch1.tab3 AS SELECT generate_series(1,10) AS a");
+$node_subscriber->safe_psql('postgres', "CREATE TABLE sch1.tab3(a int)");
+$node_publisher->wait_for_catchup('tap_sub_schema');
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM sch1.tab3");
+is($result, qq(0), 'check rows on subscriber catchup');
+
+# Table data should be reflected after refreshing the publication in
+# subscriber.
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION tap_sub_schema REFRESH PUBLICATION");
+
+# Also wait for initial table sync to finish
+$node_subscriber->poll_query_until('postgres', $synced_query)
+ or die "Timed out while waiting for subscriber to synchronize data";
+
+$node_publisher->safe_psql('postgres', "INSERT INTO sch1.tab3 VALUES(11)");
+$node_publisher->wait_for_catchup('tap_sub_schema');
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*), min(a), max(a) FROM sch1.tab3");
+is($result, qq(11|1|11), 'check rows on subscriber catchup');
+
+# Set the schema of a publication schema table to a non publication schema and
+# verify that inserted data is not reflected by the subscriber.
+$node_publisher->safe_psql('postgres', "ALTER TABLE sch1.tab3 SET SCHEMA sch3");
+$node_publisher->safe_psql('postgres', "INSERT INTO sch3.tab3 VALUES(11)");
+$node_publisher->wait_for_catchup('tap_sub_schema');
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*), min(a), max(a) FROM sch1.tab3");
+is($result, qq(11|1|11), 'check rows on subscriber catchup');
+
+# Verify that the subscription relation list is updated after refresh.
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM pg_subscription_rel WHERE srsubid IN (SELECT oid FROM pg_subscription WHERE subname = 'tap_sub_schema')");
+is($result, qq(5),
+ 'check subscription relation status is not yet dropped on subscriber');
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION tap_sub_schema REFRESH PUBLICATION");
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM pg_subscription_rel WHERE srsubid IN (SELECT oid FROM pg_subscription WHERE subname = 'tap_sub_schema')");
+is($result, qq(4),
+ 'check subscription relation status was dropped on subscriber');
+
+# Drop table from the publication schema, verify that subscriber removes the
+# table entry after refresh.
+$node_publisher->safe_psql('postgres', "DROP TABLE sch1.tab2");
+$node_publisher->wait_for_catchup('tap_sub_schema');
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM pg_subscription_rel WHERE srsubid IN (SELECT oid FROM pg_subscription WHERE subname = 'tap_sub_schema')");
+is($result, qq(4),
+ 'check subscription relation status is not yet dropped on subscriber');
+
+# Table should be removed from pg_subscription_rel after refreshing the
+# publication in subscriber.
+$node_subscriber->safe_psql('postgres',
+ "ALTER SUBSCRIPTION tap_sub_schema REFRESH PUBLICATION");
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*) FROM pg_subscription_rel WHERE srsubid IN (SELECT oid FROM pg_subscription WHERE subname = 'tap_sub_schema')");
+is($result, qq(3),
+ 'check subscription relation status was dropped on subscriber');
+
+# Drop schema from publication, verify that the inserts are not published after
+# dropping the schema from publication. Here 2nd insert should not be
+# published.
+$node_publisher->safe_psql('postgres', "INSERT INTO sch2.tab1 VALUES(21); ALTER PUBLICATION tap_pub_schema DROP ALL TABLES IN SCHEMA sch2; INSERT INTO sch2.tab1 values(22)");
+$node_publisher->wait_for_catchup('tap_sub_schema');
+$result = $node_subscriber->safe_psql('postgres',
+ "SELECT count(*), min(a), max(a) FROM sch2.tab1");
+is($result, qq(21|1|21), 'check rows on subscriber catchup');
+
+# Drop subscription as we don't need it anymore
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_schema");
+
+# Drop publication as we don't need it anymore
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_schema");
+
+# Clean up the schemas on both publisher and subscriber as we don't need them
+$node_publisher->safe_psql('postgres', "DROP SCHEMA sch1 cascade");
+$node_publisher->safe_psql('postgres', "DROP SCHEMA sch2 cascade");
+$node_publisher->safe_psql('postgres', "DROP SCHEMA sch3 cascade");
+$node_subscriber->safe_psql('postgres', "DROP SCHEMA sch1 cascade");
+$node_subscriber->safe_psql('postgres', "DROP SCHEMA sch2 cascade");
+$node_subscriber->safe_psql('postgres', "DROP SCHEMA sch3 cascade");
--
2.30.2