Обсуждение: 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.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@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 ofincluding 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 matterof 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