Re: how to monitor the progress of really large bulk operations?

2016-09-28 14:34 GMT+02:00 Mike Sofen <msofen(at)runbox(dot)com>:

> *From:* Pavel Stehule *Sent:* Tuesday, September 27, 2016 9:18 PM
> 2016-09-28 6:13 GMT+02:00 Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>:
>
> Hi
>
> 2016-09-27 23:03 GMT+02:00 Mike Sofen <msofen(at)runbox(dot)com>:
>
> Hi gang,
>
> how to view the state of a transaction in flight, seeing how many rows
> have been read or inserted (possible for a transaction in flight?), memory
> allocations across the various PG processes, etc.
>
> some years ago I used a trick http://okbob.blogspot.cz/2014/
> 09/nice-unix-filter-pv.html#links
>
>
>
> pltoolbox has counter function https://github.com/okbob/
> pltoolbox/blob/master/utils.c
>
> pavel=# insert into omega2 select (x.xx).*
>
> from (select pst.counter(omega,200000, true) xx
>
> from omega
>
> ) x;
>
> NOTICE: processed 200000 rows, current value is '(5,8)'
>
> NOTICE: processed 200000 rows, current value is '(5,8)'
>
> Regards
>
> Pavel
>
>
>
>
>
> Pavel - That’s a very interesting function and thanks for sharing your
> toolbox. The big question of course, is what is the impact on performance,
> scalability and stability? Would it work inside of a stored function that
> would allow me write out the progress to a tracking table?
>

When a IO is bottleneck then counter has zero overhead. The usage is same
as any PostgreSQL set returning function. This function should be stable -
it is pretty simple

Regards

Pavel

>
>
> Mike
>