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

Commit2f48ede

Browse files
committed
Avoid using a cursor in plpgsql's RETURN QUERY statement.
plpgsql has always executed the query given in a RETURN QUERY commandby opening it as a cursor and then fetching a few rows at a time,which it turns around and dumps into the function's result tuplestore.The point of this was to keep from blowing out memory with an oversizedSPITupleTable result (note that while a tuplestore can spill tuplesto disk, SPITupleTable cannot). However, it's rather inefficient, bothbecause of extra data copying and because of executor entry/exitoverhead. In recent versions, a new performance problem has emerged:use of a cursor prevents use of a parallel plan for the executed query.We can improve matters by skipping use of a cursor and having theexecutor push result tuples directly into the function's resulttuplestore. However, a moderate amount of new infrastructure is neededto make that idea work:* We can use the existing tstoreReceiver.c DestReceiver code to funnelexecutor output to the tuplestore, but it has to be extended to supportplpgsql's requirement for possibly applying a tuple conversion map.* SPI needs to be extended to allow use of a caller-suppliedDestReceiver instead of its usual receiver that puts tuples intoa SPITupleTable. Two new API calls are needed to handle both theRETURN QUERY and RETURN QUERY EXECUTE cases.I also felt that I didn't want these new API calls to use the legacymethod of specifying query parameter values with "char" null flags(the old ' '/'n' convention); rather they should accept ParamListInfoobjects containing the parameter type and value info. This requireda bit of additional new infrastructure since we didn't yet have anyparse analysis callback that would interpret $N parameter symbolsaccording to type data supplied in a ParamListInfo. There seems to beno harm in letting makeParamList install that callback by default,rather than leaving a new ParamListInfo's parserSetup hook as NULL.(Indeed, as of HEAD, I couldn't find anyplace that was using theparserSetup field at all; plpgsql was using parserSetupArg for itsown purposes, but parserSetup seemed to be write-only.)We can actually get plpgsql out of the business of using legacy nullflags altogether, and using ParamListInfo instead of its ad-hocPreparedParamsData structure; but this requires inventing one moreSPI API call that can replace SPI_cursor_open_with_args. That seemsworth doing, though.SPI_execute_with_args and SPI_cursor_open_with_args are now unusedanywhere in the core PG distribution. Perhaps someday we coulddeprecate/remove them. But cleaning up the crufty bits of the SPIAPI is a task for a different patch.Per bug #16040 from Jeremy Smith. This is unfortunately too invasive toconsider back-patching. Patch by me; thanks to Hamid Akhtar for review.Discussion:https://postgr.es/m/16040-eaacad11fecfb198@postgresql.org
1 parentaaf8c99 commit2f48ede

File tree

9 files changed

+796
-147
lines changed

9 files changed

+796
-147
lines changed

‎doc/src/sgml/spi.sgml

