Search Postgresql Archives

Couple of question on functions

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



Hello,

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) 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. Also
> note that the current_timestamp family of functions qualify as stable, since their values do not change within a transaction.


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

create table t1(id int);

create or replace function f1() returns void as
$$
declare
  i int;
begin
    select count(*) into i from t1;
    raise notice '%', i;
    -- waste some time
    for i in 1..700000000 loop
    end loop;
    select count(*) into i from t1;
    raise notice '%', i;
end;
$$
language 'plpgsql';

Now in first connection do:
select f1();

While the execution is in the loop which takes a while do in another connection:
insert into t1 values (1);

The function returns with the following notices:
NOTICE:  0
NOTICE:  1

Should I change the volatility type of f1() to STABLE and run the above again I would get:
NOTICE:  1
NOTICE:  1

It looks like at least plpgsql functions use most recent snapshot on each call to SPI manager instead that of a calling query, so since default transaction isolation level in postgres is READ COMMITTED concurrent transactions may affect result of pure-reader VOLATILE function. I wonder if any-language (including SQL,and C) function would behave in the same way?

Another thing I've recently discover is that SQL function seem to be unexpectedly slow to call. Example:

create or replace function f2sql(int) returns int as
$$
select case when $1 < 100000 then 1
            when 100000 <= $1 and $1 < 500000 then 2
            when $1 >= 500000 then 3
       end;    
$$
language 'sql' immutable;

create or replace function f2plpgsql(int) returns int as
$$
begin
return case when $1 < 100000 then 1
            when 100000 <= $1 and $1 < 500000 then 2
            when $1 >= 500000 then 3
       end;    
end;      
$$
language 'plpgsql' immutable;

These two function do exactly the same calculation on input and differ only in language used. Now I write some query involving them and wrap it into another function (so that I could use PERFORM to avoid possible overhead on fetching results to the client, to cache the plan  and to measure the time in more precise manner):

create or replace function f3() returns void as
$$
declare
  st timestamp;
begin
    st := clock_timestamp();
    perform f2sql(trunc(1000000*random())::int) +
               f2sql(trunc(1000000*random())::int) +
            f2sql(trunc(1000000*random())::int) +
            f2sql(trunc(1000000*random())::int) +
            f2sql(trunc(1000000*random())::int) +
            f2sql(trunc(1000000*random())::int) +
            f2sql(trunc(1000000*random())::int) +
            f2sql(trunc(1000000*random())::int) +
            f2sql(trunc(1000000*random())::int) +
            f2sql(trunc(1000000*random())::int) 
       from generate_series(1, 100000);
    raise notice '%', clock_timestamp() - st;  
end;
$$
language 'plpgsql' ;

create or replace function f4() returns void as
$$
declare
  st timestamp;
begin
    st := clock_timestamp();
    perform f2plpgsql(trunc(1000000*random())::int) +
               f2plpgsql(trunc(1000000*random())::int) +
            f2plpgsql(trunc(1000000*random())::int) +
            f2plpgsql(trunc(1000000*random())::int) +
            f2plpgsql(trunc(1000000*random())::int) +
            f2plpgsql(trunc(1000000*random())::int) +
            f2plpgsql(trunc(1000000*random())::int) +
            f2plpgsql(trunc(1000000*random())::int) +
            f2plpgsql(trunc(1000000*random())::int) +
            f2plpgsql(trunc(1000000*random())::int) 
       from generate_series(1, 100000);
    raise notice '%', clock_timestamp() - st;  
end;
$$
language 'plpgsql' ;

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?


[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux