[Pdns-users] GPGSQL and PostgreSQL restart

[Radek Hladik]

Radek Hladik wrote:
> Hi!
>     I Have another question about PowerDNS. I'm using the gpgsql 
> backend. The problem is, that I need to shutdown postgresql server every 
> day for backups. I've found that the gpgsql backend connects to the db 
> server once it is started and is connected for the whole time pdns is 
> running. When I restart DB server, pdns is not able to reconnect to the 
> db engine and stops working. It looks that the only way is to modify the 
> gpgsql module. Or is there any other possibility?

I wrote small patch to workaround this problem. It's rather quick fix 
but it works for me. Whenever the pgsql query fails it calls the PQreset 
function and retries the query. Failing the query for the second time 
produces exception just as the non-patched module.
I hope you will find this patch useful.


Radek Hladik



-----------------------------------patch begin-------------------------
--- pdns-2.9.16/modules/gpgsqlbackend/spgsql.cc	2004-01-17 
14:18:22.000000000 +0100
+++ pdns-2.9.16-radek/modules/gpgsqlbackend/spgsql.cc	2004-08-05 
13:12:43.000000000 +0200
@@ -70,7 +70,21 @@
        error=PQresultErrorMessage(d_result);
        PQclear(d_result);
      }
+    L<<Logger::Error<< "PostgreSQL failed to execute command: 
"<<error<<endl;
+    L<<Logger::Error<< "Trying reset the db connection"<<endl;
+    PQreset (d_db);
+    L<<Logger::Info<< "Reexecuting command"<<endl;
+      if(!(d_result=PQexec(d_db,query.c_str())) || 
PQresultStatus(d_result)!=PGRES_COMMAND_OK) {
+    error="unknown reason";
+    if(d_result) {
+      error=PQresultErrorMessage(d_result);
+      PQclear(d_result);
+    }
      throw SSqlException("PostgreSQL failed to execute command: "+error);
+    } else
+    {
+     L<<Logger::Info<< "Command executed succesfully"<<endl;
+    }
    }
    if(d_result)
      PQclear(d_result);
@@ -90,7 +104,21 @@
        error=PQresultErrorMessage(d_result);
        PQclear(d_result);
      }
-    throw SSqlException("PostgreSQL failed to execute command: "+error);
+    L<<Logger::Error<< "PostgreSQL failed to execute query: "<<error<<endl;
+    L<<Logger::Error<< "Trying reset the db connection"<<endl;
+    PQreset (d_db);
+    L<<Logger::Info<< "Reexecuting query"<<endl;
+    if(!(d_result=PQexec(d_db,query.c_str())) || 
PQresultStatus(d_result)!=PGRES_TUPLES_OK) {
+    error="unknown reason";
+    if(d_result) {
+      error=PQresultErrorMessage(d_result);
+      PQclear(d_result);
+    }
+    throw SSqlException("PostgreSQL failed to execute query: "+error);
+    } else
+    {
+     L<<Logger::Info<< "Query executed succesfully"<<endl;
+    }
    }

    d_count=0;
@@ -109,7 +137,21 @@
        error=PQresultErrorMessage(d_result);
        PQclear(d_result);
      }
-    throw SSqlException("PostgreSQL failed to execute command: "+error);
+    L<<Logger::Error<< "PostgreSQL failed to execute command: 
"<<error<<endl;
+    L<<Logger::Error<< "Trying reset the db connection"<<endl;
+    PQreset (d_db);
+    L<<Logger::Info<< "Reexecuting query"<<endl;
+    if(!(d_result=PQexec(d_db,query.c_str())) || 
PQresultStatus(d_result)!=PGRES_TUPLES_OK) {
+    error="unknown reason";
+    if(d_result) {
+      error=PQresultErrorMessage(d_result);
+      PQclear(d_result);
+    }
+    throw SSqlException("PostgreSQL failed to execute query: "+error);
+    } else
+    {
+     L<<Logger::Info<< "Query executed succesfully"<<endl;
+    }
    }

    d_count=0;
-----------------------------------patch end---------------------------