Received: from localhost (maia-2.hub.org [200.46.204.187]) by postgresql.org (Postfix) with ESMTP id 74F589FB1BC for ; Sun, 15 Jul 2007 17:37:58 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.187]) (amavisd-maia, port 10024) with ESMTP id 88460-07 for ; Sun, 15 Jul 2007 17:37:38 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from BRUBLUmx12.mactelecom.net (BRUBLUmx12.mactelecom.net [217.64.240.4]) by postgresql.org (Postfix) with ESMTP id 7B8C49FB1BB for ; Sun, 15 Jul 2007 17:37:55 -0300 (ADT) Received: from smtp.eurofer.be (217.64.254.218.mactelecom.net [217.64.254.218]) by BRUBLUmx12.mactelecom.net (Postfix) with ESMTP id 3E0F8838069 for ; Sun, 15 Jul 2007 22:37:44 +0200 (CEST) Received: from ip-80-236-202-130.dsl.scarlet.be ([80.236.202.130] helo=[192.168.1.104]) by smtp.eurofer.be with esmtpsa (TLS-1.0:DHE_RSA_AES_256_CBC_SHA1:32) (Exim 4.63) (envelope-from ) id 1IAAqK-0000cH-9z for pgsql-admin@postgresql.org; Sun, 15 Jul 2007 22:37:41 +0200 Message-ID: <469A8580.7050907@eurofer.be> Date: Sun, 15 Jul 2007 22:37:20 +0200 From: De Leeuw Guy User-Agent: IceDove 1.5.0.10 (X11/20070329) MIME-Version: 1.0 To: pgsql-admin@postgresql.org X-Enigmail-Version: 0.94.2.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Spam_score: -4.1 X-SA-Exim-Connect-IP: 80.236.202.130 X-SA-Exim-Mail-From: G.De_Leeuw@eurofer.be Subject: cache problem (urgent) X-SA-Exim-Scanned: Yes (on smtp.eurofer.be) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/155 X-Sequence-Number: 26136 Hello all I encoured a problem with specific kind of commands a table : CREATE TABLE test ( code int8 NOT NULL, qte float8, CONSTRAINT test_pkey PRIMARY KEY (code) ) CREATE TRIGGER trig_update_sum BEFORE INSERT OR UPDATE OR DELETE ON table_test FOR EACH ROW EXECUTE PROCEDURE gd_trigfunc_before_buildsum(); EOF code maybe 1,2,3 3 = sum of 1 & 2 the trigger make the sum : if event = INSERT code = 3 ==> skip if event = INSERT code = 1 or 2 if select code 3 exist ? yes = update qte code 3 + qte code x no = insert qte to code 3 finally process the initial command pgsql test : INSERT INTO test VALUES (1, 50); INSERT INTO test VALUES (3, 60); ok code 3 = 110 psql : delete from test; code 1 & 2 deleted code 3 = 50 a flat file : 1;50 2;60 COPY test FROM '/var/lib/postgresql/flatfile' WITH DELIMITER ';'; code 1 & 2 ok code 3 = 60 maybe this is because these commands start a transaction with a cache and each time that I request a select for a sum code that are on the cache the value returned are not the real new value. two questions : It is possible to work around this ? More it is possible to optimize postgres to query the cache before querying the row on disk ? Thanks in advance for your attention (sorry my english are poor) Guy