pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedDatabase query: Notification about change?
4+ messages / 2 participants
[nested] [flat]
* Database query: Notification about change?
@ 2005-12-05 18:22 Erik Sigra <sigra@kth.se>
2005-12-05 20:40 ` Re: Database query: Notification about change? Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 4+ messages in thread
From: Erik Sigra @ 2005-12-05 18:22 UTC (permalink / raw)
To: pgsql-sql
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.)
Thanks,
Erik
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Database query: Notification about change?
2005-12-05 18:22 Database query: Notification about change? Erik Sigra <sigra@kth.se>
@ 2005-12-05 20:40 ` Bruno Wolff III <bruno@wolff.to>
2005-12-06 01:00 ` Re: Database query: Notification about change? Erik Sigra <sigra@kth.se>
0 siblings, 1 reply; 4+ messages in thread
From: Bruno Wolff III @ 2005-12-05 20:40 UTC (permalink / raw)
To: Erik Sigra <sigra@kth.se>; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Database query: Notification about change?
2005-12-05 18:22 Database query: Notification about change? Erik Sigra <sigra@kth.se>
2005-12-05 20:40 ` Re: Database query: Notification about change? Bruno Wolff III <bruno@wolff.to>
@ 2005-12-06 01:00 ` Erik Sigra <sigra@kth.se>
2005-12-06 03:35 ` Re: Database query: Notification about change? Bruno Wolff III <bruno@wolff.to>
0 siblings, 1 reply; 4+ messages in thread
From: Erik Sigra @ 2005-12-06 01:00 UTC (permalink / raw)
To: Bruno Wolff III <bruno@wolff.to>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: Database query: Notification about change?
2005-12-05 18:22 Database query: Notification about change? Erik Sigra <sigra@kth.se>
2005-12-05 20:40 ` Re: Database query: Notification about change? Bruno Wolff III <bruno@wolff.to>
2005-12-06 01:00 ` Re: Database query: Notification about change? Erik Sigra <sigra@kth.se>
@ 2005-12-06 03:35 ` Bruno Wolff III <bruno@wolff.to>
0 siblings, 0 replies; 4+ messages in thread
From: Bruno Wolff III @ 2005-12-06 03:35 UTC (permalink / raw)
To: Erik Sigra <sigra@kth.se>; +Cc: pgsql-sql
On Tue, Dec 06, 2005 at 02:00:18 +0100,
Erik Sigra <sigra@kth.se> wrote:
> 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:
I would say, that this is a really odd thing to do. If you are going to allow
any old user written query, you are going to find it hard to update the cells
at the time the data changes. It might be better to have the user hit a
refresh button or to have timed refreshes.
> 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
Its pretty much the same thing. The materialized view is going to be
maintained by triggers. Postgres doesn't have a built in way to automate
this, so the application would have to parse the queries and create the
trigger functions.
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2005-12-06 03:35 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-05 18:22 Database query: Notification about change? Erik Sigra <sigra@kth.se>
2005-12-05 20:40 ` Bruno Wolff III <bruno@wolff.to>
2005-12-06 01:00 ` Erik Sigra <sigra@kth.se>
2005-12-06 03:35 ` Bruno Wolff III <bruno@wolff.to>
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