View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000060 | Pgpool-II | Bug | public | 2013-05-31 22:48 | 2013-07-02 17:44 |
| Reporter | emhn | Assigned To | t-ishii | ||
| Priority | normal | Severity | major | Reproducibility | always |
| Status | resolved | Resolution | open | ||
| Platform | pgpool-II 3.1.3 | OS | Linux amd64 | OS Version | Debian 7.0 |
| Summary | 0000060: Exceptions on COMMIT cause abnormal pgpool process termination and disconnects | ||||
| Description | We detected this bug using pgpool-II 3.1.3 as shipped by Debian GNU/Linux 7.0. We have streaming replication working between a master and a single slave using PostgreSQL 9.1.9. The database has several DEFERRED constraints. 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. 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. 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 fthe pgpool process should continue working normally. We haven't tried newer versions of pgpool-II, as we must keep on using pgpool-II 3.1.3 as shipped by Debian for ease of operation and deployment. We'll file a bug on Debian's BTS as soon as we have resolution with pgpool, to ask for an upgrade if possible. | ||||
| Steps To Reproduce | Provided you have set up streaming replication between two 9.1 databases, you'll find a tarball attached containing three scripts written by Luis Muñoz <lem@isc.org> 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. We can always reproduce the problem, with output similar to 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 | ||||
| Additional Information | The same tarball has a patch written by Francisco Obispo <fobispo@isc.org> that was applied to our pgpool-II 3.1.3 installation. 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. | ||||
| Tags | No tags attached. | ||||
|
|
|
|
|
Problem confirmed. However attached patch cannot be applied since it has some problems: 1) does not address the case when a transaction is committed by "end" command(even does not work if " commit" for example) 2) it will not work for the case there's more than 1 standbys. I have committed modified patch to git repository. Just in case I attached patch for V3.1 stable tree. |
|
|
|
|
|
I backported your patch into a private Debian package for 3.1.3 and it's been working fine so far. Thanks! Is this patch going to make it into 3.1.8? If so, what's the expected date of release? I'm working with the official Debian maintainer to see if we can refresh the package to the latest one. |
|
|
> Is this patch going to make it into 3.1.8? Yes. > If so, what's the expected date of release? Not decided yet but probably this week or next week. |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2013-05-31 22:48 | emhn | New Issue | |
| 2013-05-31 22:48 | emhn | File Added: fails-on-commit.tgz | |
| 2013-06-02 18:41 | t-ishii | Assigned To | => t-ishii |
| 2013-06-02 18:41 | t-ishii | Status | new => assigned |
| 2013-06-02 20:44 | t-ishii | Note Added: 0000290 | |
| 2013-06-02 20:45 | t-ishii | File Added: pool_process_query.c-v3.1.patch | |
| 2013-06-02 20:46 | t-ishii | Status | assigned => feedback |
| 2013-06-02 21:01 | t-ishii | Changeset attached | => pgpool2 master e7e35046 |
| 2013-06-02 21:01 | t-ishii | Changeset attached | => pgpool2 V3_1_STABLE 6d8c6d26 |
| 2013-06-02 21:01 | t-ishii | Changeset attached | => pgpool2 V3_0_STABLE 865d7690 |
| 2013-07-02 03:00 | emhn | Note Added: 0000300 | |
| 2013-07-02 03:00 | emhn | Status | feedback => assigned |
| 2013-07-02 08:04 | t-ishii | Note Added: 0000301 | |
| 2013-07-02 17:44 | t-ishii | Status | assigned => resolved |
