Thread: Patching dblink.c to avoid warning about open transaction
<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>
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
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")); }
[ 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
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.
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
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