View Issue Details

IDProjectCategoryView StatusLast Update
0000056Pgpool-IIBugpublic2013-04-19 14:54
ReporterharukatAssigned Tot-ishii 
PrioritynormalSeverityminorReproducibilityalways
Status resolvedResolutionopen 
Product Version 
Target VersionFixed in Version 
Summary0000056: UPDATE with alias does not discard cache
DescriptionThis UPDATE statement does not discard query result caches about t1 table.

UPDATE t1 AS ta SET v = 'x' WHERE id = 5;
          ~~~~~
Steps To Reproduceversion:
 pgpool-II 3.2.3

pgpool.conf:
 memory_cache_enabled = on
 memqcache_method = 'memcached'


db=# \timing
Timing is on.

db=# SELECT tak(16,12,6), v FROM t1 WHERE id = 5;
 tak | v
-----+-----------------
  16 | xxx xx x x x xx
(1 row)
Time: 6560.081 ms

db=# UPDATE t1 AS ta SET v = 'x' WHERE id = 5;
UPDATE 1
Time: 18.362 ms

db=# SELECT tak(16,12,6), v FROM t1 WHERE id = 5;
 tak | v
-----+-----------------
  16 | xxx xx x x x xx
(1 row)
Time: 1.541 ms

db=# UPDATE t1 SET v = 'x' WHERE id = 5;
UPDATE 1
Time: 15.709 ms

db=# SELECT tak(16,12,6), v FROM t1 WHERE id = 5;
 tak | v
-----+---
  16 | x
(1 row)
Time: 6552.969 ms
TagsNo tags attached.

Activities

t-ishii

2013-04-18 18:41

developer   ~0000264

Last edited: 2013-04-18 18:44

View 2 revisions

It appeared that it is a bug with memory cache handling module. If table is used with table alias in UPDATE, pgpool-II mistakenly recognizes "foo as bar" as table name. Attached patch should fix the problem. Please try it out. Note that similar problem exists with DELETE statmente as well.

t-ishii

2013-04-18 18:41

developer  

query_cache.patch (1,898 bytes)
diff --git a/pool_memqcache.c b/pool_memqcache.c
index f294ea1..2bd3442 100644
--- a/pool_memqcache.c
+++ b/pool_memqcache.c
@@ -113,6 +113,7 @@ static int pool_hash_insert(POOL_QUERY_HASH *key, POOL_CACHEID *cacheid, bool up
 static uint32 create_hash_key(POOL_QUERY_HASH *key);
 static volatile POOL_HASH_ELEMENT *get_new_hash_element(void);
 static void put_back_hash_element(volatile POOL_HASH_ELEMENT *element);
+static char *get_relation_without_alias(RangeVar *relation);
 
 /*
  * Connect to Memcached
@@ -883,12 +884,12 @@ int pool_extract_table_oids(Node *node, int **oidsp)
 	else if (IsA(node, UpdateStmt))
 	{
 		UpdateStmt *stmt = (UpdateStmt *)node;
-		table = nodeToString(stmt->relation);
+		table = get_relation_without_alias(stmt->relation);
 	}
 	else if (IsA(node, DeleteStmt))
 	{
 		DeleteStmt *stmt = (DeleteStmt *)node;
-		table = nodeToString(stmt->relation);
+		table = get_relation_without_alias(stmt->relation);
 	}
 
 #ifdef NOT_USED
@@ -998,6 +999,37 @@ int pool_extract_table_oids(Node *node, int **oidsp)
 	return num_oids;
 }
 
+/*
+ * Get relation name without alias.  NodeToString() calls
+ * _outRangeVar(). Unfortunately _outRangeVar() returns table with
+ * alias ("t1 AS foo") as a table name if table alias is used. This
+ * function just trim down "AS..." part from the table name when table
+ * alias is used.
+ */
+static char *get_relation_without_alias(RangeVar *relation)
+{
+	char *table;
+	char *p;
+
+	if (!IsA(relation, RangeVar))
+	{
+		pool_error("get_relation_without_alias: not RangeVar(%d)", relation->type);
+		return "";
+	}
+	table = nodeToString(relation);
+	if (relation->alias)
+	{
+		p = strchr(table, ' ');
+		if (!p)
+		{
+			pool_error("get_relation_without_alias: cannot locate space;%s", table);
+			return "";
+		}
+		*p = '\0';
+	}
+	return table;		
+}
+
 #define POOL_OIDBUF_SIZE 1024
 static int* oidbuf;
 static int oidbufp;
query_cache.patch (1,898 bytes)

harukat

2013-04-19 14:40

developer   ~0000265

I test this patch, and it works well.

t-ishii

2013-04-19 14:53

developer   ~0000266

Thanks for testing. Fix committed.

Issue History

Date Modified Username Field Change
2013-04-18 17:33 harukat New Issue
2013-04-18 18:41 t-ishii Note Added: 0000264
2013-04-18 18:41 t-ishii Assigned To => t-ishii
2013-04-18 18:41 t-ishii Status new => feedback
2013-04-18 18:41 t-ishii File Added: query_cache.patch
2013-04-18 18:44 t-ishii Note Edited: 0000264 View Revisions
2013-04-19 14:40 harukat Note Added: 0000265
2013-04-19 14:40 harukat Status feedback => assigned
2013-04-19 14:53 t-ishii Note Added: 0000266
2013-04-19 14:54 t-ishii Status assigned => resolved