I want to find a name of the script that runs psql query. From pg_stat_activity I can extract active query and pid, but this pid is of postgres process. If there any way to match that pid to pid of script (from ps for example)?
2 Answers
The only way I can think to make this work is to start every script with
SET application_name = 'psql executing myscript.sql';
and to end it with
SET application_name = 'psql';
I haven't find yet how to get the psql script name, that appears on error or on RAISE.
e.g. : psql:/path/to/my_script.psql:9999: WARNING: error message.
I want to get the '/path/to/my_script.psql', to show in log what script is running. But failed to find the way to get it.
The workaround I use is to put at the top of my psql scripts this lines :
\set scr_nam 'my_script_name.psql === '
\set QUIET on \pset tuples_only on \pset format unaligned \pset pager \set QUIET
SELECT TO_CHAR(clock_timestamp(),'Dy DD/MM/YYYY HH24:MI:SS,MS') || ' # ' || :'scr_nam' || 'START ===' msg;
<code>
the code of my psql script
</code>
\set QUIET on \pset format unaligned \pset tuples_only on \set QUIET off
SELECT TO_CHAR(clock_timestamp(),'Dy DD/MM/YYYY HH24:MI:SS,MS') || ' # ' || :'scr_nam' || 'E N D ===' msg;
Related