cache problem (urgent)

From: De Leeuw Guy <G(dot)De_Leeuw(at)eurofer(dot)be>
To: pgsql-admin(at)postgresql(dot)org
Subject: cache problem (urgent)
Date: 2007-07-15 20:37:20
Message-ID: 469A8580.7050907@eurofer.be
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-admin

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

Browse pgsql-admin by date

  From Date Subject
Next Message Anoo Sivadasan Pillai 2007-07-16 04:43:44 Re: Several postgres installation on windows possible?
Previous Message Srinivas Kotapally 2007-07-13 19:01:13 Re: Anyone seen this message?