Re: Couple of question on functions

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Vyacheslav Kalinin" <vka(at)mgcp(dot)com>
Cc: PGSQL <pgsql-general(at)postgresql(dot)org>
Subject: Re: Couple of question on functions
Date: 2008-05-14 02:51:00
Message-ID: 10677.1210733460@sss.pgh.pa.us
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

"Vyacheslav Kalinin" <vka(at)mgcp(dot)com> writes:
> Reading the manual recently I came across this: (
> http://www.postgresql.org/docs/8.3/interactive/xfunc-volatility.html )
>> Because of the snapshotting behavior of MVCC (see Chapter 13<http://www.postgresql.org/docs/8.3/interactive/mvcc.html>)
> a function containing only SELECT commands can safely be marked
>> STABLE, even if it selects from tables that might be undergoing
> modifications by concurrent queries. PostgreSQL will execute a STABLE
>> function using the snapshot established for the calling query, and so it
> will see a fixed view of the database throughout that query.

> It stroke me that it might be not all that safe to mark SELECTing only
> function STABLE vs VOLATILE (or vice versa).

What it says is that you *can* mark such a function stable, without
violating the rules for a stable function. It doesn't say that this
choice doesn't affect the results. Feel free to propose better wording...

> Another thing I've recently discover is that SQL function seem to be
> unexpectedly slow to call. Example:
> ...
> perform f2sql(trunc(1000000*random())::int) +
> ...

> Now f4() reports 4.2 sec of runtime on average while f3() - 10.3 sec, that
> is a notable difference especially considering that SQL function is likely
> to be inlined. Do i miss something?

You might think it's "likely", but in fact no inlining will happen
because you have a volatile argument that's used more than once inside
the function body. Inlining would change the results.

regards, tom lane

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Chris 2008-05-14 03:04:20 Re: Strange behaviour - performance decreases after each TRUNCATE
Previous Message Alvaro Herrera 2008-05-14 00:43:28 Re: Making sure \timing is on