Re: xReader, double-effort (was: Temporary tables under hot standby)

On Thu, Apr 26, 2012 at 11:02 AM, Simon Riggs <simon@2ndquadrant.com> wrote:

On Thu, Apr 26, 2012 at 3:40 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:

[resending because of postgresql.org bounces on first try]

Simon Riggs <simon@2ndquadrant.com> wrote:

Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:

The GSoC xReader project is intended to be a major step toward
that, by providing a way to translate the WAL stream to a series
of notifications of logical events to clients which register with
xReader.

<sigh> Yes, it appears very similar. I didn't know about this.

I don't recall seeing any previous postings about the 2Q project,
either. We should try to keep these discussions on-list.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On Thu, Apr 26, 2012 at 5:57 PM, Robert Haas <robertmhaas@gmail.com> wrote:

On Thu, Apr 26, 2012 at 11:02 AM, Simon Riggs <simon@2ndquadrant.com> wrote:

On Thu, Apr 26, 2012 at 3:40 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:

[resending because of postgresql.org bounces on first try]

Simon Riggs <simon@2ndquadrant.com> wrote:

Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote:

The GSoC xReader project is intended to be a major step toward
that, by providing a way to translate the WAL stream to a series
of notifications of logical events to clients which register with
xReader.

<sigh> Yes, it appears very similar. I didn't know about this.

I don't recall seeing any previous postings about the 2Q project,
either.  We should try to keep these discussions on-list.

While that's true, it wasn't much of a stretch to think that I might
be planning to do more work on replication since I've done that for
years and years now. Also, I do keep requesting GSOC doesn't put such
major tasks out there, but not much heed taken.

Beautiful illustration of a multi-master conflict case though.

Not sure just yet what the resolution is, and it will probably take a
few weeks to resolve that happily.

Glad we're all facing in the same direction, which is the most important thing.

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

The GSoC xReader project is intended to be a major step toward
that, by providing a way to translate the WAL stream to a series
of notifications of logical events to clients which register with
xReader.

<sigh> Yes, it appears very similar. I didn't know about this.

This isn't a problem. In fact, it's the *exact opposite* of a problem.

The student wanted to do more advanced things with the xlog stream, and
we convinced him to narrow the scope in order to make the project
achievable.

With 2Q having done some/most of the basic work, the student can focus
on more advanced applications of the data again. Win!

However, it would be great if someone from 2Q co-mentored him.

--Josh

Sure Kevin, will get the wiki page ready asap, and reply back. Thanks.

On Thu, Apr 26, 2012 at 8:10 PM, Kevin Grittner <Kevin.Grittner@wicourts.gov

All, the wiki page is now up at http://wiki.postgresql.org/wiki/XReader.

On Sat, Apr 28, 2012 at 1:19 AM, Aakash Goel <aakash.bits@gmail.com> wrote:

[replaced bad email address for Josh (which was my fault)]

Aakash Goel <aakash.bits@gmail.com> wrote:

All, the wiki page is now up at
http://wiki.postgresql.org/wiki/XReader.

Note that the approach Aakash is taking doesn't involve changes to
the backend code, it is strictly a standalone executable to which
functions as a proxy to a hot standby and to which clients like
replications systems connect. There is a possible additional
configuration which wouldn't require a hot standby, if time permits.
I am not clear on whether 2nd Quadrant's code takes this approach
or builds it into the server. I think we need to know that much
before we can get very far in discussion.

-Kevin

On Friday, April 27, 2012 11:04:04 PM Kevin Grittner wrote:

[replaced bad email address for Josh (which was my fault)]

Aakash Goel <aakash.bits@gmail.com> wrote:

All, the wiki page is now up at

http://wiki.postgresql.org/wiki/XReader.

Note that the approach Aakash is taking doesn't involve changes to
the backend code, it is strictly a standalone executable to which
functions as a proxy to a hot standby and to which clients like
replications systems connect. There is a possible additional
configuration which wouldn't require a hot standby, if time permits.
I am not clear on whether 2nd Quadrant's code takes this approach
or builds it into the server. I think we need to know that much
before we can get very far in discussion.

In the current, prototypal, state there is one component thats integrated into
the server (because it needs information thats only available there). That
component is layered ontop of a totally generic xlog reading/parsing library
that doesn't care at all where its running. Its also used in another cluster
to read the received (filtered) stream.
I plan to submit the XLogReader (thats what its called atm) before everything
else, so everybody can take a look as soon as possible.

