Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id C20529FBA12 for ; Tue, 17 Jul 2007 15:45:36 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 29576-01 for ; Tue, 17 Jul 2007 15:45:34 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from BRUBLUmx12.mactelecom.net (BRUBLUmx12.mactelecom.net [217.64.240.4]) by postgresql.org (Postfix) with ESMTP id 18CEB9FB9A6 for ; Tue, 17 Jul 2007 15:45:33 -0300 (ADT) Received: from smtp.eurofer.be (217.64.254.218.mactelecom.net [217.64.254.218]) by BRUBLUmx12.mactelecom.net (Postfix) with ESMTP id 1D731838002; Tue, 17 Jul 2007 20:45:32 +0200 (CEST) Received: from ip-80-236-221-18.dsl.scarlet.be ([80.236.221.18] helo=[192.168.1.105]) by smtp.eurofer.be with esmtpsa (TLS-1.0:DHE_RSA_AES_256_CBC_SHA1:32) (Exim 4.63) (envelope-from ) id 1IAs2s-0003Je-N6; Tue, 17 Jul 2007 20:45:31 +0200 Message-ID: <469D0E39.7040805@eurofer.be> Date: Tue, 17 Jul 2007 20:45:13 +0200 From: De Leeuw Guy User-Agent: IceDove 1.5.0.10 (X11/20070329) MIME-Version: 1.0 To: Andrew Sullivan CC: pgsql-admin@postgresql.org References: <469B4927.9030604@eurofer.be> <20070716170957.GV18049@phlogiston.dyndns.org> <469BAF1D.8030108@eurofer.be> <20070716184837.GB18049@phlogiston.dyndns.org> <469BD389.4030606@eurofer.be> <20070716203641.GF18049@phlogiston.dyndns.org> <469C5AC4.7040903@eurofer.be> <20070717152848.GD21688@phlogiston.dyndns.org> <469CE4FB.9080403@eurofer.be> <20070717162306.GH21688@phlogiston.dyndns.org> In-Reply-To: <20070717162306.GH21688@phlogiston.dyndns.org> X-Enigmail-Version: 0.94.2.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit X-Spam_score: -3.8 X-SA-Exim-Connect-IP: 80.236.221.18 X-SA-Exim-Mail-From: G.De_Leeuw@eurofer.be Subject: Re: cache problem (v2) X-SA-Exim-Scanned: Yes (on smtp.eurofer.be) X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/184 X-Sequence-Number: 26165 > Yes, sorry, I phrased that wrong. Let me put it differently: your > trigger runs only inside the transaction of the calling statement, > unless that statement itself is inside a longer explicitly-called > transaction. For example: > > t1 t2 > > BEGIN > UPDATE table_with_trigger > SELECT something SELECT ...FROM trigger_effect > INSERT something else > COMMIT > > In this case, t2 does _not_ see the effects of the trigger in t1, > because those effects are not visible until the COMMIT. But > > t1 t2 > > UPDATE table_with_trigger > SELECT something SELECT...FROM trigger_effect > INSERT something else > > in this case, t2 _does_ see the effects, because the trigger's > effects are COMMITted implicitly after the UPDATE statement. > Yes, that I understand, but it's not my case I have : table test ( int code, int qte); t1 INSERT test values(1, 150) call my trigger that SELECT WHERE code=3 (does not exist) and INSERT INTO test (code=3, qte=150) INSERT test values(2, 450) call my trigger that SELECT WHERE code = 3 (exist) and UPDATE test (code=3, qte=600) .... Ok all work fine Now I have a flat file : 1,150 2,450 COPY .... path_to_this_flat_file code=3,qte=450 Why ? Another error also is "duplicate key" It's like that, into my trigger, the second SELECT return 0 rows (SPI_processed = 0) and in this case the trigger try to INSERT instead of UPDATE I loose one week with this problem. > If I read that right, you admit that you are inexperienced with the > concepts and the software, and you are unable to show us all the > relevant code or send us a precise description of what you are > doing; but, you are convinced nevertheless that the problem is a bug > or deficiency in PostgreSQL that nobody else seems to be having, and > not a problem with your approach? I suggest you think again. > I read the documentation from postgres not all. But sufficiently to start a test of a trigger. but sure, I'm not a veteran :-) I never say that it's a bug, I say that with COPY the trigger does not work like with INSERT. The event received by the trigger are the same no ? And it's what I try to understand why the comportment of my trigger change when the call come from COPY or from a lot of INSERT it's all. You give me a way with the COMMITTED READ. I try to explain more : (tomorrow I try to put on our web site a full example and data) I work on a statics project, before this project are builded in 1990 with CIsam from informix. In 2004 I migrate the development from CIsam to berkeleyDB. Now to allow more possibilities for our users (like connection from OOo base, calc and so on) I try to migrate the model to postgres The problem is that I receive about 4 millions of data by month. and to speed up the major type of query called by our users I build a sum of different items. example : Origins (company a, company b,....) I build a "Total Eurofer" Market (France, Belgium, ....) I build a "Total All Markets" Products (product a, product b) total product a+b In real world : for the Import data : 30 Origins, 249 Markets, 1472 products, 360 periods (like 1999-01) The sum are about 3 Origins, 28 Markets, 158 products This is the job of my trigger : build the sum code to speed up the standard query of our users. for each origins insert/update the base code check if this code update a sum code if yes insert/update the sum code for each market insert/update the base code check if this code update a sum code if yes do it ... finally from about 4 millions of input data I output about 16 millions of records Sorry if I disturb you. Regards Guy