On 2017/04/15 3:53, Peter Eisentraut wrote:
> On 4/13/17 06:48, Amit Langote wrote:
>> That is an important consideration because of pg_dump. See below:
>>
>> create table foo (a int);
>> create table bar () inherits (foo);
>> create publication mypub for table foo; -- bar is added too.
>>
>> $ pg_dump -s | psql -e test
>> <snip>
>> ALTER PUBLICATION mypub ADD TABLE foo;
>> ERROR: relation "bar" is already member of publication "mypub"
>
> To fix this, pg_dump should emit ADD TABLE ONLY foo.
Yeah, that's one way. Attached is a tiny patch for that.
By the way, I noticed that although grammar accepts ONLY and * against a
table name to affect whether descendant tables are included, the same is
not mentioned in the CREATE PUBLICATION and ALTER PUBLICATION reference
pages. I suspect it was probably not intentional, so attached a doc patch
for that too.
Thanks,
Amit
>From da6335c1727be7d2a12c225842baa64b9bc4a24a Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Mon, 17 Apr 2017 11:35:02 +0900
Subject: [PATCH 1/2] Make pg_dump emit ONLY before table added to publication
---
src/bin/pg_dump/pg_dump.c | 2 +-
src/bin/pg_dump/t/002_pg_dump.pl | 12 ++++++------
2 files changed, 7 insertions(+), 7 deletions(-)
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 3eccfa626b..22b5f784dc 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -3627,7 +3627,7 @@ dumpPublicationTable(Archive *fout, PublicationRelInfo *pubrinfo)
query = createPQExpBuffer();
- appendPQExpBuffer(query, "ALTER PUBLICATION %s ADD TABLE",
+ appendPQExpBuffer(query, "ALTER PUBLICATION %s ADD TABLE ONLY",
fmtId(pubrinfo->pubname));
appendPQExpBuffer(query, " %s;",
fmtId(tbinfo->dobj.name));
diff --git a/src/bin/pg_dump/t/002_pg_dump.pl b/src/bin/pg_dump/t/002_pg_dump.pl
index 4dd208e8e1..b2f4ab6baf 100644
--- a/src/bin/pg_dump/t/002_pg_dump.pl
+++ b/src/bin/pg_dump/t/002_pg_dump.pl
@@ -4417,13 +4417,13 @@ qr/CREATE TRANSFORM FOR integer LANGUAGE sql \(FROM SQL WITH FUNCTION pg_catalog
section_pre_data => 1,
test_schema_plus_blobs => 1, }, },
- 'ALTER PUBLICATION pub1 ADD TABLE test_table' => {
+ 'ALTER PUBLICATION pub1 ADD TABLE ONLY test_table' => {
all_runs => 1,
create_order => 51,
create_sql =>
- 'ALTER PUBLICATION pub1 ADD TABLE dump_test.test_table;',
+ 'ALTER PUBLICATION pub1 ADD TABLE ONLY dump_test.test_table;',
regexp => qr/^
- \QALTER PUBLICATION pub1 ADD TABLE test_table;\E
+ \QALTER PUBLICATION pub1 ADD TABLE ONLY test_table;\E
/xm,
like => {
binary_upgrade => 1,
@@ -4452,12 +4452,12 @@ qr/CREATE TRANSFORM FOR integer LANGUAGE sql \(FROM SQL WITH FUNCTION pg_catalog
pg_dumpall_globals => 1,
pg_dumpall_globals_clean => 1,
test_schema_plus_blobs => 1, }, },
- 'ALTER PUBLICATION pub1 ADD TABLE test_second_table' => {
+ 'ALTER PUBLICATION pub1 ADD TABLE ONLY test_second_table' => {
create_order => 52,
create_sql =>
- 'ALTER PUBLICATION pub1 ADD TABLE dump_test.test_second_table;',
+ 'ALTER PUBLICATION pub1 ADD TABLE ONLY dump_test.test_second_table;',
regexp => qr/^
- \QALTER PUBLICATION pub1 ADD TABLE test_second_table;\E
+ \QALTER PUBLICATION pub1 ADD TABLE ONLY test_second_table;\E
/xm,
like => {
binary_upgrade => 1,
--
2.11.0
>From d65be70d9a8515d8e7d6d0fe11362e9f4cdc8c2b Mon Sep 17 00:00:00 2001
From: amit <amitlangot...@gmail.com>
Date: Mon, 17 Apr 2017 11:45:45 +0900
Subject: [PATCH 2/2] Document that ONLY can be specified in publication
commands
---
doc/src/sgml/ref/alter_publication.sgml | 12 ++++++++----
doc/src/sgml/ref/create_publication.sgml | 9 +++++++--
2 files changed, 15 insertions(+), 6 deletions(-)
diff --git a/doc/src/sgml/ref/alter_publication.sgml b/doc/src/sgml/ref/alter_publication.sgml
index 0a965b3bbf..858231fbcb 100644
--- a/doc/src/sgml/ref/alter_publication.sgml
+++ b/doc/src/sgml/ref/alter_publication.sgml
@@ -31,9 +31,9 @@ ALTER PUBLICATION <replaceable class="PARAMETER">name</replaceable> WITH ( <repl
ALTER PUBLICATION <replaceable class="PARAMETER">name</replaceable> OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_USER | SESSION_USER }
ALTER PUBLICATION <replaceable class="PARAMETER">name</replaceable> RENAME TO <replaceable>new_name</replaceable>
-ALTER PUBLICATION <replaceable class="PARAMETER">name</replaceable> ADD TABLE <replaceable class="PARAMETER">table_name</replaceable> [, ...]
-ALTER PUBLICATION <replaceable class="PARAMETER">name</replaceable> SET TABLE <replaceable class="PARAMETER">table_name</replaceable> [, ...]
-ALTER PUBLICATION <replaceable class="PARAMETER">name</replaceable> DROP TABLE <replaceable class="PARAMETER">table_name</replaceable> [, ...]
+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> [ * ] [, ...]
</synopsis>
</refsynopsisdiv>
@@ -116,7 +116,11 @@ ALTER PUBLICATION <replaceable class="PARAMETER">name</replaceable> DROP TABLE <
<term><replaceable class="parameter">table_name</replaceable></term>
<listitem>
<para>
- Name of an existing table.
+ Name of an existing table. If <literal>ONLY</> is specified before the
+ table name, only that table is affected. If <literal>ONLY</> is not
+ specified, the table and all its descendant tables (if any) are
+ affected. Optionally, <literal>*</> can be specified after the table
+ name to explicitly indicate that descendant tables are included.
</para>
</listitem>
</varlistentry>
diff --git a/doc/src/sgml/ref/create_publication.sgml b/doc/src/sgml/ref/create_publication.sgml
index 3cdde801fa..0369b579c5 100644
--- a/doc/src/sgml/ref/create_publication.sgml
+++ b/doc/src/sgml/ref/create_publication.sgml
@@ -22,7 +22,7 @@ PostgreSQL documentation
<refsynopsisdiv>
<synopsis>
CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
- [ FOR TABLE <replaceable class="parameter">table_name</replaceable> [, ...]
+ [ FOR TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [, ...]
| FOR ALL TABLES ]
[ WITH ( <replaceable class="parameter">option</replaceable> [, ... ] ) ]
@@ -68,7 +68,12 @@ CREATE PUBLICATION <replaceable class="parameter">name</replaceable>
<term><literal>FOR TABLE</literal></term>
<listitem>
<para>
- Specifies a list of tables to add to the publication.
+ Specifies a list of tables to add to the publication. If
+ <literal>ONLY</> is specified before the table name, only
+ that table is added to the publication. If <literal>ONLY</> is not
+ specified, the table and all its descendant tables (if any) are added.
+ Optionally, <literal>*</> can be specified after the table name to
+ explicitly indicate that descendant tables are included.
</para>
</listitem>
</varlistentry>
--
2.11.0
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
