pglogical_output - a general purpose logical decoding output plugin

Hi,

On 2015-11-02 20:17:21 +0800, Craig Ringer wrote:

I'd like to submit pglogical_output for inclusion in the 9.6 series as
a contrib.

Cool!

See the README.md and DESIGN.md in the attached patch for details on
the plugin. I will follow up with a summary in a separate mail, along
with a few points I'd value input on or want to focus discussion on.

Sounds to me like at least a portion of this should be in sgml, either
in a separate contrib page or in the logical decoding section.

A quick readthrough didn't have a separate description of the
"sub-protocol" in which changes and such are encoded - I think we're
going to need that.

I anticipate that I'll be following up with a few tweaks, but at this
point the plugin is feature-complete, documented and substantially
ready for inclusion as a contrib.

There's a bunch of changes that are hinted at in the files in various
places. Could you extract the ones you think need to be fixed before
integration see in some central place (or just an email)?

Regards,

Andres

--
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 November 2015 at 20:35, Andres Freund <andres@anarazel.de> wrote:

On 2015-11-02 20:17:21 +0800, Craig Ringer wrote:

See the README.md and DESIGN.md in the attached patch for details on
the plugin. I will follow up with a summary in a separate mail, along
with a few points I'd value input on or want to focus discussion on.

Sounds to me like at least a portion of this should be in sgml, either
in a separate contrib page or in the logical decoding section.

Yes, I think so. Before rewriting to SGML I wanted to solicit opinions
on what should be hidden away in a for-developers README file and what
parts deserve exposure in the public docs.

A quick readthrough didn't have a separate description of the
"sub-protocol" in which changes and such are encoded - I think we're
going to need that.

It didn't quite make the first cut as I have to make a couple of edits
to reflect late changes. I should be able to follow up with that later
today.

The protocol design documentation actually predates the plugin its
self, though it saw a few changes where it became clear something
wouldn't work as envisioned. It's been quite a pleasure starting with
a detailed design, then implementing it.

There's a bunch of changes that are hinted at in the files in various
places. Could you extract the ones you think need to be fixed before
integration see in some central place (or just an email)?

Yep, writing that up at the moment. I didn't want to make the initial
post too verbose.

--
Craig Ringer 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 November 2015 at 20:17, Craig Ringer <craig@2ndquadrant.com> wrote:

Hi all

I'd like to submit pglogical_output for inclusion in the 9.6 series as
a contrib.

A few points are likely to come up in anything but the most cursory
examination of the patch.

The README alludes to protocol docs that aren't in the tree. A
followup will add them shortly, they just need a few tweaks.

There are pg_regress tests, but they're limited. The output plugin
uses the binary output mode, and pg_regress doesn't play well with
that at all. Timestamps, XIDs, LSNs, etc are embedded in the output.
Also, pglogical its self emits LSNs and timestamps in commit messages.
Some things, like the startup message, are likely to contain variable
data in future too. So we can't easily do a "dumb" comparison of
expected output.

