Thread: how to know when a table is altered
i'm developing a framework (mod_perl+apache) that reads the db-schema and explode html forms. now i read the schema and cache it into perl-hashes to speedup things. my problem is to recognise when a table is altered so that the framework can update the related forms connected to the db tables. i don't want to read the schema every time. How can i implement this ? thank you in advance for your help valter, italy ________________________________________________________________________ Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
Vincenzo Passoli wrote: > > i'm developing a framework (mod_perl+apache) that reads the db-schema and > explode html forms. > > now i read the schema and cache it into perl-hashes to speedup things. > > my problem is to recognise when a table is altered so that the framework can > update the related forms connected to the db tables. > i don't want to read the schema every time. > > How can i implement this ? My sub-optimal approach was to cache all of the generally static tables (requiring a restart to reload them if they changed), and query the rest. You can avoid a lot of joins by querying the db for the foreign keys to static tables and then looking them up only in the app cache. But caching query results and invalidating them when the underlying tables changed would greatly simplify my app and speed things up, so I'd love to hear if others have a better/faster solution here. Regards, Ed Loehr
Ed Loehr wrote: > > Vincenzo Passoli wrote: > > > > i'm developing a framework (mod_perl+apache) that reads the db-schema and > > explode html forms. > > > > now i read the schema and cache it into perl-hashes to speedup things. > > > > my problem is to recognise when a table is altered so that the framework can > > update the related forms connected to the db tables. > > i don't want to read the schema every time. > > > > How can i implement this ? > > My sub-optimal approach was to cache all of the generally static tables > (requiring a restart to reload them if they changed), and query the > rest. You can avoid a lot of joins by querying the db for the foreign > keys to static tables and then looking them up only in the app cache. > But caching query results and invalidating them when the underlying > tables changed would greatly simplify my app and speed things up, so I'd > love to hear if others have a better/faster solution here. I was thinking about another possible approach (and definitely half-baked). I'd call it "table-based caching". Suppose you created a table specifically for tracking how recently a table had been updated, e.g., create table table_status ( tablename varchar not null unique, last_change timestamp not null); Then create triggers for every table that updated table_status.last_change = now() on every UPDATE/DELETE/INSERT. Then, to determine when you need to invalidate the application cache, you'd load this table at the beginning of the request and invalidate cache entries involving tables with table_status.last_change more recent than when the query results were cached. If, like most DBs yours is mostly reads, you'd suffer one pretty light DB query in order to validate your cache on each request. Then, each That would be a significant hit on big changes involving many records. But where that's unusual, it might be a big win. There are a lot of gotchas with this approach (figuring out the query-table dependencies, etc.), but it seems possible. BTW, I thinking server-side caching is the optimal solution here. I previously lobbied -hackers for implementing a server-side result-set cache in which entire query result sets could be cached (up to a configurable limit) and returned immediately when none of the underlying tables had changed (http://www.deja.com/viewthread.xp?AN=602084610&search=thread). I still think that would be a huge performance win in the vast majority of systems (including mine), but it is not supposedly not trivial. The idea won absolutely no fans among the developers/hackers. There was some talk about caching the query plans, but I think that ultimately got dismissed as well. I wish I had time to work on this one. Regards, Ed Loehr
hello Ed Loehr,
1.your solution (table-based caching) is very close to my actual thinking.
2.Another problem is the 'alter table' command.
3.SQL does't have a TRIGGER on this 'event' (CREATE TRIGGER mytrig ON
mytable FOR ALTER AS ...). Can be Added ?
4.May be beautiful if the db tells to the app when a trigger is fired, so
the app can update thing without go crazy with asking that to the db every
time. Is there a solution?
5.For the query table dependencies (a proposal, i've not used this
solution!):
$sql= "select a.f1,a.f2,b.f3,c.f4 from t1 as a, t2 as b, t3 as c where
...."
we can extract the tables used in a query
instead of writing $sql=as before, write a thing similar to (supposing
DBI+perl+mod_perl)
my @array;
my $ptr_array= \@array;
$sql = "select a.f1,a.f2,b.f3,c.f4 from
".&add_check_table('t1',$ptr_array)." as a,". &add_check_table('t2',$ptr_array) . " as b, ....
---> &add_check_table=sub to push table to check in the array @array, return
the name of the table, i.e. t1, t2 ...
then
call &do_check ($ptr_array)
using table_status, the sub do_check return 1 if min(last_changes for every
table in @array) is older that the caching of this query results, we must
have the query result somewhere (on ( properly locked) files?) and the last
time we perfomed the query.
then
if (&do_check($ptr_array)){ fetch rows store in cache
}
-->use the cache
Probably this solution must be used when is logical to be used.
bye,
valter
>From: Ed Loehr <eloehr@austin.rr.com>
>To: Vincenzo Passoli <maweb@hotmail.com>, pgsql-sql@postgresql.org
>Subject: Re: [SQL] how to know when a table is altered
>Date: Wed, 07 Jun 2000 11:49:55 -0500
>
>Ed Loehr wrote:
> >
> > Vincenzo Passoli wrote:
> > >
> > > i'm developing a framework (mod_perl+apache) that reads the db-schema
>and
> > > explode html forms.
> > >
> > > now i read the schema and cache it into perl-hashes to speedup things.
> > >
> > > my problem is to recognise when a table is altered so that the
>framework can
> > > update the related forms connected to the db tables.
> > > i don't want to read the schema every time.
> > >
> > > How can i implement this ?
> >
> > My sub-optimal approach was to cache all of the generally static tables
> > (requiring a restart to reload them if they changed), and query the
> > rest. You can avoid a lot of joins by querying the db for the foreign
> > keys to static tables and then looking them up only in the app cache.
> > But caching query results and invalidating them when the underlying
> > tables changed would greatly simplify my app and speed things up, so I'd
> > love to hear if others have a better/faster solution here.
>
>I was thinking about another possible approach (and definitely
>half-baked). I'd call it "table-based caching". Suppose you created a
>table specifically for tracking how recently a table had been updated,
>e.g.,
>
> create table table_status (
> tablename varchar not null unique,
> last_change timestamp not null
> );
>
>Then create triggers for every table that updated
>table_status.last_change = now() on every UPDATE/DELETE/INSERT. Then, to
>determine when you need to invalidate the application cache, you'd load
>this table at the beginning of the request and invalidate cache entries
>involving tables with table_status.last_change more recent than when the
>query results were cached.
>
>If, like most DBs yours is mostly reads, you'd suffer one pretty light DB
>query in order to validate your cache on each request. Then, each That
>would be a significant hit on big changes involving many records. But
>where that's unusual, it might be a big win. There are a lot of gotchas
>with this approach (figuring out the query-table dependencies, etc.), but
>it seems possible.
>
>BTW, I thinking server-side caching is the optimal solution here. I
>previously lobbied -hackers for implementing a server-side result-set
>cache in which entire query result sets could be cached (up to a
>configurable limit) and returned immediately when none of the underlying
>tables had changed
>(http://www.deja.com/viewthread.xp?AN=602084610&search=thread). I still
>think that would be a huge performance win in the vast majority of
>systems (including mine), but it is not supposedly not trivial. The idea
>won absolutely no fans among the developers/hackers. There was some talk
>about caching the query plans, but I think that ultimately got dismissed
>as well. I wish I had time to work on this one.
>
>Regards,
>Ed Loehr
________________________________________________________________________
Get Your Private, Free E-mail from MSN Hotmail at http://www.hotmail.com
Vincenzo Passoli wrote: > > 3.SQL does't have a TRIGGER on this 'event' (CREATE TRIGGER mytrig ON > mytable FOR ALTER AS ...). Can be Added ? I don't know. Maybe someone else does (though I think pgsql-sql is very low volume...pgsql-general would get a lot more readers). > 4.May be beautiful if the db tells to the app when a trigger is fired, so > the app can update thing without go crazy with asking that to the db every > time. Is there a solution? Maybe. Check out NOTIFY (and LISTEN) at http://www.postgresql.org/docs/postgres/sql-listen.htmhttp://www.postgresql.org/docs/postgres/sql-notify.htm I haven't tried it, not sure it fits into DBI's API or model. I'd like to hear if you use it with success (or anyone else who is already using it successfully within modperl/DBI). If it turns out that DBI can't handle LISTEN/NOTIFY, it might be possible to do it through a 3rd app that somehow listens and signals the modperl servers (yuck). > 5.For the query table dependencies (a proposal, i've not used this > solution!): > > $sql= "select a.f1,a.f2,b.f3,c.f4 from t1 as a, t2 as b, t3 as c where > ...." > we can extract the tables used in a query > > instead of writing $sql=as before, write a thing similar to (supposing > DBI+perl+mod_perl) > > my @array; > my $ptr_array= \@array; > > $sql = "select a.f1,a.f2,b.f3,c.f4 from > ".&add_check_table('t1',$ptr_array)." as a,". > &add_check_table('t2',$ptr_array) . " as b, .... > > ---> &add_check_table=sub to push table to check in the array @array, return > the name of the table, i.e. t1, t2 ... > > then > > call &do_check ($ptr_array) > using table_status, the sub do_check return 1 if min(last_changes for every > table in @array) is older that the caching of this query results, we must > have the query result somewhere (on ( properly locked) files?) and the last > time we perfomed the query. > > then > > if (&do_check($ptr_array)){ > fetch rows > store in cache > } > -->use the cache I haven't seen that syntax before with your use of "as", but I get your gist. Sounds reasonable, though it looks like a major pain, stealing most of the pleasure and convenience of SQL. I'd almost be tempted to build a regex'er to pick out the table names from each query in a layer between DBI and the app until the regex performance became an issue. Regards, Ed Loehr