pg.ddx.io  pgsql-admin@postgresql.org mailing list archive  
help / color / mirror / Atom feed
From: De Leeuw Guy <G.De_Leeuw@eurofer.be>
To: pgsql-admin@postgresql.org
Subject: cache problem (v2)
Date: Mon, 16 Jul 2007 12:32:07 +0200
Message-ID: <469B4927.9030604@eurofer.be> (raw)

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



view thread (14+ messages)  latest in thread

Message-ID: <469B4927.9030604@eurofer.be>
Permalink:  ../469B4927.9030604@eurofer.be/
Also on:    postgresql.org/message-id/469B4927.9030604@eurofer.be

 · 

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-admin@postgresql.org
  Cc: G.De_Leeuw@eurofer.be
  Subject: Re: cache problem (v2)
  In-Reply-To: <469B4927.9030604@eurofer.be>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox