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

Commit0dca5d6

Browse files
committed
Change SQL-language functions to use the plan cache.
In the historical implementation of SQL functions (if they don't getinlined), we built plans for all the contained queries at first callwithin an outer query, and then re-used those plans for the durationof the outer query, and then forgot everything. This was not ideal,not least because the plans could not be customized to specific valuesof the function's parameters. Our plancache infrastructure seemsmature enough to be used here. That will solve both the problem withnot being able to build custom plans and the problem with not beingable to share work across successive outer queries.Aside from those performance concerns, this change fixes alongstanding bugaboo with SQL functions: you could not write DDL thatwould affect later statements in the same function. That's mostlystill true with new-style SQL functions, since the results of parseanalysis are baked into the stored query trees (and protected bydependency records). But for old-style SQL functions, it will nowwork much as it does with PL/pgSQL functions, because we delay parseanalysis and planning of each query until we're ready to run it.Some edge cases that require replanning are now handled better too;see for example the new rowsecurity test, where we now detect an RLScontext change that was previously missed.One other edge-case change that might be worthy of a release noteis that we now insist that a SQL function's result be generatedby the physically-last query within it. Previously, if the lastoriginal query was deleted by a DO INSTEAD NOTHING rule, we'd bewilling to take the result from the preceding query instead.This behavior was undocumented except in source-code comments,and it seems hard to believe that anyone's relying on it.Along the way to this feature, we needed a few infrastructure changes:* The plancache can now take either a raw parse tree or ananalyzed-but-not-rewritten Query as the starting point for aCachedPlanSource. If given a Query, it is caller's responsibilitythat nothing will happen to invalidate that form of the query.We use this for new-style SQL functions, where what's in pg_proc isserialized Query(s) and we trust the dependency mechanism to disallowDDL that would break those.* The plancache now offers a way to invoke a post-rewrite callbackto examine/modify the rewritten parse tree when it is rebuildingthe parse trees after a cache invalidation. We need this becauseSQL functions sometimes adjust the parse tree to make its outputexactly match the declared result type; if the plan gets rebuilt,that has to be re-done.* There is a new backend module utils/cache/funccache.c thatabstracts the idea of caching data about a specific functionusage (a particular function and set of input data types).The code in it is moved almost verbatim from PL/pgSQL, whichhas done that for a long time. We use that logic now forSQL-language functions too, and maybe other PLs will have usefor it in the future.Author: Alexander Pyhalov <a.pyhalov@postgrespro.ru>Co-authored-by: Tom Lane <tgl@sss.pgh.pa.us>Reviewed-by: Pavel Stehule <pavel.stehule@gmail.com>Discussion:https://postgr.es/m/8216639.NyiUUSuA9g@aivenlaptop
1 parente9e7b66 commit0dca5d6

File tree

24 files changed

+2132
-989
lines changed

24 files changed

+2132
-989
lines changed

‎doc/src/sgml/xfunc.sgml‎

Lines changed: 0 additions & 15 deletions
Original file line numberDiff line numberDiff line change
@@ -234,21 +234,6 @@ CALL clean_emp();
234234
whereas returning <type>void</type> is a PostgreSQL extension.
235235
</para>
236236

237-
<note>
238-
<para>
239-
The entire body of an SQL function is parsed before any of it is
240-
executed. While an SQL function can contain commands that alter
241-
the system catalogs (e.g., <command>CREATE TABLE</command>), the effects
242-
of such commands will not be visible during parse analysis of
243-
later commands in the function. Thus, for example,
244-
<literal>CREATE TABLE foo (...); INSERT INTO foo VALUES(...);</literal>
245-
will not work as desired if packaged up into a single SQL function,
246-
since <structname>foo</structname> won't exist yet when the <command>INSERT</command>
247-
command is parsed. It's recommended to use <application>PL/pgSQL</application>
248-
instead of an SQL function in this type of situation.
249-
</para>
250-
</note>
251-
252237
<para>
253238
The syntax of the <command>CREATE FUNCTION</command> command requires
254239
the function body to be written as a string constant. It is usually

‎src/backend/catalog/pg_proc.c‎

Lines changed: 1 addition & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -960,7 +960,7 @@ fmgr_sql_validator(PG_FUNCTION_ARGS)
960960
(void)check_sql_fn_retval(querytree_list,
961961
rettype,rettupdesc,
962962
proc->prokind,
963-
false,NULL);
963+
false);
964964
}
965965

966966
error_context_stack=sqlerrcontext.previous;

0 commit comments

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