Thread: Patching dblink.c to avoid warning about open transaction

Patching dblink.c to avoid warning about open transaction

From
Jonathan Beit-Aharon
Date:
<font size="+1">Hi,<br /></font><font size="+1">I'm not a member of this list (yet), so please CC me on responses and
discussion.<br/></font><br /><font size="+1">The patch below seems to be completion of work already started, because
theboolean remoteTrFlag was already defined, and all I had to add was its setting and two references.<br /><br /> I
hopesomeone will find it useful,<br /> Jonathan<br /><br /><br /> --- dblink.c    Sat Jan  1 00:43:05 2005<br /> +++
/home/jbeitaharon/dev/third/postgreSQL/contrib/dblink/dblink.c     Thu Sep 22 16:10:20 2005<br /> @@ -329,12 +329,16
@@<br/><br />         if (!conn)<br />                 DBLINK_CONN_NOT_AVAIL;<br /> +<br /> +       if (rcon)<br />
+              rcon->remoteTrFlag = (PQtransactionStatus(conn) != PQTRANS_IDLE);<br /><br /> -       res =
PQexec(conn,"BEGIN");<br /> -       if (PQresultStatus(res) != PGRES_COMMAND_OK)<br /> -              
DBLINK_RES_INTERNALERROR("beginerror");<br /> -<br /> -       PQclear(res);<br /> +       if ((!rcon) ||
(!(rcon->remoteTrFlag))){<br /> +               res = PQexec(conn, "BEGIN");<br /> +               if
(PQresultStatus(res)!= PGRES_COMMAND_OK)<br /> +                       DBLINK_RES_INTERNALERROR("begin error");<br />
+              PQclear(res);<br /> +       }<br /><br />         appendStringInfo(str, "DECLARE %s CURSOR FOR %s",
curname,sql);<br />         res = PQexec(conn, str->data);<br /> @@ -424,12 +428,13 @@<br /><br />        
PQclear(res);<br/><br /> -       /* commit the transaction */<br /> -       res = PQexec(conn, "COMMIT");<br /> -      
if(PQresultStatus(res) != PGRES_COMMAND_OK)<br /> -               DBLINK_RES_INTERNALERROR("commit error");<br /> -<br
/>-       PQclear(res);<br /> +       if ((!rcon) || (!(rcon->remoteTrFlag))) {<br /> +               /* commit the
transaction*/<br /> +               res = PQexec(conn, "COMMIT");<br /> +               if (PQresultStatus(res) !=
PGRES_COMMAND_OK)<br/> +                       DBLINK_RES_INTERNALERROR("commit error");<br /> +              
PQclear(res);<br/> +       }<br /><br />         PG_RETURN_TEXT_P(GET_TEXT("OK"));<br />  }</font> 

Re: Patching dblink.c to avoid warning about open transaction

From
Tom Lane
Date:
Jonathan Beit-Aharon <jbeitaharon@intrusic.com> writes:
>         if (!conn)<br>
>                
DBLINK_CONN_NOT_AVAIL;<br>
> +<br>
> +       if (rcon)<br>
> +               rcon->remoteTrFlag =
(PQtransactionStatus(conn)!=
 
> PQTRANS_IDLE);<br>
> <br>
> -       res = PQexec(conn, "BEGIN");<br>
> -       if (PQresultStatus(res) != PGRES_COMMAND_OK)<br>
>
> [etc]

Could we see this in a less broken format?
        regards, tom lane


Re: Patching dblink.c to avoid warning about open transaction

From
Bruce Momjian
Date:
Tom Lane wrote:
> Jonathan Beit-Aharon <jbeitaharon@intrusic.com> writes:
> >         if (!conn)<br>
> >                
DBLINK_CONN_NOT_AVAIL;<br>
>
> Could we see this in a less broken format?

Here is the patch in text format.

--
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 359-1001
  +  If your life is a hard drive,     |  13 Roberts Road
  +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073
