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

Commit44b928e

Browse files
author
Neil Conway
committed
Add a new system view, pg_prepared_statements, that can be used to
access information about the prepared statements that are availablein the current session. Original patch from Joachim Wieland, variousimprovements by Neil Conway.The "statement" column of the view contains the literal query stringsent by the client, without any rewriting or pretty printing. Thismeans that prepared statements created via SQL will be prefixed with"PREPARE ... AS ", whereas those prepared via the FE/BE protocol willnot. That is unfortunate, but discussion on -patches did not yield anefficient way to improve this, and there is some merit in returningexactly what the client sent to the backend.Catalog version bumped, regression tests updated.
1 parentafa8f19 commit44b928e

File tree

13 files changed

+362
-32
lines changed

13 files changed

+362
-32
lines changed

‎doc/src/sgml/catalogs.sgml

Lines changed: 101 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,6 +1,6 @@
11
<!--
22
Documentation of the system catalogs, directed toward PostgreSQL developers
3-
$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.115 2005/11/04 23:13:59 petere Exp $
3+
$PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.116 2006/01/08 07:00:24 neilc Exp $
44
-->
55

66
<chapter id="catalogs">
@@ -4372,6 +4372,11 @@
43724372
<entry>currently held locks</entry>
43734373
</row>
43744374

4375+
<row>
4376+
<entry><link linkend="view-pg-prepared-statements"><structname>pg_prepared_statements</structname></link></entry>
4377+
<entry>current prepared statements</entry>
4378+
</row>
4379+
43754380
<row>
43764381
<entry><link linkend="view-pg-prepared-xacts"><structname>pg_prepared_xacts</structname></link></entry>
43774382
<entry>currently prepared transactions</entry>
@@ -4778,6 +4783,101 @@
47784783

47794784
</sect1>
47804785

4786+
<sect1 id="view-pg-prepared-statements">
4787+
<title><structname>pg_prepared_statements</structname></title>
4788+
4789+
<indexterm zone="view-pg-prepared-statements">
4790+
<primary>pg_prepared_statements</primary>
4791+
</indexterm>
4792+
4793+
<para>
4794+
The <structname>pg_prepared_statements</structname> view displays
4795+
all the prepared statements that are available in the current
4796+
session. See <xref linkend="sql-prepare"
4797+
endterm="sql-prepare-title"> for more information about prepared
4798+
statements.
4799+
</para>
4800+
4801+
<para>
4802+
<structname>pg_prepared_statements</structname> contains one row
4803+
for each prepared statement. Rows are added to the view when a new
4804+
prepared statement is created, and removed when a prepared
4805+
statement is released (for example, via the <xref
4806+
linkend="sql-deallocate" endterm="sql-deallocate-title">
4807+
command).
4808+
</para>
4809+
4810+
<table>
4811+
<title><structname>pg_prepared_statements</> Columns</title>
4812+
4813+
<tgroup cols=4>
4814+
<thead>
4815+
<row>
4816+
<entry>Name</entry>
4817+
<entry>Type</entry>
4818+
<entry>References</entry>
4819+
<entry>Description</entry>
4820+
</row>
4821+
</thead>
4822+
<tbody>
4823+
<row>
4824+
<entry><structfield>name</structfield></entry>
4825+
<entry><type>text</type></entry>
4826+
<entry></entry>
4827+
<entry>
4828+
The identifier of the prepared statement.
4829+
</entry>
4830+
</row>
4831+
<row>
4832+
<entry><structfield>statement</structfield></entry>
4833+
<entry><type>text</type></entry>
4834+
<entry></entry>
4835+
<entry>
4836+
The query string submitted by the client to create this
4837+
prepared statement. For prepared statements created via SQL,
4838+
this is the <command>PREPARE</command> statement submitted by
4839+
the client. For prepared statements created via the
4840+
frontend/backend protocol, this is the text of the prepared
4841+
statement itself.
4842+
</entry>
4843+
</row>
4844+
<row>
4845+
<entry><structfield>prepare_time</structfield></entry>
4846+
<entry><type>timestamptz</type></entry>
4847+
<entry></entry>
4848+
<entry>
4849+
The time at which the prepared statement was created.
4850+
</entry>
4851+
</row>
4852+
<row>
4853+
<entry><structfield>parameter_types</structfield></entry>
4854+
<entry><type>oid[]</type></entry>
4855+
<entry></entry>
4856+
<entry>
4857+
The expected parameter types for the prepared statement in the form of
4858+
an array of type OIDs.
4859+
</entry>
4860+
</row>
4861+
<row>
4862+
<entry><structfield>from_sql</structfield></entry>
4863+
<entry><type>boolean</type></entry>
4864+
<entry></entry>
4865+
<entry>
4866+
<literal>true</literal> if the prepared statement was created
4867+
via the <command>PREPARE</command> SQL statement;
4868+
<literal>false</literal> if the statement was prepared via the
4869+
frontend/backend protocol.
4870+
</entry>
4871+
</row>
4872+
</tbody>
4873+
</tgroup>
4874+
</table>
4875+
4876+
<para>
4877+
The <structname>pg_prepared_statements</structname> view is read only.
4878+
</para>
4879+
</sect1>
4880+
47814881
<sect1 id="view-pg-prepared-xacts">
47824882
<title><structname>pg_prepared_xacts</structname></title>
47834883