That's why the bulk of the tests in test/ are in Python, using
psycopg2. Python and psycopg2 were used partly because of the
excellent work done by Oleksandr Shulgin at Zalando
(https://github.com/zalando/psycopg2/tree/feature/replication-protocol,
https://github.com/psycopg/psycopg2/pull/322) which means we can
connect to the walsender and consume the replication protocol, rather
than relying only on the SQL interfaces. Both are supported, and only
the SQL interface is used by default. It also means the tests can have
logic to validate the protocol stream, examining it message by message
to ensure it's exactly what's expected. Rather than a diff where two
lines of binary gibberish don't match, you get a specific error. Of
course, I'm aware that the buildfarm animals aren't required to have
Python, let alone a patched psycopg2, so we can't rely on these as
smoketests. That's why the pg_regress tests are there too.

There another extension inside it, in
contrib/pglogical_output/examples/hooks . I'm not sure if this should
be separated out into a separate contrib/ since it's very tightly
coupled to pglogical_output. Its purpose is to expose the hooks from
pglogical_output to SQL, so that they can be implemented by plpgsql or
whatever, instead of having to be C functions. It's not integrated
into pglogical_output proper because I see this as mainly a test and
prototyping facility. It's necessary to have this in order for the
unit tests to cover filtering and hooks, but most practical users will
never want or need it. So I'd rather not integrate it into
pglogical_output proper.

pglogical_output has headers, and it installs them into Pg's include/
tree at install time. This is not something that prior contribs have
done, so there's no policy for it as yet. The reason for doing so is
that the output plugin exposes a hooks API so that it can be reused by
different clients with different needs, rather than being tightly
coupled to just one downstream user. For example, it makes no
assumptions about things like what replication origin names mean -
keeping with the design of replication origins, which just provide
mechanism without policy. That means that the client needs to tell the
output plugin how to filter transactions if it wants to do selective
replication on a node-by-node basis. Similarly, there's no built-in
support for selective replication on a per-table basis, just a hook
you can implement. So clients can provide their own policy for how to
decide what tables to replicate. When we're calling hooks for each and
every row we really want a C function pointer so we can avoid the need
to go through the fmgr each time, and so we can pass a `struct
Relation` and bypass the need for catalog lookups. That sort of thing.

Table metadata is sent for each row. It really needs to be sent once
for each consecutive series of rows for the same table. Some care is
required to make sure it's invalidated and re-sent when the table
structure changes mid-series. So that's a pending change. It's
important for efficiency, but pretty isolated and doesn't make the
plugin less useful otherwise, so I thought it could wait.

Sending the whole old tuple is not yet supported, per the fixme in
pglogical_write_update . It should really be a TODO, since to support
this we really need a way to keep track of replica identity for a
table, but also WAL-log the whole old tuple. (ab)using REPLICA
IDENTITY FULL to log the old tuple means we lose information about
what the real identity key is. So this is more of a wanted future
feature, and I'll change it to a TODO.

I'd like to delay some ERROR messages until after the startup
parameters are sent. That way the client can see more info about the
server's configuration, version, capabilities, etc, and possibly
reconnect with acceptable settings. Because a logical decoding plugin
isn't allowed to generate input during its startup callback, though,
this could mean indefinitely delaying an error until the upstream does
some work that results in a decoding callback. So for now errors on
protocol mismatches, etc, are sent immediately.

Text encoding names are compared byte-wise. They should be looked up
in the catalogs and compared properly. Just not done yet.

I think those are the main points.

--
Craig Ringer 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 November 2015 at 20:17, Craig Ringer <craig@2ndquadrant.com> wrote:

Hi all

I'd like to submit pglogical_output for inclusion in the 9.6 series as
a contrib.

Here's the protocol documentation discussed in the README. It's
asciidoc at the moment, so it can be formatted into something with
readable tables.

If everyone thinks it's reasonable to document the pglogical protocol
as part of the SGML docs then it can be converted. Since the walsender
protocol is documented in the public SGML docs it probably should be,
it's just a matter of deciding what goes where.

Thanks to Darko Milojković for the asciidoc conversion. All errors are
likely to be my edits.

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

On Mon, Nov 2, 2015 at 1:17 PM, Craig Ringer <craig@2ndquadrant.com> wrote:

Hi all

I'd like to submit pglogical_output for inclusion in the 9.6 series as
a contrib.

Yay, that looks pretty advanced! :-)

Still digesting...

--
Alex

On 11/2/15 8:36 AM, Craig Ringer wrote:

Here's the protocol documentation discussed in the README. It's
asciidoc at the moment, so it can be formatted into something with
readable tables.

Is this by chance up on github? It'd be easier to read the final output
there than the raw asciidoctor. ;)

Great work on this!
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

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

On 3 November 2015 at 02:58, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

On 11/2/15 8:36 AM, Craig Ringer wrote:

Here's the protocol documentation discussed in the README. It's
asciidoc at the moment, so it can be formatted into something with
readable tables.

Is this by chance up on github? It'd be easier to read the final output
there than the raw asciidoctor. ;)

Not yet, no. Should be able to format it to PDF, HTML, etc if needed though.

Depending on the consensus here, I'm expecting the protocol docs will
likely get turned into a plaintext formatted README in the source
tree, or into SGML docs.

The rest are in rather more readable Markdown form at this point,
again pending opinions on where they should live - in the public SGML
docs or in-tree READMEs.

--
Craig Ringer 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 3 November 2015 at 16:41, Craig Ringer <craig@2ndquadrant.com> wrote:

On 3 November 2015 at 02:58, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

On 11/2/15 8:36 AM, Craig Ringer wrote:

Here's the protocol documentation discussed in the README. It's
asciidoc at the moment, so it can be formatted into something with
readable tables.

Is this by chance up on github? It'd be easier to read the final output
there than the raw asciidoctor. ;)

Not yet, no. Should be able to format it to PDF, HTML, etc if needed though.

In fact, I'll just put a PDF up shortly.

--
Craig Ringer 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 3 November 2015 at 02:58, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

On 11/2/15 8:36 AM, Craig Ringer wrote:

Here's the protocol documentation discussed in the README. It's
asciidoc at the moment, so it can be formatted into something with
readable tables.

Is this by chance up on github? It'd be easier to read the final output
there than the raw asciidoctor. ;)

HTML for the protocol documentation attached.

The docs are being converted to SGML at the moment.

I'll be pushing an update of pglogical_output soon. Patch in a followup
mail.

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

On 2015/11/02 23:36, Craig Ringer wrote:

On 2 November 2015 at 20:17, Craig Ringer <craig@2ndquadrant.com> wrote:

Hi all

I'd like to submit pglogical_output for inclusion in the 9.6 series as
a contrib.

Here's the protocol documentation discussed in the README. It's
asciidoc at the moment, so it can be formatted into something with
readable tables.

Kudos! From someone who doesn't really read wire protocol docs a lot, this
was such an enlightening read.

Thanks,
Amit

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

Hi all

Here's an updated pglogical_output patch.

Selected changes since v1:

- add json protocol output support
- fix encoding comparisons to use parsed encoding not string name
- import protocol documentation
- significantly expand pg_regress tests
- move pglogical_output_plhooks to a top-level contrib
- remove 9.4 compatibility

Full diff since last version at
https://github.com/2ndQuadrant/postgres/compare/pglogical-output-v1...2ndQuadrant:pglogical-output-v2

Notably, it now has support for a basic json-format text-based protocol as
well as the main binary protocol. Mostly for debugging, testing and
diagnostics.

I've used the newly added json support in pglogical_output to significantly
expand the pg_regress tests, since the json mode can support text-mode
decoding in the SQL interface to logical decoding, where the lsn, xid, etc
can be skipped.

I've removed the Python based test framework. Test coverage in-tree is
reduced as a result, with no coverage of the details of the binary
protocol, no coverage of walsender based use, etc. I'll have a look at
whether it'll be practical to convert the tests to Perl code driving
pg_recvlogical as a co-process but don't think evaluation should wait on
this.

I've also removed the 9.4 backward compatibility code from the version
submitted for 9.6.

Docs conversion to SGML is still pending/WIP.

I've been unable to find a reasonable way to send the startup message
before raising an ERROR when there's an issue with parameter/protocol
negotiation. Advice/suggestions appreciated. The main issue is that the
setup callback can't write to the protocol stream since in walsender mode
the walsender isn't ready for writes yet. (Otherwise we could just write a
record with InvalidXLogRecPtr, etc). Delaying the startup msg to the first
begin callback works, as is done now. But delaying an error would involve
allowing the begin callback to complete, then ERRORing at the *next*
callback, which could be anything. Very ugly for what should be an uncommon
case. So for now ERRORs are immediate, even though that makes negotiation
much more difficult. Ideas welcomed.

