Re: Watching for view changes - Mailing list pgsql-general

Hi!

Thanks for more responses.

On Thu, Dec 20, 2018 at 6:28 PM George Neuner <gneuner2@comcast.net> wrote:
> >You mean that for every SQL query I am making to a database, I would
> >manually determine which tables are involved and then setup triggers
> >with NOTIFY that the SELECT might have changed?
>
> You can just put an update trigger on every table.  You aren't forced
> to listen for notifications.

You are right. It is easier to just have triggers in advance.

> The difficulty is that views are not guaranteed to be updateable.

What you mean by that? I mean, just to be clear. I care only about
read-only views. The changes to views I care about change view because
underlying tables are updated. I do not care about UPDATE queries
against views themselves.

So you mean here that it is not guaranteed that you can make an UPDATE
query against a view? Yes, that I know. But if underlying tables
change, view is always updated, no? I mean, this is the main purpose
of a view. :-)

> As a technical matter, you can put a trigger on a view, but it may never fire.

If fires only for UPDATE queries against views themselves (and only if
INSTEAD OF trigger does not invalidate the update). But it never fires
for updates which happen because of changes to the underlying tables.
I would like to know:

a) Do people agree/think that would be a good API for my use case?
b) How hard would it be to implement/develop something like that? Is
this something PostgreSQL already knows internally and it is just a
question of exposing it?
c) Is there some better way to achieve this?

> AFAIK, update and delete triggers do work on materialized views,
> because they really are physical tables.  Depending on your use case,
> materialized views may or may not be of help to you.

Yes, materialized views are too heavy for me. But having update and
delete triggers only memory-only temporary views would be perfect.

Also, materialized views have to be manually refreshed, no? So it is
not really true that they get updated automatically (and that update
triggers would run) as soon as underling tables are modified?

> >I would like to not have to do SQL query parsing and understanding
> >on the client. So ideally, I would get information directly from
> >PostgreSQL. For me, an API where I could do AFTER UPDATE trigger on
> >FOR EACH ROW on a view would be perfect. In that trigger I could get
> >information which rows of the view changed and then use NOTIFY to
> >inform the client.
>
> You might want to use BEFORE UPDATE so you get both the old and new
> row data.

Not sure how this helps. If I have a query like (or a view defined
with such query):

SELECT "_id", "body", (SELECT row_to_json(posts) FROM posts WHERE
posts."_id"=comments."postId") AS "post" FROM comments

If I get a trigger notification that some row in "comments" table has
changed. How do I get an updated row in the query results (or the view
defined with such query). I would have to parse the SQL and figure out
how to do transformation myself, no?

So, I am not sure how triggers on underlying tables can really inform
how to know what in the view has been updated?


Mitar

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