[pgpool-general: 1523] Re: After failover to a newly promoted master, pgpools sends COMMIT to slave causing error

Ron Phipps ron at endpoint.com
Fri Mar 22 14:23:09 JST 2013


I have some additional information about this issue.

Npgsql and ODBC when setting up a transaction wait to send the "BEGIN;" 
with the first statement.  So the following code:

                OdbcConnection cn;
                 OdbcCommand cmd;
                 OdbcDataReader dr;
                 OdbcTransaction tr = null;

                 cn = new OdbcConnection("dsn=" + dsn);

                 cmd = new OdbcCommand("{call GetEventLogTableNames 
()}", cn);
                 cn.Open();

                 tr = cn.BeginTransaction();
                 cmd.Transaction = tr;

                 dr = cmd.ExecuteReader();

                 while (dr.Read())
                 {
                     MessageBox.Show(dr.GetString(0));
                 }

                 tr.Commit();
                 cn.Close();

Results in the following in pgpool:

Mar 22 05:17:13 centos62 pgpool[5324]: DB node id: 1 backend pid: 9450 
statement: BEGIN;SELECT * FROM GetEventLogTableNames ()
Mar 22 05:17:21 centos62 pgpool[5324]: DB node id: 1 backend pid: 9450 
statement: COMMIT
Mar 22 05:17:21 centos62 pgpool[5324]: DB node id: 0 backend pid: 23482 
statement: COMMIT
Mar 22 05:17:21 centos62 pgpool[5324]: pool_send_and_wait: Error or 
notice message from backend: : DB node id: 0 backend pid: 23482 
statement: COMMIT message: there is no transaction in progress
Mar 22 05:17:21 centos62 pgpool[5324]: read_kind_from_backend: 1 th kind 
C does not match with master or majority connection kind N
Mar 22 05:17:21 centos62 pgpool[5324]: kind mismatch among backends. 
Possible last query was: "COMMIT" kind details are: 0[N: there is no 
transaction in progress] 1[C]
Mar 22 05:17:21 centos62 pgpool[5324]: do_child: exits with status 1 due 
to error

See how the BEGIN; comes through with the SELECT?  Pgpool is only 
sending this to the master, likely because of the select should only go 
to the master, but the commits go to all nodes.

I have duplicated this behavior with the attached c application.

If instead of sending the BEGIN; with the SELECT we do it in 2 calls, 
then the BEGIN; goes to all nodes and the COMMIT's all succeed, even 
though they probably shouldn't be sent in the first place.

Thanks for the help,
-Ron



-------------- next part --------------
#include <stdlib.h>
#include <stdio.h>
#include <libpq-fe.h>

int main()
{

PGconn *conn;
PGresult *result;

const char *connection_str = "host=localhost port=5432 dbname=OnlineData2 user=a
tsuser password=js1L+5Bn^\\Yh";

conn = PQconnectdb(connection_str);
if (PQstatus(conn) == CONNECTION_BAD) {
        fprintf(stderr, "Connection to %s failed, %s", connection_str, 
        PQerrorMessage(conn));
} else {
        printf("Connected OK\n");
}

// result = PQexec(conn, "BEGIN;");
// result = PQexec(conn, "select * from geteventlogtablenames()");
result = PQexec(conn, "BEGIN; select * from geteventlogtablenames()");

if (!result) {
  printf("PQexec command failed, no error code\n");
} else {
  switch (PQresultStatus(result)) {
  case PGRES_COMMAND_OK:
   printf("Command executed OK, %s rows affected\n",PQcmdTuples(result));
   break;
  case PGRES_TUPLES_OK:
   printf("Query may have returned data\n");

result = PQexec(conn, "COMMIT;");

   break;
   default:
  printf("Command failed with code %s, error message %s\n", PQresStatus(PQresult
Status(result)), PQresultErrorMessage(result));
  break;
  }
PQclear(result);
}

PQfinish(conn);
return EXIT_SUCCESS;
}



More information about the pgpool-general mailing list