Skip to content

Commit 5a28324

Browse files
author
Amit Kapila
committed
Allow publishing the tables of schema.
A new option "FOR ALL TABLES IN SCHEMA" in Create/Alter Publication allows one or more schemas to be specified, whose tables are selected by the publisher for sending the data to the subscriber. The new syntax allows specifying both the tables and schemas. For example: CREATE PUBLICATION pub1 FOR TABLE t1,t2,t3, ALL TABLES IN SCHEMA s1,s2; OR ALTER PUBLICATION pub1 ADD TABLE t1,t2,t3, ALL TABLES IN SCHEMA s1,s2; A new system table "pg_publication_namespace" has been added, to maintain the schemas that the user wants to publish through the publication. Modified the output plugin (pgoutput) to publish the changes if the relation is part of schema publication. Updates pg_dump to identify and dump schema publications. Updates the \d family of commands to display schema publications and \dRp+ variant will now display associated schemas if any. Author: Vignesh C, Hou Zhijie, Amit Kapila Syntax-Suggested-by: Tom Lane, Alvaro Herrera Reviewed-by: Greg Nancarrow, Masahiko Sawada, Hou Zhijie, Amit Kapila, Haiying Tang, Ajin Cherian, Rahila Syed, Bharath Rupireddy, Mark Dilger Tested-by: Haiying Tang Discussion: https://www.postgresql.org/message-id/CALDaNm0OANxuJ6RXqwZsM1MSY4s19nuH3734j4a72etDwvBETQ@mail.gmail.com
1 parent f0b051e commit 5a28324

Some content is hidden

Large Commits have some content hidden by default. Use the searchbox below for content that may be hidden.

46 files changed

+2830
-212
lines changed

‎doc/src/sgml/catalogs.sgml

+69-3
Original file line numberDiff line numberDiff line change
@@ -240,6 +240,11 @@
240240
<entry>publications for logical replication</entry>
241241
</row>
242242

243+
<row>
244+
<entry><link linkend="catalog-pg-publication-namespace"><structname>pg_publication_namespace</structname></link></entry>
245+
<entry>schema to publication mapping</entry>
246+
</row>
247+
243248
<row>
244249
<entry><link linkend="catalog-pg-publication-rel"><structname>pg_publication_rel</structname></link></entry>
245250
<entry>relation to publication mapping</entry>
@@ -6176,6 +6181,67 @@ SCRAM-SHA-256$<replaceable>&lt;iteration count&gt;</replaceable>:<replaceable>&l
61766181
</table>
61776182
</sect1>
61786183

6184+
<sect1 id="catalog-pg-publication-namespace">
6185+
<title><structname>pg_publication_namespace</structname></title>
6186+
6187+
<indexterm zone="catalog-pg-publication-namespace">
6188+
<primary>pg_publication_namespace</primary>
6189+
</indexterm>
6190+
6191+
<para>
6192+
The catalog <structname>pg_publication_namespace</structname> contains the
6193+
mapping between schemas and publications in the database. This is a
6194+
many-to-many mapping.
6195+
</para>
6196+
6197+
<table>
6198+
<title><structname>pg_publication_namespace</structname> Columns</title>
6199+
<tgroup cols="1">
6200+
<thead>
6201+
<row>
6202+
<entry role="catalog_table_entry"><para role="column_definition">
6203+
Column Type
6204+
</para>
6205+
<para>
6206+
Description
6207+
</para></entry>
6208+
</row>
6209+
</thead>
6210+
6211+
<tbody>
6212+
<row>
6213+
<entry role="catalog_table_entry"><para role="column_definition">
6214+
<structfield>oid</structfield> <type>oid</type>
6215+
</para>
6216+
<para>
6217+
Row identifier
6218+
</para></entry>
6219+
</row>
6220+
6221+
<row>
6222+
<entry role="catalog_table_entry"><para role="column_definition">
6223+
<structfield>pnpubid</structfield> <type>oid</type>
6224+
(references <link linkend="catalog-pg-publication"><structname>pg_publication</structname></link>.<structfield>oid</structfield>)
6225+
</para>
6226+
<para>
6227+
Reference to publication
6228+
</para></entry>
6229+
</row>
6230+
6231+
<row>
6232+
<entry role="catalog_table_entry"><para role="column_definition">
6233+
<structfield>pnnspid</structfield> <type>oid</type>
6234+
(references <link linkend="catalog-pg-namespace"><structname>pg_namespace</structname></link>.<structfield>oid</structfield>)
6235+
</para>
6236+
<para>
6237+
Reference to schema
6238+
</para></entry>
6239+
</row>
6240+
</tbody>
6241+
</tgroup>
6242+
</table>
6243+
</sect1>
6244+
61796245
<sect1 id="catalog-pg-publication-rel">
61806246
<title><structname>pg_publication_rel</structname></title>
61816247

