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
Appearance settings

Commit 2abae34

Browse filesBrowse files
tglsfdctglsfdc
committed
Implement function-local GUC parameter settings, as per recent discussion.
There are still some loose ends: I didn't do anything about the SET FROM CURRENT idea yet, and it's not real clear whether we are happy with the interaction of SET LOCAL with function-local settings. The documentation is a bit spartan, too.
1 parent fcfe801 commit 2abae34
Copy full SHA for 2abae34

File tree

Expand file treeCollapse file tree

23 files changed

+2595
-2163
lines changed
Filter options
Expand file treeCollapse file tree

23 files changed

+2595
-2163
lines changed

‎doc/src/sgml/catalogs.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/catalogs.sgml
+8-1Lines changed: 8 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,4 +1,4 @@
1-
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.155 2007/06/20 19:24:05 neilc Exp $ -->
1+
<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.156 2007/09/03 00:39:11 tgl Exp $ -->
22
<!--
33
Documentation of the system catalogs, directed toward PostgreSQL developers
44
-->
@@ -3635,6 +3635,13 @@
36353635
</entry>
36363636
</row>
36373637

3638+
<row>
3639+
<entry><structfield>proconfig</structfield></entry>
3640+
<entry><type>text[]</type></entry>
3641+
<entry></entry>
3642+
<entry>Function's local settings for run-time configuration variables</entry>
3643+
</row>
3644+
36383645
<row>
36393646
<entry><structfield>proacl</structfield></entry>
36403647
<entry><type>aclitem[]</type></entry>

‎doc/src/sgml/ref/alter_function.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/alter_function.sgml
+42-1Lines changed: 42 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_function.sgml,v 1.13 2007/01/22 01:35:19 tgl Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/alter_function.sgml,v 1.14 2007/09/03 00:39:12 tgl Exp $
33
PostgreSQL documentation
44
-->
55

@@ -36,6 +36,8 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
3636
[ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
3737
COST <replaceable class="parameter">execution_cost</replaceable>
3838
ROWS <replaceable class="parameter">result_rows</replaceable>
39+
SET <replaceable class="parameter">parameter</replaceable> { TO | = } { <replaceable class="parameter">value</replaceable> | DEFAULT }
40+
RESET <replaceable class="parameter">parameter</replaceable>
3941
</synopsis>
4042
</refsynopsisdiv>
4143

@@ -212,6 +214,28 @@ where <replaceable class="PARAMETER">action</replaceable> is one of:
212214
</listitem>
213215
</varlistentry>
214216

217+
<varlistentry>
218+
<term><replaceable>parameter</replaceable></term>
219+
<term><replaceable>value</replaceable></term>
220+
<listitem>
221+
<para>
222+
Add or change the assignment to be made to a configuration parameter
223+
when the function is called. If
224+
<replaceable>value</replaceable> is <literal>DEFAULT</literal>
225+
or, equivalently, <literal>RESET</literal> is used, the function-local
226+
setting is removed, so that the function executes with the value
227+
present in its environment. Use <literal>RESET
228+
ALL</literal> to clear all function-local settings.
229+
</para>
230+
231+
<para>
232+
See <xref linkend="sql-set" endterm="sql-set-title"> and
233+
<xref linkend="runtime-config">
234+
for more information about allowed parameter names and values.
235+
</para>
236+
</listitem>
237+
</varlistentry>
238+
215239
<varlistentry>
216240
<term><literal>RESTRICT</literal></term>
217241

@@ -250,6 +274,22 @@ ALTER FUNCTION sqrt(integer) OWNER TO joe;
250274
ALTER FUNCTION sqrt(integer) SET SCHEMA maths;
251275
</programlisting>
252276
</para>
277+
278+
<para>
279+
To adjust the search path that is automatically set for a function:
280+
<programlisting>
281+
ALTER FUNCTION check_password(text) SET search_path = admin, pg_temp;
282+
</programlisting>
283+
</para>
284+
285+
<para>
286+
To disable automatic setting of <varname>search_path</> for a function:
287+
<programlisting>
288+
ALTER FUNCTION check_password(text) RESET search_path;
289+
</programlisting>
290+
The function will now execute with whatever search path is used by its
291+
caller.
292+
</para>
253293
</refsect1>
254294

255295
<refsect1>
@@ -260,6 +300,7 @@ ALTER FUNCTION sqrt(integer) SET SCHEMA maths;
260300
FUNCTION</> statement in the SQL standard. The standard allows more
261301
properties of a function to be modified, but does not provide the
262302
ability to rename a function, make a function a security definer,
303+
attach configuration parameter values to a function,
263304
or change the owner, schema, or volatility of a function. The standard also
264305
requires the <literal>RESTRICT</> key word, which is optional in
265306
<productname>PostgreSQL</>.

‎doc/src/sgml/ref/create_function.sgml

Copy file name to clipboardExpand all lines: doc/src/sgml/ref/create_function.sgml
+66-16Lines changed: 66 additions & 16 deletions
Original file line numberDiff line numberDiff line change
@@ -1,5 +1,5 @@
11
<!--
2-
$PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.75 2007/04/23 16:52:53 neilc Exp $
2+
$PostgreSQL: pgsql/doc/src/sgml/ref/create_function.sgml,v 1.76 2007/09/03 00:39:13 tgl Exp $
33
-->
44

55
<refentry id="SQL-CREATEFUNCTION">
@@ -28,6 +28,7 @@ CREATE [ OR REPLACE ] FUNCTION
2828
| [ EXTERNAL ] SECURITY INVOKER | [ EXTERNAL ] SECURITY DEFINER
2929
| COST <replaceable class="parameter">execution_cost</replaceable>
3030
| ROWS <replaceable class="parameter">result_rows</replaceable>
31+
| SET <replaceable class="parameter">parameter</replaceable> { TO | = } { <replaceable class="parameter">value</replaceable> | DEFAULT }
3132
| AS '<replaceable class="parameter">definition</replaceable>'
3233
| AS '<replaceable class="parameter">obj_file</replaceable>', '<replaceable class="parameter">link_symbol</replaceable>'
3334
} ...
@@ -71,6 +72,8 @@ CREATE [ OR REPLACE ] FUNCTION
7172
triggers, etc. that refer to the old function. Use
7273
<command>CREATE OR REPLACE FUNCTION</command> to change a function
7374
definition without breaking objects that refer to the function.
75+
Also, <command>ALTER FUNCTION</> can be used to change most of the
76+
auxiliary properties of an existing function.
7477
</para>
7578

