[pgpool-general: 4565] kind mismatch among backends with COMMIT;

[Dan Foster]

Hi,

I have a 2 node PgPool 3.4.3 set-up with streaming replication (i.e.
replication_mode = off) & load balancing enabled. Some clients get
disconnected and I see the following sort of behaviour in the logs (as
an example):

Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 68008: LOG:
DB node id: 0 backend pid: 68027 statement: BEGIN
Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 68008: LOG:
DB node id: 1 backend pid: 27748 statement: BEGIN
Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 68008: LOG:
DB node id: 0 backend pid: 68027 statement: CREATE OR REPLACE FUNCTION
create_label() RETURNS TRIGGER AS ' BEGIN UPDATE survey_question SET
label = ''b'' || NEW.id WHERE (NEW.label = '''' OR NEW.label IS NULL)
AND id = NEW.id; RETURN NEW; END ' LANGUAGE plpgsql;
Mar 22 23:10:17 db-pg-p3 pgpool: DROP TRIGGER IF EXISTS create_label
ON survey_question;
Mar 22 23:10:17 db-pg-p3 pgpool: CREATE TRIGGER create_label AFTER
INSERT ON survey_question FOR EACH ROW EXECUTE PROCEDURE
create_label();
Mar 22 23:10:17 db-pg-p3 pgpool: CREATE OR REPLACE FUNCTION
update_label() RETURNS TRIGGER AS ' BEGIN UPDATE survey_question SET
label = ''b'' || NEW.id WHERE (OLD.label = '''' OR OLD.label IS NULL)
AND id = OLD.id; RETURN NEW; END ' LANGUAGE plpgsql;
Mar 22 23:10:17 db-pg-p3 pgpool: DROP TRIGGER IF EXISTS update_label
ON survey_question;
Mar 22 23:10:17 db-pg-p3 pgpool: CREATE TRIGGER update_label AFTER
UPDATE ON survey_question FOR EACH ROW  EXECUTE PROCEDURE
update_label();
Mar 22 23:10:17 db-pg-p3 pgpool: -- THIS IS A HACK TO REMOVE
content_id constraint for positive integers only
Mar 22 23:10:17 db-pg-p3 pgpool: -- since we need negative numbers for
FGS -1 for core -2 for default (removable)
Mar 22 23:10:17 db-pg-p3 pgpool: ALTER TABLE
permissions_objectpermission DROP constraint IF EXISTS
Mar 22 23:10:17 db-pg-p3 pgpool: permissions_objectpermission_content_id_check;
Mar 22 23:10:17 db-pg-p3 pgpool: COMMIT;
Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 68008: LOG:
DB node id: 1 backend pid: 27748 statement: COMMIT
Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 68008: LOG:
DB node id: 0 backend pid: 68027 statement: COMMIT
Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 68008: LOG:
pool_send_and_wait: Error or notice message from
backend: : DB node id: 0 backend pid: 68027 statement: "COMMIT"
message: "there is no transaction in progress"
Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 68008:
WARNING:  packet kind of backend 1 ['C'] does not match
 with master/majority nodes packet kind ['N']
Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 68008:
FATAL:  failed to read kind from backend
Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 68008:
DETAIL:  kind mismatch among backends. Possible last qu
ery was: "COMMIT" kind details are: 0[N: there is no transaction in
progress] 1[C]
Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 68008: HINT:
 check data consistency among db nodes
Mar 22 23:10:17 db-pg-p3 pgpool: 2016-03-22 23:10:17: pid 67974: LOG:
child process with pid: 68008 exits with status 5

I can see that it's the return of the COMMITs returning different
values that's causing problems. I can also see why this would be,
because the query from the application contains a COMMIT of it's own
on the end and this only gets run on one backend.

What I do not understand is where the BEGIN and COMMIT statements are
coming from and why they are sent to both backend nodes. Or how to
approach fixing this.

Could anyone offer some guidance?

Thanks,
Dan.
-- 
Dan Foster | Senior Storage Systems Administrator
Advanced Computing Research Centre, University of Bristol