--- dblink.c    Sat Jan  1 00:43:05 2005
+++
/home/jbeitaharon/dev/third/postgreSQL/contrib/dblink/dblink.c
Thu Sep 22 16:10:20 2005
@@ -329,12 +329,16 @@
        if (!conn)
                DBLINK_CONN_NOT_AVAIL;
+
+       if (rcon)
+               rcon->remoteTrFlag = (PQtransactionStatus(conn) !=
PQTRANS_IDLE);
-       res = PQexec(conn, "BEGIN");
-       if (PQresultStatus(res) != PGRES_COMMAND_OK)
-               DBLINK_RES_INTERNALERROR("begin error");
-
-       PQclear(res);
+       if ((!rcon) || (!(rcon->remoteTrFlag))) {
+               res = PQexec(conn, "BEGIN");
+               if (PQresultStatus(res) != PGRES_COMMAND_OK)
+                       DBLINK_RES_INTERNALERROR("begin error");
+               PQclear(res);
+       }
        appendStringInfo(str, "DECLARE %s CURSOR FOR %s", curname,
sql);
        res = PQexec(conn, str->data);
@@ -424,12 +428,13 @@
        PQclear(res);
-       /* commit the transaction */
-       res = PQexec(conn, "COMMIT");
-       if (PQresultStatus(res) != PGRES_COMMAND_OK)
-               DBLINK_RES_INTERNALERROR("commit error");
-
-       PQclear(res);
+       if ((!rcon) || (!(rcon->remoteTrFlag))) {
+               /* commit the transaction */
+               res = PQexec(conn, "COMMIT");
+               if (PQresultStatus(res) != PGRES_COMMAND_OK)
+                       DBLINK_RES_INTERNALERROR("commit error");
+               PQclear(res);
+       }
        PG_RETURN_TEXT_P(GET_TEXT("OK"));
 }

Re: Patching dblink.c to avoid warning about open transaction

From
Bruce Momjian
Date:
[ Joe, would you review this? ]

Your patch has been added to the PostgreSQL unapplied patches list at:
http://momjian.postgresql.org/cgi-bin/pgpatches

It will be applied as soon as one of the PostgreSQL committers reviews
and approves it.

---------------------------------------------------------------------------


Bruce Momjian wrote:
> Tom Lane wrote:
> > Jonathan Beit-Aharon <jbeitaharon@intrusic.com> writes:
> > >         if (!conn)<br>
> > >                
DBLINK_CONN_NOT_AVAIL;<br>
> > 
> > Could we see this in a less broken format?
> 
> Here is the patch in text format.
> 
> -- 
>   Bruce Momjian                        |  http://candle.pha.pa.us
>   pgman@candle.pha.pa.us               |  (610) 359-1001
>   +  If your life is a hard drive,     |  13 Roberts Road
>   +  Christ can be your backup.        |  Newtown Square, Pennsylvania 19073

> --- dblink.c    Sat Jan  1 00:43:05 2005
> +++
> /home/jbeitaharon/dev/third/postgreSQL/contrib/dblink/dblink.c
> Thu Sep 22 16:10:20 2005
> @@ -329,12 +329,16 @@
>         if (!conn)
>                 DBLINK_CONN_NOT_AVAIL;
> +
> +       if (rcon)
> +               rcon->remoteTrFlag = (PQtransactionStatus(conn) !=
> PQTRANS_IDLE);
> -       res = PQexec(conn, "BEGIN");
> -       if (PQresultStatus(res) != PGRES_COMMAND_OK)
> -               DBLINK_RES_INTERNALERROR("begin error");
> -
> -       PQclear(res);
> +       if ((!rcon) || (!(rcon->remoteTrFlag))) {
> +               res = PQexec(conn, "BEGIN");
> +               if (PQresultStatus(res) != PGRES_COMMAND_OK)
> +                       DBLINK_RES_INTERNALERROR("begin error");
> +               PQclear(res);
> +       }
>         appendStringInfo(str, "DECLARE %s CURSOR FOR %s", curname,
> sql);
>         res = PQexec(conn, str->data);
> @@ -424,12 +428,13 @@
>         PQclear(res);
> -       /* commit the transaction */
> -       res = PQexec(conn, "COMMIT");
> -       if (PQresultStatus(res) != PGRES_COMMAND_OK)
> -               DBLINK_RES_INTERNALERROR("commit error");
> -
> -       PQclear(res);
> +       if ((!rcon) || (!(rcon->remoteTrFlag))) {
> +               /* commit the transaction */
> +               res = PQexec(conn, "COMMIT");
> +               if (PQresultStatus(res) != PGRES_COMMAND_OK)
> +                       DBLINK_RES_INTERNALERROR("commit error");
> +               PQclear(res);
> +       }
>         PG_RETURN_TEXT_P(GET_TEXT("OK"));
>  }