@@ -11278,9 +11344,9 @@ SELECT * FROM pg_locks pl LEFT JOIN pg_prepared_xacts ppx
1127811344
information about the mapping between publications and the tables they
1127911345
contain. Unlike the underlying catalog
1128011346
<link linkend="catalog-pg-publication-rel"><structname>pg_publication_rel</structname></link>,
11281-
this view expands
11282-
publications defined as <literal>FOR ALL TABLES</literal>, so for such
11283-
publications there will be a row for each eligible table.
11347+
this view expands publications defined as <literal>FOR ALL TABLES</literal>
11348+
and <literal>FOR ALL TABLES IN SCHEMA</literal>, so for such publications
11349+
there will be a row for each eligible table.
1128411350
</para>
1128511351

1128611352
<table>

‎doc/src/sgml/logical-replication.sgml

+8-6
Original file line numberDiff line numberDiff line change
@@ -108,9 +108,9 @@
108108
<para>
109109
Publications are different from schemas and do not affect how the table is
110110
accessed. Each table can be added to multiple publications if needed.
111-
Publications may currently only contain tables. Objects must be added
112-
explicitly, except when a publication is created for <literal>ALL
113-
TABLES</literal>.
111+
Publications may currently only contain tables and all tables in schema.
112+
Objects must be added explicitly, except when a publication is created for
113+
<literal>ALL TABLES</literal>.
114114
</para>
115115

116116
<para>
@@ -534,7 +534,8 @@
534534
and <literal>TRIGGER</literal> privilege on such tables to roles that
535535
superusers trust. Moreover, if untrusted users can create tables, use only
536536
publications that list tables explicitly. That is to say, create a
537-
subscription <literal>FOR ALL TABLES</literal> only when superusers trust
537+
subscription <literal>FOR ALL TABLES</literal> or
538+
<literal>FOR ALL TABLES IN SCHEMA</literal> only when superusers trust
538539
every user permitted to create a non-temp table on the publisher or the
539540
subscriber.
540541
</para>
@@ -564,8 +565,9 @@
564565

565566
<para>
566567
To add tables to a publication, the user must have ownership rights on the
567-
table. To create a publication that publishes all tables automatically,
568-
the user must be a superuser.
568+
table. To add all tables in schema to a publication, the user must be a
569+
superuser. To create a publication that publishes all tables or all tables in
570+
schema automatically, the user must be a superuser.
569571
</para>
570572

571573
<para>

‎doc/src/sgml/ref/alter_publication.sgml

+61-16
Original file line numberDiff line numberDiff line change
@@ -21,12 +21,17 @@ PostgreSQL documentation
2121

2222
<refsynopsisdiv>
2323
<synopsis>
24-
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> ADD TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ...]
25-
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ...]
26-
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> DROP TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ...]
24+
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> ADD <replaceable class="parameter">publication_object</replaceable> [, ...]
25+
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET <replaceable class="parameter">publication_object</replaceable> [, ...]
26+
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> DROP <replaceable class="parameter">publication_object</replaceable> [, ...]
2727
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )
2828
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
2929
ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
30+
31+
<phrase>where <replaceable class="parameter">publication_object</replaceable> is one of:</phrase>
32+
33+
TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ... ]
34+
ALL TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
3035
</synopsis>
3136
</refsynopsisdiv>
3237

@@ -39,14 +44,15 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
3944
</para>
4045

4146
<para>
42-
The first three variants change which tables are part of the publication.
43-
The <literal>SET TABLE</literal> clause will replace the list of tables in
44-
the publication with the specified one. The <literal>ADD TABLE</literal>
45-
and <literal>DROP TABLE</literal> clauses will add and remove one or more
46-
tables from the publication. Note that adding tables to a publication that
47-
is already subscribed to will require a <literal>ALTER SUBSCRIPTION
48-
... REFRESH PUBLICATION</literal> action on the subscribing side in order
49-
to become effective.
47+
The first three variants change which tables/schemas are part of the
48+
publication. The <literal>SET</literal> clause will replace the list of
49+
tables/schemas in the publication with the specified list; the existing
50+
tables/schemas that were present in the publication will be removed. The
51+
<literal>ADD</literal> and <literal>DROP</literal> clauses will add and
52+
remove one or more tables/schemas from the publication. Note that adding
53+
tables/schemas to a publication that is already subscribed to will require a
54+
<literal>ALTER SUBSCRIPTION ... REFRESH PUBLICATION</literal> action on the
55+
subscribing side in order to become effective.
5056
</para>
5157

5258
<para>
@@ -63,11 +69,22 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
6369
<para>
6470
You must own the publication to use <command>ALTER PUBLICATION</command>.
6571
Adding a table to a publication additionally requires owning that table.
66-
To alter the owner, you must also be a direct or indirect member of the new
67-
owning role. The new owner must have <literal>CREATE</literal> privilege on
68-
the database. Also, the new owner of a <literal>FOR ALL TABLES</literal>
69-
publication must be a superuser. However, a superuser can change the
70-
ownership of a publication regardless of these restrictions.
72+
The <literal>ADD ALL TABLES IN SCHEMA</literal> and
73+
<literal>SET ALL TABLES IN SCHEMA</literal> to a publication requires the
74+
invoking user to be a superuser. To alter the owner, you must also be a
75+
direct or indirect member of the new owning role. The new owner must have
76+
<literal>CREATE</literal> privilege on the database. Also, the new owner
77+
of a <literal>FOR ALL TABLES</literal> or <literal>FOR ALL TABLES IN
78+
SCHEMA</literal> publication must be a superuser. However, a superuser can
79+
change the ownership of a publication regardless of these restrictions.
80+
</para>
81+
82+
<para>
83+
Adding/Setting a table that is part of schema specified in
84+
<literal>ALL TABLES IN SCHEMA</literal>, adding/setting a schema to a
85+
publication that already has a table that is part of specified schema or
86+
adding/setting a table to a publication that already has a table's schema as
87+
part of the specified schema is not supported.
7188
</para>
7289
</refsect1>
7390

@@ -97,6 +114,15 @@ ALTER PUBLICATION <replaceable class="parameter">name</replaceable> RENAME TO <r
97114
</listitem>
98115
</varlistentry>
99116

117+
<varlistentry>
118+
<term><replaceable class="parameter">schema_name</replaceable></term>
119+
<listitem>
120+
<para>
121+
Name of an existing schema.
122+
</para>
123+
</listitem>
124+
</varlistentry>
125+
100126
<varlistentry>
101127
<term><literal>SET ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
102128
<listitem>
@@ -142,6 +168,25 @@ ALTER PUBLICATION noinsert SET (publish = 'update, delete');
142168
<programlisting>
143169
ALTER PUBLICATION mypublication ADD TABLE users, departments;
144170
</programlisting></para>
171+
172+
<para>
173+
Add schemas <structname>marketing</structname> and
174+
<structname>sales</structname> to the publication
175+
<structname>sales_publication</structname>:
176+
<programlisting>
177+
ALTER PUBLICATION sales_publication ADD ALL TABLES IN SCHEMA marketing, sales;
178+
</programlisting>
179+
</para>
180+
181+
<para>
182+
Add tables <structname>users</structname>,
183+
<structname>departments</structname> and schema
184+
<structname>production</structname> to the publication
185+
<structname>production_publication</structname>:
186+
<programlisting>
187+
ALTER PUBLICATION production_publication ADD TABLE users, departments, ALL TABLES IN SCHEMA production;
188+
</programlisting>
189+
</para>
145190
</refsect1>
146191