Lines changed: 349 additions & 0 deletions
Original file line numberDiff line numberDiff line change
@@ -785,6 +785,133 @@ int SPI_execute_with_args(const char *<parameter>command</parameter>,
785785

786786
<!-- *********************************************** -->
787787

788+
<refentry id="spi-spi-execute-with-receiver">
789+
<indexterm><primary>SPI_execute_with_receiver</primary></indexterm>
790+
791+
<refmeta>
792+
<refentrytitle>SPI_execute_with_receiver</refentrytitle>
793+
<manvolnum>3</manvolnum>
794+
</refmeta>
795+
796+
<refnamediv>
797+
<refname>SPI_execute_with_receiver</refname>
798+
<refpurpose>execute a command with out-of-line parameters</refpurpose>
799+
</refnamediv>
800+
801+
<refsynopsisdiv>
802+
<synopsis>
803+
int SPI_execute_with_receiver(const char *<parameter>command</parameter>,
804+
ParamListInfo <parameter>params</parameter>,
805+
bool <parameter>read_only</parameter>,
806+
long <parameter>count</parameter>,
807+
DestReceiver *<parameter>dest</parameter>)
808+
</synopsis>
809+
</refsynopsisdiv>
810+
811+
<refsect1>
812+
<title>Description</title>
813+
814+
<para>
815+
<function>SPI_execute_with_receiver</function> executes a command that might
816+
include references to externally supplied parameters. The command text
817+
refers to a parameter as <literal>$<replaceable>n</replaceable></literal>,
818+
and the <parameter>params</parameter> object provides values and type
819+
information for each such symbol.
820+
<parameter>read_only</parameter> and <parameter>count</parameter> have
821+
the same interpretation as in <function>SPI_execute</function>.
822+
</para>
823+
824+
<para>
825+
If <parameter>dest</parameter> is not NULL, then result tuples are passed
826+
to that object as they are generated by the executor, instead of being
827+
accumulated in <varname>SPI_tuptable</varname>. Using a
828+
caller-supplied <literal>DestReceiver</literal> object is particularly
829+
helpful for queries that might generate many tuples, since the data can
830+
be processed on-the-fly instead of being accumulated in memory.
831+
</para>
832+
833+
<para>
834+
The <parameter>params</parameter> object should normally mark each
835+
parameter with the <literal>PARAM_FLAG_CONST</literal> flag, since
836+
a one-shot plan is always used for the query.
837+
</para>
838+
</refsect1>
839+
840+
<refsect1>
841+
<title>Arguments</title>
842+
843+
<variablelist>
844+
<varlistentry>
845+
<term><literal>const char * <parameter>command</parameter></literal></term>
846+
<listitem>
847+
<para>
848+
command string
849+
</para>
850+
</listitem>
851+
</varlistentry>
852+
853+
<varlistentry>
854+
<term><literal>ParamListInfo <parameter>params</parameter></literal></term>
855+
<listitem>
856+
<para>
857+
data structure containing parameter types and values; NULL if none
858+
</para>
859+
</listitem>
860+
</varlistentry>
861+
862+
<varlistentry>
863+
<term><literal>bool <parameter>read_only</parameter></literal></term>
864+
<listitem>
865+
<para><literal>true</literal> for read-only execution</para>
866+
</listitem>
867+
</varlistentry>
868+
869+
<varlistentry>
870+
<term><literal>long <parameter>count</parameter></literal></term>
871+
<listitem>
872+
<para>
873+
maximum number of rows to return,
874+
or <literal>0</literal> for no limit
875+
</para>
876+
</listitem>
877+
</varlistentry>
878+
879+
<varlistentry>
880+
<term><literal>DestReceiver * <parameter>dest</parameter></literal></term>
881+
<listitem>
882+
<para>
883+
<literal>DestReceiver</literal> object that will receive any tuples
884+
emitted by the query; if NULL, tuples are returned
885+
in <varname>SPI_tuptable</varname>
886+
</para>
887+
</listitem>
888+
</varlistentry>
889+
</variablelist>
890+
</refsect1>
891+
892+
<refsect1>
893+
<title>Return Value</title>
894+
895+
<para>
896+
The return value is the same as for <function>SPI_execute</function>.
897+
</para>
898+
899+
<para>
900+
When <parameter>dest</parameter> is NULL,
901+
<varname>SPI_processed</varname> and
902+
<varname>SPI_tuptable</varname> are set as in
903+
<function>SPI_execute</function>.
904+
When <parameter>dest</parameter> is not NULL,
905+
<varname>SPI_processed</varname> is set to zero and
906+
<varname>SPI_tuptable</varname> is set to NULL. If a tuple count
907+
is required, the caller's <literal>DestReceiver</literal> object must
908+
calculate it.
909+
</para>
910+
</refsect1>
911+
</refentry>
912+
913+
<!-- *********************************************** -->
914+
788915
<refentry id="spi-spi-prepare">
789916
<indexterm><primary>SPI_prepare</primary></indexterm>
790917

@@ -1564,6 +1691,120 @@ int SPI_execute_plan_with_paramlist(SPIPlanPtr <parameter>plan</parameter>,
15641691

15651692
<!-- *********************************************** -->
15661693

1694+
<refentry id="spi-spi-execute-plan-with-receiver">
1695+
<indexterm><primary>SPI_execute_plan_with_receiver</primary></indexterm>
1696+
1697+
<refmeta>
1698+
<refentrytitle>SPI_execute_plan_with_receiver</refentrytitle>
1699+
<manvolnum>3</manvolnum>
1700+
</refmeta>
1701+
1702+
<refnamediv>
1703+
<refname>SPI_execute_plan_with_receiver</refname>
1704+
<refpurpose>execute a statement prepared by <function>SPI_prepare</function></refpurpose>
1705+
</refnamediv>
1706+
1707+
<refsynopsisdiv>
1708+
<synopsis>
1709+
int SPI_execute_plan_with_receiver(SPIPlanPtr <parameter>plan</parameter>,
1710+
ParamListInfo <parameter>params</parameter>,
1711+
bool <parameter>read_only</parameter>,
1712+
long <parameter>count</parameter>,
1713+
DestReceiver *<parameter>dest</parameter>)
1714+
</synopsis>
1715+
</refsynopsisdiv>
1716+
1717+
<refsect1>
1718+
<title>Description</title>
1719+
1720+
<para>
1721+
<function>SPI_execute_plan_with_receiver</function> executes a statement
1722+
prepared by <function>SPI_prepare</function>. This function is
1723+
equivalent to <function>SPI_execute_plan_with_paramlist</function>
1724+
except that, instead of always accumulating the result tuples into a
1725+
<varname>SPI_tuptable</varname> structure, tuples can be passed to a
1726+
caller-supplied <literal>DestReceiver</literal> object as they are
1727+
generated by the executor. This is particularly helpful for queries
1728+
that might generate many tuples, since the data can be processed
1729+
on-the-fly instead of being accumulated in memory.
1730+
</para>
1731+
</refsect1>
1732+
1733+
<refsect1>
1734+
<title>Arguments</title>
1735+
1736+
<variablelist>
1737+
<varlistentry>
1738+
<term><literal>SPIPlanPtr <parameter>plan</parameter></literal></term>
1739+
<listitem>
1740+
<para>
1741+
prepared statement (returned by <function>SPI_prepare</function>)
1742+
</para>
1743+
</listitem>
1744+
</varlistentry>
1745+
1746+
<varlistentry>
1747+
<term><literal>ParamListInfo <parameter>params</parameter></literal></term>
1748+
<listitem>
1749+
<para>
1750+
data structure containing parameter types and values; NULL if none
1751+
</para>
1752+
</listitem>
1753+
</varlistentry>
1754+
1755+
<varlistentry>
1756+
<term><literal>bool <parameter>read_only</parameter></literal></term>
1757+
<listitem>
1758+
<para><literal>true</literal> for read-only execution</para>
1759+
</listitem>
1760+
</varlistentry>
1761+
1762+
<varlistentry>
1763+
<term><literal>long <parameter>count</parameter></literal></term>
1764+
<listitem>
1765+
<para>
1766+
maximum number of rows to return,
1767+
or <literal>0</literal> for no limit
1768+
</para>
1769+
</listitem>
1770+
</varlistentry>
1771+
1772+
<varlistentry>
1773+
<term><literal>DestReceiver * <parameter>dest</parameter></literal></term>
1774+
<listitem>
1775+
<para>
1776+
<literal>DestReceiver</literal> object that will receive any tuples
1777+
emitted by the query; if NULL, this function is exactly equivalent to
1778+
<function>SPI_execute_plan_with_paramlist</function>
1779+
</para>
1780+
</listitem>
1781+
</varlistentry>
1782+
</variablelist>
1783+
</refsect1>
1784+
1785+
<refsect1>
1786+
<title>Return Value</title>
1787+
1788+
<para>
1789+
The return value is the same as for <function>SPI_execute_plan</function>.
1790+
</para>
1791+
1792+
<para>
1793+
When <parameter>dest</parameter> is NULL,
1794+
<varname>SPI_processed</varname> and
1795+
<varname>SPI_tuptable</varname> are set as in
1796+
<function>SPI_execute_plan</function>.
1797+
When <parameter>dest</parameter> is not NULL,
1798+
<varname>SPI_processed</varname> is set to zero and
1799+
<varname>SPI_tuptable</varname> is set to NULL. If a tuple count
1800+
is required, the caller's <literal>DestReceiver</literal> object must
1801+
calculate it.
1802+
</para>
1803+
</refsect1>
1804+
</refentry>
1805+
1806+
<!-- *********************************************** -->
1807+
15671808
<refentry id="spi-spi-execp">
15681809
<indexterm><primary>SPI_execp</primary></indexterm>
15691810

@@ -2041,6 +2282,114 @@ Portal SPI_cursor_open_with_paramlist(const char *<parameter>name</parameter>,
20412282

20422283
<!-- *********************************************** -->
20432284

2285+
<refentry id="spi-spi-cursor-parse-open-with-paramlist">
2286+
<indexterm><primary>SPI_cursor_parse_open_with_paramlist</primary></indexterm>
2287+
2288+
<refmeta>
2289+
<refentrytitle>SPI_cursor_parse_open_with_paramlist</refentrytitle>
2290+
<manvolnum>3</manvolnum>
2291+
</refmeta>
2292+
2293+
<refnamediv>
2294+
<refname>SPI_cursor_parse_open_with_paramlist</refname>
2295+
<refpurpose>set up a cursor using a query and parameters</refpurpose>
2296+
</refnamediv>
2297+
2298+
<refsynopsisdiv>
2299+
<synopsis>
2300+
Portal SPI_cursor_parse_open_with_paramlist(const char *<parameter>name</parameter>,
2301+
const char *<parameter>command</parameter>,
2302+
ParamListInfo <parameter>params</parameter>,
2303+
bool <parameter>read_only</parameter>,
2304+
int <parameter>cursorOptions</parameter>)
2305+
</synopsis>
2306+
</refsynopsisdiv>
2307+
2308+
<refsect1>
2309+
<title>Description</title>
2310+
2311+
<para>
2312+
<function>SPI_cursor_parse_open_with_paramlist</function> sets up a cursor
2313+
(internally, a portal) that will execute the specified query. This
2314+
function is equivalent to <function>SPI_cursor_open_with_args</function>
2315+
except that any parameters referenced by the query are provided by
2316+
a <literal>ParamListInfo</literal> object, rather than in ad-hoc arrays.
2317+
</para>
2318+
2319+
<para>
2320+
The <parameter>params</parameter> object should normally mark each
2321+
parameter with the <literal>PARAM_FLAG_CONST</literal> flag, since
2322+
a one-shot plan is always used for the query.
2323+
</para>
2324+
2325+
<para>
2326+
The passed-in parameter data will be copied into the cursor's portal, so it
2327+
can be freed while the cursor still exists.
2328+
</para>
2329+
</refsect1>
2330+
2331+
<refsect1>
2332+
<title>Arguments</title>
2333+
2334+
<variablelist>
2335+
<varlistentry>
2336+
<term><literal>const char * <parameter>name</parameter></literal></term>
2337+
<listitem>
2338+
<para>
2339+
name for portal, or <symbol>NULL</symbol> to let the system
2340+
select a name
2341+
</para>
2342+
</listitem>
2343+
</varlistentry>
2344+
2345+
<varlistentry>
2346+
<term><literal>const char * <parameter>command</parameter></literal></term>
2347+
<listitem>
2348+
<para>
2349+
command string
2350+
</para>
2351+
</listitem>
2352+
</varlistentry>
2353+
2354+
<varlistentry>
2355+
<term><literal>ParamListInfo <parameter>params</parameter></literal></term>
2356+
<listitem>
2357+
<para>
2358+
data structure containing parameter types and values; NULL if none
2359+
</para>
2360+
</listitem>
2361+
</varlistentry>
2362+
2363+
<varlistentry>
2364+
<term><literal>bool <parameter>read_only</parameter></literal></term>
2365+
<listitem>
2366+
<para><literal>true</literal> for read-only execution</para>
2367+
</listitem>
2368+
</varlistentry>
2369+
2370+
<varlistentry>
2371+
<term><literal>int <parameter>cursorOptions</parameter></literal></term>
2372+
<listitem>
2373+
<para>
2374+
integer bit mask of cursor options; zero produces default behavior
2375+
</para>
2376+
</listitem>
2377+
</varlistentry>
2378+
</variablelist>
2379+
</refsect1>
2380+
2381+
<refsect1>
2382+
<title>Return Value</title>
2383+
2384+
<para>
2385+
Pointer to portal containing the cursor. Note there is no error
2386+
return convention; any error will be reported via <function>elog</function>.
2387+
</para>
2388+
</refsect1>
2389+
</refentry>
2390+
2391+
<!-- *********************************************** -->
2392+
20442393
<refentry id="spi-spi-cursor-find">
20452394
<indexterm><primary>SPI_cursor_find</primary></indexterm>
20462395

‎src/backend/commands/portalcmds.c

Lines changed: 3 additions & 1 deletion
Original file line numberDiff line numberDiff line change
@@ -383,7 +383,9 @@ PersistHoldablePortal(Portal portal)
383383
SetTuplestoreDestReceiverParams(queryDesc->dest,
384384
portal->holdStore,
385385
portal->holdContext,
386-
true);
386+
true,
387+
NULL,
388+
NULL);
387389

388390
/* Fetch the result set into the tuplestore */
389391
ExecutorRun(queryDesc,ForwardScanDirection,0L, false);

0 commit comments

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