Re: [GENERAL] Postgres 7.2 - Updating rows in cursor problem - Mailing list pgsql-patches

From Tom Lane
Subject Re: [GENERAL] Postgres 7.2 - Updating rows in cursor problem
Date
Msg-id 5882.1013652725@sss.pgh.pa.us
Whole thread Raw
List pgsql-patches
Vaclav Kulakovsky <vaclav.kulakovsky@definity.cz> writes:
> I've a problem in PG 7.2. If you update rows which are included in plpgsql
> RECORD , updated rows are again added to the RECORD, so you will get into
> infinite loop.

The attached patch against 7.2 appears to fix this problem, as well as
the cursor misbehavior that I exhibited in my followup on pghackers.

If I don't hear any complaints I will commit this soon.

            regards, tom lane

*** src/backend/commands/command.c.orig    Thu Jan  3 18:19:30 2002
--- src/backend/commands/command.c    Wed Feb 13 19:24:00 2002
***************
*** 103,108 ****
--- 103,109 ----
      QueryDesc  *queryDesc;
      EState       *estate;
      MemoryContext oldcontext;
+     CommandId    savedId;
      bool        temp_desc = false;

      /*
***************
*** 156,162 ****
      }

      /*
!      * tell the destination to prepare to receive some tuples.
       */
      BeginCommand(name,
                   queryDesc->operation,
--- 157,163 ----
      }

      /*
!      * Tell the destination to prepare to receive some tuples.
       */
      BeginCommand(name,
                   queryDesc->operation,
***************
*** 169,174 ****
--- 170,183 ----
                   queryDesc->dest);

      /*
+      * Restore the scanCommandId that was current when the cursor was
+      * opened.  This ensures that we see the same tuples throughout the
+      * execution of the cursor.
+      */
+     savedId = GetScanCommandId();
+     SetScanCommandId(PortalGetCommandId(portal));
+
+     /*
       * Determine which direction to go in, and check to see if we're
       * already at the end of the available tuples in that direction.  If
       * so, do nothing.    (This check exists because not all plan node types
***************
*** 213,218 ****
--- 222,232 ----
                  portal->atStart = true; /* we retrieved 'em all */
          }
      }
+
+     /*
+      * Restore outer command ID.
+      */
+     SetScanCommandId(savedId);

      /*
       * Clean up and switch back to old context.
*** src/backend/executor/spi.c.orig    Thu Jan  3 15:30:47 2002
--- src/backend/executor/spi.c    Wed Feb 13 19:23:55 2002
***************
*** 740,748 ****
      _SPI_current->processed = 0;
      _SPI_current->tuptable = NULL;

-     /* Make up a portal name if none given */
      if (name == NULL)
      {
          for (;;)
          {
              unnamed_portal_count++;
--- 740,748 ----
      _SPI_current->processed = 0;
      _SPI_current->tuptable = NULL;

      if (name == NULL)
      {
+         /* Make up a portal name if none given */
          for (;;)
          {
              unnamed_portal_count++;
***************
*** 755,765 ****

          name = portalname;
      }
!
!     /* Ensure the portal doesn't exist already */
!     portal = GetPortalByName(name);
!     if (portal != NULL)
!         elog(ERROR, "cursor \"%s\" already in use", name);

      /* Create the portal */
      portal = CreatePortal(name);
--- 755,767 ----

          name = portalname;
      }
!     else
!     {
!         /* Ensure the portal doesn't exist already */
!         portal = GetPortalByName(name);
!         if (portal != NULL)
!             elog(ERROR, "cursor \"%s\" already in use", name);
!     }

      /* Create the portal */
      portal = CreatePortal(name);
***************
*** 1228,1233 ****
--- 1230,1236 ----
      QueryDesc  *querydesc;
      EState       *estate;
      MemoryContext oldcontext;
+     CommandId    savedId;
      CommandDest olddest;

      /* Check that the portal is valid */
***************
*** 1245,1250 ****
--- 1248,1254 ----

      /* Switch to the portals memory context */
      oldcontext = MemoryContextSwitchTo(PortalGetHeapMemory(portal));
+
      querydesc = PortalGetQueryDesc(portal);
      estate = PortalGetState(portal);

***************
*** 1253,1258 ****
--- 1257,1270 ----
      olddest = querydesc->dest;
      querydesc->dest = dest;

+     /*
+      * Restore the scanCommandId that was current when the cursor was
+      * opened.  This ensures that we see the same tuples throughout the
+      * execution of the cursor.
+      */
+     savedId = GetScanCommandId();
+     SetScanCommandId(PortalGetCommandId(portal));
+
      /* Run the executor like PerformPortalFetch and remember states */
      if (forward)
      {
***************
*** 1278,1283 ****
--- 1290,1300 ----
                  portal->atStart = true;
          }
      }
+
+     /*
+      * Restore outer command ID.
+      */
+     SetScanCommandId(savedId);

      /* Restore the old command destination and switch back to callers */
      /* memory context */
*** src/backend/utils/mmgr/portalmem.c.orig    Thu Oct 25 01:49:51 2001
--- src/backend/utils/mmgr/portalmem.c    Wed Feb 13 19:23:48 2002
***************
*** 168,173 ****
--- 168,174 ----

      portal->queryDesc = queryDesc;
      portal->attinfo = attinfo;
+     portal->commandId = GetScanCommandId();
      portal->state = state;
      portal->atStart = true;        /* Allow fetch forward only */
      portal->atEnd = false;
***************
*** 213,218 ****
--- 214,220 ----
      /* initialize portal query */
      portal->queryDesc = NULL;
      portal->attinfo = NULL;
+     portal->commandId = 0;
      portal->state = NULL;
      portal->atStart = true;        /* disallow fetches until query is set */
      portal->atEnd = true;
*** src/include/utils/portal.h.orig    Mon Nov  5 14:44:35 2001
--- src/include/utils/portal.h    Wed Feb 13 19:23:42 2002
***************
*** 31,36 ****
--- 31,37 ----
      MemoryContext heap;            /* subsidiary memory */
      QueryDesc  *queryDesc;        /* Info about query associated with portal */
      TupleDesc    attinfo;
+     CommandId    commandId;        /* Command counter value for query */
      EState       *state;            /* Execution state of query */
      bool        atStart;        /* T => fetch backwards is not allowed */
      bool        atEnd;            /* T => fetch forwards is not allowed */
***************
*** 48,55 ****
   */
  #define PortalGetQueryDesc(portal)    ((portal)->queryDesc)
  #define PortalGetTupleDesc(portal)    ((portal)->attinfo)
! #define PortalGetState(portal)    ((portal)->state)
! #define PortalGetHeapMemory(portal)  ((portal)->heap)

  /*
   * estimate of the maximum number of open portals a user would have,
--- 49,57 ----
   */
  #define PortalGetQueryDesc(portal)    ((portal)->queryDesc)
  #define PortalGetTupleDesc(portal)    ((portal)->attinfo)
! #define PortalGetCommandId(portal)    ((portal)->commandId)
! #define PortalGetState(portal)        ((portal)->state)
! #define PortalGetHeapMemory(portal)    ((portal)->heap)

  /*
   * estimate of the maximum number of open portals a user would have,

pgsql-patches by date:

Previous
From: Fernando Nasser
Date:
Subject: Allow arbitrary levels of analyze/rewriting
Next
From: Jason Tishler
Date:
Subject: Re: Cygwin plperl patch