Thread: Reaping Temp tables to avoid XID wraparound

Reaping Temp tables to avoid XID wraparound

From

James Sewell

Date:

13 February 2019, 03:04:51

Hi all,

I hit an issue yesterday where I was quickly nearing XID wraparound on a database due to a temp table being created in a session which was then left IDLE out of transaction for 6 days.

I solved the issue by tracing the owner of the temp table back to a session and terminating it - in my case I was just lucky that there was one session for that user.

I'm looking for a way to identify the PID of the backend which owns a temp table identified as being an issue so I can terminate it.

From the temp table namespace I can get the backend ID using a regex - but I have no idea how I can map that to a PID - any thoughts?

Cheers,

James Sewell,

Suite 112, Jones Bay Wharf, 26-32 Pirrama Road, Pyrmont NSW 2009

P (+61) 2 8099 9000 W www.jirotech.com F (+61) 2 8099 9099

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.

RE: Reaping Temp tables to avoid XID wraparound

From

"Tsunakawa, Takayuki"

Date:

13 February 2019, 03:32:18

From: James Sewell [mailto:james.sewell@jirotech.com]
> From the temp table namespace I can get the backend ID using a regex - but
> I have no idea how I can map that to a PID - any thoughts?
> 

SELECT pg_stat_get_backend_pid(backendid);

https://www.postgresql.org/docs/devel/monitoring-stats.html

This mailing list is for PostgreSQL development.  You can post questions as a user to
pgsql-general@lists.postgresql.org.


Regards
Takayuki Tsunakawa

Re: Reaping Temp tables to avoid XID wraparound

From

Andrew Gierth

Date:

13 February 2019, 03:38:51

>>>>> "Tsunakawa" == Tsunakawa, Takayuki <tsunakawa.takay@jp.fujitsu.com> writes:

 >> From the temp table namespace I can get the backend ID using a regex
 >> - but I have no idea how I can map that to a PID - any thoughts?

 Tsunakawa> SELECT pg_stat_get_backend_pid(backendid);

Doesn't work - that function's idea of "backend id" doesn't match the
real one, since it's looking at a local copy of the stats from which
unused slots have been removed.

postgres=# select pg_my_temp_schema()::regnamespace;
 pg_my_temp_schema 
-------------------
 pg_temp_5
(1 row)

postgres=# select pg_stat_get_backend_pid(5);
 pg_stat_get_backend_pid 
-------------------------
                    4730
(1 row)

postgres=# select pg_backend_pid();
 pg_backend_pid 
----------------
          21086
(1 row)

-- 
Andrew (irc:RhodiumToad)

Re: Reaping Temp tables to avoid XID wraparound

From

Michael Paquier

Date:

13 February 2019, 04:25:46

On Wed, Feb 13, 2019 at 12:38:51AM +0000, Andrew Gierth wrote:
> Doesn't work - that function's idea of "backend id" doesn't match the
> real one, since it's looking at a local copy of the stats from which
> unused slots have been removed.

The temporary namespace OID is added to PGPROC since v11, so it could
be easy enough to add a system function which maps a temp schema to a
PID.  Now, it could actually make sense to add this information into
pg_stat_get_activity() and that would be cheaper.
--
Michael

Attachment

signature.asc

RE: Reaping Temp tables to avoid XID wraparound

From

"Tsunakawa, Takayuki"

Date:

13 February 2019, 04:48:40

From: Andrew Gierth [mailto:andrew@tao11.riddles.org.uk]
>  Tsunakawa> SELECT pg_stat_get_backend_pid(backendid);
> 
> Doesn't work - that function's idea of "backend id" doesn't match the
> real one, since it's looking at a local copy of the stats from which
> unused slots have been removed.

Ouch, the argument of pg_stat_get_backend_pid() and the number in pg_temp_N are both backend IDs, but they are
allocatedfrom two different data structures.  Confusing.
 


From: Michael Paquier [mailto:michael@paquier.xyz]
> The temporary namespace OID is added to PGPROC since v11, so it could be
> easy enough to add a system function which maps a temp schema to a PID.
> Now, it could actually make sense to add this information into
> pg_stat_get_activity() and that would be cheaper.

That sounds good.


Regards
Takayuki Tsunakawa

Re: Reaping Temp tables to avoid XID wraparound

From

Magnus Hagander

Date:

13 February 2019, 19:48:39

On Wed, Feb 13, 2019 at 2:26 AM Michael Paquier <michael@paquier.xyz> wrote:

On Wed, Feb 13, 2019 at 12:38:51AM +0000, Andrew Gierth wrote:
> Doesn't work - that function's idea of "backend id" doesn't match the
> real one, since it's looking at a local copy of the stats from which
> unused slots have been removed.

The temporary namespace OID is added to PGPROC since v11, so it could
be easy enough to add a system function which maps a temp schema to a
PID. Now, it could actually make sense to add this information into
pg_stat_get_activity() and that would be cheaper.

I think that would be useful and make sense.

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?

//Magnus

Re: Reaping Temp tables to avoid XID wraparound

From

Tom Lane

Date:

13 February 2019, 20:05:18

Magnus Hagander <magnus@hagander.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.

            regards, tom lane

Re: Reaping Temp tables to avoid XID wraparound

From

Magnus Hagander

Date:

13 February 2019, 20:09:40

On Wed, Feb 13, 2019 at 6:05 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:

Magnus Hagander <magnus@hagander.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/
Work: https://www.redpill-linpro.com/

Re: Reaping Temp tables to avoid XID wraparound

From

James Sewell

Date:

14 February 2019, 02:00:01

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 W www.jirotech.com F (+61) 2 8099 9099

On Thu, 14 Feb 2019 at 04:09, Magnus Hagander <magnus@hagander.net> wrote:

On Wed, Feb 13, 2019 at 6:05 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Magnus Hagander <magnus@hagander.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/
Work: https://www.redpill-linpro.com/

Re: Reaping Temp tables to avoid XID wraparound

From

Michael Paquier

Date:

14 February 2019, 03:43:30

On Wed, Feb 13, 2019 at 05:48:39PM +0100, Magnus Hagander wrote:
> On Wed, Feb 13, 2019 at 2:26 AM Michael Paquier <michael@paquier.xyz> wrote:
>> The temporary namespace OID is added to PGPROC since v11, so it could
>> be easy enough to add a system function which maps a temp schema to a
>> PID.  Now, it could actually make sense to add this information into
>> pg_stat_get_activity() and that would be cheaper.
>
> I think that would be useful and make sense.

One thing to keep in mind here is that tempNamespaceId in PGPROC gets
set before the transaction creating it has committed, hence it is
necessary to also check that the namespace actually exists from the
point of view of the session running pg_stat_get_activity() before
showing it, which can be done with a simple
SearchSysCacheExists1(NAMESPACEOID) normally.

> 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?

Yeah, possibly.  I think that it could be tricky though to get that at
a global level in a cheap way.  It makes also little sense to only
show the temp namespace OID if that information is not enough.
--
Michael

Attachment

signature.asc

Re: Reaping Temp tables to avoid XID wraparound

From

Magnus Hagander

Date:

17 February 2019, 19:47:09

On Thu, Feb 14, 2019 at 1:43 AM Michael Paquier <michael@paquier.xyz> wrote:

On Wed, Feb 13, 2019 at 05:48:39PM +0100, Magnus Hagander wrote:
> On Wed, Feb 13, 2019 at 2:26 AM Michael Paquier <michael@paquier.xyz> wrote:
>> The temporary namespace OID is added to PGPROC since v11, so it could
>> be easy enough to add a system function which maps a temp schema to a
>> PID. Now, it could actually make sense to add this information into
>> pg_stat_get_activity() and that would be cheaper.
>
> I think that would be useful and make sense.

One thing to keep in mind here is that tempNamespaceId in PGPROC gets
set before the transaction creating it has committed, hence it is
necessary to also check that the namespace actually exists from the
point of view of the session running pg_stat_get_activity() before
showing it, which can be done with a simple
SearchSysCacheExists1(NAMESPACEOID) normally.

Oh, that's a good point.

> 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?

Yeah, possibly. I think that it could be tricky though to get that at
a global level in a cheap way. It makes also little sense to only
show the temp namespace OID if that information is not enough.

We could I guess add a field specifically for temp_namespace_xid or such. The question is if it's worth the overhead to do that.

Just having the namespace oid is at least enough to know that there is potentially something to go look at it. But it doesn't make for automated monitoring very well, at least not in systems that have a larger number of databases.

Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/

Re: Reaping Temp tables to avoid XID wraparound

From

James Sewell

Date:

18 February 2019, 03:19:01

Yeah, possibly. I think that it could be tricky though to get that at
a global level in a cheap way. It makes also little sense to only
show the temp namespace OID if that information is not enough.

We could I guess add a field specifically for temp_namespace_xid or such. The question is if it's worth the overhead to do that.

Just having the namespace oid is at least enough to know that there is potentially something to go look at it. But it doesn't make for automated monitoring very well, at least not in systems that have a larger number of databases.

You can get the namespace oid today with a JOIN, the issue is that this isn't enough information to go an look at - at the end of the day it's useless unless you can remove the temp table or terminate the session which owns it.

Re: Reaping Temp tables to avoid XID wraparound

