Movatterモバイル変換

[0]ホーム
URL:

Skip to content

Navigation Menu

Sign in
Appearance settings

Search code, repositories, users, issues, pull requests...

Provide feedback

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly

 Sign up
Appearance settings

Commit4f42b54

Browse files
committed
Separate state from query string in pg_stat_activity
This separates the state (running/idle/idleintransaction etc) intoit's own field ("state"), and leaves the query field containing justquery text.The query text will now mean "current query" when a query is runningand "last query" in other states. Accordingly,the field has beenrenamed from current_query to query.Since backwards compatibility was broken anyway to make that, the procpidfield has also been renamed to pid - along with the same field inpg_stat_replication for consistency.Scott Mead and Magnus Hagander, review work from Greg Smith
1 parentfa352d6 commit4f42b54

File tree

10 files changed

+378
-85
lines changed

10 files changed

+378
-85
lines changed

‎doc/src/sgml/monitoring.sgml

Lines changed: 219 additions & 14 deletions
Original file line numberDiff line numberDiff line change
@@ -242,20 +242,20 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
242242

243243
<tbody>
244244
<row>
245-
<entry><structname>pg_stat_activity</><indexterm><primary>pg_stat_activity</primary></indexterm></entry>
246-
<entry>One row per server process, showing database OID, database
247-
name, process <acronym>ID</>, user OID, user name, application name,
248-
client's address, host name (if available), and port number, times at
249-
which the server process, current transaction, and current query began
250-
execution, process's waiting status, and text of the current query.
251-
The columns that report data onthecurrent query are available unless
252-
the parameter <varname>track_activities</varname>has been turned off.
253-
Furthermore, these columns are only visible if the user examining
254-
the view is a superuser or the same as the user owning the process
255-
being reported on. The client's host name will be available only if
256-
<xref linkend="guc-log-hostname"> is set or if the user's host name
257-
needed to be looked up during <filename>pg_hba.conf</filename>
258-
processing.
245+
<entry>
246+
<structname>pg_stat_activity</structname>
247+
<indexterm><primary>pg_stat_activity</primary></indexterm>
248+
</entry>
249+
<entry>
250+
<para>One row per server process, showing information related to
251+
each connection totheserver. Unless the
252+
<xref linkend="guc-track-activities"> parameterhas been turned
253+
off, it is possible to monitor state and query information of
254+
all running processes.
255+
</para>
256+
<para>
257+
See <xref linkend="pg-stat-activity-view"> for more details.
258+
</para>
259259
</entry>
260260
</row>
261261

@@ -529,6 +529,210 @@ postgres: <replaceable>user</> <replaceable>database</> <replaceable>host</> <re
529529
into the kernel's handling of I/O.
530530
</para>
531531

