Thread
-
Track pgsql steps
Olivier Leprêtre <o.lepretre@gmail.com> — 2020-07-29T15:44:58Z
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, its 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 ? Thanks, Olivier -- L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast. https://www.avast.com/antivirus
-
Re: Track pgsql steps
Adrian Klaver <adrian.klaver@aklaver.com> — 2020-07-29T17:58:09Z
On 7/29/20 8:44 AM, Olivier Leprêtre wrote: > 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 ? RAISE NOTICE?: https://www.postgresql.org/docs/12/plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-RAISE > > 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> > > > <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2> -- Adrian Klaver adrian.klaver@aklaver.com
-
Re: Track pgsql steps
Julien Rouhaud <rjuju123@gmail.com> — 2020-07-30T10:18:45Z
On Wed, Jul 29, 2020 at 7:58 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > On 7/29/20 8:44 AM, Olivier Leprêtre wrote: > > 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 ? > > RAISE NOTICE?: > > https://www.postgresql.org/docs/12/plpgsql-errors-and-messages.html#PLPGSQL-STATEMENTS-RAISE You can also abuse SET application_name, as the value will be directly seen by other transactions. You're quite limited in the number of bytes to use, but if you just need to do some quick testing it can be helpful.
-
RE: Track pgsql steps
Olivier Leprêtre <olivier.lepretre@noetika.com> — 2020-07-30T13:11:09Z
Hi, thanks for your answers, Application_name is a good tip, 64 chars are enough to code steps, I'll use that I expected being able to write raise events to a table or to store data in another table but it doesn't appear to be possible due to transaction isolation. Thanks a lot. Olivier -----Message d'origine----- De : Julien Rouhaud <rjuju123@gmail.com> Envoyé : jeudi 30 juillet 2020 12:19 À : Adrian Klaver <adrian.klaver@aklaver.com> Cc : Olivier Leprêtre <o.lepretre@gmail.com>; pgsql-general <pgsql-general@postgresql.org> Objet : Re: Track pgsql steps On Wed, Jul 29, 2020 at 7:58 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote: > > On 7/29/20 8:44 AM, Olivier Leprêtre wrote: > > 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 ? > > RAISE NOTICE?: > > https://www.postgresql.org/docs/12/plpgsql-errors-and-messages.html#PL > PGSQL-STATEMENTS-RAISE You can also abuse SET application_name, as the value will be directly seen by other transactions. You're quite limited in the number of bytes to use, but if you just need to do some quick testing it can be helpful. -- L'absence de virus dans ce courrier électronique a été vérifiée par le logiciel antivirus Avast. https://www.avast.com/antivirus
-
Re: Track pgsql steps
Diego <mrstephenamell@gmail.com> — 2020-07-30T14:03:24Z
are you running the psql with -c or -f? add -o to put all in a file and -e to write the last query, and with a tail to the log, you can see where is the problem On 2020-07-30 10:11, Olivier Leprêtre wrote: > Hi, thanks for your answers, > > Application_name is a good tip, 64 chars are enough to code steps, I'll use that > I expected being able to write raise events to a table or to store data in another table but it doesn't appear to be possible due to transaction isolation. > > Thanks a lot. > > Olivier > > -----Message d'origine----- > De : Julien Rouhaud <rjuju123@gmail.com> > Envoyé : jeudi 30 juillet 2020 12:19 > À : Adrian Klaver <adrian.klaver@aklaver.com> > Cc : Olivier Leprêtre <o.lepretre@gmail.com>; pgsql-general <pgsql-general@postgresql.org> > Objet : Re: Track pgsql steps > > On Wed, Jul 29, 2020 at 7:58 PM Adrian Klaver <adrian.klaver@aklaver.com> wrote: >> On 7/29/20 8:44 AM, Olivier Leprêtre wrote: >>> 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 ? >> RAISE NOTICE?: >> >> https://www.postgresql.org/docs/12/plpgsql-errors-and-messages.html#PL >> PGSQL-STATEMENTS-RAISE > You can also abuse SET application_name, as the value will be directly seen by other transactions. You're quite limited in the number of bytes to use, but if you just need to do some quick testing it can be helpful. > >
-
Re: Track pgsql steps
Pavel Stehule <pavel.stehule@gmail.com> — 2020-08-05T09:28:49Z
Hi st 29. 7. 2020 v 19:55 odesílatel Olivier Leprêtre <o.lepretre@gmail.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> >