7679
<para>
@@ -320,6 +323,24 @@ CREATE [ OR REPLACE ] FUNCTION
320323
</listitem>
321324
</varlistentry>
322325

326+
<varlistentry>
327+
<term><replaceable>parameter</replaceable></term>
328+
<term><replaceable>value</replaceable></term>
329+
<listitem>
330+
<para>
331+
The <literal>SET</> clause causes the specified configuration
332+
parameter to be set to the specified value when the function is
333+
entered, and then restored to its prior value when the function exits.
334+
</para>
335+
336+
<para>
337+
See <xref linkend="sql-set" endterm="sql-set-title"> and
338+
<xref linkend="runtime-config">
339+
for more information about allowed parameter names and values.
340+
</para>
341+
</listitem>
342+
</varlistentry>
343+
323344
<varlistentry>
324345
<term><replaceable class="parameter">definition</replaceable></term>
325346

@@ -451,6 +472,18 @@ CREATE FUNCTION foo(int, out text) ...
451472
be escaped by doubling them.
452473
</para>
453474

475+
<para>
476+
If any <literal>SET</> clauses are attached to a function, then
477+
the effects of a <command>SET LOCAL</> command executed inside the
478+
function are restricted to the function: the configuration parameter's
479+
value is restored at function exit. This is true even for parameters
480+
not mentioned in the <literal>SET</> clause(s). However, an ordinary
481+
<command>SET</> command (without <literal>LOCAL</>) overrides the
482+
<literal>SET</> clause, much as it would do for a previous <command>SET
483+
LOCAL</> command: the effects of such a command will persist after
484+
function exit, unless the current transaction is rolled back.
485+
</para>
486+
454487
<para>
455488
To be able to define a function, the user must have the
456489
<literal>USAGE</literal> privilege on the language.
@@ -530,28 +563,45 @@ SELECT * FROM dup(42);
530563
CREATE FUNCTION check_password(uname TEXT, pass TEXT)
531564
RETURNS BOOLEAN AS $$
532565
DECLARE passed BOOLEAN;
533-
old_path TEXT;
534566
BEGIN
535-
-- Save old search_path; notice we must qualify current_setting
536-
-- to ensure we invoke the right function
537-
old_path := pg_catalog.current_setting('search_path');
538-
539-
-- Set a secure search_path: trusted schemas, then 'pg_temp'.
540-
-- We set is_local = true so that the old value will be restored
541-
-- in event of an error before we reach the function end.
542-
PERFORM pg_catalog.set_config('search_path', 'admin, pg_temp', true);
543-
544-
-- Do whatever secure work we came for.
545567
SELECT (pwd = $2) INTO passed
546568
FROM pwds
547569
WHERE username = $1;
548570

549-
-- Restore caller's search_path
550-
PERFORM pg_catalog.set_config('search_path', old_path, true);
551-
552571
RETURN passed;
553572
END;
554-
$$ LANGUAGE plpgsql SECURITY DEFINER;
573+
$$ LANGUAGE plpgsql
574+
SECURITY DEFINER
575+
-- Set a secure search_path: trusted schema(s), then 'pg_temp'.
576+
SET search_path = admin, pg_temp;
577+
</programlisting>
578+
579+
<para>
580+
Before <productname>PostgreSQL</productname> version 8.3, the
581+
<literal>SET</> option was not available, and so older functions may
582+
contain rather complicated logic to save, set, and restore
583+
<varname>search_path</>. The <literal>SET</> option is far easier
584+
to use for this purpose.
585+
</para>
586+
587+
<para>
588+
Another point to keep in mind is that by default, execute privilege
589+
is granted to <literal>PUBLIC</> for newly created functions
590+
(see <xref linkend="sql-grant" endterm="sql-grant-title"> for more
591+
information). Frequently you will wish to restrict use of a security
592+
definer function to only some users. To do that, you must revoke
593+
the default <literal>PUBLIC</> privileges and then grant execute
594+
privilege selectively. To avoid having a window where the new function
595+
is accessible to all, create it and set the privileges within a single
596+
transaction. For example:
597+
</para>
598+
599+
<programlisting>
600+
BEGIN;
601+
CREATE FUNCTION check_password(uname TEXT, pass TEXT) ... SECURITY DEFINER;
602+
REVOKE ALL ON FUNCTION check_password(uname TEXT, pass TEXT) FROM PUBLIC;
603+
GRANT EXECUTE ON FUNCTION check_password(uname TEXT, pass TEXT) TO admins;
604+
COMMIT;
555605
</programlisting>
556606

557607
</refsect1>

‎src/backend/access/transam/xact.c

