How to implement table caching
To increase performance, I'm thinking about storing copies of less
frequently changed tables in a client computer.
At startup client application compares last change times and downloads newer
tables from server.
CREATE TABLE lastchange (
tablename CHAR(8) PRIMARY KEY,
lastchange timestamp without time zone );
INSERT INTO lastupdated (tablename) values ('mytable1');
....
INSERT INTO lastupdated (tablename) values ('mytablen');
CREATE OR REPLACE FUNCTION setlastchange() RETURNS "trigger"
AS $$BEGIN
UPDATE lastchange SET lastchange='now' WHERE tablename=TG_RELNAME;
RETURN NULL;
END$$ LANGUAGE plpgsql STRICT;
CREATE TRIGGER mytable1_trig BEFORE INSERT OR UPDATE OR DELETE ON mytable1
EXECUTE PROCEDURE setlastchange();
....
CREATE TRIGGER mytablen_trig BEFORE INSERT OR UPDATE OR DELETE ON mytablen
EXECUTE PROCEDURE setlastchange();
Is table caching good idea?
Is this best way to implement table caching ?
Andrus.
Andrus,
You might consider something like materialized views:
http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html
Whether table caching is a good idea depends completely on the
demands of your application.
--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLC
Strategic Open Source: Open Your i™
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)
On Aug 14, 2005, at 1:12 PM, Andrus Moor wrote:
Show quoted text
To increase performance, I'm thinking about storing copies of less
frequently changed tables in a client computer.
At startup client application compares last change times and
downloads newer
tables from server.CREATE TABLE lastchange (
tablename CHAR(8) PRIMARY KEY,
lastchange timestamp without time zone );INSERT INTO lastupdated (tablename) values ('mytable1');
....
INSERT INTO lastupdated (tablename) values ('mytablen');CREATE OR REPLACE FUNCTION setlastchange() RETURNS "trigger"
AS $$BEGIN
UPDATE lastchange SET lastchange='now' WHERE tablename=TG_RELNAME;
RETURN NULL;
END$$ LANGUAGE plpgsql STRICT;CREATE TRIGGER mytable1_trig BEFORE INSERT OR UPDATE OR DELETE ON
mytable1
EXECUTE PROCEDURE setlastchange();
....
CREATE TRIGGER mytablen_trig BEFORE INSERT OR UPDATE OR DELETE ON
mytablen
EXECUTE PROCEDURE setlastchange();Is table caching good idea?
Is this best way to implement table caching ?Andrus.
There is also http://people.freebsd.org/~seanc/pgmemcache/
On Mon, Aug 15, 2005 at 04:54:31PM -0500, Thomas F. O'Connell wrote:
Andrus,
You might consider something like materialized views:
http://jonathangardner.net/PostgreSQL/materialized_views/matviews.html
Whether table caching is a good idea depends completely on the
demands of your application.--
Thomas F. O'Connell
Co-Founder, Information Architect
Sitening, LLCStrategic Open Source: Open Your i?
http://www.sitening.com/
110 30th Avenue North, Suite 6
Nashville, TN 37203-6320
615-469-5150
615-469-5151 (fax)On Aug 14, 2005, at 1:12 PM, Andrus Moor wrote:
To increase performance, I'm thinking about storing copies of less
frequently changed tables in a client computer.
At startup client application compares last change times and
downloads newer
tables from server.CREATE TABLE lastchange (
tablename CHAR(8) PRIMARY KEY,
lastchange timestamp without time zone );INSERT INTO lastupdated (tablename) values ('mytable1');
....
INSERT INTO lastupdated (tablename) values ('mytablen');CREATE OR REPLACE FUNCTION setlastchange() RETURNS "trigger"
AS $$BEGIN
UPDATE lastchange SET lastchange='now' WHERE tablename=TG_RELNAME;
RETURN NULL;
END$$ LANGUAGE plpgsql STRICT;CREATE TRIGGER mytable1_trig BEFORE INSERT OR UPDATE OR DELETE ON
mytable1
EXECUTE PROCEDURE setlastchange();
....
CREATE TRIGGER mytablen_trig BEFORE INSERT OR UPDATE OR DELETE ON
mytablen
EXECUTE PROCEDURE setlastchange();Is table caching good idea?
Is this best way to implement table caching ?Andrus.
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com 512-569-9461