Re: Watching for view changes

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?

[1] https://github.com/meteor/meteor

Mitar

On Sat, Dec 22, 2018 at 1:16 PM Rob Sargent <robjsargent(at)gmail(dot)com> 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(at)hotmail(dot)com> wrote:
>
> Hi, perhaps you can use triggers for some tables.
> Regards.
>
> Obtener Outlook para Android <https://aka.ms/ghei36>
>
> ------------------------------
> *From:* Mitar <mmitar(at)gmail(dot)com>
> *Sent:* Saturday, December 22, 2018 1:21:49 AM
> *To:* Kevin Brannen
> *Cc:* pgsql-general(at)lists(dot)postgresql(dot)org
> *Subject:* Re: Watching for view changes
>
> Hi!
>
> On Thu, Dec 20, 2018 at 12:54 PM Kevin Brannen <KBrannen(at)efji(dot)com> 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
>
> --
> http://mitar.tnode.com/
> https://twitter.com/mitar_m
>
>
>

--
http://mitar.tnode.com/
https://twitter.com/mitar_m