From

Michael Paquier

Date:

18 February 2019, 04:31:02

On Sun, Feb 17, 2019 at 05:47:09PM +0100, Magnus Hagander wrote:
> We could I guess add a field specifically for temp_namespace_xid or such.
> The question is if it's worth the overhead to do that.

That would mean an extra 4 bytes in PGPROC, which is something we
could live with, still the use-case looks rather narrow to me to
justify that.

> Just having the namespace oid is at least enough to know that there is
> potentially something to go look at it. But it doesn't make for automated
> monitoring very well, at least not in systems that have a larger number of
> databases.

Yep.  It would be good to make sure about the larger picture before
doing something.
--
Michael

Attachment

signature.asc

Re: Reaping Temp tables to avoid XID wraparound

From

James Sewell

Date:

19 February 2019, 02:52:54

On Mon, 18 Feb 2019 at 12:31, Michael Paquier <michael@paquier.xyz> wrote:

On Sun, Feb 17, 2019 at 05:47:09PM +0100, Magnus Hagander wrote:
> We could I guess add a field specifically for temp_namespace_xid or such.
> The question is if it's worth the overhead to do that.

That would mean an extra 4 bytes in PGPROC, which is something we
could live with, still the use-case looks rather narrow to me to
justify that.

I agree the use case is narrow - but it's also pretty critical.

This is a very real way that transaction wraparound can be hit, with no automated or manual way of solving it (apart from randomly terminating backends (you have to search via user and hope there is only one, and that it matches the temp table owner) or restarting Postgres).

I suppose an in-core way of disconnecting idle sessions after x time would work too - but that seems like a sledgehammer approach.

James

Re: Reaping Temp tables to avoid XID wraparound

From

Michael Paquier

Date:

19 February 2019, 06:36:48

On Tue, Feb 19, 2019 at 10:52:54AM +1100, James Sewell wrote:
> I agree the use case is narrow - but it's also pretty critical.

Yeah..

> I suppose an in-core way of disconnecting idle sessions after x time would
> work too - but that seems like a sledgehammer approach.

Such solutions at SQL level need to connect to a specific database and
I implemented one for fun, please see the call to
BackgroundWorkerInitializeConnection() here:
https://github.com/michaelpq/pg_plugins/tree/master/kill_idle

So that's not the end of it as long as we don't have a cross-database
solution.  If we can get something in PGPROC then just connecting to
shared memory would be enough.
--
Michael

Attachment

signature.asc

Re: Reaping Temp tables to avoid XID wraparound

From

Magnus Hagander

Date:

19 February 2019, 11:56:28

On Mon, Feb 18, 2019 at 2:31 AM Michael Paquier <michael@paquier.xyz> wrote:

On Sun, Feb 17, 2019 at 05:47:09PM +0100, Magnus Hagander wrote:
> We could I guess add a field specifically for temp_namespace_xid or such.
> The question is if it's worth the overhead to do that.

That would mean an extra 4 bytes in PGPROC, which is something we
could live with, still the use-case looks rather narrow to me to
justify that.

It does, tha'ts why I questioned if it's worth it. But, thinking some more about it, some other options would be:

1. This is only set once per backend in normal operations, right? (Unless I go drop the schema manually, but that's not exactly normal). So maybe we could invent a pg stat message and send the information through the collector? Since it doesn't have to be frequently updated, like your typical backend_xmin.

2. Or probably even better, just put it in PgBackendStatus? Overhead here is a lot cheaper than PGPROC.

ISTM 2 is probably the most reasonable option here?

Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/

Re: Reaping Temp tables to avoid XID wraparound

From

Michael Paquier

Date:

20 February 2019, 05:41:04

On Tue, Feb 19, 2019 at 09:56:28AM +0100, Magnus Hagander wrote:
> 2. Or probably even better, just put it in PgBackendStatus? Overhead here
> is a lot cheaper than PGPROC.
>
> ISTM 2 is probably the most reasonable option here?

Yes, I forgot this one.  That would be more consistent, even if the
information can be out of date quickly we don't care here.
--
Michael

Attachment

signature.asc

Re: Reaping Temp tables to avoid XID wraparound

From

Magnus Hagander

Date:

22 February 2019, 18:01:02

On Wed, Feb 20, 2019 at 3:41 AM Michael Paquier <michael@paquier.xyz> wrote:

On Tue, Feb 19, 2019 at 09:56:28AM +0100, Magnus Hagander wrote:
> 2. Or probably even better, just put it in PgBackendStatus? Overhead here
> is a lot cheaper than PGPROC.
>
> ISTM 2 is probably the most reasonable option here?

Yes, I forgot this one. That would be more consistent, even if the
information can be out of date quickly we don't care here.

I think it would be something like the attached. Thoughts?

I did the "insert column in the middle of pg_stat_get_activity", I'm not sure that is right -- how do we treate that one? Do we just append at the end because people are expected to use the pg_stat_activity view? It's a nontrivial part of the patch.

That one aside, does the general way to track it appear reasonable? (docs excluded until we have agreement on that)

And should we also expose the oid in pg_stat_activity in this case, since we have it?

Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/

Attachment

stat_temp_namespace_xid.patch

Re: Reaping Temp tables to avoid XID wraparound

From

Michael Paquier

Date:

26 February 2019, 09:45:00

On Fri, Feb 22, 2019 at 04:01:02PM +0100, Magnus Hagander wrote:
> I did the "insert column in the middle of pg_stat_get_activity", I'm not
> sure that is right -- how do we treate that one? Do we just append at the
> end because people are expected to use the pg_stat_activity view? It's a
> nontrivial part of the patch.

I think that it would be more confusing to add the new column at the
tail, after all the SSL fields.

> That one aside, does the general way to track it appear reasonable? (docs
> excluded until we have agreement on that)

It does.  A temp table is associated to a session so it's not like
autovacuum can work on it.  With this information it is at least
possible to take actions.  We could even get autovacuum to kill such
sessions. /me hides

> And should we also expose the oid in pg_stat_activity in this case, since
> we have it?

For the case reported here, just knowing the XID where the temporary
namespace has been created is enough so as the goal is to kill the
session associated with it.  Still, it seems to me that knowing the
temporary schema name used by a given session is useful, and that's
cheap to get as the information is already there.

One problem that I can see with your patch is that you would set the
XID once any temporary object created, including when objects other
than tables are created in pg_temp, including functions, etc.  And it
does not really matter for wraparound monitoring.  Still, the patch is
simple..
--
Michael

Attachment

signature.asc

Re: Reaping Temp tables to avoid XID wraparound

From

Magnus Hagander

Date:

08 March 2019, 22:14:46

On Mon, Feb 25, 2019 at 10:45 PM Michael Paquier <michael@paquier.xyz> wrote:

On Fri, Feb 22, 2019 at 04:01:02PM +0100, Magnus Hagander wrote:
> I did the "insert column in the middle of pg_stat_get_activity", I'm not
> sure that is right -- how do we treate that one? Do we just append at the
> end because people are expected to use the pg_stat_activity view? It's a
> nontrivial part of the patch.

I think that it would be more confusing to add the new column at the
tail, after all the SSL fields.

> That one aside, does the general way to track it appear reasonable? (docs
> excluded until we have agreement on that)

It does. A temp table is associated to a session so it's not like
autovacuum can work on it. With this information it is at least
possible to take actions. We could even get autovacuum to kill such
sessions. /me hides

> And should we also expose the oid in pg_stat_activity in this case, since
> we have it?

For the case reported here, just knowing the XID where the temporary
namespace has been created is enough so as the goal is to kill the
session associated with it. Still, it seems to me that knowing the
temporary schema name used by a given session is useful, and that's
cheap to get as the information is already there.

it should be since it's in pgproc.

One problem that I can see with your patch is that you would set the
XID once any temporary object created, including when objects other
than tables are created in pg_temp, including functions, etc. And it
does not really matter for wraparound monitoring. Still, the patch is
simple..

I'm not entirely sure what you mean here. Sure, it will log it even when a temp function is created, but the namespace is still created then is it not?

Magnus Hagander
Me: https://www.hagander.net/
Work: https://www.redpill-linpro.com/

Re: Reaping Temp tables to avoid XID wraparound

From

Michael Paquier

Date:

09 March 2019, 04:28:55

On Fri, Mar 08, 2019 at 11:14:46AM -0800, Magnus Hagander wrote:
> On Mon, Feb 25, 2019 at 10:45 PM Michael Paquier <michael@paquier.xyz>
> wrote:
>> One problem that I can see with your patch is that you would set the
>> XID once any temporary object created, including when objects other
>> than tables are created in pg_temp, including functions, etc.  And it
>> does not really matter for wraparound monitoring.  Still, the patch is
>> simple..
>
> I'm not entirely sure what you mean here. Sure, it will log it even when a
> temp function is created, but the namespace is still created then is it
> not?

What I mean here is: imagine the case of a session which creates a
temporary function, creating as well the temporary schema, but creates
no other temporary objects.  In this case we don't really care about
the wraparound issue because, even if we have a temporary schema, we
do not have temporary relations.  And this could confuse the user?
Perhaps that's not worth bothering, still not all temporary objects
are tables.
--
Michael

Attachment

signature.asc