Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oO5bt-0000JC-0B for pgsql-hackers@arkaria.postgresql.org; Tue, 16 Aug 2022 23:04:33 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1oO5bq-0005Yp-5x for pgsql-hackers@arkaria.postgresql.org; Tue, 16 Aug 2022 23:04:30 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1oO5bp-0005Yd-Mq for pgsql-hackers@lists.postgresql.org; Tue, 16 Aug 2022 23:04:29 +0000 Received: from mail-pl1-x636.google.com ([2607:f8b0:4864:20::636]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1oO5bn-00062A-0R for pgsql-hackers@postgresql.org; Tue, 16 Aug 2022 23:04:28 +0000 Received: by mail-pl1-x636.google.com with SMTP id u22so2256906plq.12 for ; Tue, 16 Aug 2022 16:04:26 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:from:to:cc; bh=Cb0U6zntlv5N+Nhun/ff1Xap9TmdI7M13eRtoDc5kww=; b=BNaGdPdJMI+XC4T4CIFJFdTbR2u8dqs1pskPtXwKQejD4ZkqDIOhBPIXqphj3Ljrza POoF0Z8h3r5/Rf+kIKqdp0N8Ix+zBiFEwr3RO0XxvbrNphAEQAGLYsKmvx8VPyEjTcCo CZSY1Bj93ZH16CUt9wPbS6gI+6c8NaKqHXDiUMRod95AsgdxYiGm2qjlxIfAPSZ7iYQ7 JMfSeb1dY/nmiKjavd2yxa2H6DKyx48atStikjOKNdY3kvZ+VqJEvYgkGqtBm4M4xvqw A5w8pvK6CFQcZ3x+iuCgvxbZ3Iuc+fg2L4DJRmyfEeQhu6KL3rJUV1JjKIW/8T/BQ6tx vSDA== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=in-reply-to:content-disposition:mime-version:references:message-id :subject:cc:to:from:date:x-gm-message-state:from:to:cc; bh=Cb0U6zntlv5N+Nhun/ff1Xap9TmdI7M13eRtoDc5kww=; b=66FCYbwnN8Bol0Ticdyvue8fc+O8vasPyjhsXMBgWEeqd8DbrDI2Hjn55ElNCvLG7r KlhVqpFPCxgP1fumO15gTpfxHQeF1sF0IZf+XDyZJRvzHN5iezIVVpdx0jrRVz5dzyAI J/Hu661XB3CVTjm7Qe0K8BZKcV5dwxNexTHNzd+rNMvm6KJ/ELEio6YfWhwtUan+7NbG okdsIH6J+44oSBV+ZWMfjlGGaXFvXMg6zXVkGlFFyZ2dIc0FvI7Qkp1pwVC3hH/ytZMm zOfUy1ibtTeYzuZxxcyrtXJk9AzZktpjRs2rIxZfKyq2k/N+wQBkMqOfJQqUlXXxOe/T n7sQ== X-Gm-Message-State: ACgBeo1rf59UaNqfxhFalqBlHxwUA/+wTLC/SyFnd1NDKuU5/aBQ77rh +RkgtYaGpLJFshgJ9bMZTW8= X-Google-Smtp-Source: AA6agR53tl90JeBji4dn828nsuj5Z7f2d7QA/39kkPBbylXVc+xyWaJALNHmiQ3jKNwsfur95Bh12g== X-Received: by 2002:a17:902:e749:b0:171:2480:4320 with SMTP id p9-20020a170902e74900b0017124804320mr23813473plf.153.1660691065870; Tue, 16 Aug 2022 16:04:25 -0700 (PDT) Received: from nathanxps13 ([50.54.155.70]) by smtp.gmail.com with ESMTPSA id a4-20020a1709027e4400b0016784c93f23sm9522793pln.197.2022.08.16.16.04.24 (version=TLS1_3 cipher=TLS_AES_256_GCM_SHA384 bits=256/256); Tue, 16 Aug 2022 16:04:25 -0700 (PDT) Date: Tue, 16 Aug 2022 16:04:23 -0700 From: Nathan Bossart To: Jeremy Schneider Cc: pgsql-hackers@postgresql.org Subject: Re: identifying the backend that owns a temporary schema Message-ID: <20220816230423.GA341431@nathanxps13> References: <20220815205811.GA250990@nathanxps13> <36e8f3ee-dd29-83e3-7f04-3cdb1737d6a3@amazon.com> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="xHFwDpU9dbj6ez1V" Content-Disposition: inline In-Reply-To: <36e8f3ee-dd29-83e3-7f04-3cdb1737d6a3@amazon.com> List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --xHFwDpU9dbj6ez1V Content-Type: text/plain; charset=us-ascii Content-Disposition: inline On Mon, Aug 15, 2022 at 02:47:25PM -0700, Jeremy Schneider wrote: > I'll take a look at the patch if I can... and I'm hopeful that we're > able to move this idea forward and get this little gap in PG filled once > and for all! Thanks! I noticed that the "result" variable in pg_stat_get_backend_idset() is kind of pointless after my patch is applied, so here is a v2 with it removed. -- Nathan Bossart Amazon Web Services: https://aws.amazon.com --xHFwDpU9dbj6ez1V Content-Type: text/x-diff; charset=us-ascii Content-Disposition: attachment; filename="v2-0001-Adjust-pg_stat_get_backend_-to-use-backends-PGPRO.patch" From 0f2af75e8b1be3dc75979f4902cc4634636a3fe2 Mon Sep 17 00:00:00 2001 From: Nathan Bossart Date: Fri, 12 Aug 2022 23:07:37 -0700 Subject: [PATCH v2 1/1] Adjust pg_stat_get_backend_*() to use backends' PGPROC backend IDs. Presently, these functions use the index of the backend's entry in localBackendStatusTable as the backend ID. While this might bear some resemblance to the backend ID of the backend's PGPROC struct, it can quickly diverge as sessions connect and disconnect. This change modifies the pg_stat_get_backend_*() suite of functions to use the PGPROC backend IDs instead. While we could instead introduce a new function for retrieving PGPROC backend IDs, presenting two sets of backend IDs to users seems likely to cause even more confusion than what already exists. This is primarily useful for discovering the session that owns a resource named with its PGPROC backend ID. For example, temporary schema names include the PGPROC backend ID, and it might be necessary to identify the session that owns a temporary table that is putting the cluster in danger of transaction ID wraparound. Author: Nathan Bossart --- doc/src/sgml/monitoring.sgml | 8 ++--- src/backend/utils/activity/backend_status.c | 40 +++++++++++++++++++-- src/backend/utils/adt/pgstatfuncs.c | 11 +++--- src/include/utils/backend_status.h | 8 +++++ 4 files changed, 55 insertions(+), 12 deletions(-) diff --git a/doc/src/sgml/monitoring.sgml b/doc/src/sgml/monitoring.sgml index 1d9509a2f6..ecd0410229 100644 --- a/doc/src/sgml/monitoring.sgml +++ b/doc/src/sgml/monitoring.sgml @@ -5488,10 +5488,9 @@ SELECT pid, wait_event_type, wait_event FROM pg_stat_activity WHERE wait_event i information. In such cases, an older set of per-backend statistics access functions can be used; these are shown in . - These access functions use a backend ID number, which ranges from one - to the number of currently active backends. + These access functions use the process's backend ID number. The function pg_stat_get_backend_idset provides a - convenient way to generate one row for each active backend for + convenient way to list all the active backends' ID numbers for invoking these functions. For example, to show the PIDs and current queries of all backends: @@ -5526,8 +5525,7 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS pid, setof integer - Returns the set of currently active backend ID numbers (from 1 to the - number of active backends). + Returns the set of currently active backend ID numbers. diff --git a/src/backend/utils/activity/backend_status.c b/src/backend/utils/activity/backend_status.c index c7ed1e6d7a..159d022070 100644 --- a/src/backend/utils/activity/backend_status.c +++ b/src/backend/utils/activity/backend_status.c @@ -849,6 +849,7 @@ pgstat_read_current_status(void) BackendIdGetTransactionIds(i, &localentry->backend_xid, &localentry->backend_xmin); + localentry->backend_id = i; localentry++; localappname += NAMEDATALEN; @@ -1045,6 +1046,22 @@ pgstat_get_my_query_id(void) return MyBEEntry->st_query_id; } +/* ---------- + * cmp_lbestatus + * + * Comparison function for bsearch() on an array of LocalPgBackendStatus. The + * backend_id field is used to compare the arguments. + * + * ---------- + */ +static int +cmp_lbestatus(const void *a, const void *b) +{ + LocalPgBackendStatus *lbestatus1 = (LocalPgBackendStatus *) a; + LocalPgBackendStatus *lbestatus2 = (LocalPgBackendStatus *) b; + + return lbestatus1->backend_id - lbestatus2->backend_id; +} /* ---------- * pgstat_fetch_stat_beentry() - @@ -1052,6 +1069,10 @@ pgstat_get_my_query_id(void) * Support function for the SQL-callable pgstat* functions. Returns * our local copy of the current-activity entry for one backend. * + * Unlike pgstat_fetch_stat_local_beentry(), the beid argument refers to the + * backend ID stored in the backend's PGPROC struct instead of its index in + * the localBackendStatusTable. + * * NB: caller is responsible for a check if the user is permitted to see * this info (especially the querystring). * ---------- @@ -1059,12 +1080,23 @@ pgstat_get_my_query_id(void) PgBackendStatus * pgstat_fetch_stat_beentry(int beid) { + LocalPgBackendStatus key; + LocalPgBackendStatus *ret; + pgstat_read_current_status(); - if (beid < 1 || beid > localNumBackends) + if (beid < 1 || beid > NumBackendStatSlots) return NULL; - return &localBackendStatusTable[beid - 1].backendStatus; + key.backend_id = beid; + ret = (LocalPgBackendStatus *) bsearch(&key, localBackendStatusTable, + localNumBackends, + sizeof(LocalPgBackendStatus), + cmp_lbestatus); + if (ret) + return &ret->backendStatus; + + return NULL; } @@ -1074,6 +1106,10 @@ pgstat_fetch_stat_beentry(int beid) * Like pgstat_fetch_stat_beentry() but with locally computed additions (like * xid and xmin values of the backend) * + * Unlike pgstat_fetch_stat_beentry(), the beid argument refers to the index + * in the localBackendStatusTable instead of the backend ID stored in the + * backend's PGPROC struct. + * * NB: caller is responsible for a check if the user is permitted to see * this info (especially the querystring). * ---------- diff --git a/src/backend/utils/adt/pgstatfuncs.c b/src/backend/utils/adt/pgstatfuncs.c index d9e2a79382..981bd2b372 100644 --- a/src/backend/utils/adt/pgstatfuncs.c +++ b/src/backend/utils/adt/pgstatfuncs.c @@ -415,7 +415,6 @@ pg_stat_get_backend_idset(PG_FUNCTION_ARGS) { FuncCallContext *funcctx; int *fctx; - int32 result; /* stuff done only on the first call of the function */ if (SRF_IS_FIRSTCALL()) @@ -436,12 +435,13 @@ pg_stat_get_backend_idset(PG_FUNCTION_ARGS) fctx = funcctx->user_fctx; fctx[0] += 1; - result = fctx[0]; - if (result <= fctx[1]) + if (fctx[0] <= fctx[1]) { + LocalPgBackendStatus *localStatus = pgstat_fetch_stat_local_beentry(fctx[0]); + /* do when there is more left to send */ - SRF_RETURN_NEXT(funcctx, Int32GetDatum(result)); + SRF_RETURN_NEXT(funcctx, Int32GetDatum(localStatus->backend_id)); } else { @@ -1187,7 +1187,8 @@ pg_stat_get_db_numbackends(PG_FUNCTION_ARGS) result = 0; for (beid = 1; beid <= tot_backends; beid++) { - PgBackendStatus *beentry = pgstat_fetch_stat_beentry(beid); + LocalPgBackendStatus *localStatus = pgstat_fetch_stat_local_beentry(beid); + PgBackendStatus *beentry = &localStatus->backendStatus; if (beentry && beentry->st_databaseid == dbid) result++; diff --git a/src/include/utils/backend_status.h b/src/include/utils/backend_status.h index 7403bca25e..f7c7c6e671 100644 --- a/src/include/utils/backend_status.h +++ b/src/include/utils/backend_status.h @@ -13,6 +13,7 @@ #include "datatype/timestamp.h" #include "libpq/pqcomm.h" #include "miscadmin.h" /* for BackendType */ +#include "storage/backendid.h" #include "utils/backend_progress.h" @@ -258,6 +259,13 @@ typedef struct LocalPgBackendStatus * not. */ TransactionId backend_xmin; + + /* + * The backend ID. For auxiliary processes, this will be set to a value + * greater than MaxBackends (since auxiliary processes do not have proper + * backend IDs). + */ + BackendId backend_id; } LocalPgBackendStatus; -- 2.25.1 --xHFwDpU9dbj6ez1V--