532+
<table id="pg-stat-activity-view" xreflabel="pg_stat_activity">
533+
<title>pg_stat_activity view</title>
534+
535+
<tgroup cols="3">
536+
<thead>
537+
<row>
538+
<entry>Column</entry>
539+
<entry>Type</entry>
540+
<entry>Description</entry>
541+
</row>
542+
</thead>
543+
544+
<tbody>
545+
<row>
546+
<entry>datid</entry>
547+
<entry><type>oid</></entry>
548+
<entry>The oid of the database the backend is connected to.</entry>
549+
</row>
550+
<row>
551+
<entry>datname</entry>
552+
<entry><type>name</></entry>
553+
<entry>The name of the database the backend is connected to.</entry>
554+
</row>
555+
<row>
556+
<entry>pid</entry>
557+
<entry><type>integer</></entry>
558+
<entry>The process ID of the backend.</entry>
559+
</row>
560+
<row>
561+
<entry>usesysid</entry>
562+
<entry><type>oid</></entry>
563+
<entry>The id of the user logged into the backend.</entry>
564+
</row>
565+
<row>
566+
<entry>usename</entry>
567+
<entry><type>name</></entry>
568+
<entry>The name of the user logged into the backend.</entry>
569+
</row>
570+
<row>
571+
<entry>application_name</entry>
572+
<entry><type>text</></entry>
573+
<entry>The name of the application that has initiated the connection
574+
to the backend.</entry>
575+
</row>
576+
<row>
577+
<entry>client_addr</entry>
578+
<entry><type>inet</></entry>
579+
<entry>The remote IP of the client connected to the backend.
580+
If this field is not set, it indicates that the client is either:
581+
<itemizedlist spacing="compact" mark="bullet">
582+
<listitem>
583+
<para>
584+
Connected via unix sockets on the server machine
585+
</para>
586+
</listitem>
587+
<listitem>
588+
<para>An internal process such as autovacuum</para>
589+
</listitem>
590+
</itemizedlist>
591+
</entry>
592+
</row>
593+
<row>
594+
<entry>client_hostname</entry>
595+
<entry><type>text</></entry>
596+
<entry>
597+
If available, the hostname of the client as reported by a
598+
reverse lookup of <structfield>client_addr</>. This field will
599+
only be set when <xref linkend="guc-log-hostname"> is enabled.
600+
</entry>
601+
</row>
602+
<row>
603+
<entry>client_port</entry>
604+
<entry><type>integer</></entry>
605+
<entry>
606+
The remote TCP port that the client is using for communication
607+
to the backend, or <symbol>NULL</> if a unix socket is used.
608+
</entry>
609+
</row>
610+
<row>
611+
<entry>backend_start</entry>
612+
<entry><type>timestamp with time zone</></entry>
613+
<entry>
614+
The time when this process was started, i.e. when the
615+
client connected to the server.
616+
</entry>
617+
</row>
618+
<row>
619+
<entry>xact_start</entry>
620+
<entry><type>timestamp with time zone</></entry>
621+
<entry>
622+
The time when the current transaction was started. If the client is
623+
using autocommit for transactions, this value is equal to the
624+
query_start column.
625+
</entry>
626+
</row>
627+
<row>
628+
<entry>query_start</entry>
629+
<entry><type>timestamp with time zone</></entry>
630+
<entry>
631+
The time when the currently active query started, or if
632+
<structfield>state</> is <literal>idle</>, when the last query
633+
was started.
634+
</entry>
635+
</row>
636+
<row>
637+
<entry>state_change</entry>
638+
<entry><type>timestamp with time zone</></entry>
639+
<entry>The time when the <structfield>state</> was last changed.</entry>
640+
</row>
641+
<row>
642+
<entry>waiting</entry>
643+
<entry><type>boolean</></entry>
644+
<entry>
645+
Boolean indicating if a backend is currently waiting on a lock.
646+
</entry>
647+
</row>
648+
<row>
649+
<entry>state</entry>
650+
<entry><type>text</></entry>
651+
<entry>
652+
The <structfield>state</> of the currently running query.
653+
Can be one of:
654+
<variablelist>
655+
<varlistentry>
656+
<term>active</term>
657+
<listitem>
658+
<para>
659+
The backend is executing a query.
660+
</para>
661+
</listitem>
662+
</varlistentry>
663+
<varlistentry>
664+
<term>idle</term>
665+
<listitem>
666+
<para>
667+
There is no query executing in the backend.
668+
</para>
669+
</listitem>
670+
</varlistentry>
671+
<varlistentry>
672+
<term>idle in transaction</term>
673+
<listitem>
674+
<para>
675+
The backend is in a transaction, but is currently not currently
676+
executing a query.
677+
</para>
678+
</listitem>
679+
</varlistentry>
680+
<varlistentry>
681+
<term>idle in transaction (aborted)</term>
682+
<listitem>
683+
<para>
684+
This state is similar to <literal>idle in transaction</>,
685+
except one of the statements in the transaction caused an error.
686+
</para>
687+
</listitem>
688+
</varlistentry>
689+
<varlistentry>
690+
<term>fastpath function call</term>
691+
<listitem>
692+
<para>
693+
The backend is executing a fast-path function.
694+
</para>
695+
</listitem>
696+
</varlistentry>
697+
<varlistentry>
698+
<term>disabled</term>
699+
<listitem>
700+
<para>
701+
This state indicates that <xref linkend="guc-track-activities">
702+
is disabled.
703+
</para>
704+
</listitem>
705+
</varlistentry>
706+
</variablelist>
707+
<note>
708+
<para>
709+
The <structfield>waiting</> and <structfield>state</> columns are
710+
independent. If a query is in the <literal>active</> state,
711+
it may or may not be <literal>waiting</>. If a query is
712+
<literal>active</> and <structfield>waiting</> is true, it means
713+
that the query is being executed, but is being blocked by a lock
714+
somewhere in the system.
715+
</para>
716+
</note>
717+
</entry>
718+
</row>
719+
<row>
720+
<entry>query</entry>
721+
<entry><type>text</></entry>
722+
<entry>
723+
The most recent query that the backend has executed. If
724+
<structfield>state</> is <literal>active</> this means the currently
725+
executing query. In all other states, it means the last query that was
726+
executed.
727+
</entry>
728+
</row>
729+
</tbody>
730+
</tgroup>
731+
</table>
732+
733+
<sect3 id="monitoring-stats-functions">
734+
<title>Statistics Access Functions</title>
735+
532736
<para>
533737
Other ways of looking at the statistics can be set up by writing
534738
queries that use the same underlying statistics access functions as
@@ -1264,6 +1468,7 @@ SELECT pg_stat_get_backend_pid(s.backendid) AS procpid,
12641468
</programlisting>
12651469
</para>
12661470

