Re: [PROPOSAL] Drop orphan temp tables in single-mode

On 03/07/2019 06:49 PM, Robert Haas wrote:
> On Thu, Mar 7, 2019 at 10:24 AM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>> So if we think we can invent a "MAGICALLY FIX MY DATABASE" command,
>> let's do that. But please let's not turn a well defined command
>> like VACUUM into something that you don't quite know what it will do.
> I am on the fence about that. I see your point, but on the other
> hand, autovacuum drops temp tables all the time in multi-user mode and
> I think it's pretty clear that, with the possible exception of you,
> users find that an improvement. So it could be argued that we're
> merely proposing to make the single-user mode behavior of vacuum
> consistent with the behavior people are already expecting it to do.
>
> The underlying and slightly more general problem here is that users
> find it really hard to know what to do when vacuum fails to advance
> relfrozenxid. Of course, temp tables are only one reason why that can
> happen: logical decoding slots and prepared transactions are others,
> and I don't think we can automatically drop that stuff because
> somebody may still be expecting them to accomplish whatever their
> intended purpose is. The difference with temp tables is that users
> imagine -- quite naturally I think -- that they are in fact temporary,
> and that they will in fact go away when the session ends. The user
> would tend to view their continued existence as an unwanted
> implementation artifact, not something that they should be responsible
> for removing.

I`m no hacker, but I would like to express my humble opinion on the matter.
1. Proposed patch is fairly conservative, to be on fully consistent with
autovacuum behaivour VACUUM should be able to drop orphaned temp table
even in mult-user mode.

2. There is indeed a problem of expected behavior from user perspective.
Every PostgreSQL user knows that if you hit wraparound, you go
single-mode, run VACUUM and the problem goes away. Exactly because of
this I`ve got involved with this problem:
https://www.postgresql.org/message-id/0c7c2f84-74f5-2cd9-767e-9b2566065d71%40postgrespro.ru
Poor guy repeatedly run VACUUM after VACUUM and had no clue what to do.
He even considered to just restore from backup and be done with it. It
took some time to figure out a true culprit, and time = money.

--
Grigory Smolkin
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company