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

On Fri, Mar 8, 2019 at 9:28 AM Michael Paquier <michael(at)paquier(dot)xyz> wrote:
> On Thu, Mar 07, 2019 at 10:49:29AM -0500, 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.
>
> It is possible for a session to drop temporary tables of other
> sessions. Wouldn't it work as well in this case for single-user mode
> when seeing an orphan temp table still defined? Like Tom, I don't
> think that it is a good idea to play with the heuristics of VACUUM in
> the way the patch proposes.

I think we have a kind of agreement, that having simple way to get rid
of all orphan tables in single-user mode is good. The question is
whether it's good for VACUUM command to do this. Naturally, user may
enter single-user mode for different reasons, not only for xid
wraparound fixing. For example, one may enter this mode for examining
temporary tables present in the database. Then it would be
disappointing surprise that all of them gone after VACUUM command.

So, what about special option, which would make VACUUM to drop orphan
tables in single-user mode? Do we need it in multi-user mode too?

BTW, does this patch checks that temporary table is really orphan?
AFAICS, user may define some temporary tables in single-user mode
before running VACUUM.

------
Alexander Korotkov
Postgres Professional: http://www.postgrespro.com
The Russian Postgres Company