X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (neptune.hub.org [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id C0025D1D168 for ; Mon, 26 Jan 2004 14:40:39 +0000 (GMT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 69192-09 for ; Mon, 26 Jan 2004 10:40:09 -0400 (AST) Received: from bramble.mmrd.com (unknown [65.217.53.66]) by svr1.postgresql.org (Postfix) with ESMTP id 25144D1CC98 for ; Mon, 26 Jan 2004 10:39:50 -0400 (AST) Received: from thorn.mmrd.com (thorn.mmrd.com [172.25.10.100]) by bramble.mmrd.com (8.12.8/8.12.8) with ESMTP id i0QDrCcM030913; Mon, 26 Jan 2004 08:53:13 -0500 Received: from gnvex001.mmrd.com (gnvex001.mmrd.com [192.168.3.55]) by thorn.mmrd.com (8.11.6/8.11.6) with ESMTP id i0QEUZl30844; Mon, 26 Jan 2004 09:30:36 -0500 Received: from camel.mmrd.com ([172.25.5.213]) by gnvex001.mmrd.com with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2657.72) id XT88B06V; Mon, 26 Jan 2004 09:30:34 -0500 Subject: Re: Selects query inside function must read commited data From: Robert Treat To: Andrew Biagioni Cc: aspire420@hotpop.com, Oliver Elphick , pgsql-admin@postgresql.org In-Reply-To: <40140645.2090404@e-greek.net> References: <40138377.2070503@sancharnet.in> <1075047334.1491.16.camel@linda.lfix.co.uk> <4013EF71.9080603@sancharnet.in> <40140645.2090404@e-greek.net> Content-Type: text/plain Content-Transfer-Encoding: 7bit X-Mailer: Ximian Evolution 1.0.8 Date: 26 Jan 2004 09:30:35 -0500 Message-Id: <1075127435.22457.21032.camel@camel> Mime-Version: 1.0 X-Virus-Scanned: by amavisd-new at postgresql.org X-Archive-Number: 200401/213 X-Sequence-Number: 12076 All this is well and good, but inside a function you might not see data that is committed since the query snapshot is not updated within a function. Search on setQuerySnapshot in the pgsql-bugs list and I think you'll find more info. So far no one has come up with the evidence needed to convince the core developers to change this behavior. Robert Treat On Sun, 2004-01-25 at 13:09, Andrew Biagioni wrote: > In the v7.3 docs: > > http://www.postgresql.org/docs/7.3/interactive/transaction-iso.html#XACT > -READ-COMMITTED > T-READ-COMMITTED> > > Read Committed is the default isolation level in PostgreSQL. When a > transaction runs on this isolation level, a SELECT query sees only data > committed before the query began; it never sees either uncommitted data > or changes committed during query execution by concurrent transactions. > (However, the SELECT does see the effects of previous updates executed > within its own transaction, even though they are not yet committed.) In > effect, a SELECT query sees a snapshot of the database as of the instant > that that query begins to run. Notice that two successive SELECTs can > see different data, even though they are within a single transaction, if > other transactions commit changes during execution of the first SELECT. > > Andrew > > V i s h a l Kashyap @ [Sai Hertz And Control Systems] wrote: > > > Dear Oliver Elphick , > > > If I am not wrong PostgreSQL select statements works on committed data > > thus the new > > inserted data at #3 must not be available to sai_func_b() till #5 > > > > > > No. All operations by a single transaction are visible inside the > > transaction. Therefore an encapsulated function can see anything already > > done in the whole transaction. A function must be entirely inside a > > transaction and cannot start a transaction, so there can never be any > > problem about this. > > > > Loads of thanks for the kind reply . > Would be greatefull if you kindly post some links/refrences to support > your answer for the said. > Thanks one again . > > > > -- > > Regards, > > Vishal Kashyap > > > > ~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* > > I Know you believe my words so logon to Jabber.org > > and add vishalkashyap@jabber.org to > your roster. > > OR > > Seek Me at 264360076 > > ~*~*~*~*~*~*~*~* > > I am usually called as Vishal Kashyap > > but my Girlfriend calls me as Vishal CASH UP. > > This is because others identify me because of my generosity > > but my Girlfriend identify me because of my CASH. > > ~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* > > > -- Build A Brighter Lamp :: Linux Apache {middleware} PostgreSQL