Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id A247E9FB5B5 for ; Tue, 17 Jul 2007 13:23:16 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 47069-08 for ; Tue, 17 Jul 2007 13:23:11 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from main2.mycybernet.net (main2.mycybernet.net [209.222.63.140]) by postgresql.org (Postfix) with ESMTP id 341B19FB592 for ; Tue, 17 Jul 2007 13:23:12 -0300 (ADT) Received: from 227-54-222-209.mycybernet.net ([209.222.54.227] helo=phlogiston.dydns.org) by main2.mycybernet.net with esmtp (Exim 4.62) (envelope-from ) id 1IAppP-0004qM-F0 for pgsql-admin@postgresql.org; Tue, 17 Jul 2007 12:23:11 -0400 Received: by phlogiston.dydns.org (Postfix, from userid 1000) id 1E5523FAF; Tue, 17 Jul 2007 12:23:06 -0400 (EDT) Date: Tue, 17 Jul 2007 12:23:06 -0400 From: Andrew Sullivan To: pgsql-admin@postgresql.org Subject: Re: cache problem (v2) Message-ID: <20070717162306.GH21688@phlogiston.dyndns.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> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <469CE4FB.9080403@eurofer.be> User-Agent: Mutt/1.5.9i X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200707/179 X-Sequence-Number: 26160 On Tue, Jul 17, 2007 at 05:49:15PM +0200, De Leeuw Guy wrote: > > Your trigger function runs inside the transaction of the calling > > statement, unless you have explicitly started a transaction. > > > Not possible from a trigger 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 true, It's my first step with postgres but also with a relationnal > database. > But I'm sure that with COPY the data are not correctly updated and > if I edit the file loaded by the COPY command and adapt it with each > line with a command INSERT all work fine. 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. A -- Andrew Sullivan | ajs@crankycanuck.ca However important originality may be in some fields, restraint and adherence to procedure emerge as the more significant virtues in a great many others. --Alain de Botton