View Issue Details

IDProjectCategoryView StatusLast Update
0000153Pgpool-IIBugpublic2015-10-14 12:14
ReportercpacejoAssigned Tot-ishii 
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionopen 
PlatformLinuxOSCentOSOS Version7
Product Version 
Target VersionFixed in Version 
Summary0000153: pgpool doesn't replicate data-modifying CTEs
DescriptionSince 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 ReproduceWith 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
TagsNo tags attached.

Activities

cpacejo

2015-10-14 02:23

reporter  

pgpool-cte.patch (952 bytes)
commit 1db8a0b4dd35462681b06849cf630be2ef7ba2bb
Author: Chris Pacejo <cpacejo@clearsky-data.com>
Date:   Tue Oct 13 13:23:23 2015 -0400

    bug #153: parse CTEs for data-modifying statements

diff --git a/src/protocol/pool_process_query.c b/src/protocol/pool_process_query.c
index 4671139..2520adc 100644
--- a/src/protocol/pool_process_query.c
+++ b/src/protocol/pool_process_query.c
@@ -1470,6 +1470,19 @@ bool is_select_query(Node *node, char *sql)
 		if (select_stmt->intoClause || select_stmt->lockingClause)
 			return false;
 
+        if (select_stmt->withClause)
+		{
+			ListCell *cte_cell;
+
+			foreach (cte_cell, select_stmt->withClause->ctes)
+			{
+				CommonTableExpr *cte = (CommonTableExpr *) lfirst(cte_cell);
+
+				if (!is_select_query(cte->ctequery, ""))
+					return false;
+			}
+		}
+
 		if (!pool_config->allow_sql_comments)
 			/* '\0' and ';' signify empty query */
 			return (*sql == 's' || *sql == 'S' || *sql == '(' ||
pgpool-cte.patch (952 bytes)

cpacejo

2015-10-14 02:24

reporter   ~0000585

Attached a patch to fix the issue.

t-ishii

2015-10-14 09:21

developer   ~0000586

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).

t-ishii

2015-10-14 12:13

developer   ~0000589

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.

Issue History

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