patternsqlMinor
Determine the currently executing statement in a long running PL/pgSQL stored procedure
Viewed 0 times
storedexecutingthestatementlongprocedurerunningdeterminepgsqlcurrently
Problem
I have a long running PL/pgSQL stored procedure in PostgreSQL. How can I determine the currently executing statement in it?
Solution
One crude but effective method, available in any version, is to raise notices in your plpgsql function:
Another possibility would be to use auto-explain with
The second method gives you a lot more than you asked for. Both are crutches and work-arounds and more or less intrusive.
The upcoming feature in 9.4 by Pavel that @Craig identified in his comment seems like a major improvement:
It's already documented in the /devel manual.
CREATE OR REPLACE FUNCTION foo() RETURNS void AS
$func$
BEGIN
PERFORM ...
RAISE NOTICE 'step1';
INSERT ...
RAISE NOTICE 'step2';
DELETE ...
END
$func$ LANGUAGE plpgsqlAnother possibility would be to use auto-explain with
SET auto_explain.log_nested_statements = ON;The second method gives you a lot more than you asked for. Both are crutches and work-arounds and more or less intrusive.
The upcoming feature in 9.4 by Pavel that @Craig identified in his comment seems like a major improvement:
GET DIAGNOSTICS text_var = PG_CONTEXT;It's already documented in the /devel manual.
Code Snippets
CREATE OR REPLACE FUNCTION foo() RETURNS void AS
$func$
BEGIN
PERFORM ...
RAISE NOTICE 'step1';
INSERT ...
RAISE NOTICE 'step2';
DELETE ...
END
$func$ LANGUAGE plpgsqlSET auto_explain.log_nested_statements = ON;GET DIAGNOSTICS text_var = PG_CONTEXT;Context
StackExchange Database Administrators Q#56689, answer score: 4
Revisions (0)
No revisions yet.