Re: unreliable behaviour of track_functions - Mailing list pgsql-general

On 03/31/2018 04:40 PM, pinker wrote:
> Hi All!
> 
> I've been experimenting with track_functions options and what I've saw it's
> really puzzling me.
> Documentation says:
> / SQL-language functions that are simple enough to be "inlined" into the
> calling query will not be tracked, regardless of this setting./
> 
> But it came up, it depends on much more factors, like duration or placing it
> in the query, it is totally non-deterministic behaviour.
> 
> This really simple SQL function:
> CREATE FUNCTION a(a bigint)
>    RETURNS bigint
> STABLE
> LANGUAGE SQL
> AS $$
> SELECT $1
> $$;
> 
> Is not shown in the pg_stat_user_functions at all. It is started to be shown
> when one line:
> select pg_sleep(1);
> 
> is added???
> 
> Another one, gets tracked only if I use:
> SELECT get_unique_term(2556);
> 
> If it lands in FROM then it's not tracked...
> SELECT * FROM get_unique_term(2556);
> 
> That's the body of the function:
> CREATE FUNCTION get_unique_term(i_game_pin bigint)
>    RETURNS TABLE(term text, category text)
> STABLE
> LANGUAGE SQL
> AS $$
> SELECT
>    i.term,
>    i.dict_category_id
> FROM (SELECT
>          categories.term,
>          categories.dict_category_id
>        FROM categories
>        EXCEPT ALL
>        SELECT
>          games.term,
>          games.category
>        FROM games
>        WHERE game_pin = $1) i
> ORDER BY (random())
> LIMIT 1;
> $$;
> 
> What's going on here? That's pretty unreliable behaviour...

?:

https://www.postgresql.org/docs/10/static/monitoring-stats.html#PG-STAT-USER-FUNCTIONS-VIEW

"...But if you want to see new results with each query, be sure to do 
the queries outside any transaction block. Alternatively, you can invoke 
pg_stat_clear_snapshot(), which will discard the current transaction's 
statistics snapshot (if any). The next use of statistical information 
will cause a new snapshot to be fetched.

A transaction can also see its own statistics (as yet untransmitted to 
the collector) in the views pg_stat_xact_all_tables, 
pg_stat_xact_sys_tables, pg_stat_xact_user_tables, and 
pg_stat_xact_user_functions. These numbers do not act as stated above; 
instead they update continuously throughout the transaction.

"
> 
> 
> My version of postgres:
>   PostgreSQL 9.6.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.3
> 20140911 (Red Hat 4.8.3-9), 64-bit
> 
> show track_functions;
>   track_functions
> -----------------
>   all
> (1 wiersz)
> 
> 
> 
>   
> 
> 
> 
> 
> --
> Sent from: http://www.postgresql-archive.org/PostgreSQL-general-f1843780.html
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com