(Cc'd a few people who expressed interest)

On 11/2/15 7:17 AM, Craig Ringer wrote:

The output plugin is suitable for a number of uses. It's designed
primarily to supply a data stream to drive a logical replication
client running in another PostgreSQL instance, like the pglogical
client discussed at PGConf.EU 2015.

So where is that client?

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

On 16 November 2015 at 09:57, Peter Eisentraut <peter_e@gmx.net> wrote:

On 11/2/15 7:17 AM, Craig Ringer wrote:

The output plugin is suitable for a number of uses. It's designed
primarily to supply a data stream to drive a logical replication
client running in another PostgreSQL instance, like the pglogical
client discussed at PGConf.EU 2015.

So where is that client?

Not finished baking yet - in particular, the catalogs and UI are still in
flux. The time scale for getting that out is in the order of a few weeks.

The output plugin stands alone to a fair degree, especially with the json
output support. Comments would be greatly appreciated, especially from
people who're involved in replication, are currently using triggers to feed
data to external systems, etc.

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

W dniu 12.11.2015, czw o godzinie 22∶23 +0800, użytkownik Craig Ringer
napisał:

Hi all

Here's an updated pglogical_output patch.

Selected changes since v1:

    - add json protocol output support
    - fix encoding comparisons to use parsed encoding not string name
    - import protocol documentation
    - significantly expand pg_regress tests
    - move pglogical_output_plhooks to a top-level contrib
    - remove 9.4 compatibility

I've just skimmed through the patch.
As you removed 9.4 compatibility - are mentions of 9.4 and 9.5 
compatibility needed in README.md and README.plhooks?
It's not much text, but I'm not sure whether they shouldn't be
removed for 9.6-targeting change.

--
Tomasz Rybak GPG/PGP key ID: 2AD5 9860
Fingerprint A481 824E 7DD3 9C0E C40A 488E C654 FB33 2AD5 9860
http://member.acm.org/~tomaszrybak

On 19 November 2015 at 03:42, Tomasz Rybak <tomasz.rybak@post.pl> wrote:

I've just skimmed through the patch.

As you removed 9.4 compatibility - are mentions of 9.4 and 9.5
compatibility needed in README.md and README.plhooks?

Thanks. Removed.

Here's a v3 patch. Changes since v2:

- fix stray braces in commit json output
- significantly expand pg_regress tests
- quote all json keys
- validate all json in tests
- decode json startup message to a table
- filter out mutable keys in json startup message
- troubleshooting section of README covers using json
- documentation copy editing

Full patch against master attached. Diff since v2 at

https://github.com/2ndQuadrant/postgres/compare/pglogical-output-v2...2ndQuadrant:pglogical-output-v3

While the downstream client using the binary protocol is still too WIP, the
json support is useful for all sorts of apps. It has all the same support
for hooks and filtering, origin information, etc. I'd love to hear from
anyone who's trying it out.

SGML conversion of docs still WIP.

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

Hi,

I can't really do huge review considering I wrote half of the code, but
I have couple of things I noticed.

First, I wonder if it would be useful to mention somewhere, even if it's
only here in the mailing list how can the protocol be extended in
non-breaking way in future for transaction streaming if we ever get
that. I am mainly asking for this because the protocol does not
currently send xid for every change as it's not necessary, but for
streaming it will be. I know of couple of ways myself but I think they
should be described publicly.

The other thing is that I think we don't need the "forward_changesets"
parameter which currently decides if to forward changes that didn't
originate on local node. There is already hook for origin filtering
which provides same functionality in more flexible way so it seems
redundant to also have special boolean option for it.

--
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 December 2015 at 18:38, Petr Jelinek <petr@2ndquadrant.com> wrote:

First, I wonder if it would be useful to mention somewhere, even if it's
only here in the mailing list how can the protocol be extended in
non-breaking way in future for transaction streaming if we ever get that.

Good point.

I'll address that in the DESIGN.md in the next rev.

Separately, it's looking like xact streaming is possibly more complex than
I hoped due to cache invalidation issues, but I haven't been able to fully
understand the problem yet.

The other thing is that I think we don't need the "forward_changesets"

parameter which currently decides if to forward changes that didn't
originate on local node. There is already hook for origin filtering which
provides same functionality in more flexible way so it seems redundant to
also have special boolean option for it.

Removed, change pushed.

Also pushed a change to expose the decoded row data to row filter hooks.

I won't cut a v4 for this, as I'm working on merging the SGML-ified docs
and will do a v4 with that and the above readme change once that's done.

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

On Mon, Dec 7, 2015 at 12:10 PM, Craig Ringer <craig@2ndquadrant.com> wrote:

Removed, change pushed.

Also pushed a change to expose the decoded row data to row filter hooks.

I won't cut a v4 for this, as I'm working on merging the SGML-ified docs and
will do a v4 with that and the above readme change once that's done.

Patch is moved to next CF, you seem to be still working on it..
--
Michael

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

On 22 December 2015 at 15:17, Michael Paquier <michael.paquier@gmail.com>
wrote:

On Mon, Dec 7, 2015 at 12:10 PM, Craig Ringer <craig@2ndquadrant.com>
wrote:

Removed, change pushed.

Also pushed a change to expose the decoded row data to row filter hooks.

I won't cut a v4 for this, as I'm working on merging the SGML-ified docs

and

will do a v4 with that and the above readme change once that's done.

Patch is moved to next CF, you seem to be still working on it.

Thanks.

Other than SGML-ified docs it's ready. The docs are a hard pre-req of
course. In any case most people appear to be waiting for the downstream
before looking at it at all, so bumping it makes sense.

I'm a touch frustrated by that, as a large part of the point of submitting
the output plugin separately and in advance of the downstream was to get
attention for it separately, as its own entity. A lot of effort has been
put into making this usable for more than just a data source for
pglogical's replication tools. In retrospect naming it pglogical_output was
probably unwise.

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