Re: Feature improvement: can we add queryId for pg_catalog.pg_stat_activity view?

[Julien Rouhaud]

On Wed, Apr 07, 2021 at 04:22:55PM -0400, Bruce Momjian wrote:
> aOn Wed, Apr  7, 2021 at 04:15:50PM -0400, Tom Lane wrote:
> > Bruce Momjian <br...@momjian.us> writes:
> > > Patch applied.  I am ready to adjust this with any improvements people
> > > might have.  Thank you for all the good feedback we got on this, and I
> > > know many users have waited a long time for this feature.
> > 
> > For starters, you could try to make the buildfarm green again.
> 
> Wow, that's odd.  The cfbot was green, so I never even looked at the
> buildfarm.  I will look at that now, and the CVS log issue.

Sorry about that.  The issue came from animals with jit_above_cost = 0
outputting more lines than expected.  I fixed that by using the same query as
before in explain.sql, as they don't generate any JIT output.

I also added the queryid to the csvlog output and fixed the documentation that
mention how to create a table to access the data.

diff --git a/doc/src/sgml/config.sgml b/doc/src/sgml/config.sgml
index 963824d050..a9d0d63a57 100644
--- a/doc/src/sgml/config.sgml
+++ b/doc/src/sgml/config.sgml
@@ -7341,6 +7341,7 @@ CREATE TABLE postgres_log
   application_name text,
   backend_type text,
   leader_pid integer,
+  queryid bigint,
   PRIMARY KEY (session_id, session_line_num)
 );
 </programlisting>
diff --git a/doc/src/sgml/file-fdw.sgml b/doc/src/sgml/file-fdw.sgml
index 2e21806f48..27827146f1 100644
--- a/doc/src/sgml/file-fdw.sgml
+++ b/doc/src/sgml/file-fdw.sgml
@@ -266,7 +266,8 @@ CREATE FOREIGN TABLE pglog (
   location text,
   application_name text,
   backend_type text,
-  leader_pid integer
+  leader_pid integer,
+  queryid bigint
 ) SERVER pglog
 OPTIONS ( filename 'log/pglog.csv', format 'csv' );
 </programlisting>
diff --git a/src/backend/utils/error/elog.c b/src/backend/utils/error/elog.c
index 1cf71a649b..d27fb999d9 100644
--- a/src/backend/utils/error/elog.c
+++ b/src/backend/utils/error/elog.c
@@ -2965,6 +2965,9 @@ write_csvlog(ErrorData *edata)
                        appendStringInfo(&buf, "%d", leader->pid);
        }
 
+       appendStringInfoChar(&buf, ',');
+       appendStringInfo(&buf, "%zd", pgstat_get_my_queryid());
+
        appendStringInfoChar(&buf, '\n');
 
        /* If in the syslogger process, try to write messages direct to file */
diff --git a/src/test/regress/expected/explain.out 
b/src/test/regress/expected/explain.out
index 4c578d4f5e..cda28098ba 100644
--- a/src/test/regress/expected/explain.out
+++ b/src/test/regress/expected/explain.out
@@ -478,11 +478,11 @@ select jsonb_pretty(
 
 rollback;
 set compute_query_id = on;
-select explain_filter('explain (verbose) select 1');
-             explain_filter             
-----------------------------------------
- Result  (cost=N.N..N.N rows=N width=N)
-   Output: N
+select explain_filter('explain (verbose) select * from int8_tbl i8');
+                         explain_filter                         
+----------------------------------------------------------------
+ Seq Scan on public.int8_tbl i8  (cost=N.N..N.N rows=N width=N)
+   Output: q1, q2
  Query Identifier: N
 (3 rows)
 
diff --git a/src/test/regress/sql/explain.sql b/src/test/regress/sql/explain.sql
index 468caf4037..3f9ae9843a 100644
--- a/src/test/regress/sql/explain.sql
+++ b/src/test/regress/sql/explain.sql
@@ -105,4 +105,4 @@ select jsonb_pretty(
 rollback;
 
 set compute_query_id = on;
-select explain_filter('explain (verbose) select 1');
+select explain_filter('explain (verbose) select * from int8_tbl i8');