To: pgsql-hackers@lists.postgresql.org
From: Gilles Darold <gilles.darold@dalibo.com>
Subject: [patch] Bug in pg_dump/pg_restore using --no-publication
Openpgp: preference=signencrypt
Autocrypt: addr=gilles.darold@dalibo.com; prefer-encrypt=mutual; keydata=
 xsBNBEyscTYBCADOHjxbECHbFaVEA8fKAh8gtdRMKCXPhYwgrhIq+435c824l0NkxxT6xAq/
 U2embiCGSBy13yVT016bzcxOnOqxgw4dJgXPE1P6RYLgjd17c5Xt7uzkbn4ChO1LOVtx+7y/
 uf8GV1fiPTtnilzGQvWdXMDDTNaFbyfIsebE6rB2p47Ns0m+cj46hS22pmEWELM4RilmQ2MX
 X7vNZPxQ/C6SKQDgeGZSHrvw9OLzFty8yY7HnExfUmCzfN4asAH3q/lCu1YQgud84bCC6hXz
 Cbw/DvR7pW8QK/ej/UkNzeGUWsWX6GYDF26P3wO9Zqk5T8JixrveiCaWA/e4DR5yr2bTABEB
 AAHNKEdpbGxlcyBEYXJvbGQgPGdpbGxlcy5kYXJvbGRAZGFsaWJvLmNvbT7CwHgEEwECACIF
 AkyscTYCGyMGCwkIBwMCBhUIAgkKCwQWAgMBAh4BAheAAAoJEHAqzJ89qW5hIlgH/0cWzoKx
 6Ir1K8L2uA5qDYbpxoryeMoCMSHHvK7TGPNrb1C1exHuGOQhb7bD3rkerohGy4ZR4EytjRA8
 oj6MttW96w9hMzQKQI7k5v17R53zU/1f7BTWLRVgt7lrTMSxryx/Z92PFfjw4yjcSoNy1iNG
 OPdTo2wGgJ1U8TcF7ny3f92O3BoNMwOkH1cYJnLhTrTLZVMAPpxjcNYEyR47b/2eIidBngMA
 cn87UnSpa0n8Z/5CirzP0B+ofopRz4PMYWKRPdvelTKB1iiJtvBWLcQkG2H3NZJxpvso91rY
 97Rwt/Nez2rsA8pZzGoL13Sf4LqscKXK0qVnhIGORW0qBMvOwE0ETKxxNgEIALVvukwz2p2V
 w37pnMk2CGWCgoj76Lnts/3sG42Jw6Ewjvjg7u9SGg2fJJ4dzywcoBW5ufJlZalghO3RpbZq
 DwT2432CUdGG500GLLzYrHIhGBw8h9SkoJqnfel3MOjGy/HZMymKmCy/uqx3ti3Tp0l0Is38
 C7GK/lG3NKnYUwQuFaM2pDkIoIx3hyogGXEMwV+IHtYpvFroCBViCgkYubsOEfqJti40BMXJ
 fKv74ecDk43I7x9V6Pzt0j0rfmj4vFbSKHF6ptJNeZBrXrNSeTmfAJXaPD/olDLEStWu6cr5
 XyMmYxYE9D2eMOQHhFb7UOZdh++HhWcPxMMyKNNB2YsAEQEAAcLAXwQYAQIACQUCTKxxNgIb
 DAAKCRBwKsyfPaluYZETB/91iP031oHNFMU62RC8x+bERW5x6Dpu7Kk9rOSKf7ChcSWElBQ2
 A7OnNAACgPNbJgYyuEquYHTdXLP43phNDJ93EhL+af5z2BLx1gkdV+1rotlzbzIaUv9uofRW
 U6UJ/emgL3kn1TRmf3PKWWgJkHRlePqKDeNVc53uApbXHoNrS4uluiwD88mx3+xgZqteCQO2
 DhEpAckzki82S4n4Ww6fRFB3KIGyafO7fzHFK8cFuT4za4YUFQN9Ix6WTABleoRLz3Cn+Jde
 049Zhg1222l0N07KWRS3ZZxAhwr3rv+pg3cJdbNf/rDszym92rig79SbGEEuL3nFCUXmA/QR IE+v
Message-ID: <3f48e812-b0fa-388e-2043-9a176bdee27e@dalibo.com>
Date: Fri, 21 Sep 2018 17:44:02 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:52.0) Gecko/20100101
 Thunderbird/52.9.1
