Re: Listen/notify across clusters

Hi masters of PostgreSQL,

I recently got asked about possibility of listening to notifications on warm standby.
So question, how hard would that be to implement ?
Is it even possible without major changes to the architecture ?

Shouldn't be possible.

The act of requesting to LISTEN requires doing a sort of update to the
database. In elder versions, it put tuple(s) into pg_catalog.pg_listener,
and that's Right Well Disallowed on a WAL-based replica.

I would think that if you're keen on building an "event detection
substrate," particularly one that's supposed to cross clusters, then you
should consider using something actually attuned to that, such as a message
queueing system, whether an AMQP implementation such as RabbitMQ, or a
message bus like Spread. If you do that, then you can do this in much
broader cross-cluster ways for unrelated Postgres clusters.

On 07/10/2013 09:27 AM, Christopher Browne wrote:
> The act of requesting to LISTEN requires doing a sort of update to the
> database. In elder versions, it put tuple(s) into pg_catalog.pg_listener,
> and that's Right Well Disallowed on a WAL-based replica.
>
> I would think that if you're keen on building an "event detection
> substrate," particularly one that's supposed to cross clusters, then you
> should consider using something actually attuned to that, such as a message
> queueing system, whether an AMQP implementation such as RabbitMQ, or a
> message bus like Spread. If you do that, then you can do this in much
> broader cross-cluster ways for unrelated Postgres clusters.

Huh? LISTEN/NOTIFY across replication has been a desired feature since
we introduced streaming replication. We want it, there's just no
obvious way to do it.

Your email kinda implies that it's not desirable.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 10 Jul 2013, at 19:26, Josh Berkus <josh(at)agliodbs(dot)com> wrote:
>
>
> Huh? LISTEN/NOTIFY across replication has been a desired feature since
> we introduced streaming replication. We want it, there's just no
> obvious way to do it.
>
> Your email kinda implies that it's not desirable.

Thanks Josh. I was under impression that it is desirable.
It certainly makes sense to be used with the existing database infrastructure.

In terms of features, apart from separating LISTEN so that it can be actually used on Standbys, wouldn't it be a matter of including the notifications in the WAL stream, as simple packets ?
This would guarantee same behaviour as on the master.

--
GJ

On 07/15/2013 04:10 PM, Greg Jaskiewicz wrote:
> In terms of features, apart from separating LISTEN so that it can be actually used on Standbys, wouldn't it be a matter of including the notifications in the WAL stream, as simple packets ?
> This would guarantee same behaviour as on the master.

I guess one problem is to implement writing to the WAL with the smallest
possible performance hit. As far as I can see there are two possible
approaches: either write to WAL when NOTIFY is run or write to WAL on
commit. The former seems more in line with how commands in PostgreSQL
usually work.

There shouldn't be any major problems with implementing LISTEN on the
slaves since LISTEN is done in memory.

I feel like I as a beginner to the codebase am missing something bit
because while this is a fair bit of work it does not too hard to implement.

Andreas

--
Andreas Karlsson

On 07/16/2013 07:16 PM, Andreas Karlsson wrote:
> I guess one problem is to implement writing to the WAL with the smallest
> possible performance hit. As far as I can see there are two possible
> approaches: either write to WAL when NOTIFY is run or write to WAL on
> commit. The former seems more in line with how commands in PostgreSQL
> usually work.

Yes.

There was some work being done by Heikki or Andreas Freund on "log-only
tables" which seems like it would be a perfect solution to this. Anyone
know what happened to that patch?

> There shouldn't be any major problems with implementing LISTEN on the
> slaves since LISTEN is done in memory.

Actually, that's not the hard part. Listeners need to be registered on
the standby, which requires a write to a system catalog, currently. So
you'd need some alternate way to register listeners on the standby.

Presumably all LISTEN events would need to be broadcast to all standbys,
whether or not they had LISTENERs registered. Otherwise we'd have to
push the listener registrations back to the master.

--
Josh Berkus
PostgreSQL Experts Inc.
http://pgexperts.com

On 07/17/2013 02:08 PM, Josh Berkus wrote:
>
>> There shouldn't be any major problems with implementing LISTEN on the
>> slaves since LISTEN is done in memory.
> Actually, that's not the hard part. Listeners need to be registered on
> the standby, which requires a write to a system catalog, currently.

Er, not since 9.0 I think - there is no pg_listener any more.

cheers

andrew