Jan Wieck wrote:

>> I'd like to develop a patch that loosens this restriction if
>> possible.  I'm finding it quite troublesome especially on my dev and
>> qa systems.
>
>
> If you have broken clients that leave open transactions around for
> infinite times (I don't say you have, but I have seen that before),
> then you have much bigger problems to worry about. 

That being said, if there is an easy way for us to figure out that
"seemingly offending old transactions" are attached to other databases,
and thus irrelevant, then this cuts down on the "unexpected *it won't
subscribe! $&$!*(!#!!!*" complaints.

> One of them would be that vacuum cannot remove rows, which in the
> Slony case will also cause that pg_listener and other relations will
> clog up with dead tuples.

On that latter note, I have at least one idea vis-a-vis the "clog" in
pg_listener, and possibly two.

Right now, confirmations are also propagated via pg_listener, which
means that for every event, you have (I think) FOUR pg_listener entries
that go thru their life cycle.  But really, the confirmation isn't all
that interesting; we don't take action on those; we only take action on
"real events."

My thought is to essentially drop the confirmation events out of what
gets stowed as a NOTIFY in pg_listener.

Acceptance of the confirmation may wait until the next event; I don't
*think* that hurts us.

A while back, you indicated:

Don't forget that WAIT FOR EVENT relies on CONFIRM's to come in. If your 
make their delivery random on "something happens anyway", you might end 
up waiting forever.

If the slon for the node is alive, it'll be generating at least its own
events once every minute or so, and in a live cluster, *some* "real"
events should be flowing (e.g. - aside from confirmations).  If we have
to be dependent on sort of artificially generated events, to this
degree, it seems to me we're borrowing some trouble.

In any case, I did some walkthru of WAIT FOR EVENT, and didn't see
reliance on CONFIRMs *being notifications.*  If the event loop in
remote_listen.c performs both the pulling of new events as well as
checking confirmations in its main loop, I *think* we'll be OK.  It
should be easy enough to test.

That's "idea #1", which ought to help us to the tune of ~50% of the
pg_listener clog.  (The more nodes, the less diversely connected, the
more confirmations there normally are, and so the MORE helpful this
gets.)  I can't find the patch that I thought I had for this; it
shouldn't be hard to recreate it, and it should be quite easy to test
it.  I set up a cluster, have NO traffic, along with pretty long timeout
parameters on the slons, thereby minimizing confirmations, and then
submit a slonik script that does WAIT FOR EVENT to see if it blocks
forever.  I'm optimistic :-)

Idea #2 is then to restructure the material in remote_listen.c so that
if we see, for a given slon+node, that communications are going well, we
turn off the LISTEN (which will eliminate pg_listener tuple generation)
and instead poll...  Every listener that you eliminate eliminates a
barrel of dead pg_listener tuples...  If each node is working well, then
this would mean that there are NO listeners, therefore NO pg_listener
entries, and therefore NO bloat there at all.  That's the tougher change.