Package: pgpool2
Version: 3.1.3-5
Severity: normal
Tags: upstream patch
Dear Maintainer,
* What led up to the situation?
We have streaming replication working between a master and a single
slave using PostgreSQL 9.1.9. The database has several DEFERRED
constraints.
* What exactly did you do that was ineffective?
A transaction starts to perform a sequence of operations that will
violate those constraints. When the transaction attempts to COMMIT,
some deferred constraint will raise an exception on the master node
as expected, but it won't raise it on the slave node.
* What was the outcome of this action?
Pgpool interprets this as a "kind error" since it got conflicting
answers from both nodes, and abnormally shuts down the process,
breaking the connection with the client application.
Bear in mind that we detected the error because we are explicitly
raising exceptions on COMMIT through deferred constraints, but
this would also happen when implicit exceptions were raised on
COMMIT (as in out of disk/memory on the server, or other invalid
operational issues not related to DDL/DML).
* What outcome did you expect instead?
The expected behavior would be to effectively pass the exception to
the caller, as if the exception was triggered by a statement within the
transaction, and the pgpool process should continue working normally.
* Can you reproduce the bug?
Yes we can.
Provided you have set up streaming replication between two 9.1
databases, attached you'll find three scripts that help reproduce
the bug:
1. database-setup.sql will create a table with a deferred constraint
enforced by a trigger that simply rises and exception. This simulates
the otherwise more complex setup we have, that just raises and
exception on commit.
2. bug.sql should be run through pgpool to reproduce the bug. The script
performs a simple statement that will raise the exception on COMMIT.
The pgpool daemon will die abnormally because the master will answer
with an error while the slave won't.
3. database-clean.sql will clean-up the objects created by
database-setup.sql.
If you run the script through pgpool, you'll get something like:
psql (9.1.9)
Type "help" for help.
test=# \i bug.sql
BEGIN
INSERT 0 1
psql:bug.sql:12: ERROR: kind mismatch among backends. Possible last query
was: "commit;" kind details are: 0[E: some integrity violation] 1[C]
HINT: check data consistency among db nodes
ERROR: kind mismatch among backends. Possible last query was: "commit;" kind
details are: 0[E: some integrity violation] 1[C]
HINT: check data consistency among db nodes
psql:bug.sql:12: connection to server was lost
You'll also find a patch that was applied (quilt) to build a local
Debian package. When using the aforementioned test scripts on the
patched installation, we get
psql (9.1.9)
Type "help" for help.
test=# \i bug.sql
BEGIN
INSERT 0 1
psql:bug.sql:12: ERROR: some integrity violation
test=# \q
as expected. The original application that led us to the discovery of
the bug also works as expected.
We've filed the bug upstream
http://www.pgpool.net/mantisbt/view.php?id=60
We haven't tried newer versions of pgpool-II, as we must keep on
using pgpool-II 3.1.3 as shipped by Debian Stable for ease of operation
and deployment. Since newer 3.1.x releases only add bug-fixes, we are
hoping either for a future package update ("proposed update") on Wheezy
as soon as we have resolution upstream, or a patched 3.1.3 version.
-- System Information:
Debian Release: 7.0
APT prefers stable
APT policy: (500, 'stable')
Architecture: amd64 (x86_64)
Kernel: Linux 3.2.0-4-amd64 (SMP w/4 CPU cores)
Locale: LANG=en_US.utf8, LC_CTYPE=en_US.utf8 (charmap=UTF-8)
Shell: /bin/sh linked to /bin/dash
Versions of packages pgpool2 depends on:
ii libc6 2.13-38
ii libpgpool0 3.1.3-5
ii libpq5 9.1.9-1
ii lsb-base 4.1+Debian8
ii postgresql-common 134wheezy3
ii ucf 3.0025+nmu3
pgpool2 recommends no packages.
pgpool2 suggests no packages.
-- no debconf information
--- a/pool_process_query.c 2012-04-22 21:40:07.000000000 -0700
+++ b/pool_process_query.c 2013-05-17 23:09:07.633098738 -0700
@@ -3584,7 +3584,15 @@ POOL_STATUS read_kind_from_backend(POOL_
/* degenerate */
pool_error("read_kind_from_backend: %d th kind %c does not match with master or majority connection kind %c",
i, kind_list[i], trust_kind);
- degenerate_node[degenerate_node_num++] = i;
+ if(kind_list[i] == 'C' && trust_kind == 'E' && strncasecmp(query_string_buffer,"commit",6) == 0)
+ {
+ *decided_kind = trust_kind;
+ pool_error("ignoring error because it's caused by a delayed commit");
+ return POOL_CONTINUE;
+ }
+ else
+ pool_error("degenerating node: %d",i);
+ degenerate_node[degenerate_node_num++] = i;
}
}
}
-- This is a simple -*- sql -*- file that sets up a simple table to
-- which inserts cannot happen. This is enforced by a deferred
-- trigger, which allows for the easy reproduction of an issue
-- observed with commit raises an exception.
create table my_table (
col1 text not null primary key
);
-- The p1() function simply takes the place of a trigger that would
-- perform semantic or integrity validations that must occur at the
-- end of the transaction. In our case, it simply raises an exception
-- (ie, always fails).
create function p1() returns trigger as
$$
begin
raise exception 'some integrity violation';
end;
$$
language plpgsql;
-- A simple delayed constraint that insures that the p1() function is
-- invoked at the commit stage.
create constraint trigger t1 after insert on my_table
deferrable initially deferred
for each row execute procedure p1();
-- This script shows the problem. When run in a psql connected to
-- pgpool with one master and one read-only replica, this will cause
-- the pgpool process to exit because the exception found in the
-- commit (in the master node) does not match the success code for the
-- commit in the read-only replica.
-- The expected behavior would be to continue execution normally
-- without dropping the connection.
begin;
insert into my_table ( col1 ) values ( 'ouch' );
commit;
-- This is -*- sql -*- code that removes the test data from the
-- database, to leave things as clean as we found them.
drop trigger t1 on my_table;
drop function p1();
drop table my_table;
