Skip site navigation (1) Skip section navigation (2)

cache problem (v2)

From: De Leeuw Guy <G(dot)De_Leeuw(at)eurofer(dot)be>
To: pgsql-admin(at)postgresql(dot)org
Subject: cache problem (v2)
Date: 2007-07-16 10:32:07
Message-ID: 469B4927.9030604@eurofer.be (view raw or flat)
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

Responses

pgsql-admin by date

Next:From: Jayakumar_MukundarajuDate: 2007-07-16 11:51:58
Subject: Re: PgAgent Job Scheduling-Backup
Previous:From: Jayakumar_MukundarajuDate: 2007-07-16 10:18:10
Subject: Re: PgAgent Job Scheduling-Backup

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group