Copy file name to clipboardExpand all lines: src/backend/access/transam/xact.c
+16-8Lines changed: 16 additions & 8 deletions
Original file line numberDiff line numberDiff line change
@@ -10,7 +10,7 @@
1010
*
1111
*
1212
* IDENTIFICATION
13-
* $PostgreSQL: pgsql/src/backend/access/transam/xact.c,v 1.246 2007/08/01 22:45:07 tgl Exp $
13+
* $PostgreSQL: pgsql/src/backend/access/transam/xact.c,v 1.247 2007/09/03 00:39:13 tgl Exp $
1414
*
1515
*-------------------------------------------------------------------------
1616
*/
@@ -117,7 +117,8 @@ typedef struct TransactionStateData
117117
int savepointLevel; /* savepoint level */
118118
TransState state; /* low-level state */
119119
TBlockState blockState; /* high-level state */
120-
int nestingLevel; /* nest depth */
120+
int nestingLevel; /* transaction nesting depth */
121+
int gucNestLevel; /* GUC context nesting depth */
121122
MemoryContext curTransactionContext; /* my xact-lifetime context */
122123
ResourceOwner curTransactionOwner; /* my query resources */
123124
List *childXids; /* subcommitted child XIDs */
@@ -141,7 +142,8 @@ static TransactionStateData TopTransactionStateData = {
141142
TRANS_DEFAULT, /* transaction state */
142143
TBLOCK_DEFAULT, /* transaction block state from the client
143144
* perspective */
144-
0, /* nesting level */
145+
0, /* transaction nesting depth */
146+
0, /* GUC context nesting depth */
145147
NULL, /* cur transaction context */
146148
NULL, /* cur transaction resource owner */
147149
NIL, /* subcommitted child Xids */
@@ -1499,6 +1501,7 @@ StartTransaction(void)
14991501
* initialize current transaction state fields
15001502
*/
15011503
s->nestingLevel = 1;
1504+
s->gucNestLevel = 1;
15021505
s->childXids = NIL;
15031506

15041507
/*
@@ -1513,6 +1516,7 @@ StartTransaction(void)
15131516
/*
15141517
* initialize other subsystems for new transaction
15151518
*/
1519+
AtStart_GUC();
15161520
AtStart_Inval();
15171521
AtStart_Cache();
15181522
AfterTriggerBeginXact();
@@ -1699,7 +1703,7 @@ CommitTransaction(void)
16991703
/* Check we've released all catcache entries */
17001704
AtEOXact_CatCache(true);
17011705

1702-
AtEOXact_GUC(true, false);
1706+
AtEOXact_GUC(true, 1);
17031707
AtEOXact_SPI(true);
17041708
AtEOXact_on_commit_actions(true);
17051709
AtEOXact_Namespace(true);
@@ -1721,6 +1725,7 @@ CommitTransaction(void)
17211725
s->transactionId = InvalidTransactionId;
17221726
s->subTransactionId = InvalidSubTransactionId;
17231727
s->nestingLevel = 0;
1728+
s->gucNestLevel = 0;
17241729
s->childXids = NIL;
17251730

17261731
/*
@@ -1920,7 +1925,7 @@ PrepareTransaction(void)
19201925
AtEOXact_CatCache(true);
19211926

19221927
/* PREPARE acts the same as COMMIT as far as GUC is concerned */
1923-
AtEOXact_GUC(true, false);
1928+
AtEOXact_GUC(true, 1);
19241929
AtEOXact_SPI(true);
19251930
AtEOXact_on_commit_actions(true);
19261931
AtEOXact_Namespace(true);
@@ -1941,6 +1946,7 @@ PrepareTransaction(void)
19411946
s->transactionId = InvalidTransactionId;
19421947
s->subTransactionId = InvalidSubTransactionId;
19431948
s->nestingLevel = 0;
1949+
s->gucNestLevel = 0;
19441950
s->childXids = NIL;
19451951

19461952
/*
@@ -2075,7 +2081,7 @@ AbortTransaction(void)
20752081
false, true);
20762082
AtEOXact_CatCache(false);
20772083

2078-
AtEOXact_GUC(false, false);
2084+
AtEOXact_GUC(false, 1);
20792085
AtEOXact_SPI(false);
20802086
AtEOXact_on_commit_actions(false);
20812087
AtEOXact_Namespace(false);
@@ -2124,6 +2130,7 @@ CleanupTransaction(void)
21242130
s->transactionId = InvalidTransactionId;
21252131
s->subTransactionId = InvalidSubTransactionId;
21262132
s->nestingLevel = 0;
2133+
s->gucNestLevel = 0;
21272134
s->childXids = NIL;
21282135

21292136
/*
@@ -3788,7 +3795,7 @@ CommitSubTransaction(void)
37883795
RESOURCE_RELEASE_AFTER_LOCKS,
37893796
true, false);
37903797

3791-
AtEOXact_GUC(true, true);
3798+
AtEOXact_GUC(true, s->gucNestLevel);
37923799
AtEOSubXact_SPI(true, s->subTransactionId);
37933800
AtEOSubXact_on_commit_actions(true, s->subTransactionId,
37943801
s->parent->subTransactionId);
@@ -3901,7 +3908,7 @@ AbortSubTransaction(void)
39013908
RESOURCE_RELEASE_AFTER_LOCKS,
39023909
false, false);
39033910

3904-
AtEOXact_GUC(false, true);
3911+
AtEOXact_GUC(false, s->gucNestLevel);
39053912
AtEOSubXact_SPI(false, s->subTransactionId);
39063913
AtEOSubXact_on_commit_actions(false, s->subTransactionId,
39073914
s->parent->subTransactionId);
@@ -4017,6 +4024,7 @@ PushTransaction(void)
40174024
s->subTransactionId = currentSubTransactionId;
40184025
s->parent = p;
40194026
s->nestingLevel = p->nestingLevel + 1;
4027+
s->gucNestLevel = NewGUCNestLevel();
40204028
s->savepointLevel = p->savepointLevel;
40214029
s->state = TRANS_DEFAULT;
40224030
s->blockState = TBLOCK_SUBBEGIN;

‎src/backend/catalog/pg_aggregate.c

Copy file name to clipboardExpand all lines: src/backend/catalog/pg_aggregate.c
+2-1Lines changed: 2 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -8,7 +8,7 @@
88
*
99
*
1010
* IDENTIFICATION
11-
* $PostgreSQL: pgsql/src/backend/catalog/pg_aggregate.c,v 1.86 2007/04/02 03:49:37 tgl Exp $
11+
* $PostgreSQL: pgsql/src/backend/catalog/pg_aggregate.c,v 1.87 2007/09/03 00:39:14 tgl Exp $
1212
*
1313
*-------------------------------------------------------------------------
1414
*/
@@ -212,6 +212,7 @@ AggregateCreate(const char *aggName,
212212
PointerGetDatum(NULL), /* allParamTypes */
213213
PointerGetDatum(NULL), /* parameterModes */
214214
PointerGetDatum(NULL), /* parameterNames */
215+
PointerGetDatum(NULL), /* proconfig */
215216
1, /* procost */
216217
0); /* prorows */
217218

0 commit comments

Comments
0 (0)
Morty Proxy This is a proxified and sanitized view of the page, visit original site.