Hi!
This is of very practical concern. :-) I have many apps I developed in Meteor [1] using MongoDB. The whole Meteor stack is about reactive programming where web UI automatically rerenders as data in the database is changing. Meteor achieves this using complicated server-side code which tails MongoDB oplog and then maps this to reactive queries and maps how they update based on changes it observes in the oplog. This is then pushed to the client which rerenders.
This approach has scalability issues and also it costs a lot of resources on the server side to first copy data from the DB into server-side component and then keep that state in the server-side component in sync with the DB. It generally has to reimplement oplog parsing, query parsing and evaluation, to be able to do all that.
I like this declerative style of programming. Where you define reactive queries where you select data from DB, define a transformation, and then render it in UI. As data in DB is changing, everything else gets updated automatically. It is a pretty nice way of programming. Without having to think about which all places might be updating DB and how to update UI based on all those places. Maybe not for everyone and all tasks, but in my case I generally work with collaborative online tools where such real-time aspect of working together is pretty neat.
So I like PostgreSQL and I have used in other apps. And now I am trying to see if I could find an efficient way for PostgreSQL to have such reactive query and send me data as the query is changing. I think DB already has to deal with most of such logic and wiring it together in the DB instead of server-side of the app might allow better performance and scaling here.
For example, CREATE PUBLICATION seems a reasonable API as well (instead of a trigger + notification + working around a limit on how much data can be send in a notification), but it does not work on materialized views. Why is that? I thought materialized views are the same as tables, just that there is nicer API to copy a query into those tables when wanted. Currently it seems what is there is very similar to what MongoDB provides: publication/observe on a table level. So if I would not be using joins I could SUBSCRIBE to the PUBLICATION. I would still have to implement logic how to map those changes to changes to a result of a query though, to know how to update results. If I could publish a materialized view, PostgreSQL could do that mapping for me. And I could also do joins.
So it seems materialized views are close to this, but not perfect. So I have some questions:
- Is there a technical reason why PUBLICATION cannot be done on a materialized views? I mean, I could manually create/simulate materialized views through regular tables probably. What are differences between regular tables and materialized views?
- Are there limits on how many subscribers to a PUBLICATION can there effectively be?
- For my case it would be great if materialized views could be TEMPORARY, in-memory (and if PostgreSQL runs out of buffer space for it, I would prefer an error), and UNLOGGED. Any particular reasons which would prevent them to be implemented as such?
Mitar
On Sat, Dec 22, 2018 at 1:16 PM Rob Sargent <robjsargent@xxxxxxxxx> wrote:
Is this of theoretical interest (up to and including a specification/requirement) or this a practical concern (i.e. need to know when to update somebody’s dashboard widget (but the query is too slow to simply refresh on-demand)?On Dec 22, 2018, at 9:42 AM, Ricardo Martin Gomez <rimartingomez@xxxxxxxxxxx> wrote:Hi, perhaps you can use triggers for some tables.
Regards.
Obtener Outlook para Android
From: Mitar <mmitar@xxxxxxxxx>
Sent: Saturday, December 22, 2018 1:21:49 AM
To: Kevin Brannen
Cc: pgsql-general@xxxxxxxxxxxxxxxxxxxx
Subject: Re: Watching for view changesHi!
On Thu, Dec 20, 2018 at 12:54 PM Kevin Brannen <KBrannen@xxxxxxxx> wrote:
Hmm, I guess I could see that as long as the DB wasn't too [write] busy, else you'd be flooded with notifications.
Sure. But this could be resolved by allowing notifications to be batched together. Debounce them. So could maybe configure how often you want such notifications and if they are more often they would be combined together into one.
Maybe it's a useful idea for you ... or maybe not. 😊
Thanks. Yes, this is one approach to do it. Hooking into every modify call at the app level and in this way have some information what is changing. I would prefer doing it in the database though, so that it could be independent from the source of the change. Moreover, not all UPDATE queries really do end up updating the data.
Mitar
--
--
