[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