Re: Track pgsql steps

Hi

st 29. 7. 2020 v 19:55 odesílatel Olivier Leprêtre <o(dot)lepretre(at)gmail(dot)com>
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)

Regards

Pavel

>
>
> Thanks,
>
>
>
> Olivier
>
>
>
>
>
>
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient> Garanti
> sans virus. www.avast.com
> <https://www.avast.com/sig-email?utm_medium=email&utm_source=link&utm_campaign=sig-email&utm_content=emailclient>
> <#m_-9154849454718330009_DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2>
>