On Mon, Nov 18, 2013 at 1:54 AM, Sameer Thakur <samthaku...@gmail.com> wrote:
> Please find v10 of patch attached. This patch addresses following
> review comments
I've cleaned this up - revision attached - and marked it "ready for committer".
I decided that queryid should be of type oid, not bigint. This is
arguably a slight abuse of notation, but since ultimately Oids are
just abstract object identifiers (so say the docs), but also because
there is no other convenient, minimal way of representing unsigned
32-bit integers in the view that I'm aware of, I'm inclined to think
that it's appropriate.
In passing, I've made pg_stat_statements invalidate serialized entries
if there is a change in major version. This seems desirable as a
catch-all invalidator of entries.
I note that Tom has objected to exposing the queryid in the past, on
numerous occasions. I'm more confident than ever that it's actually
the right thing to do. I've had people I don't know walk up to me at
conferences and ask me what we don't already expose this at least
twice now. There are very strong indications that many people want
this, and given that I've documented the caveats, I think that we
should trust those calling for this. At the very least, it allows
people to GROUP BY queryid, when they don't want things broken out by
userid.
We're self-evidently already effectively relying on the queryid to be
as stable as it is documented to be in this patch. The hash function
cannot really change in minor releases, because to do so would at the
very least necessitate re-indexing hash indexes, and would of course
invalidate internally managed pg_stat_statements entries, both of
which are undesirable outcomes (and therefore, for these reasons and
more, unlikely). Arguments for not documenting hash_any() do not apply
here -- we're already suffering the full consequences of whatever
queryid instability may exist.
Quite apart from all of that, I think we need to have a way of
identifying particular entries for the purposes of supporting
per-entry "settings". Recent discussion about min/max query time, or
somehow characterizing the distribution of each entry's historic
execution time (or whatever) have not considered one important
questoin: What are you supposed to do when you find out that there is
an outlier (whatever an outlier is)?
I won't go into the details, because there is little point, but I'm
reasonably confident that it will be virtually impossible for
pg_stat_statements itself to usefully classify particular query
executions as outliers (I'm not even sure that we could do it if we
assumed a normal distribution, which would be bogus, and certainly
made very noisy by caching effects and so on. Furthermore, who are we
to say that an outlier is an execution time two sigmas to the right?
Seems useless).
Outliers are typically caused by things like bad plans, or problematic
constant values that appear in the most common values list (and are
therefore just inherently far more expensive to query against), or
lock contention. In all of those cases, with a min/max or something we
probably won't even get to know what the problematic constant values
were when response time suddenly suffers, because of course
pg_stat_statements doesn't help with that. So have we gained much?
Even with detective work, the trail might have gone cold by the time
the outlier is examined. And, monitoring is only one concern -- what
about alerting?
The bigger point is that having this will facilitate being able to
mark entries as "SLA queries" or something like that, where if their
execution exceeds a time (specified by the DBA per entry), that is
assumed to be very bad, and pg_stat_statements complains. That gets
dumped to the logs (which ought to be a rare occurrence when the
facility is used correctly). Of course, the (typically particularly
problematic) constant values *do* appear in the logs, and there is a
greppable keyword, potentially for the benefit of a tool like
tail_n_mail. You could think of this as being like a smart
log_min_duration_statement. I think that the DBA needs to tell
pg_stat_statements what to care about, and what bad looks like. If the
DBA doesn't know where to start specifying such things, the 5 queries
with the most calls can usefully have this set to (mean_execution_time
* 1.5) or something. SLA queries can also be "pinned", perhaps (that
is, given a "stay of execution" when eviction occurs).
--
Peter Geoghegan
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
new file mode 100644
index e8aed61..95a2767
*** a/contrib/pg_stat_statements/Makefile
--- b/contrib/pg_stat_statements/Makefile
*************** MODULE_big = pg_stat_statements
*** 4,11 ****
OBJS = pg_stat_statements.o
EXTENSION = pg_stat_statements
! DATA = pg_stat_statements--1.1.sql pg_stat_statements--1.0--1.1.sql \
! pg_stat_statements--unpackaged--1.0.sql
ifdef USE_PGXS
PG_CONFIG = pg_config
--- 4,11 ----
OBJS = pg_stat_statements.o
EXTENSION = pg_stat_statements
! DATA = pg_stat_statements--1.2.sql pg_stat_statements--1.1--1.2.sql \
! pg_stat_statements--1.0--1.1.sql pg_stat_statements--unpackaged--1.0.sql
ifdef USE_PGXS
PG_CONFIG = pg_config
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.1--1.2.sql b/contrib/pg_stat_statements/pg_stat_statements--1.1--1.2.sql
new file mode 100644
index ...7dd6368
*** a/contrib/pg_stat_statements/pg_stat_statements--1.1--1.2.sql
--- b/contrib/pg_stat_statements/pg_stat_statements--1.1--1.2.sql
***************
*** 0 ****
--- 1,43 ----
+ /* contrib/pg_stat_statements/pg_stat_statements--1.1--1.2.sql */
+
+ -- complain if script is sourced in psql, rather than via ALTER EXTENSION
+ \echo Use "ALTER EXTENSION pg_stat_statements UPDATE" to load this file. \quit
+
+ /* First we have to remove them from the extension */
+ ALTER EXTENSION pg_stat_statements DROP VIEW pg_stat_statements;
+ ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements();
+
+ /* Then we can drop them */
+ DROP VIEW pg_stat_statements;
+ DROP FUNCTION pg_stat_statements();
+
+ /* Now redefine */
+ CREATE FUNCTION pg_stat_statements(
+ OUT userid oid,
+ OUT dbid oid,
+ OUT queryid oid,
+ OUT query text,
+ OUT calls int8,
+ OUT total_time float8,
+ OUT rows int8,
+ OUT shared_blks_hit int8,
+ OUT shared_blks_read int8,
+ OUT shared_blks_dirtied int8,
+ OUT shared_blks_written int8,
+ OUT local_blks_hit int8,
+ OUT local_blks_read int8,
+ OUT local_blks_dirtied int8,
+ OUT local_blks_written int8,
+ OUT temp_blks_read int8,
+ OUT temp_blks_written int8,
+ OUT blk_read_time float8,
+ OUT blk_write_time float8
+ )
+ RETURNS SETOF record
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C;
+
+ CREATE VIEW pg_stat_statements AS
+ SELECT * FROM pg_stat_statements();
+
+ GRANT SELECT ON pg_stat_statements TO PUBLIC;
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.1.sql b/contrib/pg_stat_statements/pg_stat_statements--1.1.sql
new file mode .
index 42e4d68..e69de29
*** a/contrib/pg_stat_statements/pg_stat_statements--1.1.sql
--- b/contrib/pg_stat_statements/pg_stat_statements--1.1.sql
***************
*** 1,43 ****
- /* contrib/pg_stat_statements/pg_stat_statements--1.1.sql */
-
- -- complain if script is sourced in psql, rather than via CREATE EXTENSION
- \echo Use "CREATE EXTENSION pg_stat_statements" to load this file. \quit
-
- -- Register functions.
- CREATE FUNCTION pg_stat_statements_reset()
- RETURNS void
- AS 'MODULE_PATHNAME'
- LANGUAGE C;
-
- CREATE FUNCTION pg_stat_statements(
- OUT userid oid,
- OUT dbid oid,
- OUT query text,
- OUT calls int8,
- OUT total_time float8,
- OUT rows int8,
- OUT shared_blks_hit int8,
- OUT shared_blks_read int8,
- OUT shared_blks_dirtied int8,
- OUT shared_blks_written int8,
- OUT local_blks_hit int8,
- OUT local_blks_read int8,
- OUT local_blks_dirtied int8,
- OUT local_blks_written int8,
- OUT temp_blks_read int8,
- OUT temp_blks_written int8,
- OUT blk_read_time float8,
- OUT blk_write_time float8
- )
- RETURNS SETOF record
- AS 'MODULE_PATHNAME'
- LANGUAGE C;
-
- -- Register a view on the function for ease of use.
- CREATE VIEW pg_stat_statements AS
- SELECT * FROM pg_stat_statements();
-
- GRANT SELECT ON pg_stat_statements TO PUBLIC;
-
- -- Don't want this to be available to non-superusers.
- REVOKE ALL ON FUNCTION pg_stat_statements_reset() FROM PUBLIC;
--- 0 ----
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.2.sql b/contrib/pg_stat_statements/pg_stat_statements--1.2.sql
new file mode 100644
index ...83a8454
*** a/contrib/pg_stat_statements/pg_stat_statements--1.2.sql
--- b/contrib/pg_stat_statements/pg_stat_statements--1.2.sql
***************
*** 0 ****
--- 1,44 ----
+ /* contrib/pg_stat_statements/pg_stat_statements--1.2.sql */
+
+ -- complain if script is sourced in psql, rather than via CREATE EXTENSION
+ \echo Use "CREATE EXTENSION pg_stat_statements" to load this file. \quit
+
+ -- Register functions.
+ CREATE FUNCTION pg_stat_statements_reset()
+ RETURNS void
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C;
+
+ CREATE FUNCTION pg_stat_statements(
+ OUT userid oid,
+ OUT dbid oid,
+ OUT queryid oid,
+ OUT query text,
+ OUT calls int8,
+ OUT total_time float8,
+ OUT rows int8,
+ OUT shared_blks_hit int8,
+ OUT shared_blks_read int8,
+ OUT shared_blks_dirtied int8,
+ OUT shared_blks_written int8,
+ OUT local_blks_hit int8,
+ OUT local_blks_read int8,
+ OUT local_blks_dirtied int8,
+ OUT local_blks_written int8,
+ OUT temp_blks_read int8,
+ OUT temp_blks_written int8,
+ OUT blk_read_time float8,
+ OUT blk_write_time float8
+ )
+ RETURNS SETOF record
+ AS 'MODULE_PATHNAME'
+ LANGUAGE C;
+
+ -- Register a view on the function for ease of use.
+ CREATE VIEW pg_stat_statements AS
+ SELECT * FROM pg_stat_statements();
+
+ GRANT SELECT ON pg_stat_statements TO PUBLIC;
+
+ -- Don't want this to be available to non-superusers.
+ REVOKE ALL ON FUNCTION pg_stat_statements_reset() FROM PUBLIC;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
new file mode 100644
index ea930af..3e6b146
*** a/contrib/pg_stat_statements/pg_stat_statements.c
--- b/contrib/pg_stat_statements/pg_stat_statements.c
*************** PG_MODULE_MAGIC;
*** 67,73 ****
#define PGSS_DUMP_FILE "global/pg_stat_statements.stat"
/* This constant defines the magic number in the stats file header */
! static const uint32 PGSS_FILE_HEADER = 0x20120328;
/* XXX: Should USAGE_EXEC reflect execution time and/or buffer usage? */
#define USAGE_EXEC(duration) (1.0)
--- 67,75 ----
#define PGSS_DUMP_FILE "global/pg_stat_statements.stat"
/* This constant defines the magic number in the stats file header */
! static const uint32 PGSS_FILE_HEADER = 0x20131115;
! /* Postgres major version number, changes in which invalidate all entries */
! static const uint32 PGSS_PG_MAJOR_VERSION = PG_VERSION_NUM / 100;
/* XXX: Should USAGE_EXEC reflect execution time and/or buffer usage? */
#define USAGE_EXEC(duration) (1.0)
*************** static const uint32 PGSS_FILE_HEADER = 0
*** 80,85 ****
--- 82,97 ----
#define JUMBLE_SIZE 1024 /* query serialization buffer size */
/*
+ * Extension version number, for supporting older extension versions' objects
+ */
+ typedef enum pgssVersion
+ {
+ PGSS_V1_0,
+ PGSS_V1_1,
+ PGSS_V1_2
+ } pgssVersion;
+
+ /*
* Hashtable key that defines the identity of a hashtable entry. We separate
* queries by user and by database even if they are otherwise identical.
*
*************** typedef struct pgssHashKey
*** 92,98 ****
Oid userid; /* user OID */
Oid dbid; /* database OID */
int encoding; /* query encoding */
! uint32 queryid; /* query identifier */
} pgssHashKey;
/*
--- 104,110 ----
Oid userid; /* user OID */
Oid dbid; /* database OID */
int encoding; /* query encoding */
! Oid queryid; /* query identifier */
} pgssHashKey;
/*
*************** static void pgss_ProcessUtility(Node *pa
*** 245,251 ****
static uint32 pgss_hash_fn(const void *key, Size keysize);
static int pgss_match_fn(const void *key1, const void *key2, Size keysize);
static uint32 pgss_hash_string(const char *str);
! static void pgss_store(const char *query, uint32 queryId,
double total_time, uint64 rows,
const BufferUsage *bufusage,
pgssJumbleState *jstate);
--- 257,263 ----
static uint32 pgss_hash_fn(const void *key, Size keysize);
static int pgss_match_fn(const void *key1, const void *key2, Size keysize);
static uint32 pgss_hash_string(const char *str);
! static void pgss_store(const char *query, Oid queryId,
double total_time, uint64 rows,
const BufferUsage *bufusage,
pgssJumbleState *jstate);
*************** pgss_shmem_startup(void)
*** 390,395 ****
--- 402,408 ----
FILE *file;
uint32 header;
int32 num;
+ int32 pgver;
int32 i;
int query_size;
int buffer_size;
*************** pgss_shmem_startup(void)
*** 465,470 ****
--- 478,485 ----
if (fread(&header, sizeof(uint32), 1, file) != 1 ||
header != PGSS_FILE_HEADER ||
+ fread(&pgver, sizeof(uint32), 1, file) != 1 ||
+ pgver != PGSS_PG_MAJOR_VERSION ||
fread(&num, sizeof(int32), 1, file) != 1)
goto error;
*************** pgss_shmem_shutdown(int code, Datum arg)
*** 565,570 ****
--- 580,587 ----
if (fwrite(&PGSS_FILE_HEADER, sizeof(uint32), 1, file) != 1)
goto error;
+ if (fwrite(&PGSS_PG_MAJOR_VERSION, sizeof(uint32), 1, file) != 1)
+ goto error;
num_entries = hash_get_num_entries(pgss_hash);
if (fwrite(&num_entries, sizeof(int32), 1, file) != 1)
goto error;
*************** pgss_ExecutorFinish(QueryDesc *queryDesc
*** 756,762 ****
static void
pgss_ExecutorEnd(QueryDesc *queryDesc)
{
! uint32 queryId = queryDesc->plannedstmt->queryId;
if (queryId != 0 && queryDesc->totaltime && pgss_enabled())
{
--- 773,779 ----
static void
pgss_ExecutorEnd(QueryDesc *queryDesc)
{
! Oid queryId = (Oid) queryDesc->plannedstmt->queryId;
if (queryId != 0 && queryDesc->totaltime && pgss_enabled())
{
*************** pgss_ProcessUtility(Node *parsetree, con
*** 809,815 ****
uint64 rows = 0;
BufferUsage bufusage_start,
bufusage;
! uint32 queryId;
bufusage_start = pgBufferUsage;
INSTR_TIME_SET_CURRENT(start);
--- 826,832 ----
uint64 rows = 0;
BufferUsage bufusage_start,
bufusage;
! Oid queryId;
bufusage_start = pgBufferUsage;
INSTR_TIME_SET_CURRENT(start);
*************** pgss_hash_string(const char *str)
*** 942,948 ****
* query string. total_time, rows, bufusage are ignored in this case.
*/
static void
! pgss_store(const char *query, uint32 queryId,
double total_time, uint64 rows,
const BufferUsage *bufusage,
pgssJumbleState *jstate)
--- 959,965 ----
* query string. total_time, rows, bufusage are ignored in this case.
*/
static void
! pgss_store(const char *query, Oid queryId,
double total_time, uint64 rows,
const BufferUsage *bufusage,
pgssJumbleState *jstate)
*************** pg_stat_statements_reset(PG_FUNCTION_ARG
*** 1069,1075 ****
}
#define PG_STAT_STATEMENTS_COLS_V1_0 14
! #define PG_STAT_STATEMENTS_COLS 18
/*
* Retrieve statement statistics.
--- 1086,1093 ----
}
#define PG_STAT_STATEMENTS_COLS_V1_0 14
! #define PG_STAT_STATEMENTS_COLS_V1_1 18
! #define PG_STAT_STATEMENTS_COLS 19
/*
* Retrieve statement statistics.
*************** pg_stat_statements(PG_FUNCTION_ARGS)
*** 1086,1092 ****
bool is_superuser = superuser();
HASH_SEQ_STATUS hash_seq;
pgssEntry *entry;
! bool sql_supports_v1_1_counters = true;
if (!pgss || !pgss_hash)
ereport(ERROR,
--- 1104,1110 ----
bool is_superuser = superuser();
HASH_SEQ_STATUS hash_seq;
pgssEntry *entry;
! pgssVersion detected_version;
if (!pgss || !pgss_hash)
ereport(ERROR,
*************** pg_stat_statements(PG_FUNCTION_ARGS)
*** 1107,1114 ****
/* Build a tuple descriptor for our result type */
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
! if (tupdesc->natts == PG_STAT_STATEMENTS_COLS_V1_0)
! sql_supports_v1_1_counters = false;
per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
oldcontext = MemoryContextSwitchTo(per_query_ctx);
--- 1125,1145 ----
/* Build a tuple descriptor for our result type */
if (get_call_result_type(fcinfo, NULL, &tupdesc) != TYPEFUNC_COMPOSITE)
elog(ERROR, "return type must be a row type");
!
! switch (tupdesc->natts)
! {
! case PG_STAT_STATEMENTS_COLS_V1_0:
! detected_version = PGSS_V1_0;
! break;
! case PG_STAT_STATEMENTS_COLS_V1_1:
! detected_version = PGSS_V1_1;
! break;
! case PG_STAT_STATEMENTS_COLS:
! detected_version = PGSS_V1_2;
! break;
! default:
! elog(ERROR, "pgss version unrecognized from tuple descriptor");
! }
per_query_ctx = rsinfo->econtext->ecxt_per_query_memory;
oldcontext = MemoryContextSwitchTo(per_query_ctx);
*************** pg_stat_statements(PG_FUNCTION_ARGS)
*** 1140,1145 ****
--- 1171,1179 ----
{
char *qstr;
+ if (detected_version >= PGSS_V1_2)
+ values[i++] = ObjectIdGetDatum(entry->key.queryid);
+
qstr = (char *)
pg_do_encoding_conversion((unsigned char *) entry->query,
entry->query_len,
*************** pg_stat_statements(PG_FUNCTION_ARGS)
*** 1150,1156 ****
--- 1184,1195 ----
pfree(qstr);
}
else
+ {
+ if (detected_version >= PGSS_V1_2)
+ nulls[i++] = true;
+
values[i++] = CStringGetTextDatum("<insufficient privilege>");
+ }
/* copy counters to a local variable to keep locking time short */
{
*************** pg_stat_statements(PG_FUNCTION_ARGS)
*** 1170,1193 ****
values[i++] = Int64GetDatumFast(tmp.rows);
values[i++] = Int64GetDatumFast(tmp.shared_blks_hit);
values[i++] = Int64GetDatumFast(tmp.shared_blks_read);
! if (sql_supports_v1_1_counters)
values[i++] = Int64GetDatumFast(tmp.shared_blks_dirtied);
values[i++] = Int64GetDatumFast(tmp.shared_blks_written);
values[i++] = Int64GetDatumFast(tmp.local_blks_hit);
values[i++] = Int64GetDatumFast(tmp.local_blks_read);
! if (sql_supports_v1_1_counters)
values[i++] = Int64GetDatumFast(tmp.local_blks_dirtied);
values[i++] = Int64GetDatumFast(tmp.local_blks_written);
values[i++] = Int64GetDatumFast(tmp.temp_blks_read);
values[i++] = Int64GetDatumFast(tmp.temp_blks_written);
! if (sql_supports_v1_1_counters)
{
values[i++] = Float8GetDatumFast(tmp.blk_read_time);
values[i++] = Float8GetDatumFast(tmp.blk_write_time);
}
! Assert(i == (sql_supports_v1_1_counters ?
! PG_STAT_STATEMENTS_COLS : PG_STAT_STATEMENTS_COLS_V1_0));
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
}
--- 1209,1235 ----
values[i++] = Int64GetDatumFast(tmp.rows);
values[i++] = Int64GetDatumFast(tmp.shared_blks_hit);
values[i++] = Int64GetDatumFast(tmp.shared_blks_read);
! if (detected_version >= PGSS_V1_1)
values[i++] = Int64GetDatumFast(tmp.shared_blks_dirtied);
values[i++] = Int64GetDatumFast(tmp.shared_blks_written);
values[i++] = Int64GetDatumFast(tmp.local_blks_hit);
values[i++] = Int64GetDatumFast(tmp.local_blks_read);
! if (detected_version >= PGSS_V1_1)
values[i++] = Int64GetDatumFast(tmp.local_blks_dirtied);
values[i++] = Int64GetDatumFast(tmp.local_blks_written);
values[i++] = Int64GetDatumFast(tmp.temp_blks_read);
values[i++] = Int64GetDatumFast(tmp.temp_blks_written);
! if (detected_version >= PGSS_V1_1)
{
values[i++] = Float8GetDatumFast(tmp.blk_read_time);
values[i++] = Float8GetDatumFast(tmp.blk_write_time);
}
! Assert(i == (detected_version == PGSS_V1_0?
! PG_STAT_STATEMENTS_COLS_V1_0:
! detected_version == PGSS_V1_1?
! PG_STAT_STATEMENTS_COLS_V1_1:
! PG_STAT_STATEMENTS_COLS));
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
}
diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control
new file mode 100644
index 428fbb2..6ecf2b6
*** a/contrib/pg_stat_statements/pg_stat_statements.control
--- b/contrib/pg_stat_statements/pg_stat_statements.control
***************
*** 1,5 ****
# pg_stat_statements extension
comment = 'track execution statistics of all SQL statements executed'
! default_version = '1.1'
module_pathname = '$libdir/pg_stat_statements'
relocatable = true
--- 1,5 ----
# pg_stat_statements extension
comment = 'track execution statistics of all SQL statements executed'
! default_version = '1.2'
module_pathname = '$libdir/pg_stat_statements'
relocatable = true
diff --git a/doc/src/sgml/pgstatstatements.sgml b/doc/src/sgml/pgstatstatements.sgml
new file mode 100644
index c02fdf4..f54c6a0
*** a/doc/src/sgml/pgstatstatements.sgml
--- b/doc/src/sgml/pgstatstatements.sgml
***************
*** 23,33 ****
<title>The <structname>pg_stat_statements</structname> View</title>
<para>
! The statistics gathered by the module are made available via a system view
! named <structname>pg_stat_statements</>. This view contains one row for
! each distinct query, database ID, and user ID (up to the maximum
! number of distinct statements that the module can track). The columns
! of the view are shown in <xref linkend="pgstatstatements-columns">.
</para>
<table id="pgstatstatements-columns">
--- 23,34 ----
<title>The <structname>pg_stat_statements</structname> View</title>
<para>
! The statistics gathered by the module are made available via a
! system view named <structname>pg_stat_statements</>. This view
! contains one row for each distinct database ID, user ID and query
! ID (up to the maximum number of distinct statements that the module
! can track). The columns of the view are shown in
! <xref linkend="pgstatstatements-columns">.
</para>
<table id="pgstatstatements-columns">
***************
*** 57,63 ****
<entry>OID of database in which the statement was executed</entry>
</row>
! <row>
<entry><structfield>query</structfield></entry>
<entry><type>text</type></entry>
<entry></entry>
--- 58,71 ----
<entry>OID of database in which the statement was executed</entry>
</row>
! <row>
! <entry><structfield>queryid</structfield></entry>
! <entry><type>oid</type></entry>
! <entry></entry>
! <entry>OID of internal identifier, a hash value computed from the entry's observed post-parse-analysis tree</entry>
! </row>
!
! <row>
<entry><structfield>query</structfield></entry>
<entry><type>text</type></entry>
<entry></entry>
***************
*** 189,197 ****
</para>
<para>
! For security reasons, non-superusers are not allowed to see the text of
! queries executed by other users. They can see the statistics, however,
! if the view has been installed in their database.
</para>
<para>
--- 197,206 ----
</para>
<para>
! For security reasons, non-superusers are not allowed to see the SQL
! text or queryid of queries executed by other users. They can see
! the statistics, however, if the view has been installed in their
! database.
</para>
<para>
***************
*** 209,216 ****
When a constant's value has been ignored for purposes of matching the
query to other queries, the constant is replaced by <literal>?</literal>
in the <structname>pg_stat_statements</> display. The rest of the query
! text is that of the first query that had the particular hash value
! associated with the <structname>pg_stat_statements</> entry.
</para>
<para>
--- 218,226 ----
When a constant's value has been ignored for purposes of matching the
query to other queries, the constant is replaced by <literal>?</literal>
in the <structname>pg_stat_statements</> display. The rest of the query
! text is that of the first query that had the particular
! <structfield>queryid</> hash value associated with the
! <structname>pg_stat_statements</> entry.
</para>
<para>
***************
*** 223,232 ****
</para>
<para>
! Since the hash value is computed on the post-parse-analysis representation
! of the queries, the opposite is also possible: queries with identical texts
! might appear as separate entries, if they have different meanings as a
! result of factors such as different <varname>search_path</> settings.
</para>
</sect2>
--- 233,277 ----
</para>
<para>
! Since the <structfield>queryid</> hash value is computed on the
! post-parse-analysis representation of the queries, the opposite is
! also possible: queries with identical texts might appear as
! separate entries, if they have different meanings as a result of
! factors such as different <varname>search_path</> settings.
! </para>
!
! <para>
! Consumers of <literal>pg_stat_statements</> may wish to use
! <structfield>queryid</> (perhaps in composite with
! <structfield>dbid</> and <structfield>userid</>) as a more stable
! and reliable identifier for each entry than its query text.
! However, it is important to understand that there are only limited
! guarantees around the stability of the <structfield>queryid</> hash
! value. Since the identifier is derived from the
! post-parse-analysis tree, its value is a function of, among other
! things, the internal identifiers that comprise this representation.
! This has some counterintuitive implications. For example, a query
! against a table that is fingerprinted by
! <literal>pg_stat_statements</> will appear distinct to a
! subsequently executed query that a reasonable observer might judge
! to be a non-distinct, if in the interim the table was dropped and
! re-created. The hashing process is sensitive to difference in
! machine architecture and other facets of the platform.
! Furthermore, it is not safe to assume that <structfield>queryid</>
! will be stable across major versions of <productname>PostgreSQL</>.
! </para>
!
! <para>
! As a rule of thumb, an assumption of the stability or comparability
! of <structfield>querid</> values should be predicated on the the
! underlying catalog metadata and hash function implementation
! details exactly matching. Any two servers participating in any
! variety of replication based on physical WAL-replay can be expected
! to have identical <structfield>querid</> values for the same query.
! Logical replication schemes do not have replicas comparable in all
! relevant regards, and so <structfield>querid</> will not be a
! useful identifier for accumulating costs for the entire replica set
! in aggregate. If in doubt, direct testing is recommended.
</para>
</sect2>
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
