Обсуждение: the best way to catch table modification
Hello, I'm implementing db-queries caching system - for this I need to know each table's modification time (or at least modification counter). I know that I can make a statement-level trigger, which will update a table with tables modification times - however this is inefficient if many inserts or updates are made on single table (in single transaction). The best would be some kind of transaction-level trigger, but this is not available. Are there any other, better options ? Thanks in advance. ML
Hi, I posted on the same subject a month ago . .you can search for the current title in the JDBC mailing list [JDBC] implementing asynchronous notifications PLEASE CONFIRM MY I ended using statement-level trigger. I haven't found another way to do it . Regards /David Marek Lewczuk wrote: > Hello, > I'm implementing db-queries caching system - for this I need to know > each table's modification time (or at least modification counter). I > know that I can make a statement-level trigger, which will update a > table with tables modification times - however this is inefficient if > many inserts or updates are made on single table (in single > transaction). The best would be some kind of transaction-level > trigger, but this is not available. Are there any other, better options ? > > Thanks in advance. > ML > > > > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match >
Am Dienstag, 25. Oktober 2005 19:40 schrieb David Gagnon: > Hi, > > I posted on the same subject a month ago . .you can search for the > current title in the JDBC mailing list > [JDBC] implementing asynchronous notifications PLEASE CONFIRM MY > > I ended using statement-level trigger. I haven't found another way to > do it . > > Regards > /David > > Marek Lewczuk wrote: > > Hello, > > I'm implementing db-queries caching system - for this I need to know > > each table's modification time (or at least modification counter). I > > know that I can make a statement-level trigger, which will update a > > table with tables modification times - however this is inefficient if > > many inserts or updates are made on single table (in single > > transaction). The best would be some kind of transaction-level > > trigger, but this is not available. Are there any other, better options ? What did you mean with "many inserts or updates"? Did you mean statements which modify or insert many rows but are still one single statement: you could use rules instead. I think rules are much more powerful than triggers and they are much faster if a statement affects many rows. Triggers fires for each row, rules are just modifying the original statement. so usually if you can handle the load of the statements you should handle the loads of statements rewritten by rules, too. Rules are usually much more efficient than triggers. And they are much more relational in my opinion. kind regards, janning