On Sun, 23 Dec 2018 10:10:50 -0800, Mitar <mmitar@gmail.com> wrote:
>I can then wire triggers on underlying tables to REFRESH materialized
>views automatically. Is there some easy way to debounce those
>refreshes? If I just blindly trigger REFRESH in every row trigger,
>this could do a lot of work. I would prefer to combine all changes for
>example into 100 ms batches and refresh only once per 100 ms. So in
>some way I would like to be able to control the level of real-time I
>would need. I have found a blog post [1] about this, but it seems
>pretty tricky and requires an outside cron job. For 100 ms batching
>time this feels like something better done inside PostgreSQL itself.
see the pg_cron extension.
But cron doesn't allow sub-second scheduling. Why do you need it so
frequently?
PG is not a real time platform, or even an all-in-memory database. If
you try to use it that way, you are guaranteed to have problems.
>The last question is how do I get changes in materialized views
>streamed to the client. It seems one option is a trigger on the
>materialized view which uses NOTIFY to tell the client about the
>change. But NOTIFY has limit on the payload size, so I cannot just
>send the change to the client.
If you trigger by row, the NOTIFY payload is just that one row. If
the data can't fit into the 8K payload, then you need to send some
kind of row id and have the client read the changes explicitly.
Also remember that you may have to deal with DELETEd rows. If you
can't send row data by NOTIFY, then the client HAS to cache the whole
view anyway to see what's been deleted.
>It seems I would need additional table
>to store the change and then client would get notification, read from
>that table, and remove the rows read. So in a way I would need my own
>queue for changes.
There's no reason to do that. The client has to interpret the view
changes and incorporate them into its own local data structures.
Segregating new/modified rows separately on the server side seems to
me to be a waste of effort.
If it makes sense, have the client collect some number of
notifications and read all the indicated rows in one query.
George