MIME-Version: 1.0
Content-Type: multipart/mixed;
 boundary="------------153A4365CAFDA9E0F8AC5CB5"
Content-Language: en-US
Precedence: bulk

This is a multi-part message in MIME format.
--------------153A4365CAFDA9E0F8AC5CB5
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 7bit

Hi,


Attached is a patch that fixes a bug in pg_dump since 10.0 and
reproducible in master. When using option --no-publication : ALTER
PUBLICATION orders are still present in the dump.


Steps to reproduce:

postgres=# CREATE DATABASE test;
CREATE DATABASE
postgres=# \c test
You are now connected to database "test" as user "postgres".
test=# create table t1 (id integer);
CREATE TABLE
test=# CREATE PUBLICATION p1 FOR TABLE t1;
CREATE PUBLICATION
test=# CREATE PUBLICATION p_all FOR ALL TABLES;
CREATE PUBLICATION
test=# CREATE PUBLICATION p_insert_only FOR TABLE t1 WITH (publish =
'insert');
CREATE PUBLICATION
test=# \q

$ pg_dump -p 5400 --no-publication test | grep PUBLICATION
-- Name: p1 t1; Type: PUBLICATION TABLE; Schema: public; Owner:
ALTER PUBLICATION p1 ADD TABLE ONLY public.t1;
-- Name: p_insert_only t1; Type: PUBLICATION TABLE; Schema: public; Owner:
ALTER PUBLICATION p_insert_only ADD TABLE ONLY public.t1;


pg_restore suffers the same problem:

pg_restore --no-publication test.dump | grep PUBLICATION
-- Name: p1 t1; Type: PUBLICATION TABLE; Schema: public; Owner:
ALTER PUBLICATION p1 ADD TABLE ONLY public.t1;
-- Name: p_insert_only t1; Type: PUBLICATION TABLE; Schema: public; Owner:
ALTER PUBLICATION p_insert_only ADD TABLE ONLY public.t1;

pg_restore --no-publication test.dump -l test.dump| grep PUBLICATION
2230; 6106 16389 PUBLICATION TABLE public p1 t1
2231; 6106 16392 PUBLICATION TABLE public p_insert_only t1


Should I add it to current commitfest ?


-- 
Gilles Darold
Consultant PostgreSQL
http://dalibo.com - http://dalibo.org


--------------153A4365CAFDA9E0F8AC5CB5
Content-Type: text/x-patch;
 name="patch_pg_dump_option_no_publication-v1.diff"
Content-Transfer-Encoding: 7bit
Content-Disposition: attachment;
 filename="patch_pg_dump_option_no_publication-v1.diff"

diff --git a/src/bin/pg_dump/pg_backup_archiver.c b/src/bin/pg_dump/pg_backup_archiver.c
index 7d1d439ba2..2e86664c17 100644
--- a/src/bin/pg_dump/pg_backup_archiver.c
+++ b/src/bin/pg_dump/pg_backup_archiver.c
@@ -2920,7 +2920,8 @@ _tocEntryRequired(TocEntry *te, teSection curSection, ArchiveHandle *AH)
 		return 0;
 
 	/* If it's a publication, maybe ignore it */
-	if (ropt->no_publications && strcmp(te->desc, "PUBLICATION") == 0)
+	if (ropt->no_publications && (strcmp(te->desc, "PUBLICATION") == 0
+						|| strcmp(te->desc, "PUBLICATION TABLE") == 0))
 		return 0;
 
 	/* If it's a security label, maybe ignore it */
diff --git a/src/bin/pg_dump/pg_dump.c b/src/bin/pg_dump/pg_dump.c
index 0687a81914..c8d01ed4a4 100644
--- a/src/bin/pg_dump/pg_dump.c
+++ b/src/bin/pg_dump/pg_dump.c
@@ -3907,6 +3907,7 @@ getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables)
 	PQExpBuffer query;
 	PGresult   *res;
 	PublicationRelInfo *pubrinfo;
+	DumpOptions *dopt = fout->dopt;
 	int			i_tableoid;
 	int			i_oid;
 	int			i_pubname;
@@ -3914,7 +3915,7 @@ getPublicationTables(Archive *fout, TableInfo tblinfo[], int numTables)
 				j,
 				ntups;
 
-	if (fout->remoteVersion < 100000)
+	if (dopt->no_publications || fout->remoteVersion < 100000)
 		return;
 
 	query = createPQExpBuffer();

--------------153A4365CAFDA9E0F8AC5CB5--