147192
<refsect1>

‎doc/src/sgml/ref/create_publication.sgml

+69-7
Original file line numberDiff line numberDiff line change
@@ -22,9 +22,14 @@ PostgreSQL documentation
2222
<refsynopsisdiv>
2323
<synopsis>
2424
CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
25-
[ FOR TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ...]
26-
| FOR ALL TABLES ]
25+
[ FOR ALL TABLES
26+
| FOR <replaceable class="parameter">publication_object</replaceable> [, ... ] ]
2727
[ WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] ) ]
28+
29+
<phrase>where <replaceable class="parameter">publication_object</replaceable> is one of:</phrase>
30+
31+
TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ... ]
32+
ALL TABLES IN SCHEMA { <replaceable class="parameter">schema_name</replaceable> | CURRENT_SCHEMA } [, ... ]
2833
</synopsis>
2934
</refsynopsisdiv>
3035

@@ -86,6 +91,11 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
8691
partition are also published via publications that its ancestors are
8792
part of.
8893
</para>
94+
95+
<para>
96+
Specifying a table that is part of a schema specified by
97+
<literal>FOR ALL TABLES IN SCHEMA</literal> is not supported.
98+
</para>
8999
</listitem>
90100
</varlistentry>
91101

@@ -99,6 +109,37 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
99109
</listitem>
100110
</varlistentry>
101111

112+
<varlistentry>
113+
<term><literal>FOR ALL TABLES IN SCHEMA</literal></term>
114+
<listitem>
115+
<para>
116+
Marks the publication as one that replicates changes for all tables in
117+
the specified list of schemas, including tables created in the future.
118+
</para>
119+
120+
<para>
121+
Specifying a schema along with a table which belongs to the specified
122+
schema using <literal>FOR TABLE</literal> is not supported.
123+
</para>
124+
125+
<para>
126+
Only persistent base tables and partitioned tables present in the schema
127+
will be included as part of the publication. Temporary tables, unlogged
128+
tables, foreign tables, materialized views, and regular views from the
129+
schema will not be part of the publication.
130+
</para>
131+
132+
<para>
133+
When a partitioned table is published via schema level publication, all
134+
of its existing and future partitions irrespective of it being from the
135+
publication schema or not are implicitly considered to be part of the
136+
publication. So, even operations that are performed directly on a
137+
partition are also published via publications that its ancestors are
138+
part of.
139+
</para>
140+
</listitem>
141+
</varlistentry>
142+
102143
<varlistentry>
103144
<term><literal>WITH ( <replaceable class="parameter">publication_parameter</replaceable> [= <replaceable class="parameter">value</replaceable>] [, ... ] )</literal></term>
104145
<listitem>
@@ -153,9 +194,10 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
153194
<title>Notes</title>
154195

155196
<para>
156-
If neither <literal>FOR TABLE</literal> nor <literal>FOR ALL
157-
TABLES</literal> is specified, then the publication starts out with an
158-
empty set of tables. That is useful if tables are to be added later.
197+
If <literal>FOR TABLE</literal>, <literal>FOR ALL TABLES</literal> or
198+
<literal>FOR ALL TABLES IN SCHEMA</literal> is not specified, then the
199+
publication starts out with an empty set of tables. That is useful if
200+
tables or schemas are to be added later.
159201
</para>
160202

161203
<para>
@@ -171,8 +213,9 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
171213

