How to implement table caching

From: "Andrus Moor" <eetasoft(at)online(dot)ee>
To: pgsql-general(at)postgresql(dot)org
Subject: How to implement table caching
Date: 2005-08-14 18:12:11
Message-ID: ddo22k$gn7$1@news.hub.org
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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.

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Richard Huxton 2005-08-14 18:15:10 Re:
Previous Message Adam O'Toole 2005-08-14 17:03:23 Re: [despammed] Re: [GENERAL]