RE: Re: how to know when a table is altered - Mailing list pgsql-sql

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