Re: [GENERAL] dblink: rollback transaction - Mailing list pgsql-patches

Oleg Lebedev wrote:
> Your fix is awesome! That's exactly what I need.
> What version of postgres do I need to have installed to try this patch?
> I am on 7.3 now.

I plan to apply the attached to cvs tip in 24 hours or so. I don't think
it qualifies as a bug fix, and it does represent a change in user facing
behavior, so I do not intend to apply for 7.3.6 or 7.4.2.

The patch changes dblink_exec() such that an ERROR on the remote side of
the connection will generate a NOTICE on the local side, with the
dblink_exec() return value set to 'ERROR'. This allows the remote side
ERROR to be trapped and handled locally.

One question that I'd like some feedback on is the following: should the
same change be applied to other functions that might throw an ERROR
based on the remote side of the connection? For example, currently if
dblink() is used in an attempt to access a non-existent remote table, an
ERROR is thrown locally in response, killing any currently open
transaction. Thoughts?

Thanks,

Joe

Index: contrib/dblink/dblink.c
===================================================================
RCS file: /opt/src/cvs/pgsql-server/contrib/dblink/dblink.c,v
retrieving revision 1.29
diff -c -r1.29 dblink.c
*** contrib/dblink/dblink.c    28 Nov 2003 05:03:01 -0000    1.29
--- contrib/dblink/dblink.c    5 Feb 2004 19:49:00 -0000
***************
*** 135,140 ****
--- 135,150 ----
                       errmsg("%s", p2), \
                       errdetail("%s", msg))); \
      } while (0)
+ #define DBLINK_RES_ERROR_AS_NOTICE(p2) \
+     do { \
+             msg = pstrdup(PQerrorMessage(conn)); \
+             if (res) \
+                 PQclear(res); \
+             ereport(NOTICE, \
+                     (errcode(ERRCODE_SYNTAX_ERROR), \
+                      errmsg("%s", p2), \
+                      errdetail("%s", msg))); \
+     } while (0)
  #define DBLINK_CONN_NOT_AVAIL \
      do { \
          if(conname) \
***************
*** 731,739 ****
      if (!res ||
          (PQresultStatus(res) != PGRES_COMMAND_OK &&
           PQresultStatus(res) != PGRES_TUPLES_OK))
!         DBLINK_RES_ERROR("sql error");

!     if (PQresultStatus(res) == PGRES_COMMAND_OK)
      {
          /* need a tuple descriptor representing one TEXT column */
          tupdesc = CreateTemplateTupleDesc(1, false);
--- 741,762 ----
      if (!res ||
          (PQresultStatus(res) != PGRES_COMMAND_OK &&
           PQresultStatus(res) != PGRES_TUPLES_OK))
!     {
!         DBLINK_RES_ERROR_AS_NOTICE("sql error");
!
!         /* need a tuple descriptor representing one TEXT column */
!         tupdesc = CreateTemplateTupleDesc(1, false);
!         TupleDescInitEntry(tupdesc, (AttrNumber) 1, "status",
!                            TEXTOID, -1, 0, false);

!         /*
!          * and save a copy of the command status string to return as our
!          * result tuple
!          */
!         sql_cmd_status = GET_TEXT("ERROR");
!
!     }
!     else if (PQresultStatus(res) == PGRES_COMMAND_OK)
      {
          /* need a tuple descriptor representing one TEXT column */
          tupdesc = CreateTemplateTupleDesc(1, false);