1471+
</sect3>
12671472
</sect2>
12681473
</sect1>
12691474

‎src/backend/catalog/system_views.sql

Lines changed: 6 additions & 4 deletions
Original file line numberDiff line numberDiff line change
@@ -520,7 +520,7 @@ CREATE VIEW pg_stat_activity AS
520520
SELECT
521521
S.datidAS datid,
522522
D.datnameAS datname,
523-
S.procpid,
523+
S.pid,
524524
S.usesysid,
525525
U.rolnameAS usename,
526526
S.application_name,
@@ -530,15 +530,17 @@ CREATE VIEW pg_stat_activity AS
530530
S.backend_start,
531531
S.xact_start,
532532
S.query_start,
533+
S.state_change,
533534
S.waiting,
534-
S.current_query
535+
S.state,
536+
S.query
535537
FROM pg_database D, pg_stat_get_activity(NULL)AS S, pg_authid U
536538
WHERES.datid=D.oidAND
537539
S.usesysid=U.oid;
538540

539541
CREATEVIEWpg_stat_replicationAS
540542
SELECT
541-
S.procpid,
543+
S.pid,
542544
S.usesysid,
543545
U.rolnameAS usename,
544546
S.application_name,
@@ -556,7 +558,7 @@ CREATE VIEW pg_stat_replication AS
556558
FROM pg_stat_get_activity(NULL)AS S, pg_authid U,
557559
pg_stat_get_wal_senders()AS W
558560
WHERES.usesysid=U.oidAND
559-
S.procpid=W.procpid;
561+
S.pid=W.pid;
560562

561563
CREATEVIEWpg_stat_databaseAS
562564
SELECT

‎src/backend/postmaster/autovacuum.c

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -2781,7 +2781,7 @@ autovac_report_activity(autovac_table *tab)
27812781
/* Set statement_timestamp() to current time for pg_stat_activity */
27822782
SetCurrentStatementStartTimestamp();
27832783

2784-
pgstat_report_activity(activity);
2784+
pgstat_report_activity(STATE_RUNNING,activity);
27852785
}
27862786

