call pg_wait_sampling_reset_profile() after COPY ( SELECT ?, * FROM public.powa_wait_sampling_src (?)) TO STDOUT;

[jw1u1]

to avoid generating temporary files during copy, the profile should be reset after the copy.

[rjuju]

Hi,

I'm not sure that I understand your root problem. Which temporary file are you talking about, is it one that's generated by postgres when calling pg_wait_sampling_profile because there are a lot of rows, or the temporary file used to implement the copy or something else? Note that we try to avoid having side effect on the monitoring tools, so resetting pg_wait_sampling would break any other tool that relies on the various metrics being accumulated over time.

Note that in general a temporary file shouldn't really be a problem, especially here as there won't be hundreds of thousands of rows. And if it's problematic the best solution would probably be to reduce the snapshot frequency as a lot of datasources are likely to be way more expensive than this one.

[jw1u1]

this is what i'm talking about. It's not a problem in general but for our management. It would be nice to have at least the option to reset the stats after the snapshot is done.

[rjuju]

ah I see thanks.

I'm wondering if you disabled the pg_wait_sampling.wait_pid option as advised in the documentation (https://powa.readthedocs.io/en/latest/components/stats_extensions/pg_wait_sampling.html#configuration) if you don't rely on it for other applications, maybe that's the reason for the temporary files.

[jw1u1]

No, pg_wait_sampling.wait_pid could be useful in case of an incident.

[rjuju]

I'm confused. If you want to keep the pid in case of an incident, why do you want to trash every single metric at each snapshot? You will lose much more information in case of an incident.

[jw1u1]

i would have to be fast enough to disable the reset. Best option would be to keep the stats for a configurable time period.

[rjuju]

i would have to be fast enough to disable the reset.

It doesn't seem like a really practical solution.

The big problem is that the UI expects the metrics to monotonous, so doing a reset would just break all the related widgets. We could of course make the widgets aware that a reset is done each time but then it means that the reset would have to be mandatory for every user, and that's not something we want to do as it means that powa would decide that it's the only user of those metrics.

Unfortunately I don't really see any good solution for you. That being said, maybe I lack imagination but I can't really see any scenario where you would actually need the profile pid to diagnose an incident. That would mean that the incident is still happening so you can still get information on either the OS side or postgres. If the incident is finished, it's quite unlikely that you will have any useful information tied to a specific pid.