Re: Temporary tables prevent autovacuum, leading to XID wraparound

On Thu, Mar 8, 2018 at 1:09 PM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> We already have autovacuum taking care of that, and as I stated, asking
> backends to do it is provably insufficient. The right path is to make
> autovacuum cover the cases it's missing today.

Well, your counter-proposal of teaching autovacuum to recognize the
case where the backend and the orphaned schema are in different
databases is also provably insufficient. We have to make more than
one change here to really fix this, and the two changes that
Tsunakawa-san proposed, taken together, are sufficient; either one by
itself is not. Doing only #2 out of his proposals, as you proposed,
is better than doing nothing, but it's not a complete fix.

>> I don't
>> really share your concern about performance; one extra syscache lookup
>> at backend startup isn't going to break the bank.
>
> If it were just that, I wouldn't be worried either. But it's not.
> Once the pg_namespace row exists, which it will in an installation
> that's been in use for for any length of time, you're going to have
> to actively search for entries in that schema. I'm not sure how
> expensive a performDeletion() scan that finds nothing to do really
> is, but for sure it's more than the syscache lookup you expended to
> find the pg_namespace row.

True, but that's a rare scenario.

Still, I'm not sure we're too far apart on the underlying issue here;
we both would prefer, for one reason or another, if autovacuum could
just take care of this. But I think that Tsunakawa-san is correct to
guess that your proposal might have some race conditions that need to
be solved somehow.

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company