471,049 Members | 1,419 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,049 software developers and data experts.

Temporarily disable rule, is this possible?

Hello,

I have a table with a rule that goes something like this:

CREATE OR REPLACE RULE sometable_delete ON DELETE DO delete FROM cache
WHERE tablename='sometable';
CREATE OR REPLACE RULE sometable_insert ON INSERT DO delete FROM cache
WHERE tablename='sometable';
CREATE OR REPLACE RULE sometable_update ON UPDATE DO delete FROM cache
WHERE tablename='sometable';

And what I have is set of objects which for certain queries will populate
a serialized variable into the cache table, like this:

INSERT INTO cache (tablename, cache_key, datavalue) VALUES ('sometable',
'some_md5_hash', 'serialized_data');
Using this method it is possible for me to just do a:

SELECT datavalue FROM cache WHERE tablename='sometable' AND
cache_key='some_md5_hash';

... and if I get an empty value for datavalue then execute the query
normally, process it, then store the serialized data back into the cache...

This all works very well for this situation, it has sped up my
application about 20 times for 95% of the requests....

The problem I have is there are frequently times I need to update
sometable and not have the rule execute, specifically when I am updating
something in the table which does not affect aggregate results.

Is there a way to cause the rules to be temporarily disabled for these
types of queries?

Thanks!

- Greg
---------------------------(end of broadcast)---------------------------
TIP 9: the planner will ignore your desire to choose an index scan if your
joining column's datatypes do not match

Nov 23 '05 #1
1 3713
ma**********@net-virtual.com ("Net Virtual Mailing Lists") wrote:
CREATE OR REPLACE RULE sometable_delete ON DELETE DO delete FROM cache
WHERE tablename='sometable';
CREATE OR REPLACE RULE sometable_insert ON INSERT DO delete FROM cache
WHERE tablename='sometable';
CREATE OR REPLACE RULE sometable_update ON UPDATE DO delete FROM cache
WHERE tablename='sometable';

And what I have is set of objects which for certain queries will populate
a serialized variable into the cache table, like this:

INSERT INTO cache (tablename, cache_key, datavalue) VALUES ('sometable',
'some_md5_hash', 'serialized_data');
Using this method it is possible for me to just do a:

SELECT datavalue FROM cache WHERE tablename='sometable' AND
cache_key='some_md5_hash';

.. and if I get an empty value for datavalue then execute the query
normally, process it, then store the serialized data back into the cache...

This all works very well for this situation, it has sped up my
application about 20 times for 95% of the requests....

The problem I have is there are frequently times I need to update
sometable and not have the rule execute, specifically when I am updating
something in the table which does not affect aggregate results.

Is there a way to cause the rules to be temporarily disabled for these
types of queries?


Not that I know of (apart from doing a remove/add). However, possibly
if you were to add a view which had update rules to do the update on the
real table as well as the cache changes you could then update the real table
manually and not affect the cache (although you could then not guarantee
that someone wouldn't do something that would affect the cache directly).
Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Sharktyyfa | last post: by
4 posts views Thread by Jon Slaughter | last post: by
1 post views Thread by =?Utf-8?B?Q2hyaXM=?= | last post: by
5 posts views Thread by shapper | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.