Your message dated Wed, 14 Aug 2013 09:03:41 +0000
with message-id <[email protected]>
and subject line Bug#710558: fixed in pgpool2 3.3.0-1
has caused the Debian Bug report #710558,
regarding pgpool2: Exceptions on COMMIT cause abnormal pgpool process
termination and disconnects
to be marked as done.
This means that you claim that the problem has been dealt with.
If this is not the case it is now your responsibility to reopen the
Bug report if necessary, and/or fix the problem forthwith.
(NB: If you are a system administrator and have no idea what this
message is talking about, this may indicate a serious mail system
misconfiguration somewhere. Please contact [email protected]
immediately.)
--
710558: http://bugs.debian.org/cgi-bin/bugreport.cgi?bug=710558
Debian Bug Tracking System
Contact [email protected] with problems
--- Begin Message ---
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;
--- End Message ---
--- Begin Message ---
Source: pgpool2
Source-Version: 3.3.0-1
We believe that the bug you reported is fixed in the latest version of
pgpool2, which is due to be installed in the Debian FTP archive.
A summary of the changes between this version and the previous one is
attached.
Thank you for reporting the bug, which will now be closed. If you
have further comments please address them to [email protected],
and the maintainer will reopen the bug report if appropriate.
Debian distribution maintenance software
pp.
Christoph Berg <[email protected]> (supplier of updated pgpool2 package)
(This message was generated automatically at their request; if you
believe that there is a problem with it please contact the archive
administrators by mailing [email protected])
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA256
Format: 1.8
Date: Wed, 14 Aug 2013 08:47:45 +0200
Source: pgpool2
Binary: pgpool2 libpgpool0 libpgpool-dev postgresql-9.1-pgpool2
Architecture: source amd64
Version: 3.3.0-1
Distribution: unstable
Urgency: low
Maintainer: Christoph Berg <[email protected]>
Changed-By: Christoph Berg <[email protected]>
Description:
libpgpool-dev - pgpool control protocol library - headers
libpgpool0 - pgpool control protocol library
pgpool2 - connection pool server and replication proxy for PostgreSQL
postgresql-9.1-pgpool2 - connection pool server and replication proxy for
PostgreSQL - mod
Closes: 710558
Changes:
pgpool2 (3.3.0-1) unstable; urgency=low
.
* New upstream release. (Closes: #710558: Exceptions on COMMIT cause
abnormal pgpool process termination and disconnects)
* sql/pgpool-walrecrunning is gone, remove patch for it.
* Fix two -Werror=format-security errors in watchdog/wd_packet.c.
Checksums-Sha1:
e83bb45a22c56d41cc9060ab675ef651d2288b7f 2128 pgpool2_3.3.0-1.dsc
eedb4a103792bd6c92a5d2490865519fd7b91141 1680341 pgpool2_3.3.0.orig.tar.gz
66fde4c4d00773b08db3d885283a76526a0dba88 12105 pgpool2_3.3.0-1.debian.tar.gz
7e2727bd0bddf119eea1fa8782f6f87774799e01 768280 pgpool2_3.3.0-1_amd64.deb
b4958a274f3090e5d096d7019e8d7ce1204140a5 18612 libpgpool0_3.3.0-1_amd64.deb
9728a906f9d9f05071ae41e6c042833caddd65ea 21312 libpgpool-dev_3.3.0-1_amd64.deb
46acfd853aa9138232e64867b8ac6b1a5388dc26 15314
postgresql-9.1-pgpool2_3.3.0-1_amd64.deb
Checksums-Sha256:
dd1e9bb00b23472b63d0d8e2bc86c97cc45430319f62cb12f302448856ac06f2 2128
pgpool2_3.3.0-1.dsc
26e936a4854b8400dc4f4e847f0896abb5cbe8d5622ea2c8ec7f7352bd650a05 1680341
pgpool2_3.3.0.orig.tar.gz
5450067b7ceee7fab3cafb7b54ebb0788e4e5edb4fc5e9637cb4579f502bfad0 12105
pgpool2_3.3.0-1.debian.tar.gz
2adbc62360198f119d1129abd16e5bcd115607616ace5566e357b0b87dc7d8e1 768280
pgpool2_3.3.0-1_amd64.deb
b3b62e78d2c230ea2584b23fe27216aaa179ff6420f071a4e297c9ca60541d3a 18612
libpgpool0_3.3.0-1_amd64.deb
3c6fcde516659eee40f039b50bc47793a9082674a90622b4fc8d97abd2276fce 21312
libpgpool-dev_3.3.0-1_amd64.deb
26783212b576691f71a54d5c3039431dc2a6aff131fdb7d9dd0562a2eb281839 15314
postgresql-9.1-pgpool2_3.3.0-1_amd64.deb
Files:
54ce5ed9e1461a917a3c13b927aa12e1 2128 database optional pgpool2_3.3.0-1.dsc
5498af6466feee7e4ebe24b4f0afe154 1680341 database optional
pgpool2_3.3.0.orig.tar.gz
72576f45178dfbba1e4f8bf4e92a362b 12105 database optional
pgpool2_3.3.0-1.debian.tar.gz
24ab0cbc12ad4fd4a8d1d5e9f73ba328 768280 database optional
pgpool2_3.3.0-1_amd64.deb
a48580fa46c35c270c49bbda35a49255 18612 libs optional
libpgpool0_3.3.0-1_amd64.deb
944a5e58a8aea998b0860cf9741182e1 21312 libdevel optional
libpgpool-dev_3.3.0-1_amd64.deb
25410a4500f1a670b86b05cc93053230 15314 database optional
postgresql-9.1-pgpool2_3.3.0-1_amd64.deb
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.12 (GNU/Linux)
iQIcBAEBCAAGBQJSC0CaAAoJEExaa6sS0qeuHkMP/2+6X8WSC8cFPOAQT3Wy9T3Q
uVXUdPS33AFo+hhNhLahULxQ+fHn/Huj0Blozdmaa47z1FiQ2qlEuO7IVMcwt3Hk
BBtyogYoPDMWeBIlxpTYBPBCTG0tSnYGVS7QY33U+9GepryPd12HH1mBkBj5STaD
kY5lMZfoQ3oTDNeL0tayYCPk47TSb+u6jmR+J93E5t2UrWkaMxVlfPipg8FhrA5f
lD3w2fR7e7YKeqmdmVchCWH/+BVBHqmgwX+DGzrgzJ3B58Xu0UU2OsMbqZSzI6d/
rCdRwCapkuJZEKhnVwFLwYWpAgO0f9bozi0CEe68oRFv52f4fqznUJMitN2OQXQw
BSeDkK32NwzGt2Kf3jcLMlkBiBoiOl/94CeTcTWurl0+Fb5nACNT5O+0sUHFqxja
ig006HPRhI2nVDW6uN8ibmoNxbZAOoxG2pVnFf9Dtcl8YmsSZ/4AZlfqqByjsSgK
zbYSp6Vx48g9T0sozN+8ES2pRXlQYtdpfq0kNgZieGim94lKawWm+p5a3dek5blf
8rEOleXdH7CDEHgTcMxhrPM7Mw4yvmWhCqfuKvgop3uHP/+wRyXL4VnwH67s1yvJ
C+8rzhJfbyqIQbzdzusFGksIlmn/wcW1yebKdn5JP17FUhgWlOn2pnktMpaUk6Iy
PYxIop+MZdYWzjtBLRoO
=wOuz
-----END PGP SIGNATURE-----
--- End Message ---
