Hi!
On Thu, Dec 20, 2018 at 8:11 AM Mike Rylander <mrylander@gmail.com> wrote:
> If, as I assume, the FROM clause of the SELECT is essentially static,
> just with varying WHERE conditions, you could just use an AFTER
> INSERT/UPDATE/DELETE trigger on each of the tables involved to send a
> NOTIFY whenever data that is exposed by the SELECT is changed.
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?
I am trying to see if this could be something I could abstract out
that it would be done automatically for any query. I have looked into
using EXPLAIN to get a list of tables involved in a query, but the
issue is that it look non-trivial to determine what has really changed
as a consequence of those tables changing. I would have to cache
myself what was the result of a query and then myself do a diff? Are
there any ways to do this inside PostgreSQL without having to cache
results on the client and do it there?
> You can deliver a payload, such as the table name, primary key value and,
> with a little work, even a list of fields that were modified as a JSON
> blob, and let the application do whatever needs to be done to react
> the the changes -- issue other queries, etc.
But this would mean that I would have to know how changes on involved
tables influence query results. 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. Or even use transition
relations to get old and new state in the case FOR EACH STATEMENT (but
then I would still have to diff it probably myself). And view could
represent any query, without me having to try to understand and parse
it.
Mitar
--
http://mitar.tnode.com/
https://twitter.com/mitar_m