Re: how to know when a table is altered

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