172214
<para>
173215
To add a table to a publication, the invoking user must have ownership
174-
rights on the table. The <command>FOR ALL TABLES</command> clause requires
175-
the invoking user to be a superuser.
216+
rights on the table. The <command>FOR ALL TABLES</command> and
217+
<command>FOR ALL TABLES IN SCHEMA</command> clauses require the invoking
218+
user to be a superuser.
176219
</para>
177220

178221
<para>
@@ -222,6 +265,25 @@ CREATE PUBLICATION alltables FOR ALL TABLES;
222265
<programlisting>
223266
CREATE PUBLICATION insert_only FOR TABLE mydata
224267
WITH (publish = 'insert');
268+
</programlisting>
269+
</para>
270+
271+
<para>
272+
Create a publication that publishes all changes for tables
273+
<structname>users</structname>, <structname>departments</structname> and
274+
all changes for all the tables present in the schema
275+
<structname>production</structname>:
276+
<programlisting>
277+
CREATE PUBLICATION production_publication FOR TABLE users, departments, ALL TABLES IN SCHEMA production;
278+
</programlisting>
279+
</para>
280+
281+
<para>
282+
Create a publication that publishes all changes for all the tables present in
283+
the schemas <structname>marketing</structname> and
284+
<structname>sales</structname>:
285+
<programlisting>
286+
CREATE PUBLICATION sales_publication FOR ALL TABLES IN SCHEMA marketing, sales;
225287
</programlisting></para>
226288
</refsect1>
227289

‎doc/src/sgml/ref/psql-ref.sgml

+2-2
Original file line numberDiff line numberDiff line change
@@ -1853,8 +1853,8 @@ testdb=&gt;
18531853
If <replaceable class="parameter">pattern</replaceable> is
18541854
specified, only those publications whose names match the pattern are
18551855
listed.
1856-
If <literal>+</literal> is appended to the command name, the tables
1857-
associated with each publication are shown as well.
1856+
If <literal>+</literal> is appended to the command name, the tables and
1857+
schemas associated with each publication are shown as well.
18581858
</para>
18591859
</listitem>
18601860
</varlistentry>

‎src/backend/catalog/Makefile

+2-2
Original file line numberDiff line numberDiff line change
@@ -68,8 +68,8 @@ CATALOG_HEADERS := \
6868
pg_foreign_table.h pg_policy.h pg_replication_origin.h \
6969
pg_default_acl.h pg_init_privs.h pg_seclabel.h pg_shseclabel.h \
7070
pg_collation.h pg_partitioned_table.h pg_range.h pg_transform.h \
71-
pg_sequence.h pg_publication.h pg_publication_rel.h pg_subscription.h \
72-
pg_subscription_rel.h
71+
pg_sequence.h pg_publication.h pg_publication_namespace.h \
72+
pg_publication_rel.h pg_subscription.h pg_subscription_rel.h
7373

7474
GENERATED_HEADERS := $(CATALOG_HEADERS:%.h=%_d.h) schemapg.h system_fk_info.h
7575

‎src/backend/catalog/aclchk.c

+2
Original file line numberDiff line numberDiff line change
@@ -3427,6 +3427,7 @@ aclcheck_error(AclResult aclerr, ObjectType objtype,
34273427
case OBJECT_DEFAULT:
34283428
case OBJECT_DEFACL:
34293429
case OBJECT_DOMCONSTRAINT:
3430+
case OBJECT_PUBLICATION_NAMESPACE:
34303431
case OBJECT_PUBLICATION_REL:
34313432
case OBJECT_ROLE:
34323433
case OBJECT_RULE:
@@ -3566,6 +3567,7 @@ aclcheck_error(AclResult aclerr, ObjectType objtype,
35663567
case OBJECT_DEFAULT:
35673568
case OBJECT_DEFACL:
35683569
case OBJECT_DOMCONSTRAINT:
3570+
case OBJECT_PUBLICATION_NAMESPACE:
35693571
case OBJECT_PUBLICATION_REL:
35703572
case OBJECT_ROLE:
35713573
case OBJECT_TRANSFORM:

0 commit comments

Comments
 (0)