Re: Temporary tables versus wraparound... again

On 10/12/21, 3:07 PM, "Greg Stark" <stark@mit.edu> wrote:
> Here's an updated patch. I added some warning messages to autovacuum.

I think this is useful optimization, and I intend to review the patch
more closely soon.  It looks reasonable to me after a quick glance.

> One thing I learned trying to debug this situation in production is
> that it's nigh impossible to find the pid of the session using a
> temporary schema. The number in the schema refers to the backendId in
> the sinval stuff for which there's no external way to look up the
> corresponding pid. It would have been very helpful if autovacuum had
> just told me which backend pid to kill.

I certainly think it would be good to have autovacuum log the PID, but
IIUC a query like this would help you map the backend ID to the PID:

        SELECT bid, pg_stat_get_backend_pid(bid) AS pid FROM pg_stat_get_backend_idset() bid;

Nathan