I've goofed up now, sorry for failing to attach my updated patch. Am Donnerstag, den 08.03.2018, 14:55 +0100 schrieb Julian Markwort: > Tom Lane wrote on 2018-03-02: > > You need to make your changes in a 1.5--1.6 > > upgrade file. Otherwise there's no clean path for existing > > installations > > to upgrade to the new version. > > I've adressed all the issues that were brought up so far: > 1. there is now only an added 1.5--1.6.sql file. > 2. the overhead, as previously discussed (as much as a 12% decrease > in > TPS during read-only tests), is now gone, the problem was that I was > collecting the plan String before checking if it needed to be stored > at > all. > The patched version is now 99.95% as fast as unmodified > pg_stat_statements. > 3. I've cleaned up my own code and made sure it adheres to GNU C > coding > style, I was guilty of some // comments and curly brackets were > sometimes in the same line as my control structures. > > I'd love to hear more feedback, here are two ideas to polish this > patch: > a) Right now, good_plan and bad_plan collection can be activated and > deactivated with separate GUCs. I think it would be sensible to > collect > either both or none. (This would result in fewer convoluted > conditionals.) > b) Would you like to be able to tune the percentiles yourself, to > adjust for the point at which a new plan is stored? > > Greetings > Julian
diff --git a/contrib/pg_stat_statements/Makefile b/contrib/pg_stat_statements/Makefile
index 39b368b..49bb462 100644
--- a/contrib/pg_stat_statements/Makefile
+++ b/contrib/pg_stat_statements/Makefile
@@ -4,7 +4,8 @@ MODULE_big = pg_stat_statements
OBJS = pg_stat_statements.o $(WIN32RES)
EXTENSION = pg_stat_statements
-DATA = pg_stat_statements--1.4.sql pg_stat_statements--1.4--1.5.sql \
+DATA = pg_stat_statements--1.4.sql \
+ pg_stat_statements--1.5--1.6.sql pg_stat_statements--1.4--1.5.sql \
pg_stat_statements--1.3--1.4.sql pg_stat_statements--1.2--1.3.sql \
pg_stat_statements--1.1--1.2.sql pg_stat_statements--1.0--1.1.sql \
pg_stat_statements--unpackaged--1.0.sql
diff --git a/contrib/pg_stat_statements/expected/pg_stat_statements.out b/contrib/pg_stat_statements/expected/pg_stat_statements.out
index 5318c35..3e79890 100644
--- a/contrib/pg_stat_statements/expected/pg_stat_statements.out
+++ b/contrib/pg_stat_statements/expected/pg_stat_statements.out
@@ -395,4 +395,40 @@ SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
SELECT pg_stat_statements_reset() | 1 | 1
(8 rows)
+-- test to see if any plans have been recorded.
+SELECT
+ CASE WHEN good_plan_time > 0 THEN 1 ELSE 0 END,
+ CASE WHEN bad_plan_time > 0 THEN 1 ELSE 0 END,
+ CASE WHEN good_plan_timestamp >= timestamp '1970-01-01 00:00:00' THEN 1 ELSE 0 END,
+ CASE WHEN good_plan_timestamp >= timestamp '1970-01-01 00:00:00' THEN 1 ELSE 0 END
+FROM pg_stat_statements ORDER BY query COLLATE "C";
+ case | case | case | case
+------+------+------+------
+ 0 | 0 | 0 | 0
+ 0 | 0 | 0 | 0
+ 0 | 0 | 0 | 0
+ 0 | 0 | 0 | 0
+ 0 | 0 | 0 | 0
+ 0 | 0 | 0 | 0
+ 1 | 1 | 1 | 1
+ 1 | 1 | 1 | 1
+ 1 | 1 | 1 | 1
+(9 rows)
+
+-- test if there is some text in the recorded plans.
+select substr(good_plan, 0, 11), substr(bad_plan, 0, 11) from pg_stat_statements ORDER BY query COLLATE "C";
+ substr | substr
+------------+------------
+ |
+ |
+ |
+ |
+ |
+ |
+ Query Text | Query Text
+ Query Text | Query Text
+ Query Text | Query Text
+ Query Text | Query Text
+(10 rows)
+
DROP EXTENSION pg_stat_statements;
diff --git a/contrib/pg_stat_statements/pg_stat_statements--1.5--1.6.sql b/contrib/pg_stat_statements/pg_stat_statements--1.5--1.6.sql
new file mode 100644
index 0000000..5302d01
--- /dev/null
+++ b/contrib/pg_stat_statements/pg_stat_statements--1.5--1.6.sql
@@ -0,0 +1,78 @@
+/* contrib/pg_stat_statements/pg_stat_statements--1.5--1.6.sql */
+
+-- complain if script is sourced in psql, rather than via CREATE EXTENSION
+\echo Use "ALTER EXTENSION pg_stat_statements UPDATE TO '1.6'" 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(boolean);
+ALTER EXTENSION pg_stat_statements DROP FUNCTION pg_stat_statements_reset();
+
+/* Then we can drop them */
+DROP VIEW pg_stat_statements;
+DROP FUNCTION pg_stat_statements(boolean);
+DROP FUNCTION pg_stat_statements_reset();
+
+-- Register functions.
+CREATE FUNCTION pg_stat_statements_reset()
+RETURNS void
+AS 'MODULE_PATHNAME'
+LANGUAGE C PARALLEL SAFE;
+
+CREATE FUNCTION pg_stat_statements_good_plan_reset(IN queryid bigint)
+RETURNS void
+AS 'MODULE_PATHNAME'
+LANGUAGE C PARALLEL SAFE;
+
+CREATE FUNCTION pg_stat_statements_bad_plan_reset(IN queryid bigint)
+RETURNS void
+AS 'MODULE_PATHNAME'
+LANGUAGE C PARALLEL SAFE;
+
+CREATE FUNCTION pg_stat_statements(IN showtext boolean,
+ OUT userid oid,
+ OUT dbid oid,
+ OUT queryid bigint,
+ OUT query text,
+ OUT good_plan text,
+ OUT bad_plan text,
+ OUT calls int8,
+ OUT total_time float8,
+ OUT min_time float8,
+ OUT max_time float8,
+ OUT mean_time float8,
+ OUT stddev_time float8,
+ OUT good_plan_time float8,
+ OUT good_plan_timestamp timestamp,
+ OUT bad_plan_time float8,
+ OUT bad_plan_timestamp timestamp,
+ 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', 'pg_stat_statements_1_6'
+LANGUAGE C STRICT VOLATILE PARALLEL SAFE;
+
+-- Register a view on the function for ease of use.
+CREATE VIEW pg_stat_statements AS
+ SELECT * FROM pg_stat_statements(true);
+
+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;
+REVOKE ALL ON FUNCTION pg_stat_statements_good_plan_reset(bigint) FROM PUBLIC;
+REVOKE ALL ON FUNCTION pg_stat_statements_bad_plan_reset(bigint) FROM PUBLIC;
+
+GRANT EXECUTE ON FUNCTION pg_stat_statements_reset() TO pg_read_all_stats;
diff --git a/contrib/pg_stat_statements/pg_stat_statements.c b/contrib/pg_stat_statements/pg_stat_statements.c
index 9286734..66b54ff 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.c
+++ b/contrib/pg_stat_statements/pg_stat_statements.c
@@ -63,6 +63,7 @@
#include "access/hash.h"
#include "catalog/pg_authid.h"
+#include "commands/explain.h"
#include "executor/instrument.h"
#include "funcapi.h"
#include "mb/pg_wchar.h"
@@ -78,6 +79,7 @@
#include "tcop/utility.h"
#include "utils/builtins.h"
#include "utils/memutils.h"
+#include "utils/timestamp.h"
PG_MODULE_MAGIC;
@@ -119,7 +121,8 @@ typedef enum pgssVersion
PGSS_V1_0 = 0,
PGSS_V1_1,
PGSS_V1_2,
- PGSS_V1_3
+ PGSS_V1_3,
+ PGSS_V1_6
} pgssVersion;
/*
@@ -165,6 +168,14 @@ typedef struct Counters
double usage; /* usage factor */
} Counters;
+typedef struct pgssPlan
+{
+ Size offset;
+ int len;
+ double time; /* execution time in msec when the latest plan was updated */
+ TimestampTz timestamp;
+} pgssPlan;
+
/*
* Statistics per statement
*
@@ -178,6 +189,8 @@ typedef struct pgssEntry
Counters counters; /* the statistics for this query */
Size query_offset; /* query text offset in external file */
int query_len; /* # of valid bytes in query string, or -1 */
+ pgssPlan good_plan;
+ pgssPlan bad_plan;
int encoding; /* query text encoding */
slock_t mutex; /* protects the counters only */
} pgssEntry;
@@ -265,11 +278,22 @@ static const struct config_enum_entry track_options[] =
{NULL, 0, false}
};
+static const struct config_enum_entry format_options[] =
+{
+ {"text", EXPLAIN_FORMAT_TEXT, false},
+ {"xml", EXPLAIN_FORMAT_XML, false},
+ {"json", EXPLAIN_FORMAT_JSON, false},
+ {"yaml", EXPLAIN_FORMAT_YAML, false},
+ {NULL, 0, false}
+};
+
static int pgss_max; /* max # statements to track */
static int pgss_track; /* tracking level */
static bool pgss_track_utility; /* whether to track utility commands */
static bool pgss_save; /* whether to save stats across shutdown */
-
+static bool pgss_good_plan_enable; /* whether to save good_plans */
+static bool pgss_bad_plan_enable; /* whether to save good_plans */
+static int pgss_plan_format; /* id which sets the output format */
#define pgss_enabled() \
(pgss_track == PGSS_TRACK_ALL || \
@@ -291,7 +315,11 @@ void _PG_fini(void);
PG_FUNCTION_INFO_V1(pg_stat_statements_reset);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_2);
PG_FUNCTION_INFO_V1(pg_stat_statements_1_3);
+PG_FUNCTION_INFO_V1(pg_stat_statements_1_6);
PG_FUNCTION_INFO_V1(pg_stat_statements);
+PG_FUNCTION_INFO_V1(pg_stat_statements_good_plan_reset);
+PG_FUNCTION_INFO_V1(pg_stat_statements_bad_plan_reset);
+
static void pgss_shmem_startup(void);
static void pgss_shmem_shutdown(int code, Datum arg);
@@ -309,12 +337,17 @@ static void pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
static uint64 pgss_hash_string(const char *str, int len);
static void pgss_store(const char *query, uint64 queryId,
int query_location, int query_len,
+ QueryDesc *queryDesc,
double total_time, uint64 rows,
const BufferUsage *bufusage,
pgssJumbleState *jstate);
+Datum pg_stat_statements_plan_reset(uint64 query_id, uint8 plan_type);
static void pg_stat_statements_internal(FunctionCallInfo fcinfo,
pgssVersion api_version,
bool showtext);
+static int fill_plan_times(Datum values[], bool nulls[], int i, pgssPlan *plan);
+static int fill_plan_str(Datum values[], bool nulls[], int i, pgssPlan *plan,
+ pgssEntry *entry, char *qbuffer, Size qbuffer_size);
static Size pgss_memsize(void);
static pgssEntry *entry_alloc(pgssHashKey *key, Size query_offset, int query_len,
int encoding, bool sticky);
@@ -407,6 +440,40 @@ _PG_init(void)
NULL,
NULL);
+ DefineCustomBoolVariable("pg_stat_statements.good_plan_enable",
+ "Enable bad plan detection",
+ NULL,
+ &pgss_good_plan_enable,
+ true,
+ PGC_SUSET,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
+ DefineCustomBoolVariable("pg_stat_statements.bad_plan_enable",
+ "Enable bad plan detection",
+ NULL,
+ &pgss_bad_plan_enable,
+ true,
+ PGC_SUSET,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
+ DefineCustomEnumVariable("pg_stat_statements.plan_format",
+ "Sets the output format for the plans.",
+ "Notice that the plan format can not be changed after a plan is saved. Valid values are text, json, xml and yaml.",
+ &pgss_plan_format,
+ EXPLAIN_FORMAT_TEXT,
+ format_options,
+ PGC_SUSET,
+ 0,
+ NULL,
+ NULL,
+ NULL);
+
EmitWarningsOnPlaceholders("pg_stat_statements");
/*
@@ -470,7 +537,11 @@ pgss_shmem_startup(void)
int32 pgver;
int32 i;
int buffer_size;
+ int good_plan_buffer_size;
+ int bad_plan_buffer_size;
char *buffer = NULL;
+ char *good_plan_buffer = NULL;
+ char *bad_plan_buffer = NULL;
if (prev_shmem_startup_hook)
prev_shmem_startup_hook();
@@ -561,7 +632,11 @@ pgss_shmem_startup(void)
}
buffer_size = 2048;
+ good_plan_buffer_size = 2048;
+ bad_plan_buffer_size = 2048;
buffer = (char *) palloc(buffer_size);
+ good_plan_buffer = (char *) palloc(good_plan_buffer_size);
+ bad_plan_buffer = (char *) palloc(bad_plan_buffer_size);
if (fread(&header, sizeof(uint32), 1, file) != 1 ||
fread(&pgver, sizeof(uint32), 1, file) != 1 ||
@@ -595,8 +670,34 @@ pgss_shmem_startup(void)
if (fread(buffer, 1, temp.query_len + 1, file) != temp.query_len + 1)
goto read_error;
+ /* Resize good plan_buffer and read into it */
+ if (temp.good_plan.len >= good_plan_buffer_size)
+ {
+ good_plan_buffer_size =
+ Max(good_plan_buffer_size * 2, temp.good_plan.len + 1);
+ good_plan_buffer= repalloc(good_plan_buffer, good_plan_buffer_size);
+ }
+
+ if (fread(good_plan_buffer, 1, temp.good_plan.len + 1, file)
+ != temp.good_plan.len + 1)
+ goto read_error;
+
+ /* Resize bad plan_buffer and read into it */
+ if (temp.bad_plan.len >= bad_plan_buffer_size)
+ {
+ bad_plan_buffer_size =
+ Max(bad_plan_buffer_size * 2, temp.bad_plan.len + 1);
+ bad_plan_buffer= repalloc(bad_plan_buffer, bad_plan_buffer_size);
+ }
+
+ if (fread(bad_plan_buffer, 1, temp.bad_plan.len + 1, file)
+ != temp.bad_plan.len + 1)
+ goto read_error;
+
/* Should have a trailing null, but let's make sure */
buffer[temp.query_len] = '\0';
+ good_plan_buffer[temp.good_plan.len] = '\0';
+ bad_plan_buffer[temp.bad_plan.len] = '\0';
/* Skip loading "sticky" entries */
if (temp.counters.calls == 0)
@@ -608,6 +709,20 @@ pgss_shmem_startup(void)
goto write_error;
pgss->extent += temp.query_len + 1;
+ /* Store the good plan text*/
+ temp.good_plan.offset = pgss->extent;
+ if (fwrite(good_plan_buffer, 1, temp.good_plan.len + 1, qfile)
+ != temp.good_plan.len + 1)
+ goto write_error;
+ pgss->extent += temp.good_plan.len + 1;
+
+ /* Store the bad plan text*/
+ temp.bad_plan.offset = pgss->extent;
+ if (fwrite(bad_plan_buffer, 1, temp.bad_plan.len + 1, qfile)
+ != temp.bad_plan.len + 1)
+ goto write_error;
+ pgss->extent += temp.bad_plan.len + 1;
+
/* make the hashtable entry (discards old entries if too many) */
entry = entry_alloc(&temp.key, query_offset, temp.query_len,
temp.encoding,
@@ -615,9 +730,14 @@ pgss_shmem_startup(void)
/* copy in the actual stats */
entry->counters = temp.counters;
+ /* copy in the plans */
+ entry->good_plan = temp.good_plan;
+ entry->bad_plan = temp.bad_plan;
}
pfree(buffer);
+ pfree(good_plan_buffer);
+ pfree(bad_plan_buffer);
FreeFile(file);
FreeFile(qfile);
@@ -658,6 +778,10 @@ write_error:
fail:
if (buffer)
pfree(buffer);
+ if (good_plan_buffer)
+ pfree(good_plan_buffer);
+ if (bad_plan_buffer)
+ pfree(bad_plan_buffer);
if (file)
FreeFile(file);
if (qfile)
@@ -723,14 +847,54 @@ pgss_shmem_shutdown(int code, Datum arg)
while ((entry = hash_seq_search(&hash_seq)) != NULL)
{
int len = entry->query_len;
+ int good_plan_len = entry->good_plan.len;
+ int bad_plan_len = entry->bad_plan.len;
char *qstr = qtext_fetch(entry->query_offset, len,
qbuffer, qbuffer_size);
+ char *good_plan_str;
+ char *bad_plan_str;
+ if (good_plan_len > 0)
+ {
+ /* A good plan is available */
+ good_plan_str = qtext_fetch(entry->good_plan.offset, good_plan_len,
+ qbuffer, qbuffer_size);
+ }
+ else
+ {
+ /*
+ * There is no good plan available. This can be caused by a query
+ * which has no plan (for example insert or delete queries) or
+ * because good plans are disabled. In this case we have to store
+ * an empty string instead of null.
+ */
+ good_plan_str = palloc(1 * sizeof(char));
+ *good_plan_str = '\0';
+ }
+ if (bad_plan_len > 0)
+ {
+ /* A bad plan is available */
+ bad_plan_str = qtext_fetch(entry->bad_plan.offset, bad_plan_len,
+ qbuffer, qbuffer_size);
+ }
+ else
+ {
+ /*
+ * There is no bad plan available. This can be caused by a query
+ * which has no plan (for example insert or delete queries) or
+ * because bad plans are disabled. In this case we have to store
+ * an empty string instead of null.
+ */
+ bad_plan_str = palloc(1 * sizeof(char));
+ *bad_plan_str = '\0';
+ }
if (qstr == NULL)
continue; /* Ignore any entries with bogus texts */
-
+ /* Write entries, queries and plans serialized to the dump file */
if (fwrite(entry, sizeof(pgssEntry), 1, file) != 1 ||
- fwrite(qstr, 1, len + 1, file) != len + 1)
+ fwrite(qstr, 1, len + 1, file) != len + 1 ||
+ fwrite(good_plan_str, 1, good_plan_len + 1, file) != good_plan_len + 1 ||
+ fwrite(bad_plan_str, 1, bad_plan_len + 1, file) != bad_plan_len + 1)
{
/* note: we assume hash_seq_term won't change errno */
hash_seq_term(&hash_seq);
@@ -835,6 +999,7 @@ pgss_post_parse_analyze(ParseState *pstate, Query *query)
query->queryId,
query->stmt_location,
query->stmt_len,
+ NULL,
0,
0,
NULL,
@@ -942,6 +1107,7 @@ pgss_ExecutorEnd(QueryDesc *queryDesc)
queryId,
queryDesc->plannedstmt->stmt_location,
queryDesc->plannedstmt->stmt_len,
+ queryDesc, /* queryDesc itself is needed to retrieve the plan */
queryDesc->totaltime->total * 1000.0, /* convert to msec */
queryDesc->estate->es_processed,
&queryDesc->totaltime->bufusage,
@@ -1053,6 +1219,7 @@ pgss_ProcessUtility(PlannedStmt *pstmt, const char *queryString,
0, /* signal that it's a utility stmt */
pstmt->stmt_location,
pstmt->stmt_len,
+ NULL,
INSTR_TIME_GET_MILLISEC(duration),
rows,
&bufusage,
@@ -1083,6 +1250,26 @@ pgss_hash_string(const char *str, int len)
len, 0));
}
+/*
+ * Update a plan in the text file.
+ * We can re-use qtext_store to save the plan.
+ */
+static void
+update_plan(volatile pgssPlan* pgssPlan_ptr, const char* plan_str, int plan_len,
+ double total_time)
+{
+ if (plan_len > 0)
+ {
+ Size plan_offset;
+ qtext_store(plan_str, plan_len, &plan_offset, NULL);
+ pgssPlan_ptr->offset = plan_offset;
+ pgssPlan_ptr->len = plan_len;
+ pgssPlan_ptr->time = total_time;
+ pgssPlan_ptr->timestamp = GetCurrentTimestamp();
+ }
+ return;
+}
+
/*
* Store some statistics for a statement.
*
@@ -1096,6 +1283,7 @@ pgss_hash_string(const char *str, int len)
static void
pgss_store(const char *query, uint64 queryId,
int query_location, int query_len,
+ QueryDesc *queryDesc,
double total_time, uint64 rows,
const BufferUsage *bufusage,
pgssJumbleState *jstate)
@@ -1134,6 +1322,7 @@ pgss_store(const char *query, uint64 queryId,
query_len = strlen(query);
}
+
/*
* Discard leading and trailing whitespace, too. Use scanner_isspace()
* not libc's isspace(), because we want to match the lexer's behavior.
@@ -1231,9 +1420,106 @@ pgss_store(const char *query, uint64 queryId,
* locking rules at the head of the file)
*/
volatile pgssEntry *e = (volatile pgssEntry *) entry;
+ double interquartile_dist = 0.0;
+ bool good_plan_needs_updating = false;
+ bool bad_plan_needs_updating = false;
+ int plan_len = 0;
+ char* plan;
SpinLockAcquire(&e->mutex);
+ if (queryDesc && (pgss_good_plan_enable || pgss_bad_plan_enable)){
+ /*
+ * The Z-scores allows us to estimate the values at any quantiles
+ * in a list of values with (assumed) normal distribution, known mean,
+ * and known standard deviation. The value at any quantile is equal to
+ * z*stddev + mean. The distance to the mean is thus z*stddev,
+ * which we'll use to calculate the interquartile distance.
+ */
+ interquartile_dist = 2.0*(0.6745 * sqrt(e->counters.sum_var_time / e->counters.calls));
+
+ if (pgss_good_plan_enable)
+ {
+ /*
+ * Good plan needs to be updated if no previous good_plan has beed recorded.
+ * Else, if the execution time is smaller than the minimum of mean - 1.5*IQD,
+ * and the previous good_plan's time, the good_plan needs to be updated.
+ */
+ if (e->good_plan.time == 0)
+ {
+ good_plan_needs_updating = true;
+ }
+ else if (interquartile_dist > 0.0 && total_time < e->good_plan.time
+ && total_time < (e->counters.mean_time - 1.5 * interquartile_dist))
+ {
+ good_plan_needs_updating = true;
+ }
+
+ }
+
+ if (pgss_bad_plan_enable)
+ {
+ /*
+ * Bad plan needs to be updated if no previous bad_plan has beed recorded.
+ * Else, if the execution time is greater than the maximum of mean + 1.5*IQD,
+ * and the previous bad_plan's time, the bad_plan needs to be updated.
+ */
+
+ if (e->bad_plan.time == 0)
+ {
+ bad_plan_needs_updating = true;
+ }
+ else if (interquartile_dist > 0.0 && total_time > e->bad_plan.time
+ && total_time > (e->counters.mean_time + 1.5 * interquartile_dist))
+ {
+ bad_plan_needs_updating = true;
+ }
+ }
+
+ if (good_plan_needs_updating || bad_plan_needs_updating){
+ /* *es is used to retrieve the plan associated with the statement*/
+ ExplainState *es = NewExplainState();
+
+ es->analyze = (queryDesc->instrument_options && false);
+ es->verbose = false;
+ es->buffers = (es->analyze && false);
+ es->timing = (es->analyze && true);
+ es->format = pgss_plan_format;
+
+ ExplainBeginOutput(es);
+ ExplainQueryText(es, queryDesc);
+ ExplainPrintPlan(es, queryDesc);
+ ExplainEndOutput(es);
+
+ /* Remove last line break */
+ if (es->str->len > 0 && es->str->data[es->str->len - 1] == '\n')
+ es->str->data[--es->str->len] = '\0';
+
+ /* Fix JSON to output an object */
+ if (pgss_plan_format == EXPLAIN_FORMAT_JSON)
+ {
+ es->str->data[0] = '{';
+ es->str->data[es->str->len - 1] = '}';
+ }
+
+ plan = es->str->data;
+ plan_len = strlen(plan);
+
+ if (good_plan_needs_updating)
+ {
+ update_plan(&e->good_plan, plan, plan_len, total_time);
+ }
+
+ if (bad_plan_needs_updating)
+ {
+ update_plan(&e->bad_plan, plan, plan_len, total_time);
+ }
+
+ pfree(es->str->data);
+ pfree(es);
+ }
+ }
+
/* "Unstick" entry if it was previously sticky */
if (e->counters.calls == 0)
e->counters.usage = USAGE_INIT;
@@ -1305,12 +1591,89 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
PG_RETURN_VOID();
}
+Datum
+pg_stat_statements_plan_reset(uint64 query_id, uint8 plan_type)
+{
+ pgssEntry *entry;
+
+ pgssHashKey key;
+
+ if (!pgss || !pgss_hash)
+ ereport(ERROR,
+ (errcode(ERRCODE_OBJECT_NOT_IN_PREREQUISITE_STATE),
+ errmsg("pg_stat_statements must be loaded via shared_preload_libraries")));
+
+ /* Set up key for hashtable search */
+ key.userid = GetUserId();
+ key.dbid = MyDatabaseId;
+ key.queryid = query_id;
+
+ /* Lookup the hash table entry with shared lock. */
+ LWLockAcquire(pgss->lock, LW_SHARED);
+
+ entry = (pgssEntry *) hash_search(pgss_hash, &key, HASH_FIND, NULL);
+
+ if (entry)
+ {
+ if (superuser() || entry->key.userid == key.userid)
+ {
+ pgssPlan *pgssPlan_ptr;
+ switch (plan_type)
+ {
+ case 1:
+ pgssPlan_ptr = &entry->good_plan;
+ break;
+ case 2:
+ pgssPlan_ptr = &entry->bad_plan;
+ break;
+ default:
+ ereport(ERROR,
+ (errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+ errmsg_internal("unrecognized plan type: %d",
+ plan_type)));
+ break;
+ }
+ if (pgssPlan_ptr)
+ {
+ pgssPlan_ptr->offset = -1;
+ pgssPlan_ptr->len = 0;
+ pgssPlan_ptr->time = 0;
+ pgssPlan_ptr->timestamp = 0;
+ }
+ }else{
+ elog(ERROR, "insufficient permission to reset plan");
+ }
+ }
+ LWLockRelease(pgss->lock);
+ PG_RETURN_VOID();
+}
+
+/*
+ * Reset the good_plan of the entry with queryid.
+ */
+Datum
+pg_stat_statements_good_plan_reset(PG_FUNCTION_ARGS)
+{
+ return pg_stat_statements_plan_reset(PG_GETARG_INT64(0), 1);
+}
+
+/*
+ * Reset the bad_plan of the entry with queryid.
+ */
+Datum
+pg_stat_statements_bad_plan_reset(PG_FUNCTION_ARGS)
+{
+ return pg_stat_statements_plan_reset(PG_GETARG_INT64(0), 2);
+}
+
+
/* Number of output arguments (columns) for various API versions */
#define PG_STAT_STATEMENTS_COLS_V1_0 14
#define PG_STAT_STATEMENTS_COLS_V1_1 18
#define PG_STAT_STATEMENTS_COLS_V1_2 19
#define PG_STAT_STATEMENTS_COLS_V1_3 23
-#define PG_STAT_STATEMENTS_COLS 23 /* maximum of above */
+#define PG_STAT_STATEMENTS_COLS_V1_6 29
+#define PG_STAT_STATEMENTS_COLS 29 /* maximum of above */
/*
* Retrieve statement statistics.
@@ -1322,6 +1685,16 @@ pg_stat_statements_reset(PG_FUNCTION_ARGS)
* expected API version is identified by embedding it in the C name of the
* function. Unfortunately we weren't bright enough to do that for 1.1.
*/
+Datum
+pg_stat_statements_1_6(PG_FUNCTION_ARGS)
+{
+ bool showtext = PG_GETARG_BOOL(0);
+
+ pg_stat_statements_internal(fcinfo, PGSS_V1_6, showtext);
+
+ return (Datum) 0;
+}
+
Datum
pg_stat_statements_1_3(PG_FUNCTION_ARGS)
{
@@ -1428,6 +1801,10 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
if (api_version != PGSS_V1_3)
elog(ERROR, "incorrect number of output arguments");
break;
+ case PG_STAT_STATEMENTS_COLS_V1_6:
+ if (api_version != PGSS_V1_6)
+ elog(ERROR, "incorrect number of output arguments");
+ break;
default:
elog(ERROR, "incorrect number of output arguments");
}
@@ -1511,6 +1888,8 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
bool nulls[PG_STAT_STATEMENTS_COLS];
int i = 0;
Counters tmp;
+ pgssPlan tmp_good_plan;
+ pgssPlan tmp_bad_plan;
double stddev;
int64 queryid = entry->key.queryid;
@@ -1550,6 +1929,18 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
/* Just return a null if we fail to find the text */
nulls[i++] = true;
}
+
+ if (api_version >= PGSS_V1_6)
+ {
+ /*
+ * This version requires to print out the good
+ * and the bad plan if they are enabled.
+ */
+ i = fill_plan_str(values, nulls, i, &entry->good_plan,
+ entry, qbuffer, qbuffer_size);
+ i = fill_plan_str(values, nulls, i, &entry->bad_plan,
+ entry, qbuffer, qbuffer_size);
+ }
}
else
{
@@ -1579,6 +1970,8 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
SpinLockAcquire(&e->mutex);
tmp = e->counters;
+ tmp_good_plan = e->good_plan;
+ tmp_bad_plan = e->bad_plan;
SpinLockRelease(&e->mutex);
}
@@ -1606,6 +1999,14 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
stddev = 0.0;
values[i++] = Float8GetDatumFast(stddev);
}
+
+ if (api_version >= PGSS_V1_6)
+ {
+ /* Set plan times and timestamps */
+ i = fill_plan_times(values, nulls, i, &tmp_good_plan);
+ i = fill_plan_times(values, nulls, i, &tmp_bad_plan);
+ }
+
values[i++] = Int64GetDatumFast(tmp.rows);
values[i++] = Int64GetDatumFast(tmp.shared_blks_hit);
values[i++] = Int64GetDatumFast(tmp.shared_blks_read);
@@ -1629,6 +2030,7 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
api_version == PGSS_V1_1 ? PG_STAT_STATEMENTS_COLS_V1_1 :
api_version == PGSS_V1_2 ? PG_STAT_STATEMENTS_COLS_V1_2 :
api_version == PGSS_V1_3 ? PG_STAT_STATEMENTS_COLS_V1_3 :
+ api_version == PGSS_V1_6 ? PG_STAT_STATEMENTS_COLS_V1_6 :
-1 /* fail if you forget to update this assert */ ));
tuplestore_putvalues(tupstore, tupdesc, values, nulls);
@@ -1643,6 +2045,52 @@ pg_stat_statements_internal(FunctionCallInfo fcinfo,
tuplestore_donestoring(tupstore);
}
+/* Fill the plan time and timestamp into the values array. */
+static int
+fill_plan_times(Datum values[], bool nulls[], int i, pgssPlan *plan)
+{
+ values[i++] = Float8GetDatumFast(plan->time);
+
+ /* if there is noting in the timestamp field, we are not interested in it */
+ if (plan->timestamp)
+ values[i++] = TimestampTzGetDatum(plan->timestamp);
+ else
+ nulls[i++] = true;
+ return i;
+}
+
+/* Fill the plan string into the values array. */
+static int
+fill_plan_str(Datum values[], bool nulls[], int i, pgssPlan *plan,
+ pgssEntry *entry, char *qbuffer, Size qbuffer_size)
+{
+ if (plan && plan->len > 0)
+ {
+ char *pstr = qtext_fetch(plan->offset, plan->len, qbuffer, qbuffer_size);
+ if (pstr)
+ {
+ char *enc;
+ enc = pg_any_to_server(pstr, plan->len, entry->encoding);
+ values[i++] = CStringGetTextDatum(enc);
+
+ if (enc != pstr)
+ pfree(enc);
+ }
+ else
+ {
+ /* failed to get the string of the plan */
+ nulls[i++] = true;
+ }
+ }
+ else
+ {
+ /* no plan available or plan_len not greater than 0 */
+ nulls[i++] = true;
+ }
+ return i;
+}
+
+
/*
* Estimate shared memory space needed.
*/
@@ -1694,6 +2142,8 @@ entry_alloc(pgssHashKey *key, Size query_offset, int query_len, int encoding,
/* reset the statistics */
memset(&entry->counters, 0, sizeof(Counters));
+ memset(&entry->good_plan, 0, sizeof(pgssPlan));
+ memset(&entry->bad_plan, 0, sizeof(pgssPlan));
/* set the appropriate initial usage count */
entry->counters.usage = sticky ? pgss->cur_median_usage : USAGE_INIT;
/* re-initialize the mutex each time ... we assume no one using it */
@@ -2104,6 +2554,18 @@ gc_qtexts(void)
qbuffer,
qbuffer_size);
+ int good_plan_len = entry->good_plan.len;
+ char *good_plan = qtext_fetch(entry->good_plan.offset,
+ good_plan_len,
+ qbuffer,
+ qbuffer_size);
+
+ int bad_plan_len = entry->bad_plan.len;
+ char *bad_plan = qtext_fetch(entry->bad_plan.offset,
+ bad_plan_len,
+ qbuffer,
+ qbuffer_size);
+
if (qry == NULL)
{
/* Trouble ... drop the text */
@@ -2125,6 +2587,53 @@ gc_qtexts(void)
entry->query_offset = extent;
extent += query_len + 1;
+
+ if (good_plan == NULL || good_plan_len <= 0)
+ {
+ /* There was an error while loading the good_plan or there was simply never a
+ * good_plan recorded, so we make sure that this entry knows this. */
+ entry->good_plan.offset = 0;
+ entry->good_plan.len = -1;
+ }
+ else
+ {
+ /* Save the good plan */
+ if (fwrite(good_plan, 1, good_plan_len + 1, qfile) != good_plan_len + 1)
+ {
+ ereport(LOG,
+ (errcode_for_file_access(),
+ errmsg("could not write pg_stat_statement file \"%s\": %m",
+ PGSS_TEXT_FILE)));
+ hash_seq_term(&hash_seq);
+ goto gc_fail;
+ }
+ entry->good_plan.offset = extent;
+ extent += good_plan_len + 1;
+ }
+
+ if (bad_plan == NULL || bad_plan_len <= 0)
+ {
+ /* There was an error while loading the bad_plan or there was simply never a
+ * bad_plan recorded, so we make sure that this entry knows this. */
+ entry->bad_plan.offset = 0;
+ entry->bad_plan.len = -1;
+ }
+ else
+ {
+ /* Save the bad plan */
+ if (fwrite(bad_plan, 1, bad_plan_len + 1, qfile) != bad_plan_len + 1)
+ {
+ ereport(LOG,
+ (errcode_for_file_access(),
+ errmsg("could not write pg_stat_statement file \"%s\": %m",
+ PGSS_TEXT_FILE)));
+ hash_seq_term(&hash_seq);
+ goto gc_fail;
+ }
+ entry->bad_plan.offset = extent;
+ extent += bad_plan_len + 1;
+ }
+
nentries++;
}
diff --git a/contrib/pg_stat_statements/pg_stat_statements.control b/contrib/pg_stat_statements/pg_stat_statements.control
index 193fcdf..617038b 100644
--- a/contrib/pg_stat_statements/pg_stat_statements.control
+++ b/contrib/pg_stat_statements/pg_stat_statements.control
@@ -1,5 +1,5 @@
# pg_stat_statements extension
comment = 'track execution statistics of all SQL statements executed'
-default_version = '1.5'
+default_version = '1.6'
module_pathname = '$libdir/pg_stat_statements'
relocatable = true
diff --git a/contrib/pg_stat_statements/sql/pg_stat_statements.sql b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
index a8361fd..9e02470 100644
--- a/contrib/pg_stat_statements/sql/pg_stat_statements.sql
+++ b/contrib/pg_stat_statements/sql/pg_stat_statements.sql
@@ -195,4 +195,15 @@ DROP FUNCTION PLUS_TWO(INTEGER);
SELECT query, calls, rows FROM pg_stat_statements ORDER BY query COLLATE "C";
+-- test to see if any plans have been recorded.
+SELECT
+ CASE WHEN good_plan_time > 0 THEN 1 ELSE 0 END,
+ CASE WHEN bad_plan_time > 0 THEN 1 ELSE 0 END,
+ CASE WHEN good_plan_timestamp >= timestamp '1970-01-01 00:00:00' THEN 1 ELSE 0 END,
+ CASE WHEN good_plan_timestamp >= timestamp '1970-01-01 00:00:00' THEN 1 ELSE 0 END
+FROM pg_stat_statements ORDER BY query COLLATE "C";
+
+-- test if there is some text in the recorded plans.
+select substr(good_plan, 0, 11), substr(bad_plan, 0, 11) from pg_stat_statements ORDER BY query COLLATE "C";
+
DROP EXTENSION pg_stat_statements;
smime.p7s
Description: S/MIME cryptographic signature