> 
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings

--  Bruce Momjian                        |  http://candle.pha.pa.us pgman@candle.pha.pa.us               |  (610)
359-1001+  If your life is a hard drive,     |  13 Roberts Road +  Christ can be your backup.        |  Newtown Square,
Pennsylvania19073
 


Re: Patching dblink.c to avoid warning about open transaction

From
Joe Conway
Date:
Bruce Momjian wrote:
> [ Joe, would you review this? ]
> 
> Your patch has been added to the PostgreSQL unapplied patches list at:
> 
>     http://momjian.postgresql.org/cgi-bin/pgpatches
> 
> It will be applied as soon as one of the PostgreSQL committers reviews
> and approves it.

The patch itself is pretty simple, but I'm unclear on the use case. 
Jonathan, can you elaborate a bit?

Thanks,

Joe

p.s. I'm on a business trip in Asia again, so my responses may be 
delayed a bit.


Re: Patching dblink.c to avoid warning about open transaction

From
Jonathan Beit-Aharon
Date:
Joe Conway wrote:

> Bruce Momjian wrote:
>
>> [ Joe, would you review this? ]
>>
>> Your patch has been added to the PostgreSQL unapplied patches list at:
>>
>>     http://momjian.postgresql.org/cgi-bin/pgpatches
>>
>> It will be applied as soon as one of the PostgreSQL committers reviews
>> and approves it.
>
>
> The patch itself is pretty simple, but I'm unclear on the use case. 
> Jonathan, can you elaborate a bit?
>
> Thanks,
>
> Joe
>
> p.s. I'm on a business trip in Asia again, so my responses may be 
> delayed a bit.

Hi Joe,
We are using the dblink module on Sensor servers to provide 
summarization services to a Central server.  Sensors are in the business 
of populating certain Postgres databases, and the Central is in the 
business of populating a summary Postgres database.  The issue in our 
situation is that the Central server does an explicit BEGIN transaction 
some time before it calls the dblink_open() routine.  On the Sensors, we 
were getting many "there is already a transaction in progress" warnings, 
and overflowing our log storage.  Is this patch the right way to go 
about this?
Thanks,
Jonathan


Re: Patching dblink.c to avoid warning about open transaction

From
Jonathan Beit-Aharon
Date:
Joe Conway wrote:

> Bruce Momjian wrote:
>
>> [ Joe, would you review this? ]
>>
>> Your patch has been added to the PostgreSQL unapplied patches list at:
>>
>>     http://momjian.postgresql.org/cgi-bin/pgpatches
>>
>> It will be applied as soon as one of the PostgreSQL committers reviews
>> and approves it.
>
>
> The patch itself is pretty simple, but I'm unclear on the use case. 
> Jonathan, can you elaborate a bit?
>
> Thanks,
>
> Joe
>
> p.s. I'm on a business trip in Asia again, so my responses may be 
> delayed a bit.

Hi Joe,
We are using the dblink module on Sensor servers to provide 
summarization services to a Central server.  Sensors are in the business 
of populating certain Postgres databases, and the Central is in the 
business of populating a summary Postgres database.  The issue in our 
situation is that the Central server does an explicit BEGIN transaction 
some time before it calls the dblink_open() routine.  On the Sensors, we 
were getting many "there is already a transaction in progress" warnings, 
and overflowing our log storage.  Is this patch the right way to go 
about this?
Thanks,
Jonathan