From: Erik Sigra <sigra@kth.se>
To: Bruno Wolff III <bruno@wolff.to>
Cc: pgsql-sql@postgresql.org
Subject: Re: Database query: Notification about change?
Date: Tue, 06 Dec 2005 02:00:18 +0100
Message-ID: <4394E2A2.1050301@kth.se> (raw)
In-Reply-To: <20051205204053.GB19356@wolff.to>
References: <4394855E.6060201@kth.se>
<20051205204053.GB19356@wolff.to>
Bruno Wolff III wrote:
>On Mon, Dec 05, 2005 at 19:22:22 +0100,
> Erik Sigra <sigra@kth.se> wrote:
>
>
>>Hi,
>>I plan to develop an application that is somewhat like a spreadsheet
>>with cells containing formulas. When a cell value is changed, things
>>must be updated. But the formulas can contain database queries, which
>>means that the cell has to be notified when the database changes in such
>>a way that the result of the query changes. How is this done? I would
>>really like to avoid recalculating the whole thing for each change to
>>the database.
>>
>>I looked in my database book and read about materialized views and
>>triggers. It seems like I should do the following:
>>1. Make a materialized view from the query.
>>2. Add a trigger for changes to this view.
>>3. Make the trigger notify the application program when it is trigged.
>>
>>Would this be possible? (I was planning to use Qt for application
>>programming and database access.)
>>
>>
>
>Postgres provides the NOTIFY command
>(http://developer.postgresql.org/docs/postgres/sql-notify.html)
>and you could use that in appropiate triggers to let your application know
>that it needs to refresh that values in at least some cells.
>You shouldn't need to use materialized views though. Just put the triggers
>on the base tables.
>
Thanks for the hint! I thought I need to put the triggers on
materialized views. If I would have to put them on the base tables, the
application program would have to understand the query to figure out
which tables it uses. I really do not want to make a parser for SQL.
What if the user of the spreadsheet-like application enters a formula
containing a query like this:
select distinct t1.person_id--, t1.sport_id, t2.sport_id
from
(select distinct sport_id, person_id
from
(-- hitta sporten för varje lopp_match
select *
from
(select lopp_match_id, sport_id
from
ensamlopp_match
natural join
ensamgrentävling
natural join
grentävling
natural join
gren)
as t
union
(select lopp_match_id, sport_id
from
laglopp_match
natural join
laggrentävling
natural join
grentävling natural
join gren))
as t
natural join
resursbokning
natural join
personresurs
natural join
resurstyp
where resurstyp_namn = 'domare')
as t1,
(select distinct sport_id, person_id
from
(-- hitta sporten för varje lopp_match
select *
from
(select lopp_match_id, sport_id
from
ensamlopp_match
natural join
ensamgrentävling
natural join
grentävling
natural join
gren)
as t
union
(select lopp_match_id, sport_id
from
laglopp_match
natural join
laggrentävling
natural join
grentävling natural
join gren))
as t
natural join
resursbokning
natural join
personresurs
natural join
resurstyp
where resurstyp_namn = 'domare')
as t2
where t1.sport_id != t2.sport_id and t1.person_id = t2.person_id;
? (Example query taken from a university course project.) I do not want
the application program to do anything more complex with the query than
adding "create view <some-name> as" in front of it and then add some
trigger or whatever.
And I think it will be much more efficient to watch a materialized view
than to rerun the query whenever one of the base tables change. That is
what materialized view optimization is about, right? A typical query
might look like this:
select sum(price * fraction_deductible) from yearxxxx_expenditures;
Thanks. I hope it is possible to understand what I wrote eventhough I do
not know that much about practical database programming.
Erik
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sigra@kth.se, bruno@wolff.to
Subject: Re: Database query: Notification about change?
In-Reply-To: <4394E2A2.1050301@kth.se>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox