v6-0002-Tests-and-documentation-for-schema-level-support-.patch
text/x-patch
Filename: v6-0002-Tests-and-documentation-for-schema-level-support-.patch
Type: text/x-patch
Part: 1
Patch
Format: format-patch
Series: patch v6-0002
Subject: Tests and documentation for schema level support for publication.
| File | + | − |
|---|---|---|
| doc/src/sgml/catalogs.sgml | 91 | 3 |
| doc/src/sgml/ref/alter_publication.sgml | 44 | 1 |
| doc/src/sgml/ref/create_publication.sgml | 37 | 8 |
| src/test/regress/expected/object_address.out | 5 | 1 |
| src/test/regress/expected/publication.out | 276 | 1 |
| src/test/regress/sql/object_address.sql | 3 | 0 |
| src/test/regress/sql/publication.sql | 111 | 1 |
| src/test/subscription/t/001_rep_changes.pl | 140 | 1 |
From e5a31ee9e339bf062e999c76450787b48703f573 Mon Sep 17 00:00:00 2001
From: vignesh <vignesh21@gmail.com>
Date: Mon, 14 Jun 2021 11:15:39 +0530
Subject: [PATCH v6 2/4] Tests and documentation for schema level support for
publication.
Tests and documentation for schema level support for publication.
---
doc/src/sgml/catalogs.sgml | 94 ++++++-
doc/src/sgml/ref/alter_publication.sgml | 45 ++-
doc/src/sgml/ref/create_publication.sgml | 45 ++-
src/test/regress/expected/object_address.out | 6 +-
src/test/regress/expected/publication.out | 277 ++++++++++++++++++-
src/test/regress/sql/object_address.sql | 3 +
src/test/regress/sql/publication.sql | 112 +++++++-
src/test/subscription/t/001_rep_changes.pl | 141 +++++++++-
8 files changed, 707 insertions(+), 16 deletions(-)
diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index f517a7d4af..b87d84a2e1 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>
@@ -6171,6 +6176,28 @@ SCRAM-SHA-256$<replaceable><iteration count></replaceable>:<replaceable>&l
publication instead of its own.
</para></entry>
</row>
+
+ <row>
+ <entry role="catalog_table_entry"><para role="column_definition">
+ <structfield>pubtype</structfield> <type>char</type>
+ </para>
+ <para>
+ Publication type:
+ <literal>a</literal> = <literal>FOR ALL TABLES</literal> publication type,
+ <literal>t</literal> = <literal>FOR TABLE</literal> publication type,
+ <literal>s</literal> = <literal>FOR SCHEMA</literal> publication type,
+ <literal>e</literal> = Empty publication type.
+ If a publication is created without specifying any of
+ <literal>FOR ALL TABLES</literal>, <literal>FOR TABLE</literal> and
+ <literal>FOR SCHEMA</literal> option, then the publication will be
+ created as an empty publication type. When a table or schema is added to
+ the publication using <link linkend="sql-altersubscription">
+ <command>ALTER PUBLICATION</command></link> then the publication type
+ will be changed to <literal>t</literal> or <literal>s</literal>
+ respctively. The publication type cannot be changed in other cases.
+ </para></entry>
+ </row>
+
</tbody>
</tgroup>
</table>
@@ -6238,6 +6265,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>
@@ -11274,9 +11362,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 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..532ca2ff62 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 SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ...]
+ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ...]
+ALTER PUBLICATION <replaceable class="parameter">name</replaceable> DROP 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 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.
@@ -97,6 +111,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 +164,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 SCHEMA marketing_june, sales_june;
+</programlisting>
+ </para>
+
+ <para>
+ Drop some schema from the publication:
+<programlisting>
+ALTER PUBLICATION production_quarterly_publication DROP SCHEMA production_july;
+</programlisting>
+ </para>
+
+ <para>
+ Set schema to the publication:
+<programlisting>
+ALTER PUBLICATION production_publication SET 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..60fea8debe 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 TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ... ]
+ | FOR SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
+ | FOR ALL TABLES
[ WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
</synopsis>
</refsynopsisdiv>
@@ -99,6 +100,17 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
</listitem>
</varlistentry>
+ <varlistentry>
+ <term><literal>FOR 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. If
+ schema is not specified, publication will be created for CURRENT_SCHEMA.
+ </para>
+ </listitem>
+ </varlistentry>
+
<varlistentry>
<term><literal>WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
<listitem>
@@ -153,9 +165,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 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 +183,9 @@ 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>
+ clause requires the invoking user to be a superuser.
</para>
<para>
@@ -222,6 +235,22 @@ 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
+production schema:
+<programlisting>
+CREATE PUBLICATION production_publication FOR SCHEMA production;
+</programlisting>
+ </para>
+
+ <para>
+ Create a publication that publishes all changes for all the tables present in
+marketing and sales schemas:
+<programlisting>
+CREATE PUBLICATION sales_publication FOR SCHEMA marketing, sales;
</programlisting></para>
</refsect1>
diff --git a/src/test/regress/expected/object_address.out b/src/test/regress/expected/object_address.out
index 388097a695..49ea22f427 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 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 28bf8daa64..bed28ef8a7 100644
--- a/src/test/regress/expected/publication.out
+++ b/src/test/regress/expected/publication.out
@@ -255,7 +255,6 @@ 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
@@ -287,11 +286,287 @@ ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2;
testpub_default | regress_publication_user2 | f | t | t | t | f | f | t
(1 row)
+-- CREATE publication with schema
+CREATE SCHEMA pub_test1;
+CREATE SCHEMA pub_test2;
+CREATE SCHEMA pub_test3;
+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 SCHEMA pub_test1;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f | t | t | t | t | f | s
+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 SCHEMA pub_test1, pub_test2, pub_test3;
+\dRp+ testpub2_forschema
+ Publication testpub2_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f | t | t | t | t | f | s
+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 SCHEMA CURRENT_SCHEMA;
+RESET client_min_messages;
+\dRp+ testpub3_forschema
+ Publication testpub3_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f | t | t | t | t | f | s
+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 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 SCHEMA non_existent_schema;
+ERROR: schema "non_existent_schema" does not exist
+--- Check create publication on a object which is not schema
+CREATE PUBLICATION testpub1_forschema1 FOR 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 | Pubtype
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f | t | t | t | t | f | s
+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 | Pubtype
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f | t | t | t | t | f | s
+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 | Pubtype
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f | t | t | t | t | f | s
+Schemas:
+ "pub_test1"
+ "pub_test2"
+
+-- Alter publication add schema
+ALTER PUBLICATION testpub1_forschema ADD SCHEMA pub_test2;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f | t | t | t | t | f | s
+Schemas:
+ "pub_test1"
+ "pub_test2"
+
+-- Alter publication add CURRENT_SCHEMA
+ALTER PUBLICATION testpub1_forschema ADD SCHEMA CURRENT_SCHEMA;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f | t | t | t | t | f | s
+Schemas:
+ "pub_test1"
+ "pub_test2"
+ "public"
+
+-- Add non existent schema
+ALTER PUBLICATION testpub1_forschema ADD 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 | Pubtype
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f | t | t | t | t | f | s
+Schemas:
+ "pub_test1"
+ "pub_test2"
+ "public"
+
+-- Add a schema which is already added to the publication
+ALTER PUBLICATION testpub1_forschema ADD 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 | Pubtype
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f | t | t | t | t | f | s
+Schemas:
+ "pub_test1"
+ "pub_test2"
+ "public"
+
+-- Alter publication drop CURRENT_SCHEMA
+ALTER PUBLICATION testpub1_forschema DROP SCHEMA CURRENT_SCHEMA;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f | t | t | t | t | f | s
+Schemas:
+ "pub_test1"
+ "pub_test2"
+
+-- Alter publication drop schema
+ALTER PUBLICATION testpub1_forschema DROP SCHEMA pub_test2;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f | t | t | t | t | f | s
+Schemas:
+ "pub_test1"
+
+-- Drop schema that is not preset in the publication
+ALTER PUBLICATION testpub1_forschema DROP 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 | Pubtype
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f | t | t | t | t | f | s
+Schemas:
+ "pub_test1"
+
+-- Drop a schema that does not exist in the system
+ALTER PUBLICATION testpub1_forschema DROP 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 | Pubtype
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f | t | t | t | t | f | s
+Schemas:
+ "pub_test1"
+
+-- Drop all schemas
+ALTER PUBLICATION testpub1_forschema DROP SCHEMA pub_test1;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f | t | t | t | t | f | s
+(1 row)
+
+-- Alter publication set schema
+ALTER PUBLICATION testpub1_forschema SET SCHEMA pub_test1;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f | t | t | t | t | f | s
+Schemas:
+ "pub_test1"
+
+-- Alter publication set multiple schema
+ALTER PUBLICATION testpub1_forschema SET SCHEMA pub_test1, pub_test2;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f | t | t | t | t | f | s
+Schemas:
+ "pub_test1"
+ "pub_test2"
+
+-- Alter publication set non-existent schema
+ALTER PUBLICATION testpub1_forschema SET 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 | Pubtype
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f | t | t | t | t | f | s
+Schemas:
+ "pub_test1"
+ "pub_test2"
+
+-- Alter publication set it with the same schema
+ALTER PUBLICATION testpub1_forschema SET SCHEMA pub_test1, pub_test2;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f | t | t | t | t | f | s
+Schemas:
+ "pub_test1"
+ "pub_test2"
+
+-- Alter publication set it with CURRENT_SCHEMA
+ALTER PUBLICATION testpub1_forschema SET SCHEMA CURRENT_SCHEMA;
+\dRp+ testpub1_forschema
+ Publication testpub1_forschema
+ Owner | All tables | Inserts | Updates | Deletes | Truncates | Via root | Pubtype
+--------------------------+------------+---------+---------+---------+-----------+----------+---------
+ regress_publication_user | f | t | t | t | t | f | s
+Schemas:
+ "public"
+
+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 SCHEMA pub_test CASCADE;
NOTICE: drop cascades to table pub_test.testpub_nopk
+DROP SCHEMA pub_test1 CASCADE;
+NOTICE: 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..56d9b852fd 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 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..d55a6f42b3 100644
--- a/src/test/regress/sql/publication.sql
+++ b/src/test/regress/sql/publication.sql
@@ -148,7 +148,6 @@ 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 +168,122 @@ ALTER PUBLICATION testpub_default OWNER TO regress_publication_user2;
\dRp testpub_default
+-- CREATE publication with schema
+CREATE SCHEMA pub_test1;
+CREATE SCHEMA pub_test2;
+CREATE SCHEMA pub_test3;
+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 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 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 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 SCHEMA CURRENT_SCHEMA;
+RESET SEARCH_PATH;
+
+--- Check create publication on a schema that does not exist
+CREATE PUBLICATION testpub_forschema FOR SCHEMA non_existent_schema;
+
+--- Check create publication on a object which is not schema
+CREATE PUBLICATION testpub1_forschema1 FOR 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 SCHEMA pub_test2;
+\dRp+ testpub1_forschema
+
+-- Alter publication add CURRENT_SCHEMA
+ALTER PUBLICATION testpub1_forschema ADD SCHEMA CURRENT_SCHEMA;
+\dRp+ testpub1_forschema
+
+-- Add non existent schema
+ALTER PUBLICATION testpub1_forschema ADD SCHEMA non_existent_schema;
+\dRp+ testpub1_forschema
+
+-- Add a schema which is already added to the publication
+ALTER PUBLICATION testpub1_forschema ADD SCHEMA pub_test1;
+\dRp+ testpub1_forschema
+
+-- Alter publication drop CURRENT_SCHEMA
+ALTER PUBLICATION testpub1_forschema DROP SCHEMA CURRENT_SCHEMA;
+\dRp+ testpub1_forschema
+
+-- Alter publication drop schema
+ALTER PUBLICATION testpub1_forschema DROP SCHEMA pub_test2;
+\dRp+ testpub1_forschema
+
+-- Drop schema that is not preset in the publication
+ALTER PUBLICATION testpub1_forschema DROP SCHEMA pub_test2;
+\dRp+ testpub1_forschema
+
+-- Drop a schema that does not exist in the system
+ALTER PUBLICATION testpub1_forschema DROP SCHEMA non_existent_schema;
+\dRp+ testpub1_forschema
+
+-- Drop all schemas
+ALTER PUBLICATION testpub1_forschema DROP SCHEMA pub_test1;
+\dRp+ testpub1_forschema
+
+-- Alter publication set schema
+ALTER PUBLICATION testpub1_forschema SET SCHEMA pub_test1;
+\dRp+ testpub1_forschema
+
+-- Alter publication set multiple schema
+ALTER PUBLICATION testpub1_forschema SET SCHEMA pub_test1, pub_test2;
+\dRp+ testpub1_forschema
+
+-- Alter publication set non-existent schema
+ALTER PUBLICATION testpub1_forschema SET SCHEMA non_existent_schema;
+\dRp+ testpub1_forschema
+
+-- Alter publication set it with the same schema
+ALTER PUBLICATION testpub1_forschema SET SCHEMA pub_test1, pub_test2;
+\dRp+ testpub1_forschema
+
+-- Alter publication set it with CURRENT_SCHEMA
+ALTER PUBLICATION testpub1_forschema SET SCHEMA CURRENT_SCHEMA;
+\dRp+ testpub1_forschema
+
+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 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/001_rep_changes.pl b/src/test/subscription/t/001_rep_changes.pl
index ecfba0af04..36aa4393b7 100644
--- a/src/test/subscription/t/001_rep_changes.pl
+++ b/src/test/subscription/t/001_rep_changes.pl
@@ -6,7 +6,7 @@ use strict;
use warnings;
use PostgresNode;
use TestLib;
-use Test::More tests => 31;
+use Test::More tests => 44;
# Initialize publisher node
my $node_publisher = get_new_node('publisher');
@@ -253,6 +253,145 @@ $node_publisher->safe_psql('postgres', "DROP TABLE temp2");
$node_subscriber->safe_psql('postgres', "DROP TABLE temp1");
$node_subscriber->safe_psql('postgres', "DROP TABLE temp2");
+# Test replication with publications created using FOR 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 for schema sch1 and sch2 that will only be used for
+# this test
+$node_publisher->safe_psql('postgres',
+ "CREATE PUBLICATION tap_pub_schema FOR 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
+$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.
+$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 shsould 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 was 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 subscription as we don't need it anymore
+$node_subscriber->safe_psql('postgres', "DROP SUBSCRIPTION tap_sub_schema");
+
+# Drop publications as we don't need them anymore
+$node_publisher->safe_psql('postgres', "DROP PUBLICATION tap_pub_schema");
+
+# Clean up the tables 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");
+
# add REPLICA IDENTITY FULL so we can update
$node_publisher->safe_psql('postgres',
"ALTER TABLE tab_full REPLICA IDENTITY FULL");
--
2.25.1