Re: [COMMITTERS] pgsql: Logical replication

Logical replication

- Add PUBLICATION catalogs and DDL
- Add SUBSCRIPTION catalog and DDL
- Define logical replication protocol and output plugin
- Add logical replication workers

From: Petr Jelinek <petr(at)2ndquadrant(dot)com>
Reviewed-by: Steve Singer <steve(at)ssinger(dot)info>
Reviewed-by: Andres Freund <andres(at)anarazel(dot)de>
Reviewed-by: Erik Rijkers <er(at)xs4all(dot)nl>
Reviewed-by: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>

Branch
------
master

Details
-------
http://git.postgresql.org/pg/commitdiff/665d1fad99e7b11678b0d5fa24d2898424243cd6

Modified Files
--------------
doc/src/sgml/catalogs.sgml | 309 +++++
doc/src/sgml/config.sgml | 41 +
doc/src/sgml/filelist.sgml | 1 +
doc/src/sgml/func.sgml | 2 +-
doc/src/sgml/logical-replication.sgml | 396 ++++++
doc/src/sgml/monitoring.sgml | 74 +
doc/src/sgml/postgres.sgml | 1 +
doc/src/sgml/protocol.sgml | 721 ++++++++++
doc/src/sgml/ref/allfiles.sgml | 6 +
doc/src/sgml/ref/alter_publication.sgml | 139 ++
doc/src/sgml/ref/alter_subscription.sgml | 139 ++
doc/src/sgml/ref/create_publication.sgml | 206 +++
doc/src/sgml/ref/create_subscription.sgml | 176 +++
doc/src/sgml/ref/drop_publication.sgml | 107 ++
doc/src/sgml/ref/drop_subscription.sgml | 110 ++
doc/src/sgml/ref/pg_dump.sgml | 21 +
doc/src/sgml/ref/psql-ref.sgml | 28 +
doc/src/sgml/reference.sgml | 6 +
src/Makefile | 1 +
src/backend/access/transam/xact.c | 2 +
src/backend/catalog/Makefile | 7 +-
src/backend/catalog/aclchk.c | 57 +
src/backend/catalog/catalog.c | 8 +-
src/backend/catalog/dependency.c | 24 +
src/backend/catalog/objectaddress.c | 219 +++
src/backend/catalog/pg_publication.c | 457 +++++++
src/backend/catalog/pg_shdepend.c | 11 +
src/backend/catalog/pg_subscription.c | 207 +++
src/backend/catalog/system_views.sql | 25 +
src/backend/commands/Makefile | 8 +-
src/backend/commands/alter.c | 10 +
src/backend/commands/dbcommands.c | 17 +
src/backend/commands/define.c | 28 +
src/backend/commands/dropcmds.c | 4 +
src/backend/commands/event_trigger.c | 8 +
src/backend/commands/publicationcmds.c | 754 +++++++++++
src/backend/commands/subscriptioncmds.c | 643 +++++++++
src/backend/commands/tablecmds.c | 12 +
src/backend/executor/Makefile | 2 +-
src/backend/executor/execMain.c | 3 +-
src/backend/executor/execReplication.c | 553 ++++++++
src/backend/nodes/copyfuncs.c | 78 ++
src/backend/nodes/equalfuncs.c | 73 +
src/backend/parser/gram.y | 262 +++-
src/backend/postmaster/bgworker.c | 23 +-
src/backend/postmaster/pgstat.c | 6 +
src/backend/postmaster/postmaster.c | 9 +
.../libpqwalreceiver/libpqwalreceiver.c | 217 ++-
src/backend/replication/logical/Makefile | 4 +-
src/backend/replication/logical/launcher.c | 759 +++++++++++
src/backend/replication/logical/proto.c | 637 +++++++++
src/backend/replication/logical/relation.c | 489 +++++++
src/backend/replication/logical/worker.c | 1429 ++++++++++++++++++++
src/backend/replication/pgoutput/Makefile | 32 +
src/backend/replication/pgoutput/pgoutput.c | 596 ++++++++
src/backend/replication/walreceiver.c | 20 +-
src/backend/storage/ipc/ipci.c | 3 +
src/backend/storage/lmgr/lwlocknames.txt | 2 +
src/backend/tcop/utility.c | 83 ++
src/backend/utils/cache/inval.c | 35 +-
src/backend/utils/cache/relcache.c | 136 +-
src/backend/utils/cache/syscache.c | 69 +
src/backend/utils/misc/guc.c | 13 +
src/backend/utils/misc/postgresql.conf.sample | 1 +
src/bin/pg_dump/common.c | 12 +
src/bin/pg_dump/pg_backup.h | 3 +
src/bin/pg_dump/pg_backup_archiver.c | 7 +-
src/bin/pg_dump/pg_dump.c | 464 +++++++
src/bin/pg_dump/pg_dump.h | 46 +-
src/bin/pg_dump/pg_dump_sort.c | 20 +-
src/bin/pg_dump/pg_restore.c | 3 +
src/bin/pg_dump/t/002_pg_dump.pl | 76 +-
src/bin/psql/command.c | 16 +
src/bin/psql/describe.c | 292 ++++
src/bin/psql/describe.h | 9 +
src/bin/psql/help.c | 2 +
src/bin/psql/tab-complete.c | 53 +-
src/include/catalog/dependency.h | 3 +
src/include/catalog/indexing.h | 18 +
src/include/catalog/pg_proc.h | 6 +
src/include/catalog/pg_publication.h | 104 ++
src/include/catalog/pg_publication_rel.h | 52 +
src/include/catalog/pg_subscription.h | 83 ++
src/include/commands/defrem.h | 1 +
src/include/commands/publicationcmds.h | 28 +
src/include/commands/subscriptioncmds.h | 27 +
src/include/executor/executor.h | 17 +
src/include/nodes/nodes.h | 5 +
src/include/nodes/parsenodes.h | 51 +
src/include/parser/kwlist.h | 3 +
src/include/pgstat.h | 4 +-
src/include/replication/logicallauncher.h | 27 +
src/include/replication/logicalproto.h | 104 ++
src/include/replication/logicalrelation.h | 43 +
src/include/replication/logicalworker.h | 17 +
src/include/replication/pgoutput.h | 29 +
src/include/replication/walreceiver.h | 60 +-
src/include/replication/worker_internal.h | 62 +
src/include/storage/sinval.h | 3 +-
src/include/utils/acl.h | 4 +
src/include/utils/inval.h | 2 +
src/include/utils/rel.h | 6 +
src/include/utils/relcache.h | 6 +
src/include/utils/syscache.h | 6 +
src/test/Makefile | 2 +-
src/test/perl/PostgresNode.pm | 13 +-
src/test/regress/expected/publication.out | 156 +++
src/test/regress/expected/rules.out | 18 +
src/test/regress/expected/sanity_check.out | 3 +
src/test/regress/expected/subscription.out | 66 +
src/test/regress/parallel_schedule | 3 +
src/test/regress/serial_schedule | 2 +
src/test/regress/sql/publication.sql | 82 ++
src/test/regress/sql/subscription.sql | 44 +
src/test/subscription/.gitignore | 2 +
src/test/subscription/Makefile | 22 +
src/test/subscription/README | 16 +
src/test/subscription/t/001_rep_changes.pl | 188 +++
src/test/subscription/t/002_types.pl | 539 ++++++++
119 files changed, 13354 insertions(+), 95 deletions(-)

