Re: Reaping Temp tables to avoid XID wraparound

[Michael Paquier]

On Fri, Mar 08, 2019 at 11:14:46AM -0800, Magnus Hagander wrote:
> On Mon, Feb 25, 2019 at 10:45 PM Michael Paquier 
> 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


signature.asc
Description: PGP signature

Re: Reaping Temp tables to avoid XID wraparound

[Magnus Hagander]

On Mon, Feb 25, 2019 at 10:45 PM Michael Paquier 
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

[Michael Paquier]

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


signature.asc
Description: PGP signature

Re: Reaping Temp tables to avoid XID wraparound

[Magnus Hagander]

On Wed, Feb 20, 2019 at 3:41 AM Michael Paquier  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/ 
diff --git a/src/backend/catalog/namespace.c b/src/backend/catalog/namespace.c
index cdd5006a72..77be87c50a 100644
--- a/src/backend/catalog/namespace.c
+++ b/src/backend/catalog/namespace.c
@@ -45,6 +45,7 @@
 #include "miscadmin.h"
 #include "nodes/makefuncs.h"
 #include "parser/parse_func.h"
+#include "pgstat.h"
 #include "storage/ipc.h"
 #include "storage/lmgr.h"
 #include "storage/sinvaladt.h"
@@ -3929,6 +3930,9 @@ InitTempTableNamespace(void)
 	  true);
 		/* Advance command counter to make namespace visible */
 		CommandCounterIncrement();
+
+		/* Indicate that this is the xid that created the namespace */
+		pgstat_report_temp_namespace_xid(GetTopTransactionId());
 	}
 	else
 	{
diff --git a/src/backend/catalog/system_views.sql b/src/backend/catalog/system_views.sql
index 3e229c693c..081d655c91 100644
--- a/src/backend/catalog/system_views.sql
+++ b/src/backend/catalog/system_views.sql
@@ -709,7 +709,8 @@ CREATE VIEW pg_stat_activity AS
 S.backend_xid,
 s.backend_xmin,
 S.query,
-S.backend_type
+S.backend_type,
+	S.temp_namespace_xid
 FROM pg_stat_get_activity(NULL) AS S
 LEFT JOIN pg_database AS D ON (S.datid = D.oid)
 LEFT JOIN pg_authid AS U ON (S.usesysid = U.oid);
diff --git a/src/backend/postmaster/pgstat.c b/src/backend/postmaster/pgstat.c
index 81c6499251..7af6563031 100644
--- a/src/backend/postmaster/pgstat.c
+++ b/src/backend/postmaster/pgstat.c
@@ -2923,6 +2923,7 @@ pgstat_bestart(void)
 	/* Also make sure the last byte in each string area is always 0 */
 	beentry->st_clienthostname[NAMEDATALEN - 1] = '\0';
 	beentry->st_appname[NAMEDATALEN - 1] = '\0';
+	beentry->st_temp_namespace_xid = InvalidTransactionId;
 	beentry->st_activity_raw[pgstat_track_activity_query_size - 1] = '\0';
 	beentry->st_progress_command = PROGRESS_COMMAND_INVALID;
 	beentry->st_progress_command_target = InvalidOid;
@@ -3207,6 +3208,28 @@ pgstat_report_xact_timestamp(TimestampTz tstamp)
 	pgstat_increment_changecount_after(beentry);
 }
 
