Listen/notify across clusters

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

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

On 10 Jul 2013, at 19:26, Josh Berkus <josh@agliodbs.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

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

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

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

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

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

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

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