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@xxxxxxxxxxx
