Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a long running PL/pgSQL stored procedure in PostgreSQL. How can I determine the currently executing statement in it?

share|improve this question
1  
If you are able to instrument the function and restart it, you could add a RAISE LOG or RAISE NOTICE command to emit whatever you like. I can't think of any way to determine the currently-executing query of a function other than that. –  bma Jan 13 at 17:03
    
I've long wanted to be able to get a "stack" of calls via pg_stat_activity, but neither I nor anybody else have wanted it enough to actually implement it. We really should add something where you can send the back-end a signal and get it to dump a call-stack, though. –  Craig Ringer Jan 14 at 0:37
1  
Actually, it looks like Pavel cared enough to add something close and it got committed into 9.4. It isn't non-intrusive like you want, though, I don't think you can invoke it from outside a pl/pgsql function. The Pl/PgSQL debugger might be able to do this, but you'd have to have loaded it first... –  Craig Ringer Jan 14 at 0:42
    
A colleague also found github.com/johto/call_graph and pointed it out to me. Again, not quite what you're after unfortunately, as it cannot be used by signalling the running process while it's in the call. –  Craig Ringer Jan 14 at 4:05
    
@CraigRinger - some simply tool can be written with PLpgSQL plugin API. A most difficult part is only signal handling probably. –  Pavel Stehule Jan 14 at 8:19

1 Answer 1

One crude but effective method, available in any version, is to raise notices in your plpgsql function:

CREATE OR REPLACE FUNCTION foo() RETURNS void AS
$func$
BEGIN
PERFORM ...
RAISE NOTICE 'step1';

INSERT ...
RAISE NOTICE 'step2';

DELETE ...
END
$func$ LANGUAGE plpgsql

Another 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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.