Обсуждение: Track pgsql steps

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

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.

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
possibledue 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
limitedin 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

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

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.