Thread: [HACKERS] PUBLICATIONS and pg_dump

Peter,

* Peter Eisentraut (peter_e@gmx.net) wrote:
> Logical replication
>
> - Add PUBLICATION catalogs and DDL
> - Add SUBSCRIPTION catalog and DDL
> - Define logical replication protocol and output plugin
> - Add logical replication workers

I think we need to have a bit more discussion regarding where
publications (and maybe subscriptions... not sure on that though) fit
when it comes to pg_dump.

In particular, I'm trying to clean up the pg_dump TAP tests and am
finding things I wouldn't have expected.  For example, publications
appear to be included in pretty much every pg_dump output, no matter if
a specific schema or even table was explicitly called for, or if that
publication or subscription was explicitly associated with that table.

The example I'm playing with is:

CREATE PUBLICATION pub2 WITH (PUBLISH INSERT, PUBLISH UPDATE, PUBLISH
DELETE);

and a simple:

pg_dump -n public -t t1

Will end up including the CREATE PUBLICATION command.

In fact, I'm not entirely sure how to have it not included in pg_dump's
output.

I understand that this is a bit complicated, but I would have thought
we'd do something similar to what is done for DEFAULT PRIVILEGES, where
we include the "global" default privileges when we are doing a dump of
"everything", but if we're dumping a specific schema then we only
include the default privileges directly associated with that schema.

Perhaps we need to include publications which are specific to a
particular table, but the current logic of, essentially, "always include
all publications" does not seem to make a lot of sense to me.

I'm happy to be corrected if I've grossly misunderstood something here,
of course.

Thanks!

Stephen

On 2/7/17 3:19 PM, Stephen Frost wrote:
> I understand that this is a bit complicated, but I would have thought
> we'd do something similar to what is done for DEFAULT PRIVILEGES, where
> we include the "global" default privileges when we are doing a dump of
> "everything", but if we're dumping a specific schema then we only
> include the default privileges directly associated with that schema.
> 
> Perhaps we need to include publications which are specific to a
> particular table, but the current logic of, essentially, "always include
> all publications" does not seem to make a lot of sense to me.

I think it would be sensible to refine it along those lines.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 08/02/17 05:02, Stephen Frost wrote:
> Peter,
> 
> On Tue, Feb 7, 2017 at 22:49 Peter Eisentraut
> <peter.eisentraut@2ndquadrant.com
> <mailto:peter.eisentraut@2ndquadrant.com>> wrote:
> 
>     On 2/7/17 3:19 PM, Stephen Frost wrote:
>     > I understand that this is a bit complicated, but I would have thought
>     > we'd do something similar to what is done for DEFAULT PRIVILEGES,
>     where
>     > we include the "global" default privileges when we are doing a dump of
>     > "everything", but if we're dumping a specific schema then we only
>     > include the default privileges directly associated with that schema.
>     >
>     > Perhaps we need to include publications which are specific to a
>     > particular table, but the current logic of, essentially, "always
>     include
>     > all publications" does not seem to make a lot of sense to me.
> 
>     I think it would be sensible to refine it along those lines.
> 
> 
> Great!  I've added it to the open items list for PG10. 
> 

Yeah that was oversight in initial patch, publications and their
membership was supposed to be dumped only when table filter is not used.
I mistakenly made it check for data_only instead of using the
selectDumpableObject machinery.

Fix attached.

-- 
  Petr Jelinek                  http://www.2ndQuadrant.com/
  PostgreSQL Development, 24x7 Support, Training & Services

-- 
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers

On 2/26/17 14:25, Petr Jelinek wrote:
> Yeah that was oversight in initial patch, publications and their
> membership was supposed to be dumped only when table filter is not used.
> I mistakenly made it check for data_only instead of using the
> selectDumpableObject machinery.

Committed.

-- 
Peter Eisentraut              http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services