‎doc/src/sgml/ref/prepare.sgml

Lines changed: 6 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/prepare.sgml,v 1.16 2005/10/15 01:47:12 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/prepare.sgml,v 1.17 2006/01/08 07:00:25 neilc Exp $
33
PostgreSQL documentation
44
-->
55

@@ -145,6 +145,11 @@ PREPARE <replaceable class="PARAMETER">plan_name</replaceable> [ (<replaceable c
145145
the <xref linkend="sql-analyze" endterm="sql-analyze-title">
146146
documentation.
147147
</para>
148+
149+
<para>
150+
You can see all available prepared statements of a session by querying the
151+
<structname>pg_prepared_statements</> system view.
152+
</para>
148153
</refsect1>
149154

150155
<refsect1 id="sql-prepare-examples">

‎src/backend/catalog/system_views.sql

Lines changed: 7 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -3,7 +3,7 @@
33
*
44
* Copyright (c) 1996-2005, PostgreSQL Global Development Group
55
*
6-
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.22 2005/10/06 02:29:15 tgl Exp $
6+
* $PostgreSQL: pgsql/src/backend/catalog/system_views.sql,v 1.23 2006/01/08 07:00:25 neilc Exp $
77
*/
88

99
CREATEVIEWpg_rolesAS
@@ -156,6 +156,12 @@ CREATE VIEW pg_prepared_xacts AS
156156
LEFT JOIN pg_authid UONP.ownerid=U.oid
157157
LEFT JOIN pg_database DONP.dbid=D.oid;
158158

159+
CREATEVIEWpg_prepared_statementsAS
160+
SELECTP.name,P.statement,P.prepare_time,P.parameter_types,P.from_sql
161+
FROM pg_prepared_statement()AS P
162+
(nametext, statementtext, prepare_timetimestamptz,
163+
parameter_typesoid[], from_sqlboolean);
164+
159165
CREATEVIEWpg_settingsAS
160166
SELECT*
161167
FROM pg_show_all_settings()AS A

‎src/backend/commands/prepare.c

Lines changed: 137 additions & 5 deletions
Original file line numberDiff line numberDiff line change
@@ -10,21 +10,26 @@
1010
* Copyright (c) 2002-2005, PostgreSQL Global Development Group
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.44 2005/12/14 17:06:27 tgl Exp $
13+
* $PostgreSQL: pgsql/src/backend/commands/prepare.c,v 1.45 2006/01/08 07:00:25 neilc Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
1717
#include"postgres.h"
1818

19+
#include"access/heapam.h"
20+
#include"catalog/pg_type.h"
1921
#include"commands/explain.h"
2022
#include"commands/prepare.h"
2123
#include"executor/executor.h"
22-
#include"utils/guc.h"
24+
#include"funcapi.h"
25+
#include"parser/parsetree.h"
2326
#include"optimizer/planner.h"
2427
#include"rewrite/rewriteHandler.h"
2528
#include"tcop/pquery.h"
2629
#include"tcop/tcopprot.h"
2730
#include"tcop/utility.h"
31+
#include"utils/builtins.h"
32+
#include"utils/guc.h"
2833
#include"utils/hsearch.h"
2934
#include"utils/memutils.h"
3035

@@ -40,6 +45,7 @@ static HTAB *prepared_queries = NULL;
4045
staticvoidInitQueryHashTable(void);
4146
staticParamListInfoEvaluateParams(EState*estate,
4247
List*params,List*argtypes);
48+
staticDatumbuild_oid_array(List*oid_list);
4349

4450
/*
4551
* Implements the 'PREPARE' utility statement.
@@ -114,7 +120,8 @@ PrepareQuery(PrepareStmt *stmt)
114120
commandTag,
115121
query_list,
116122
plan_list,
117-
stmt->argtype_oids);
123+
stmt->argtype_oids,
124+
true);
118125
}
119126

120127
/*
@@ -298,7 +305,8 @@ StorePreparedStatement(const char *stmt_name,
298305
constchar*commandTag,
299306
List*query_list,
300307
List*plan_list,
301-
List*argtype_list)
308+
List*argtype_list,
309+
boolfrom_sql)
302310
{
303311
PreparedStatement*entry;
304312
MemoryContextoldcxt,
@@ -361,6 +369,8 @@ StorePreparedStatement(const char *stmt_name,
361369
entry->plan_list=plan_list;
362370
entry->argtype_list=argtype_list;
363371
entry->context=entrycxt;
372+
entry->prepare_time=GetCurrentTimestamp();
373+
entry->from_sql=from_sql;
364374

365375
MemoryContextSwitchTo(oldcxt);
366376
}
@@ -383,7 +393,7 @@ FetchPreparedStatement(const char *stmt_name, bool throwError)
383393
{
384394
/*
385395
* We can't just use the statement name as supplied by the user: the
386-
* hash package is picky enough that it needs to beNULL-padded out to
396+
* hash package is picky enough that it needs to beNUL-padded out to
387397
* the appropriate length to work correctly.
388398
*/
389399
StrNCpy(key,stmt_name,sizeof(key));
@@ -661,3 +671,125 @@ ExplainExecuteQuery(ExplainStmt *stmt, ParamListInfo params,
661671
if (estate)
662672
FreeExecutorState(estate);
663673
}
674+
675+
/*
676+
* This set returning function reads all the prepared statements and
677+
* returns a set of (name, statement, prepare_time, param_types).
678+
*/
679+
Datum
680+
pg_prepared_statement(PG_FUNCTION_ARGS)
681+
{
682+
FuncCallContext*funcctx;
683+
HASH_SEQ_STATUS*hash_seq;
684+
PreparedStatement*prep_stmt;
685+
686+
/* stuff done only on the first call of the function */
687+
if (SRF_IS_FIRSTCALL())
688+
{
689+
TupleDesctupdesc;
690+
MemoryContextoldcontext;
691+
692+
/* create a function context for cross-call persistence */
693+
funcctx=SRF_FIRSTCALL_INIT();
694+
695+
/*
696+
* switch to memory context appropriate for multiple function
697+
* calls
698+
*/
699+
oldcontext=MemoryContextSwitchTo(funcctx->multi_call_memory_ctx);
700+
701+
/* allocate memory for user context */
702+
if (prepared_queries)
703+
{
704+
hash_seq= (HASH_SEQ_STATUS*)palloc(sizeof(HASH_SEQ_STATUS));
705+
hash_seq_init(hash_seq,prepared_queries);
706+
funcctx->user_fctx= (void*)hash_seq;
707+
}
708+
else
709+
funcctx->user_fctx=NULL;
710+
711+
/*
712+
* build tupdesc for result tuples. This must match the
713+
* definition of the pg_prepared_statements view in
714+
* system_views.sql
715+
*/
716+
tupdesc=CreateTemplateTupleDesc(5, false);
717+
TupleDescInitEntry(tupdesc, (AttrNumber)1,"name",
718+
TEXTOID,-1,0);
719+
TupleDescInitEntry(tupdesc, (AttrNumber)2,"statement",
720+
TEXTOID,-1,0);
721+
TupleDescInitEntry(tupdesc, (AttrNumber)3,"prepare_time",
722+
TIMESTAMPTZOID,-1,0);
723+
TupleDescInitEntry(tupdesc, (AttrNumber)4,"parameter_types",
724+
OIDARRAYOID,-1,0);
725+
TupleDescInitEntry(tupdesc, (AttrNumber)5,"from_sql",
726+
BOOLOID,-1,0);
727+
728+
funcctx->tuple_desc=BlessTupleDesc(tupdesc);
729+
MemoryContextSwitchTo(oldcontext);
730+
}
731+
732+
/* stuff done on every call of the function */
733+
funcctx=SRF_PERCALL_SETUP();
734+
hash_seq= (HASH_SEQ_STATUS*)funcctx->user_fctx;
735+
736+
/* if the hash table is uninitialized, we're done */
737+
if (hash_seq==NULL)
738+
SRF_RETURN_DONE(funcctx);
739+
740+
prep_stmt=hash_seq_search(hash_seq);
741+
if (prep_stmt)
742+
{
743+
Datumresult;
744+
HeapTupletuple;
745+
Datumvalues[5];
746+
boolnulls[5];
747+
748+
MemSet(nulls,0,sizeof(nulls));
749+
750+
values[0]=DirectFunctionCall1(textin,
751+
CStringGetDatum(prep_stmt->stmt_name));
752+
753+
if (prep_stmt->query_string==NULL)
754+
nulls[1]= true;
755+
else
756+
values[1]=DirectFunctionCall1(textin,
757+
CStringGetDatum(prep_stmt->query_string));
758+
759+
values[2]=TimestampTzGetDatum(prep_stmt->prepare_time);
760+
values[3]=build_oid_array(prep_stmt->argtype_list);
761+
values[4]=BoolGetDatum(prep_stmt->from_sql);
762+
763+
tuple=heap_form_tuple(funcctx->tuple_desc,values,nulls);
764+
result=HeapTupleGetDatum(tuple);
765+
SRF_RETURN_NEXT(funcctx,result);
766+
}
767+
768+
SRF_RETURN_DONE(funcctx);
769+
}
770+
771+
/*
772+
* This utility function takes a List of Oids, and returns a Datum
773+
* pointing to a Postgres array containing those OIDs. The empty list
774+
* is returned as a zero-element array, not NULL.
775+
*/
776+
staticDatum
777+
build_oid_array(List*oid_list)
778+
{
779+
ListCell*lc;
780+
intlen;
781+
inti;
782+
Datum*tmp_ary;
783+
ArrayType*ary;
784+
785+
len=list_length(oid_list);
786+
tmp_ary= (Datum*)palloc(len*sizeof(Datum));
787+
788+
i=0;
789+
foreach(lc,oid_list)
790+
tmp_ary[i++]=ObjectIdGetDatum(lfirst_oid(lc));
791+
792+
/* XXX: this hardcodes assumptions about the OID type... */
793+
ary=construct_array(tmp_ary,len,OIDOID,sizeof(Oid), true,'i');
794+
returnPointerGetDatum(ary);
795+
}

‎src/backend/tcop/postgres.c

Lines changed: 5 additions & 2 deletions
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/tcop/postgres.c,v 1.477 2006/01/05 10:07:45 petere Exp $
11+
* $PostgreSQL: pgsql/src/backend/tcop/postgres.c,v 1.478 2006/01/0807:00:25 neilc Exp $
1212
*
1313
* NOTES
1414
* this is the "main" module of the postgres backend and
@@ -55,6 +55,7 @@
5555
#include"tcop/pquery.h"
5656
#include"tcop/tcopprot.h"
5757
#include"tcop/utility.h"
58+
#include"utils/builtins.h"
5859
#include"utils/flatfiles.h"
5960
#include"utils/guc.h"
6061
#include"utils/lsyscache.h"
@@ -1308,7 +1309,8 @@ exec_parse_message(const char *query_string,/* string to execute */
13081309
commandTag,
13091310
querytree_list,
13101311
plantree_list,
1311-
param_list);
1312+
param_list,
1313+
false);
13121314
}
13131315
else
13141316
{
@@ -1322,6 +1324,7 @@ exec_parse_message(const char *query_string,/* string to execute */
13221324
pstmt->query_list=querytree_list;
13231325
pstmt->plan_list=plantree_list;
13241326
pstmt->argtype_list=param_list;
1327+
pstmt->from_sql= false;
13251328
pstmt->context=unnamed_stmt_context;
13261329
/* Now the unnamed statement is complete and valid */
13271330
unnamed_stmt_pstmt=pstmt;

0 commit comments

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