Re: Watching for view changes

Hi!

On Sun, Dec 23, 2018 at 1:00 AM George Neuner <gneuner2(at)comcast(dot)net> wrote:
> A spreadsheet is an order of magnitude simpler to implement than a
> DBMS, but the combination would be an order of magnitude (or more)
> harder. Even object graph databases don't offer spreadsheet
> functionality, and it would be a lot easier to do there than in a
> table relational system.

But having that readily available would be so cool. :-) Especially
because it is hard. And also, it does not necessary have to be full
graph. Just one level of dependencies. Then those are recomputed. And
then anything depending on now changed values get recomputed again.
And so on. So no need to traverse the whole graph at once.

> In PG, all *named* tables are backed on disk - even temporary tables.
> Only anonymous tables of query results can exist entirely in memory
> [and even they can spill onto disk when necessary].
>
> With enough memory you can cache all your tables in shared buffers and
> have enough extra that you never run out of work buffers and never
> overflow a work buffer. But that is the best you can achieve with PG.

Thanks for all this input. So I am now thinking in terms of a
materialized views.

For my case it would be great if materialized views could be TEMPORARY
(removed at the end of session), in-memory (and if PostgreSQL runs out
of buffer space for it, I would prefer an error, instead of spilling
to the disk), and UNLOGGED. Any particular reasons which would prevent
them to be implemented as such? I through that materialized views are
just a fancy table with stored query, so that you can easily REFRESH
them, instead of doing that yourself.

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.

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. 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. Any other suggestion how to do that? I looked into
PUBLICATION and SUBSCRIBE, but it seems this is only supported between
servers, not server-client, and also works only on base tables, not
materialized views (not sure again why, because aren't materialized
views just tables). Would it be possible to use client to subscribe
instead of a server?

[1] https://onethingsimple.com/2017/10/sync-materialized-views-after-debounce-period/

Mitar

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