View Issue Details
| ID | Project | Category | View Status | Date Submitted | Last Update |
|---|---|---|---|---|---|
| 0000153 | Pgpool-II | Bug | public | 2015-10-14 00:49 | 2015-10-14 12:14 |
| Reporter | cpacejo | Assigned To | t-ishii | ||
| Priority | normal | Severity | minor | Reproducibility | always |
| Status | resolved | Resolution | open | ||
| Platform | Linux | OS | CentOS | OS Version | 7 |
| Summary | 0000153: pgpool doesn't replicate data-modifying CTEs | ||||
| Description | Since Postgres 9.1 data-modifying CTEs (i.e. UPDATE, INSERT, and DELETE within a WITH clause of a SELECT) are supported. However pgpool looks only at the outermost query to determine whether it should be replicated or not. Pgpool should look within WITH clauses to find data-modifying queries. | ||||
| Steps To Reproduce | With replication_mode=true, failover_if_affected_tuples_mismatch=true, and replicate_select=false: => CREATE TABLE foo(x serial); => WITH r AS (INSERT INTO foo VALUES (default) RETURNING x) SELECT x FROM r; => DELETE FROM foo; ERROR: pgpool detected difference of the number of inserted, updated or deleted tuples. Possible last query was: "DELETE FROM foo;" HINT: check data consistency between master and other db node | ||||
| Tags | No tags attached. | ||||
|
|
|
|
|
Attached a patch to fix the issue. |
|
|
Thanks for the report and patches. Actually we found the same issue while developing pgpool-II 3.5 and the issue has been already committed yesterday. Your patch is essentially the same as the fix in 3.5 but in addition to this we need to take care of other case: namely streaming replication mode. I will back port those fixes in 3.5 into 3.4 or before (as long as the parser supports WITH + DML). |
|
|
Fix committed to 3.3 and 3.4 stable tree. 3.2 or before does not parse data modifying WITH thus I did not back port to those branches. |
| Date Modified | Username | Field | Change |
|---|---|---|---|
| 2015-10-14 00:49 | cpacejo | New Issue | |
| 2015-10-14 02:23 | cpacejo | File Added: pgpool-cte.patch | |
| 2015-10-14 02:24 | cpacejo | Note Added: 0000585 | |
| 2015-10-14 09:11 | t-ishii | Assigned To | => t-ishii |
| 2015-10-14 09:11 | t-ishii | Status | new => assigned |
| 2015-10-14 09:21 | t-ishii | Note Added: 0000586 | |
| 2015-10-14 12:13 | t-ishii | Note Added: 0000589 | |
| 2015-10-14 12:14 | t-ishii | Status | assigned => resolved |