I took a *very* short glance over the current wiki description of xReader and
from that it seems to me it would benefit from trying to make it
architecturally more similar to the rest of pg. I also would suggest reviewing
how the current walreceiver/sender, and their protocol, work.

Andres

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

Andres Freund <andres@2ndquadrant.com> wrote:

In the current, prototypal, state there is one component thats
integrated into the server (because it needs information thats
only available there).

The xReader design was based on the idea that it would be nice not
to cause load on the master machine, and that by proxying the WAL
stream to the HS, using synchronous replication style to write from
xReader to the HS, you could use the HS for a source for that data
with it being at exactly the right point in time to query it.

I'm not convinced that I would rather see the logic fixed inside the
master as opposed to being deployable on the master's machine, the
slave machine, or even on its own machine in between.

That component is layered ontop of a totally generic xlog
reading/parsing library that doesn't care at all where its
running.

That's cool.

Its also used in another cluster to read the received (filtered)
stream.

I don't quite follow what you're saying there.

I plan to submit the XLogReader (thats what its called atm)
before everything else, so everybody can take a look as soon as
possible.

Great! That will allow more discussion and planning.

I took a *very* short glance over the current wiki description of
xReader and from that it seems to me it would benefit from trying
to make it architecturally more similar to the rest of pg.

We're planning on using existing protocol to talk between pieces.
Other than breaking it out so that it can run somewhere other than
inside the server, and allowing clients to connect to xReader to
listen to WAL events of interest, are you referring to anything
else?

I also would suggest reviewing how the current walreceiver/sender,
and their protocol, work.

Of course! The first "inch-stone" in the GSoC project plan
basically consists of creating an executable that functions as a
walreceiver and a walsender to just pass things through from the
master to the slave. We build from there by allowing clients to
connect (again, over existing protocol) and register for events of
interest, and then recognizing different WAL records to generate
events. The project was just going to create a simple client to
dump the information to disk, but with the time saved by adopting
what you've already done, that might leave more time for generating
a useful client.

Aakash, when you get a chance, could you fill in the "inch-stones"
from the GSoC proposal page onto the Wiki page? I think the
descriptions of those interim steps would help people understand
your proposal better. Obviously, some of the particulars of tasks
and the dates may need adjustment based on the new work which is
expected to appear before you start, but what's there now would be a
helpful reference.

-Kevin

On 04/26/2012 12:57 PM, Robert Haas wrote:

I don't recall seeing any previous postings about the 2Q project,
either. We should try to keep these discussions on-list.

First brought up two months ago to the list it seemed most relevant to
at the time:
http://archives.postgresql.org/pgsql-cluster-hackers/2012-02/msg00001.php and
the solid commentary started coming out yesterday, right near the
approach of May as promised. There hasn't been much posted on hackers
about this out of respect for the extended CommitFest.

I personally suggested Aakash vent ideas out on pgsql-students (or here)
at the beginning of the month:
http://archives.postgresql.org/pgsql-students/2012-04/msg00002.php but
the next time I heard about the subject was the acceptance notice.

--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com

Hi Kevin, Hi Aakash,

On Saturday, April 28, 2012 12:18:38 AM Kevin Grittner wrote:

Andres Freund <andres@2ndquadrant.com> wrote:

In the current, prototypal, state there is one component thats
integrated into the server (because it needs information thats
only available there).

The xReader design was based on the idea that it would be nice not
to cause load on the master machine, and that by proxying the WAL
stream to the HS, using synchronous replication style to write from
xReader to the HS, you could use the HS for a source for that data
with it being at exactly the right point in time to query it.

Yes, that does make sense for some workloads. I don't think its viable for
everything though, thats why were not aiming for that ourselves atm.

I'm not convinced that I would rather see the logic fixed inside the
master as opposed to being deployable on the master's machine, the
slave machine, or even on its own machine in between.

I don't think that you can do everything apart from the master. We currently
need shared memory for coordination between the moving parts, thats why we
have it inside the master.
It also have the advantage of being easier to setup.

That component is layered ontop of a totally generic xlog
reading/parsing library that doesn't care at all where its
running.

That's cool.

Its also used in another cluster to read the received (filtered)
stream.

I don't quite follow what you're saying there.