27872787
/*

‎src/backend/postmaster/pgstat.c

Lines changed: 44 additions & 11 deletions
Original file line numberDiff line numberDiff line change
@@ -2410,12 +2410,14 @@ pgstat_bestart(void)
24102410
beentry->st_procpid=MyProcPid;
24112411
beentry->st_proc_start_timestamp=proc_start_timestamp;
24122412
beentry->st_activity_start_timestamp=0;
2413+
beentry->st_state_start_timestamp=0;
24132414
beentry->st_xact_start_timestamp=0;
24142415
beentry->st_databaseid=MyDatabaseId;
24152416
beentry->st_userid=userid;
24162417
beentry->st_clientaddr=clientaddr;
24172418
beentry->st_clienthostname[0]='\0';
24182419
beentry->st_waiting= false;
2420+
beentry->st_state=STATE_UNDEFINED;
24192421
beentry->st_appname[0]='\0';
24202422
beentry->st_activity[0]='\0';
24212423
/* Also make sure the last byte in each string area is always 0 */
@@ -2476,39 +2478,70 @@ pgstat_beshutdown_hook(int code, Datum arg)
24762478
*
24772479
*Called from tcop/postgres.c to report what the backend is actually doing
24782480
*(usually "<IDLE>" or the start of the query to be executed).
2481+
*
2482+
* All updates of the status entry follow the protocol of bumping
2483+
* st_changecount before and after. We use a volatile pointer here to
2484+
* ensure the compiler doesn't try to get cute.
24792485
* ----------
24802486
*/
24812487
void
2482-
pgstat_report_activity(constchar*cmd_str)
2488+
pgstat_report_activity(BackendStatestate,constchar*cmd_str)
24832489
{
24842490
volatilePgBackendStatus*beentry=MyBEEntry;
24852491
TimestampTzstart_timestamp;
2492+
TimestampTzcurrent_timestamp;
24862493
intlen;
24872494

24882495
TRACE_POSTGRESQL_STATEMENT_STATUS(cmd_str);
24892496

2490-
if (!pgstat_track_activities|| !beentry)
2497+
if (!beentry)
24912498
return;
24922499

24932500
/*
24942501
* To minimize the time spent modifying the entry, fetch all the needed
24952502
* data first.
24962503
*/
2497-
start_timestamp=GetCurrentStatementStartTimestamp();
2504+
current_timestamp=GetCurrentTimestamp();
24982505

2499-
len=strlen(cmd_str);
2500-
len=pg_mbcliplen(cmd_str,len,pgstat_track_activity_query_size-1);
2506+
if (!pgstat_track_activities&&beentry->st_state!=STATE_DISABLED)
2507+
{
2508+
/*
2509+
* Track activities is disabled, but we have a non-disabled state set.
2510+
* That means the status changed - so as our last update, tell the
2511+
* collector that we disabled it and will no longer update.
2512+
*/
2513+
beentry->st_changecount++;
2514+
beentry->st_state=STATE_DISABLED;
2515+
beentry->st_state_start_timestamp=current_timestamp;
2516+
beentry->st_changecount++;
2517+
Assert((beentry->st_changecount&1)==0);
2518+
return;
2519+
}
25012520

25022521
/*
2503-
* Update my status entry, following the protocol of bumping
2504-
* st_changecount before and after. We use a volatile pointer here to
2505-
* ensure the compiler doesn't try to get cute.
2522+
* Fetch more data before we start modifying the entry
2523+
*/
2524+
start_timestamp=GetCurrentStatementStartTimestamp();
2525+
if (cmd_str!=NULL)
2526+
{
2527+
len=strlen(cmd_str);
2528+
len=pg_mbcliplen(cmd_str,len,pgstat_track_activity_query_size-1);
2529+
}
2530+
2531+
/*
2532+
* Now update the status entry
25062533
*/
25072534
beentry->st_changecount++;
25082535

2509-
beentry->st_activity_start_timestamp=start_timestamp;
2510-
memcpy((char*)beentry->st_activity,cmd_str,len);
2511-
beentry->st_activity[len]='\0';
2536+
beentry->st_state=state;
2537+
beentry->st_state_start_timestamp=current_timestamp;
2538+
2539+
if (cmd_str!=NULL)
2540+
{
2541+
memcpy((char*)beentry->st_activity,cmd_str,len);
2542+
beentry->st_activity[len]='\0';
2543+
beentry->st_activity_start_timestamp=start_timestamp;
2544+
}
25122545

25132546
beentry->st_changecount++;
25142547
Assert((beentry->st_changecount&1)==0);

0 commit comments

Comments
 (0)
[8]ページ先頭
©2009-2025 Movatter.jp