* Peter Eisentraut (peter_e(at)gmx(dot)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

The buildfarm is rather upset with this.

Looks like an issue in pg_upgrade / pg_dump.

Thanks!

Stephen

On Fri, Jan 20, 2017 at 7:36 PM, Peter Eisentraut <peter_e(at)gmx(dot)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
>
> From: Petr Jelinek <petr(at)2ndquadrant(dot)com>
> Reviewed-by: Steve Singer <steve(at)ssinger(dot)info>
> Reviewed-by: Andres Freund <andres(at)anarazel(dot)de>
> Reviewed-by: Erik Rijkers <er(at)xs4all(dot)nl>
> Reviewed-by: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>
>
>
..
> 119 files changed, 13354 insertions(+), 95 deletions(-)
>

Great work, Congrats Peter Jelinek and Thanks to all involved.
Getting a feature of this magnitude deserves a big round of applause.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On Sat, Jan 21, 2017 at 1:19 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com> wrote:
> On Fri, Jan 20, 2017 at 7:36 PM, Peter Eisentraut <peter_e(at)gmx(dot)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
>>
>> From: Petr Jelinek <petr(at)2ndquadrant(dot)com>
>> Reviewed-by: Steve Singer <steve(at)ssinger(dot)info>
>> Reviewed-by: Andres Freund <andres(at)anarazel(dot)de>
>> Reviewed-by: Erik Rijkers <er(at)xs4all(dot)nl>
>> Reviewed-by: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>
>>
>>
> ..
>> 119 files changed, 13354 insertions(+), 95 deletions(-)
>>
>
>
> Great work, Congrats Peter Jelinek and Thanks to all involved.
> Getting a feature of this magnitude deserves a big round of applause.

+1, congrats!

Thanks,
Amit

2017-01-21 6:56 GMT+01:00 Amit Langote <amitlangote09(at)gmail(dot)com>:

> On Sat, Jan 21, 2017 at 1:19 PM, Amit Kapila <amit(dot)kapila16(at)gmail(dot)com>
> wrote:
> > On Fri, Jan 20, 2017 at 7:36 PM, Peter Eisentraut <peter_e(at)gmx(dot)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
> >>
> >> From: Petr Jelinek <petr(at)2ndquadrant(dot)com>
> >> Reviewed-by: Steve Singer <steve(at)ssinger(dot)info>
> >> Reviewed-by: Andres Freund <andres(at)anarazel(dot)de>
> >> Reviewed-by: Erik Rijkers <er(at)xs4all(dot)nl>
> >> Reviewed-by: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>
> >>
> >>
> > ..
> >> 119 files changed, 13354 insertions(+), 95 deletions(-)
> >>
> >
> >
> > Great work, Congrats Peter Jelinek and Thanks to all involved.
> > Getting a feature of this magnitude deserves a big round of applause.
>
> +1, congrats!
>

+1 pretty big work is done!

Pavel

>
> Thanks,
> Amit
>
>
> --
> Sent via pgsql-committers mailing list (pgsql-committers(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-committers
>

On 2017-01-21 09:49:28 +0530, Amit Kapila wrote:
> On Fri, Jan 20, 2017 at 7:36 PM, Peter Eisentraut <peter_e(at)gmx(dot)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
> >
> > From: Petr Jelinek <petr(at)2ndquadrant(dot)com>
> > Reviewed-by: Steve Singer <steve(at)ssinger(dot)info>
> > Reviewed-by: Andres Freund <andres(at)anarazel(dot)de>
> > Reviewed-by: Erik Rijkers <er(at)xs4all(dot)nl>
> > Reviewed-by: Peter Eisentraut <peter(dot)eisentraut(at)2ndquadrant(dot)com>

> Great work, Congrats Peter Jelinek and Thanks to all involved.
> Getting a feature of this magnitude deserves a big round of applause.

Seconded! Or fourthed ;)

Peter,

* Peter Eisentraut (peter_e(at)gmx(dot)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

Peter,

On Tue, Feb 7, 2017 at 22:49 Peter Eisentraut <
peter(dot)eisentraut(at)2ndquadrant(dot)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.

Thanks!

Stephen

On 08/02/17 05:02, Stephen Frost wrote:
> Peter,
>
> On Tue, Feb 7, 2017 at 22:49 Peter Eisentraut
> <peter(dot)eisentraut(at)2ndquadrant(dot)com
> <mailto:peter(dot)eisentraut(at)2ndquadrant(dot)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

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

Hi,

On 2017-01-20 14:06:18 +0000, Peter Eisentraut 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 know I mentioned this before, but I just hit it again. Please split
up your commits in more logical chunks. This has a *LOT* of largely
unrelated pieces. Lots of them without so much as a comment about why
they have been changed. The above certainly doesn't explain why the
*generic* cache invalidation code was affected:

@@ -509,8 +514,10 @@ RegisterRelcacheInvalidation(Oid dbId, Oid relId)
/*
* If the relation being invalidated is one of those cached in the local
* relcache init file, mark that we need to zap that file at commit.
+ * Same is true when we are invalidating whole relcache.
*/
- if (OidIsValid(dbId) && RelationIdIsInInitFile(relId))
+ if (OidIsValid(dbId) &&
+ (RelationIdIsInInitFile(relId) || relId == InvalidOid))
transInvalInfo->RelcacheInitFileInval = true;
}

I mean, like, seriously? What is this stuff about?

I kinda guess the motivating factor is:

/*
* CacheInvalidateRelcacheAll
* Register invalidation of the whole relcache at the end of command.
*
* This is used by alter publication as changes in publications may affect
* large number of tables.
*/
void
CacheInvalidateRelcacheAll(void)
{
PrepareInvalidationState();

RegisterRelcacheInvalidation(InvalidOid, InvalidOid);
}

but that doesn't explain why it's not tied to a database? Nor why the
relcache init file is now invalidated when there's a publication change?

Greetings,

Andres Freund