RE: Re: how to know when a table is altered

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= \(at)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(at)austin(dot)rr(dot)com>
>To: Vincenzo Passoli <maweb(at)hotmail(dot)com>, pgsql-sql(at)postgresql(dot)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