RE: time stops within transaction - Mailing list pgsql-hackers

From Hiroshi Inoue
Subject RE: time stops within transaction
Date
Msg-id EKEJJICOHDIEMGPNIFIJOEOGCJAA.Inoue@tpf.co.jp
Whole thread Raw
In response to Re: time stops within transaction  (Tom Lane <tgl@sss.pgh.pa.us>)
List pgsql-hackers
> -----Original Message-----
> From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
> 
> 
> > I just remembered a report from Forest Wilkinson
> > about a month ago [SQL] SQL functions not locking
> > properly?
> 
> Yes, that was on my to-look-at list too.  Not sure if it's related.
> 

As I replied to his posting,the cause is obvious.
Because the queries in a function are executed under
the same snapshot,SELECT statements never
see the changes made by other backends.
OTOH SELECT .. FOR UPDATE has a different visiblity
from simple SELECT. Yes,SELECT .. FOR UPDATE
doesn't guarantee read consistency because it has to
acquire a lock on the latest tuples.
I recommended to use SELECT .. FOR UPDATE then
but it's far from being reasonable.

> > Don't we have to distiguish simple procedure calls
> > (select func();) and function calls as a part of a query ?
> 
> "select func()" looks like a query to me.  I don't see how you are going
> to make such a distinction in a useful way.  If we had a CALL statement
> distinct from function invocation in expressions, then maybe it'd make
> sense for that context to act differently.
>

As I mentioned before,calling functions which have strong side effect e.g.  select strong_effect(column1), column2 from
table1where ...;
 
is a problem. IMHO the use of functions should be restricted.
Of cource,we have to call(execute)procedures which change
the database. Unfortunately we don't have a command to call
(execute) functions as procedures currently.

Regards.
Hiroshi Inoue


pgsql-hackers by date:

Previous
From: "Matthew H. North"
Date:
Subject: RE: [ADMIN] Automation/scheduling of Backup stratetgy
Next
From: Tom Lane
Date:
Subject: Re: make depend (Re: Coming attractions: VPATH build; make variables issue)