Re: Reaping Temp tables to avoid XID wraparound

It's easy to identify the temp tables which are causing the problem, yes.
The issue here is just getting rid of them.

In an ideal world I wouldn't actually have to care about the session and I
could just drop the table (or vacuum the table?).

Dropping the session was just the best way I could find to currently solve
the problem.

Cheers,

James Sewell,

Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009
*P *(+61) 2 8099 9000 <(+61)%202%208099%209000> *W* www.jirotech.com *F *
(+61) 2 8099 9099 <(+61)%202%208099%209000>

On Thu, 14 Feb 2019 at 04:09, Magnus Hagander <magnus(at)hagander(dot)net> wrote:

> On Wed, Feb 13, 2019 at 6:05 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
>> Magnus Hagander <magnus(at)hagander(dot)net> writes:
>> > And while at it, what would in this particular case have been even more
>> > useful to the OP would be to actually identify that there is a temp
>> table
>> > *and which xid it's blocking at*. For regular transactions we can look
>> at
>> > backend_xid, but IIRC that doesn't work for temp tables (unless they are
>> > inside a transaction). Maybe we can find a way to expose that type of
>> > relevant information at a similar level while poking around that code?
>>
>> Maybe I'm confused, but doesn't the table's pg_class row tell you what
>> you need to know? You can't look inside another session's temp table,
>> but you don't need to.
>>
>
> I believe it does, yes.
>
> But that doesn't make for a way to conveniently go "what is it that's
> causing waparound problems", since due to pg_class being per database, you
> have to loop over all your databases to find that query. Having that
> information available in a way that's easy for monitoring to get at (much
> as the backend_xid field in pg_stat_activity can help you wrt general
> snapshots) would be useful.
>
> --
> Magnus Hagander
> Me: https://www.hagander.net/ <http://www.hagander.net/>
> Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
>

--
The contents of this email are confidential and may be subject to legal or
professional privilege and copyright. No representation is made that this
email is free of viruses or other defects. If you have received this
communication in error, you may not copy or distribute any part of it or
otherwise disclose its contents to anyone. Please advise the sender of your
incorrect receipt of this correspondence.