pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: Ed Loehr <eloehr@austin.rr.com>
To: Vincenzo Passoli <maweb@hotmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: how to know when a table is altered
Date: Wed, 07 Jun 2000 21:32:37 -0500
Message-ID: <393F05C5.FBAF7029@austin.rr.com> (raw)
References: <20000607235737.76159.qmail@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.htm
	http://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



view thread (5+ messages)

Message-ID: <393F05C5.FBAF7029@austin.rr.com>
Permalink:  ../393F05C5.FBAF7029@austin.rr.com/
Also on:    postgresql.org/message-id/393F05C5.FBAF7029@austin.rr.com

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients from your mail client; the link fills in
  the headers below: reply via email
  Some clients, webmail especially, drop In-Reply-To and References from
  a link, and the reply then starts a new thread: use the mbox below.

  To: pgsql-sql@postgresql.org
  Cc: eloehr@austin.rr.com, maweb@hotmail.com
  Subject: Re: how to know when a table is altered
  In-Reply-To: <393F05C5.FBAF7029@austin.rr.com>
  References: <20000607235737.76159.qmail@hotmail.com> <393F05C5.FBAF7029@austin.rr.com>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

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