Re: cache problem (v2)

On Tue, Jul 17, 2007 at 08:45:13PM +0200, De Leeuw Guy wrote:
> I have :
> table test ( int code, int qte);

Right, I remember that part.

> t1

Is that BEGIN;? If not, this is _not_ one transaction. It's two.

> INSERT test values(1, 150)
> call my trigger that SELECT WHERE code=3 (does not exist) and INSERT

You're not actually _calling_ the trigger, right? It just happens
automatically? Also, I don't have the slightest clue how this code=3
(does not exist) works. AFAICT code=1. So code=3 is never true, no?

> Now I have a flat file :
> 1,150
> 2,450
>
> COPY .... path_to_this_flat_file
>
> code=3,qte=450
> Why ?

Because it's all one transaction, and because there's more than one
state your variables could resolve to, only one of them actually
applies is my _guess_. It's hard for me to say with any more detail
without the code and the schema. Anyway, you have two transactions
in your first example, it appears. The COPY statement is only one.

> Another error also is "duplicate key"

This is a different problem. Where is it coming from? Anyway, you
have some sort of collision there, as the result I assume of your
modifications of the data. Does it only happen with the COPY case?
If so, that's another clue that the trigger function is not doing
what you think it is.

> I read the documentation from postgres not all. But sufficiently to
> start a test of a trigger.

But apparently not a successful one ;-) I'm just saying, it seems to
me that you have a deep misunderstanding of the way transaction scope
works. I think you need to have another look at that. I also think
you need to look a little harder at how COPY works as compared to
INSERT.

> I never say that it's a bug, I say that with COPY the trigger does not
> work like with INSERT.

I think it does, and I think your problem is coming from your
misunderstanding of how the trigger is working. But I still don't
have a clear handle on what you're trying to do. One way to try this
is to put your two INSERT statements into one transaction. If things
are really as you seem to have described them (two insert statements,
two lines in the COPY file), then if you do BEGIN;INSERT..;INSERT..;
COMMIT you should get the same problem you do with COPY.

> and to speed up the major type of query called by our users I build a
> sum of different items.

I can see why you'd do that (although it seems like a
pre-optimisation to me -- you might find that PostgreSQL is fast
enough to do this without the precompiled number).

> This is the job of my trigger : build the sum code to speed up the
> standard query of our users.

Right. Like I said, I think you have a problem, likely in your
function code, that is causing only one criterion to evaluate to TRUE
when you think two of them should.

A

--
Andrew Sullivan | ajs(at)crankycanuck(dot)ca
A certain description of men are for getting out of debt, yet are
against all taxes for raising money to pay it off.
--Alexander Hamilton