Reaping Temp tables to avoid XID wraparound
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 <(+61)%202%208099%209000> *W* www.jirotech.com *F *
(+61) 2 8099 9099 <(+61)%202%208099%209000>
--
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.
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
"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)
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
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 allocated from 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
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
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
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/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
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@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 temptable
*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.
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
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/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
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.
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
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
--
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.
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
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/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
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
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/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
Attachments:
stat_temp_namespace_xid.patchtext/x-patch; charset=US-ASCII; name=stat_temp_namespace_xid.patchDownload
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] = CStringGetTextDatum(bgw_type);
+ values[18] = CStringGetTextDatum(bgw_type);
else
- nulls[17] = true;
+ nulls[18] = true;
}
else
- values[17] =
+ values[18] =
CStringGetTextDatum(pgstat_get_backend_desc(beentry->st_backendType));
/* SSL information */
if (beentry->st_ssl)
{
- values[18] = BoolGetDatum(true); /* ssl */
- values[19] = CStringGetTextDatum(beentry->st_sslstatus->ssl_version);
- values[20] = CStringGetTextDatum(beentry->st_sslstatus->ssl_cipher);
- values[21] = Int32GetDatum(beentry->st_sslstatus->ssl_bits);
- values[22] = BoolGetDatum(beentry->st_sslstatus->ssl_compression);
+ values[19] = BoolGetDatum(true); /* ssl */
+ values[20] = CStringGetTextDatum(beentry->st_sslstatus->ssl_version);
+ values[21] = CStringGetTextDatum(beentry->st_sslstatus->ssl_cipher);
+ values[22] = Int32GetDatum(beentry->st_sslstatus->ssl_bits);
+ values[23] = BoolGetDatum(beentry->st_sslstatus->ssl_compression);
if (beentry->st_sslstatus->ssl_client_dn[0])
- values[23] = CStringGetTextDatum(beentry->st_sslstatus->ssl_client_dn);
+ values[24] = CStringGetTextDatum(beentry->st_sslstatus->ssl_client_dn);
else
- nulls[23] = true;
+ nulls[24] = true;
if (beentry->st_sslstatus->ssl_client_serial[0])
- values[24] = DirectFunctionCall3(numeric_in,
+ values[25] = DirectFunctionCall3(numeric_in,
CStringGetDatum(beentry->st_sslstatus->ssl_client_serial),
ObjectIdGetDatum(InvalidOid),
Int32GetDatum(-1));
else
- nulls[24] = true;
+ nulls[25] = true;
if (beentry->st_sslstatus->ssl_issuer_dn[0])
- values[25] = CStringGetTextDatum(beentry->st_sslstatus->ssl_issuer_dn);
+ values[26] = CStringGetTextDatum(beentry->st_sslstatus->ssl_issuer_dn);
else
- nulls[25] = true;
+ nulls[26] = true;
}
else
{
- values[18] = BoolGetDatum(false); /* ssl */
- nulls[19] = nulls[20] = nulls[21] = nulls[22] = nulls[23] = nulls[24] = nulls[25] = true;
+ values[19] = BoolGetDatum(false); /* ssl */
+ nulls[20] = nulls[21] = nulls[22] = nulls[23] = nulls[24] = nulls[25] = nulls[26] = true;
}
}
else
@@ -870,7 +875,6 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
nulls[12] = true;
nulls[13] = true;
nulls[14] = true;
- nulls[17] = true;
nulls[18] = true;
nulls[19] = true;
nulls[20] = true;
@@ -879,6 +883,7 @@ pg_stat_get_activity(PG_FUNCTION_ARGS)
nulls[23] = true;
nulls[24] = true;
nulls[25] = true;
+ nulls[26] = true;
}
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index a4e173b484..7ad1e1e284 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -5070,9 +5070,9 @@
proname => 'pg_stat_get_activity', prorows => '100', proisstrict => 'f',
proretset => 't', provolatile => 's', proparallel => 'r',
prorettype => 'record', proargtypes => 'int4',
- proallargtypes => '{int4,oid,int4,oid,text,text,text,text,text,timestamptz,timestamptz,timestamptz,timestamptz,inet,text,int4,xid,xid,text,bool,text,text,int4,bool,text,numeric,text}',
- proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
- proargnames => '{pid,datid,pid,usesysid,application_name,state,query,wait_event_type,wait_event,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin,backend_type,ssl,sslversion,sslcipher,sslbits,sslcompression,ssl_client_dn,ssl_client_serial,ssl_issuer_dn}',
+ proallargtypes => '{int4,oid,int4,oid,text,text,text,text,text,timestamptz,timestamptz,timestamptz,timestamptz,inet,text,int4,xid,xid,xid,text,bool,text,text,int4,bool,text,numeric,text}',
+ proargmodes => '{i,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o,o}',
+ proargnames => '{pid,datid,pid,usesysid,application_name,state,query,wait_event_type,wait_event,xact_start,query_start,backend_start,state_change,client_addr,client_hostname,client_port,backend_xid,backend_xmin,temp_namespace_xid,backend_type,ssl,sslversion,sslcipher,sslbits,sslcompression,ssl_client_dn,ssl_client_serial,ssl_issuer_dn}',
prosrc => 'pg_stat_get_activity' },
{ oid => '3318',
descr => 'statistics: information about progress of backends running maintenance command',
diff --git a/src/include/pgstat.h b/src/include/pgstat.h
index 88a75fb798..c224c86f39 100644
--- a/src/include/pgstat.h
+++ b/src/include/pgstat.h
@@ -1031,6 +1031,9 @@ typedef struct PgBackendStatus
/* application name; MUST be null-terminated */
char *st_appname;
+ /* If a temporary namespace was created in this session, xid of the creating transaction */
+ TransactionId st_temp_namespace_xid;
+
/*
* Current command string; MUST be null-terminated. Note that this string
* possibly is truncated in the middle of a multi-byte character. As
@@ -1208,6 +1211,7 @@ extern void pgstat_report_activity(BackendState state, const char *cmd_str);
extern void pgstat_report_tempfile(size_t filesize);
extern void pgstat_report_appname(const char *appname);
extern void pgstat_report_xact_timestamp(TimestampTz tstamp);
+extern void pgstat_report_temp_namespace_xid(TransactionId xid);
extern const char *pgstat_get_wait_event(uint32 wait_event_info);
extern const char *pgstat_get_wait_event_type(uint32 wait_event_info);
extern const char *pgstat_get_backend_current_activity(int pid, bool checkUser);
diff --git a/src/test/regress/expected/rules.out b/src/test/regress/expected/rules.out
index 98f417cb57..039c081292 100644
--- a/src/test/regress/expected/rules.out
+++ b/src/test/regress/expected/rules.out
@@ -1738,8 +1738,9 @@ pg_stat_activity| SELECT s.datid,
s.backend_xid,
s.backend_xmin,
s.query,
- s.backend_type
- FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, ssl_client_dn, ssl_client_serial, ssl_issuer_dn)
+ s.backend_type,
+ s.temp_namespace_xid
+ FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, temp_namespace_xid, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, ssl_client_dn, ssl_client_serial, ssl_issuer_dn)
LEFT JOIN pg_database d ON ((s.datid = d.oid)))
LEFT JOIN pg_authid u ON ((s.usesysid = u.oid)));
pg_stat_all_indexes| SELECT c.oid AS relid,
@@ -1871,7 +1872,7 @@ pg_stat_replication| SELECT s.pid,
w.sync_priority,
w.sync_state,
w.reply_time
- FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, ssl_client_dn, ssl_client_serial, ssl_issuer_dn)
+ FROM ((pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, temp_namespace_xid, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, ssl_client_dn, ssl_client_serial, ssl_issuer_dn)
JOIN pg_stat_get_wal_senders() w(pid, state, sent_lsn, write_lsn, flush_lsn, replay_lsn, write_lag, flush_lag, replay_lag, sync_priority, sync_state, reply_time) ON ((s.pid = w.pid)))
LEFT JOIN pg_authid u ON ((s.usesysid = u.oid)));
pg_stat_ssl| SELECT s.pid,
@@ -1883,7 +1884,7 @@ pg_stat_ssl| SELECT s.pid,
s.ssl_client_dn AS client_dn,
s.ssl_client_serial AS client_serial,
s.ssl_issuer_dn AS issuer_dn
- FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, ssl_client_dn, ssl_client_serial, ssl_issuer_dn);
+ FROM pg_stat_get_activity(NULL::integer) s(datid, pid, usesysid, application_name, state, query, wait_event_type, wait_event, xact_start, query_start, backend_start, state_change, client_addr, client_hostname, client_port, backend_xid, backend_xmin, temp_namespace_xid, backend_type, ssl, sslversion, sslcipher, sslbits, sslcompression, ssl_client_dn, ssl_client_serial, ssl_issuer_dn);
pg_stat_subscription| SELECT su.oid AS subid,
su.subname,
st.pid,
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
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 hidesAnd 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/ <http://www.hagander.net/>
Work: https://www.redpill-linpro.com/ <http://www.redpill-linpro.com/>
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