+/*
+ * Report the transaction id that created a temporary namespace in this
+ * session.
+ */
+void
+pgstat_report_temp_namespace_xid(TransactionId xid)
+{
+	volatile PgBackendStatus *beentry = MyBEEntry;
+
+	if (!pgstat_track_activities || !beentry)
+		return;
+
+	/*
+	 * Update my status entry, following the protocol of bumping
+	 * st_changecount before and after.  We use a volatile pointer here to
+	 * ensure the compiler doesn't try to get cute.
+	 */
+	pgstat_increment_changecount_before(beentry);
+	beentry->st_temp_namespace_xid = xid;
+	pgstat_increment_changecount_after(beentry);
+}
+
 /* --
  * pgstat_read_current_status() -
  *
diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c
index 69f7265779..2b98af372b 100644
--- a/src/backend/utils/adt/pgstatfuncs.c
+++ b/src/backend/utils/adt/pgstatfuncs.c
@@ -541,7 +541,7 @@ pg_stat_get_progress_info(PG_FUNCTION_ARGS)
 Datum
 pg_stat_get_activity(PG_FUNCTION_ARGS)
 {
-#define PG_STAT_GET_ACTIVITY_COLS	26
+#define PG_STAT_GET_ACTIVITY_COLS	27
 	int			num_backends = pgstat_fetch_stat_numbackends();
 	int			curr_backend;
 	int			pid = PG_ARGISNULL(0) ? -1 : PG_GETARG_INT32(0);
@@ -645,6 +645,11 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 		else
 			nulls[16] = true;
 
+		if (TransactionIdIsValid(local_beentry->backendStatus.st_temp_namespace_xid))
+			values[17] = TransactionIdGetDatum(local_beentry->backendStatus.st_temp_namespace_xid);
+		else
+			nulls[17] = true;
+
 		/* Values only available to role member or pg_read_all_stats */
 		if (has_privs_of_role(GetUserId(), beentry->st_userid) ||
 			is_member_of_role(GetUserId(), DEFAULT_ROLE_READ_ALL_STATS))
@@ -815,45 +820,45 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
 
 bgw_type = GetBackgroundWorkerTypeByPid(beentry->st_procpid);
 if (bgw_type)
-	values[17] 

Re: Reaping Temp tables to avoid XID wraparound

[Michael Paquier]

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


signature.asc
Description: PGP signature

Re: Reaping Temp tables to avoid XID wraparound

[Magnus Hagander]

On Mon, Feb 18, 2019 at 2:31 AM Michael Paquier  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

[Michael Paquier]

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


signature.asc
Description: PGP signature

Re: Reaping Temp tables to avoid XID wraparound

[James Sewell]

On Mon, 18 Feb 2019 at 12:31, Michael Paquier  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

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

[Michael Paquier]

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


signature.asc
Description: PGP signature

Re: Reaping Temp tables to avoid XID wraparound

[James Sewell]

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

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

[Magnus Hagander]

On Thu, Feb 14, 2019 at 1:43 AM Michael Paquier  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 
> 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

[Michael Paquier]

On Wed, Feb 13, 2019 at 05:48:39PM +0100, Magnus Hagander wrote:
> On Wed, Feb 13, 2019 at 2:26 AM Michael Paquier  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


signature.asc
Description: PGP signature

Re: Reaping Temp tables to avoid XID wraparound

[James Sewell]

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  wrote:

> On Wed, Feb 13, 2019 at 6:05 PM Tom Lane  wrote:
>
>> Magnus Hagander  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/ 
>

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

[Magnus Hagander]

On Wed, Feb 13, 2019 at 6:05 PM Tom Lane  wrote:

> Magnus Hagander  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

[Tom Lane]

Magnus Hagander  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

[Magnus Hagander]

On Wed, Feb 13, 2019 at 2:26 AM Michael Paquier  wrote:

> On Wed, Feb 13, 2019 at 12:38:51AM +, 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

[Tsunakawa, Takayuki]

From: Andrew Gierth [mailto:and...@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 allocated from two different data structures.  
Confusing.


From: Michael Paquier [mailto:mich...@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

[Michael Paquier]

On Wed, Feb 13, 2019 at 12:38:51AM +, 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


signature.asc
Description: PGP signature

Re: Reaping Temp tables to avoid XID wraparound

[Andrew Gierth]

> "Tsunakawa" == Tsunakawa, Takayuki  
> 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

[Tsunakawa, Takayuki]

From: James Sewell [mailto:james.sew...@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-gene...@lists.postgresql.org.


Regards
Takayuki Tsunakawa