src/backend/commands/portalcmds.c

   1 /*-------------------------------------------------------------------------
   2  *
   3  * portalcmds.c
   4  *        Utility commands affecting portals (that is, SQL cursor commands)
   5  *
   6  * Note: see also tcop/pquery.c, which implements portal operations for
   7  * the FE/BE protocol.  This module uses pquery.c for some operations.
   8  * And both modules depend on utils/mmgr/portalmem.c, which controls
   9  * storage management for portals (but doesn't run any queries in them).
  10  *
  11  *
  12  * Portions Copyright (c) 1996-2024, PostgreSQL Global Development Group
  13  * Portions Copyright (c) 1994, Regents of the University of California
  14  *
  15  *
  16  * IDENTIFICATION
  17  *        src/backend/commands/portalcmds.c
  18  *
  19  *-------------------------------------------------------------------------
  20  */
  21
  22 #include "postgres.h"
  23
  24 #include <limits.h>
  25
  26 #include "access/xact.h"
  27 #include "commands/portalcmds.h"
  28 #include "executor/executor.h"
  29 #include "executor/tstoreReceiver.h"
  30 #include "miscadmin.h"
  31 #include "nodes/queryjumble.h"
  32 #include "parser/analyze.h"
  33 #include "rewrite/rewriteHandler.h"
  34 #include "tcop/pquery.h"
  35 #include "tcop/tcopprot.h"
  36 #include "utils/memutils.h"
  37 #include "utils/snapmgr.h"
  38
  39
  40 /*
  41  * PerformCursorOpen
  42  *              Execute SQL DECLARE CURSOR command.
  43  */
  44 void
  45 PerformCursorOpen(ParseState *pstate, DeclareCursorStmt *cstmt, ParamListInfo params,
  46                                   bool isTopLevel)
  47 {
  48         Query      *query = castNode(Query, cstmt->query);
  49         JumbleState *jstate = NULL;
  50         List       *rewritten;
  51         PlannedStmt *plan;
  52         Portal          portal;
  53         MemoryContext oldContext;
  54         char       *queryString;
  55
  56         /*
  57          * Disallow empty-string cursor name (conflicts with protocol-level
  58          * unnamed portal).
  59          */
  60         if (!cstmt->portalname || cstmt->portalname[0] == '\0')
  61                 ereport(ERROR,
  62                                 (errcode(ERRCODE_INVALID_CURSOR_NAME),
  63                                  errmsg("invalid cursor name: must not be empty")));
  64
  65         /*
  66          * If this is a non-holdable cursor, we require that this statement has
  67          * been executed inside a transaction block (or else, it would have no
  68          * user-visible effect).
  69          */
  70         if (!(cstmt->options & CURSOR_OPT_HOLD))
  71                 RequireTransactionBlock(isTopLevel, "DECLARE CURSOR");
  72         else if (InSecurityRestrictedOperation())
  73                 ereport(ERROR,
  74                                 (errcode(ERRCODE_INSUFFICIENT_PRIVILEGE),
  75                                  errmsg("cannot create a cursor WITH HOLD within security-restricted operation")));
  76
  77         /* Query contained by DeclareCursor needs to be jumbled if requested */
  78         if (IsQueryIdEnabled())
  79                 jstate = JumbleQuery(query);
  80
  81         if (post_parse_analyze_hook)
  82                 (*post_parse_analyze_hook) (pstate, query, jstate);
  83
  84         /*
  85          * Parse analysis was done already, but we still have to run the rule
  86          * rewriter.  We do not do AcquireRewriteLocks: we assume the query either
  87          * came straight from the parser, or suitable locks were acquired by
  88          * plancache.c.
  89          */
  90         rewritten = QueryRewrite(query);
  91
  92         /* SELECT should never rewrite to more or less than one query */
  93         if (list_length(rewritten) != 1)
  94                 elog(ERROR, "non-SELECT statement in DECLARE CURSOR");
  95
  96         query = linitial_node(Query, rewritten);
  97
  98         if (query->commandType != CMD_SELECT)
  99                 elog(ERROR, "non-SELECT statement in DECLARE CURSOR");
 100
 101         /* Plan the query, applying the specified options */
 102         plan = pg_plan_query(query, pstate->p_sourcetext, cstmt->options, params);
 103
 104         /*
 105          * Create a portal and copy the plan and query string into its memory.
 106          */
 107         portal = CreatePortal(cstmt->portalname, false, false);
 108
 109         oldContext = MemoryContextSwitchTo(portal->portalContext);
 110
 111         plan = copyObject(plan);
 112
 113         queryString = pstrdup(pstate->p_sourcetext);
 114
 115         PortalDefineQuery(portal,
 116                                           NULL,
 117                                           queryString,
 118                                           CMDTAG_SELECT,        /* cursor's query is always a SELECT */
 119                                           list_make1(plan),
 120                                           NULL);
 121
 122         /*----------
 123          * Also copy the outer portal's parameter list into the inner portal's
 124          * memory context.  We want to pass down the parameter values in case we
 125          * had a command like
 126          *              DECLARE c CURSOR FOR SELECT ... WHERE foo = $1
 127          * This will have been parsed using the outer parameter set and the
 128          * parameter value needs to be preserved for use when the cursor is
 129          * executed.
 130          *----------
 131          */
 132         params = copyParamList(params);
 133
 134         MemoryContextSwitchTo(oldContext);
 135
 136         /*
 137          * Set up options for portal.
 138          *
 139          * If the user didn't specify a SCROLL type, allow or disallow scrolling
 140          * based on whether it would require any additional runtime overhead to do
 141          * so.  Also, we disallow scrolling for FOR UPDATE cursors.
 142          */
 143         portal->cursorOptions = cstmt->options;
 144         if (!(portal->cursorOptions & (CURSOR_OPT_SCROLL | CURSOR_OPT_NO_SCROLL)))
 145         {
 146                 if (plan->rowMarks == NIL &&
 147                         ExecSupportsBackwardScan(plan->planTree))
 148                         portal->cursorOptions |= CURSOR_OPT_SCROLL;
 149                 else
 150                         portal->cursorOptions |= CURSOR_OPT_NO_SCROLL;
 151         }
 152
 153         /*
 154          * Start execution, inserting parameters if any.
 155          */
 156         PortalStart(portal, params, 0, GetActiveSnapshot());
 157
 158         Assert(portal->strategy == PORTAL_ONE_SELECT);
 159
 160         /*
 161          * We're done; the query won't actually be run until PerformPortalFetch is
 162          * called.
 163          */
 164 }
 165
 166 /*
 167  * PerformPortalFetch
 168  *              Execute SQL FETCH or MOVE command.
 169  *
 170  *      stmt: parsetree node for command
 171  *      dest: where to send results
 172  *      qc: where to store a command completion status data.
 173  *
 174  * qc may be NULL if caller doesn't want status data.
 175  */
 176 void
 177 PerformPortalFetch(FetchStmt *stmt,
 178                                    DestReceiver *dest,
 179                                    QueryCompletion *qc)
 180 {
 181         Portal          portal;
 182         uint64          nprocessed;
 183
 184         /*
 185          * Disallow empty-string cursor name (conflicts with protocol-level
 186          * unnamed portal).
 187          */
 188         if (!stmt->portalname || stmt->portalname[0] == '\0')
 189                 ereport(ERROR,
 190                                 (errcode(ERRCODE_INVALID_CURSOR_NAME),
 191                                  errmsg("invalid cursor name: must not be empty")));
 192
 193         /* get the portal from the portal name */
 194         portal = GetPortalByName(stmt->portalname);
 195         if (!PortalIsValid(portal))
 196         {
 197                 ereport(ERROR,
 198                                 (errcode(ERRCODE_UNDEFINED_CURSOR),
 199                                  errmsg("cursor \"%s\" does not exist", stmt->portalname)));
 200                 return;                                 /* keep compiler happy */
 201         }
 202
 203         /* Adjust dest if needed.  MOVE wants destination DestNone */
 204         if (stmt->ismove)
 205                 dest = None_Receiver;
 206
 207         /* Do it */
 208         nprocessed = PortalRunFetch(portal,
 209                                                                 stmt->direction,
 210                                                                 stmt->howMany,
 211                                                                 dest);
 212
 213         /* Return command status if wanted */
 214         if (qc)
 215                 SetQueryCompletion(qc, stmt->ismove ? CMDTAG_MOVE : CMDTAG_FETCH,
 216                                                    nprocessed);
 217 }
 218
 219 /*
 220  * PerformPortalClose
 221  *              Close a cursor.
 222  */
 223 void
 224 PerformPortalClose(const char *name)
 225 {
 226         Portal          portal;
 227
 228         /* NULL means CLOSE ALL */
 229         if (name == NULL)
 230         {
 231                 PortalHashTableDeleteAll();
 232                 return;
 233         }
 234
 235         /*
 236          * Disallow empty-string cursor name (conflicts with protocol-level
 237          * unnamed portal).
 238          */
 239         if (name[0] == '\0')
 240                 ereport(ERROR,
 241                                 (errcode(ERRCODE_INVALID_CURSOR_NAME),
 242                                  errmsg("invalid cursor name: must not be empty")));
 243
 244         /*
 245          * get the portal from the portal name
 246          */
 247         portal = GetPortalByName(name);
 248         if (!PortalIsValid(portal))
 249         {
 250                 ereport(ERROR,
 251                                 (errcode(ERRCODE_UNDEFINED_CURSOR),
 252                                  errmsg("cursor \"%s\" does not exist", name)));
 253                 return;                                 /* keep compiler happy */
 254         }
 255
 256         /*
 257          * Note: PortalCleanup is called as a side-effect, if not already done.
 258          */
 259         PortalDrop(portal, false);
 260 }
 261
 262 /*
 263  * PortalCleanup
 264  *
 265  * Clean up a portal when it's dropped.  This is the standard cleanup hook
 266  * for portals.
 267  *
 268  * Note: if portal->status is PORTAL_FAILED, we are probably being called
 269  * during error abort, and must be careful to avoid doing anything that
 270  * is likely to fail again.
 271  */
 272 void
 273 PortalCleanup(Portal portal)
 274 {
 275         QueryDesc  *queryDesc;
 276
 277         /*
 278          * sanity checks
 279          */
 280         Assert(PortalIsValid(portal));
 281         Assert(portal->cleanup == PortalCleanup);
 282
 283         /*
 284          * Shut down executor, if still running.  We skip this during error abort,
 285          * since other mechanisms will take care of releasing executor resources,
 286          * and we can't be sure that ExecutorEnd itself wouldn't fail.
 287          */
 288         queryDesc = portal->queryDesc;
 289         if (queryDesc)
 290         {
 291                 /*
 292                  * Reset the queryDesc before anything else.  This prevents us from
 293                  * trying to shut down the executor twice, in case of an error below.
 294                  * The transaction abort mechanisms will take care of resource cleanup
 295                  * in such a case.
 296                  */
 297                 portal->queryDesc = NULL;
 298
 299                 if (portal->status != PORTAL_FAILED)
 300                 {
 301                         ResourceOwner saveResourceOwner;
 302
 303                         /* We must make the portal's resource owner current */
 304                         saveResourceOwner = CurrentResourceOwner;
 305                         if (portal->resowner)
 306                                 CurrentResourceOwner = portal->resowner;
 307
 308                         ExecutorFinish(queryDesc);
 309                         ExecutorEnd(queryDesc);
 310                         FreeQueryDesc(queryDesc);
 311
 312                         CurrentResourceOwner = saveResourceOwner;
 313                 }
 314         }
 315 }
 316
 317 /*
 318  * PersistHoldablePortal
 319  *
 320  * Prepare the specified Portal for access outside of the current
 321  * transaction. When this function returns, all future accesses to the
 322  * portal must be done via the Tuplestore (not by invoking the
 323  * executor).
 324  */
 325 void
 326 PersistHoldablePortal(Portal portal)
 327 {
 328         QueryDesc  *queryDesc = portal->queryDesc;
 329         Portal          saveActivePortal;
 330         ResourceOwner saveResourceOwner;
 331         MemoryContext savePortalContext;
 332         MemoryContext oldcxt;
 333
 334         /*
 335          * If we're preserving a holdable portal, we had better be inside the
 336          * transaction that originally created it.
 337          */
 338         Assert(portal->createSubid != InvalidSubTransactionId);
 339         Assert(queryDesc != NULL);
 340
 341         /*
 342          * Caller must have created the tuplestore already ... but not a snapshot.
 343          */
 344         Assert(portal->holdContext != NULL);
 345         Assert(portal->holdStore != NULL);
 346         Assert(portal->holdSnapshot == NULL);
 347
 348         /*
 349          * Before closing down the executor, we must copy the tupdesc into
 350          * long-term memory, since it was created in executor memory.
 351          */
 352         oldcxt = MemoryContextSwitchTo(portal->holdContext);
 353
 354         portal->tupDesc = CreateTupleDescCopy(portal->tupDesc);
 355
 356         MemoryContextSwitchTo(oldcxt);
 357
 358         /*
 359          * Check for improper portal use, and mark portal active.
 360          */
 361         MarkPortalActive(portal);
 362
 363         /*
 364          * Set up global portal context pointers.
 365          */
 366         saveActivePortal = ActivePortal;
 367         saveResourceOwner = CurrentResourceOwner;
 368         savePortalContext = PortalContext;
 369         PG_TRY();
 370         {
 371                 ScanDirection direction = ForwardScanDirection;
 372
 373                 ActivePortal = portal;
 374                 if (portal->resowner)
 375                         CurrentResourceOwner = portal->resowner;
 376                 PortalContext = portal->portalContext;
 377
 378                 MemoryContextSwitchTo(PortalContext);
 379
 380                 PushActiveSnapshot(queryDesc->snapshot);
 381
 382                 /*
 383                  * If the portal is marked scrollable, we need to store the entire
 384                  * result set in the tuplestore, so that subsequent backward FETCHs
 385                  * can be processed.  Otherwise, store only the not-yet-fetched rows.
 386                  * (The latter is not only more efficient, but avoids semantic
 387                  * problems if the query's output isn't stable.)
 388                  *
 389                  * In the no-scroll case, tuple indexes in the tuplestore will not
 390                  * match the cursor's nominal position (portalPos).  Currently this
 391                  * causes no difficulty because we only navigate in the tuplestore by
 392                  * relative position, except for the tuplestore_skiptuples call below
 393                  * and the tuplestore_rescan call in DoPortalRewind, both of which are
 394                  * disabled for no-scroll cursors.  But someday we might need to track
 395                  * the offset between the holdStore and the cursor's nominal position
 396                  * explicitly.
 397                  */
 398                 if (portal->cursorOptions & CURSOR_OPT_SCROLL)
 399                 {
 400                         ExecutorRewind(queryDesc);
 401                 }
 402                 else
 403                 {
 404                         /*
 405                          * If we already reached end-of-query, set the direction to
 406                          * NoMovement to avoid trying to fetch any tuples.  (This check
 407                          * exists because not all plan node types are robust about being
 408                          * called again if they've already returned NULL once.)  We'll
 409                          * still set up an empty tuplestore, though, to keep this from
 410                          * being a special case later.
 411                          */
 412                         if (portal->atEnd)
 413                                 direction = NoMovementScanDirection;
 414                 }
 415
 416                 /*
 417                  * Change the destination to output to the tuplestore.  Note we tell
 418                  * the tuplestore receiver to detoast all data passed through it; this
 419                  * makes it safe to not keep a snapshot associated with the data.
 420                  */
 421                 queryDesc->dest = CreateDestReceiver(DestTuplestore);
 422                 SetTuplestoreDestReceiverParams(queryDesc->dest,
 423                                                                                 portal->holdStore,
 424                                                                                 portal->holdContext,
 425                                                                                 true,
 426                                                                                 NULL,
 427                                                                                 NULL);
 428
 429                 /* Fetch the result set into the tuplestore */
 430                 ExecutorRun(queryDesc, direction, 0, false);
 431
 432                 queryDesc->dest->rDestroy(queryDesc->dest);
 433                 queryDesc->dest = NULL;
 434
 435                 /*
 436                  * Now shut down the inner executor.
 437                  */
 438                 portal->queryDesc = NULL;       /* prevent double shutdown */
 439                 ExecutorFinish(queryDesc);
 440                 ExecutorEnd(queryDesc);
 441                 FreeQueryDesc(queryDesc);
 442
 443                 /*
 444                  * Set the position in the result set.
 445                  */
 446                 MemoryContextSwitchTo(portal->holdContext);
 447
 448                 if (portal->atEnd)
 449                 {
 450                         /*
 451                          * Just force the tuplestore forward to its end.  The size of the
 452                          * skip request here is arbitrary.
 453                          */
 454                         while (tuplestore_skiptuples(portal->holdStore, 1000000, true))
 455                                  /* continue */ ;
 456                 }
 457                 else
 458                 {
 459                         tuplestore_rescan(portal->holdStore);
 460
 461                         /*
 462                          * In the no-scroll case, the start of the tuplestore is exactly
 463                          * where we want to be, so no repositioning is wanted.
 464                          */
 465                         if (portal->cursorOptions & CURSOR_OPT_SCROLL)
 466                         {
 467                                 if (!tuplestore_skiptuples(portal->holdStore,
 468                                                                                    portal->portalPos,
 469                                                                                    true))
 470                                         elog(ERROR, "unexpected end of tuple stream");
 471                         }
 472                 }
 473         }
 474         PG_CATCH();
 475         {
 476                 /* Uncaught error while executing portal: mark it dead */
 477                 MarkPortalFailed(portal);
 478
 479                 /* Restore global vars and propagate error */
 480                 ActivePortal = saveActivePortal;
 481                 CurrentResourceOwner = saveResourceOwner;
 482                 PortalContext = savePortalContext;
 483
 484                 PG_RE_THROW();
 485         }
 486         PG_END_TRY();
 487
 488         MemoryContextSwitchTo(oldcxt);
 489
 490         /* Mark portal not active */
 491         portal->status = PORTAL_READY;
 492
 493         ActivePortal = saveActivePortal;
 494         CurrentResourceOwner = saveResourceOwner;
 495         PortalContext = savePortalContext;
 496
 497         PopActiveSnapshot();
 498
 499         /*
 500          * We can now release any subsidiary memory of the portal's context; we'll
 501          * never use it again.  The executor already dropped its context, but this
 502          * will clean up anything that glommed onto the portal's context via
 503          * PortalContext.
 504          */
 505         MemoryContextDeleteChildren(portal->portalContext);
 506 }