To interpret the xlog back into something that can be used for replication you
need to read it again. After filtering we again write valid WAL, so we can use
the same library on the sending|filtering side and on the receiving side.
But thats actually off topic for this thread ;)

I took a *very* short glance over the current wiki description of
xReader and from that it seems to me it would benefit from trying
to make it architecturally more similar to the rest of pg.

We're planning on using existing protocol to talk between pieces.
Other than breaking it out so that it can run somewhere other than
inside the server, and allowing clients to connect to xReader to
listen to WAL events of interest, are you referring to anything
else?

It sounds like the xReader is designed to be one multiplexing process. While
this definitely has some advantages resource-usage-wise it doesn't seem to be
fitting the rest of the design that well. The advantages might outweigh
everything else, but I am not sure about that.
Something like registering/deregistering also doesn't fit that well with the
way walsender works as far as I understand it.

Greetings,

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

Greg Smith <greg@2ndQuadrant.com> wrote:

On 04/26/2012 12:57 PM, Robert Haas wrote:

I don't recall seeing any previous postings about the 2Q project,
either. We should try to keep these discussions on-list.

First brought up two months ago to the list it seemed most
relevant to at the time:

http://archives.postgresql.org/pgsql-cluster-hackers/2012-02/msg00001.php

and the solid commentary started coming out yesterday, right near
the approach of May as promised. There hasn't been much posted on
hackers about this out of respect for the extended CommitFest.

I personally suggested Aakash vent ideas out on pgsql-students (or
here) at the beginning of the month:
http://archives.postgresql.org/pgsql-students/2012-04/msg00002.php
but the next time I heard about the subject was the acceptance
notice.

That's largely my fault. Aakash's original proposal was naively
assuming that logical records were available in our xlog stream and
wanted to work on tools to do something useful with that data.
There was some confusion among the various core team members,
committers, and other contributors who volunteered to review the
proposals about whether he was talking about analyzing the server
log or xlog. We had a wide-ranging discussion on the freenode
#postgresql channel about it, and brainstormed to the current idea
-- at which point Aakash had about a week and a half to rewrite his
proposal along the lines discussed on irc. A week after that the
acceptance notices came out.

If we had had any idea that 2Q was working on something along these
lines, we would have coordinated or guided him in another direction.
The post you cite from a couple months ago was way too vague to
have even an inkling that it had anything to do with providing feeds
of the logical interpretation of the WAL stream. I don't recall
having heard the idea even mentioned before.

The timing in regard to the release process seems to have had an
effect on both groups here, since I suggested he read up on
walsender and walreceiver and a few other areas, put together a Wiki
page with the plan, and *then* describe the preliminary plan to the
community (a month or so before starting to code), so that details
could be hashed out in public before time was spent coding. Had
there not been so many people heads-down trying to get the release
out, I might have advised him to post a hand-wavy description of the
effort before he got the Wiki page set up and reviewed the relevant
code.

As Josh points out, his first choice was to write utilities to *do*
something with the logical xlog information, and he only switched
over to working on *producing* it when he heard that no such thing
was currently available. I think there's a lot of room to
coordinate these efforts so that there *isn't* a waste of effort as
long as communication is open enough.

-Kevin

Andres Freund <andres@2ndquadrant.com> wrote:

Something like registering/deregistering also doesn't fit that
well with the way walsender works as far as I understand it.

If you look at the diagrams on the xReader Wiki page, the lines
labeled "XLOG stream" are the ones using walsender/walreceiver. The
green arrows represent normal connections to the database, to run
queries to retrieve metadata needed to interpret the WAL records,
and the lines labeled "Listener n" are expected to use the pg
protocol to connect, but won't be talking page-oriented WAL -- they
will be dealing with logical interpretation of the WAL. The sort of
data which could be fed to a database which doesn't have the same
page images. Like Slony et al do.

Perhaps, given other points you made, the library for interpreting
the WAL records could be shared, and hopefully a protocol for the
clients, although that seems a lot more muddy to me at this point.
If we can share enough code, there may be room for both approaches
with minimal code duplication.

-Kevin

On 04/27/2012 06:48 PM, Kevin Grittner wrote:

That's largely my fault.

It may not have come out that way, but I was trying more to point out
the complexity of the story rather than assign blame. I find it hard to
point at any obvious "don't do that again" target here.

--
Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD
PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com

On Fri, Apr 27, 2012 at 11:18 PM, Kevin Grittner
<Kevin.Grittner@wicourts.gov> wrote:

Andres Freund <andres@2ndquadrant.com> wrote:

I'm not convinced that I would rather see the logic fixed inside the
master as opposed to being deployable on the master's machine, the
slave machine, or even on its own machine in between.

There are use cases where the translation from WAL to logical takes
place on the master, the standby or other locations.

It's becoming clear that filtering records on the source is important
in high bandwidth systems, so the initial work focuses on putting that
on the "master", i.e. the source. Which was not my first thought
either. If you use cascading, this would still allow you to have
master -> standby -> logical.

Translating WAL is a very hard task. Some time ago, I did also think
an external tool would help (my initial design was called xfilter),
but I no longer think that is likely to work very well apart from very
simple cases.

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

Aakash, when you get a chance, could you fill in the "inch-stones"
from the GSoC proposal page onto the Wiki page?

Sure, http://wiki.postgresql.org/wiki/XReader updated.

On Sat, Apr 28, 2012 at 3:48 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov

Hello Andres,

The xReader design was based on the idea that it would be nice not
to cause load on the master machine, and that by proxying the WAL
stream to the HS, using synchronous replication style to write from
xReader to the HS, you could use the HS for a source for that data
with it being at exactly the right point in time to query it.

Yes, that does make sense for some workloads. I don't think its viable for
everything though, thats why were not aiming for that ourselves atm.

Regarding the above, what would be a case where querying the HS will not
suffice?

On Sat, Apr 28, 2012 at 4:02 AM, Andres Freund <andres@2ndquadrant.com>wrote:

Simon Riggs <simon@2ndQuadrant.com> writes:

Translating WAL is a very hard task.

No kidding. I would think it's impossible on its face. Just for
starters, where will you get table and column names from? (Looking at
the system catalogs is cheating, and will not work reliably anyway.)

IMO, if we want non-physical replication, we're going to need to build
it in at a higher level than after-the-fact processing of WAL.
I foresee wasting quite a lot of effort on the currently proposed
approaches before we admit that they're unworkable.

regards, tom lane

Tom Lane wrote:

Simon Riggs writes:

Translating WAL is a very hard task.

No kidding. I would think it's impossible on its face. Just for
starters, where will you get table and column names from? (Looking
at the system catalogs is cheating, and will not work reliably
anyway.)

Well, the xReader approach (Aakash's GSoC project) is to serve as a
proxy for a WAL stream going to a hot standby, to interpret each
incoming WAL record by "cheating" and querying the HS before passing
the WAL along, and then using sync rep to the HS regardless of
whether the incoming stream is sync or async. What reliability
problem do you see with this approach?

-Kevin

"Kevin Grittner" <Kevin.Grittner@wicourts.gov> writes:

Tom Lane wrote:

Simon Riggs writes:

Translating WAL is a very hard task.

No kidding. I would think it's impossible on its face.

Well, the xReader approach (Aakash's GSoC project) is to serve as a
proxy for a WAL stream going to a hot standby, to interpret each
incoming WAL record by "cheating" and querying the HS before passing
the WAL along, and then using sync rep to the HS regardless of
whether the incoming stream is sync or async. What reliability
problem do you see with this approach?

Well, first off, it requires a replica that *is* a physical replica
(else it won't have the same table OIDs, for instance). I'm not sure I
get the point of logical replication that requires a physical replica as
a prerequisite.

Next, it breaks immediately in the face of DDL:

CREATE TABLE foo (f1 int);
BEGIN;
ALTER TABLE foo ALTER COLUMN f1 RENAME TO f2;
INSERT INTO foo (f2) VALUES (1);
COMMIT;

The standby is not going to think that the ALTER is committed, so it
will not report the right column name when it comes time to translate
the INSERT. Actually, you can break it even more easily than that:

CREATE TABLE bar AS SELECT ...

What will you do with the insertions executed by this CREATE? They have
to be executed before the creation of table bar is committed.

Also, you'd need a query connection per database (at least, maybe one
per session if you were trying to track DDL effects), which seems rather
a lot of load on the HS slave. That together with the requirement for
synchronous operation seems absolutely catastrophic from a performance
standpoint.

If you want to spend the summer building a toy, fine, but I don't see
this going anywhere for production purposes. It would be enormously
more performant for the master to be emitting logical replication
records to start with, since it already has all the right names etc
at hand at basically no cost.

regards, tom lane