Re: cache problem (v2) - Mailing list pgsql-admin
From | Andrew Sullivan |
---|---|
Subject | Re: cache problem (v2) |
Date | |
Msg-id | 20070717192156.GP21688@phlogiston.dyndns.org Whole thread Raw |
In response to | Re: cache problem (v2) (De Leeuw Guy <G.De_Leeuw@eurofer.be>) |
Responses |
Re: cache problem (v2)
|
List | pgsql-admin |
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@crankycanuck.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
pgsql-admin by date: