Hi
st 29. 7. 2020 v 19:55 odesílatel Olivier Leprêtre <o.lepretre@xxxxxxxxx> napsal:
Hi,
I have a rather long pgsql procedure and I would like to detect which step is currently executing (subscript 1,2,3…). Due to transaction isolation, it’s not possible to make it write in a table or get nexval from a sequence because values become available only after the complete end of the procedure.
Do you see any solution in this purpose ?
I wrote tracing support to plpgsql_check https://github.com/okbob/plpgsql_check#tracer
postgres=# set plpgsql_check.tracer_verbosity TO verbose;
SET
postgres=# do $$ begin perform fx(10,null, 'now', e'stěhule'); end; $$;
NOTICE: #0 ->> start of block inline_code_block (oid=0)
NOTICE: #0.1 1 --> start of PERFORM (expr='fx(10,null, 'now', e'stěhule' ..')
NOTICE: #2 ->> start of function fx(integer,integer,date,text) (oid=16405)
NOTICE: #2 call by inline_code_block line 1 at PERFORM
NOTICE: #2 "a" => '10', "b" => null, "c" => '2020-08-05', "d" => 'stěhule'
NOTICE: #2.1 1 --> start of PERFORM (expr='fx(a)')
NOTICE: #2.1 "a" => '10'
NOTICE: #4 ->> start of function fx(integer) (oid=16404)
NOTICE: #4 call by fx(integer,integer,date,text) line 1 at PERFORM
NOTICE: #4 "a" => '10'
NOTICE: #4.3 6 --> start of IF (cond='a > 10')
NOTICE: #4.3 "a" => '10'
NOTICE: #4.3 8 ELSEIF (expr='a < 0')
NOTICE: #4.3 "a" => '10'
NOTICE: #4.3 <-- end of IF (elapsed time=0.056 ms)
NOTICE: #4.4 12 --> start of assignment (expr='100 + a + b')
NOTICE: #4.4 "a" => '10', "b" => '20'
NOTICE: #4.4 <-- end of assignment (elapsed time=0.024 ms)
NOTICE: #4.4 "res" => '130'
NOTICE: #4.5 13 --> start of RETURN
NOTICE: #4.5 "res" => '130'
NOTICE: #4.5 <-- end of RETURN (elapsed time=0.045 ms)
NOTICE: #4 <<- end of function fx (elapsed time=0.248 ms)
NOTICE: #2.1 <-- end of PERFORM (elapsed time=0.354 ms)
NOTICE: #2 <<- end of function fx (elapsed time=0.441 ms)
NOTICE: #0.1 <-- end of PERFORM (elapsed time=0.710 ms)
NOTICE: #0 <<- end of block (elapsed time=0.777 ms)
SET
postgres=# do $$ begin perform fx(10,null, 'now', e'stěhule'); end; $$;
NOTICE: #0 ->> start of block inline_code_block (oid=0)
NOTICE: #0.1 1 --> start of PERFORM (expr='fx(10,null, 'now', e'stěhule' ..')
NOTICE: #2 ->> start of function fx(integer,integer,date,text) (oid=16405)
NOTICE: #2 call by inline_code_block line 1 at PERFORM
NOTICE: #2 "a" => '10', "b" => null, "c" => '2020-08-05', "d" => 'stěhule'
NOTICE: #2.1 1 --> start of PERFORM (expr='fx(a)')
NOTICE: #2.1 "a" => '10'
NOTICE: #4 ->> start of function fx(integer) (oid=16404)
NOTICE: #4 call by fx(integer,integer,date,text) line 1 at PERFORM
NOTICE: #4 "a" => '10'
NOTICE: #4.3 6 --> start of IF (cond='a > 10')
NOTICE: #4.3 "a" => '10'
NOTICE: #4.3 8 ELSEIF (expr='a < 0')
NOTICE: #4.3 "a" => '10'
NOTICE: #4.3 <-- end of IF (elapsed time=0.056 ms)
NOTICE: #4.4 12 --> start of assignment (expr='100 + a + b')
NOTICE: #4.4 "a" => '10', "b" => '20'
NOTICE: #4.4 <-- end of assignment (elapsed time=0.024 ms)
NOTICE: #4.4 "res" => '130'
NOTICE: #4.5 13 --> start of RETURN
NOTICE: #4.5 "res" => '130'
NOTICE: #4.5 <-- end of RETURN (elapsed time=0.045 ms)
NOTICE: #4 <<- end of function fx (elapsed time=0.248 ms)
NOTICE: #2.1 <-- end of PERFORM (elapsed time=0.354 ms)
NOTICE: #2 <<- end of function fx (elapsed time=0.441 ms)
NOTICE: #0.1 <-- end of PERFORM (elapsed time=0.710 ms)
NOTICE: #0 <<- end of block (elapsed time=0.777 ms)
Regards
Pavel
Thanks,
Olivier
