<!DOCTYPE html>
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=UTF-8">
</head>
<body>
<p>Dear list</p>
<p>If I run this : <br>
</p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">postgres@[local]/dynacom=#
SELECT utcts, tagid FROM tagsdata.tagsrawdata WHERE tagid IN
(49729) AND utcts BETWEEN '2025-05-07 00:00:00' AND
'2025-05-07 00:30:00' ; </span><br>
DEBUG: pool_fetch_from_memory_cache called
<br>
DEBUG: memcache encode key
<br>
DETAIL: username: "postgres" database_name: "dynacom"
<br>
DEBUG: memcache encode key
<br>
DETAIL: query: "SELECT utcts, tagid FROM tagsdata.tagsrawdata
WHERE tagid IN (49729) AND utcts BETWEEN '2025-05-07 00:00:00'
AND '2025-05-07 00:30:00' ;"
<br>
DEBUG: memcache encode key
<br>
DETAIL: `postgresSELECT utcts, tagid FROM tagsdata.tagsrawdata
WHERE tagid IN (49729) AND utcts BETWEEN '2025-05-07 00:00:00'
AND '2025-05-07 00:30:00' ;dynacom' -> `36d88569f66a6a40f630<br>
80b2e4ca0c31'
<br>
DEBUG: fetching from cache storage
<br>
DETAIL: search key "36d88569f66a6a40f63080b2e4ca0c31"
<br>
DEBUG: fetching from cache storage
<br>
DETAIL: query="SELECT utcts, tagid FROM tagsdata.tagsrawdata
WHERE tagid IN (49729) AND utcts BETWEEN '2025-05-07 00:00:00'
AND '2025-05-07 00:30:00' ;" len:499
<br>
DEBUG: memcache: sending cached messages: 'T' len: 54
<br>
DEBUG: memcache: sending cached messages: 'D' len: 42
<br>
DEBUG: memcache: sending cached messages: 'D' len: 42
<br>
DEBUG: memcache: sending cached messages: 'D' len: 42
<br>
DEBUG: memcache: sending cached messages: 'D' len: 42
<br>
DEBUG: memcache: sending cached messages: 'D' len: 41
<br>
DEBUG: memcache: sending cached messages: 'D' len: 42
<br>
DEBUG: memcache: sending cached messages: 'D' len: 42
<br>
DEBUG: memcache: sending cached messages: 'D' len: 42
<br>
DEBUG: memcache: sending cached messages: 'D' len: 42
<br>
DEBUG: memcache: sending cached messages: 'D' len: 42
<br>
DEBUG: memcache: sending cached messages: 'C' len: 14
<br>
utcts | tagid <br>
-------------------------+-------
<br>
2025-05-07 00:00:01.151 | 49729
<br>
2025-05-07 00:03:01.185 | 49729
<br>
2025-05-07 00:06:01.936 | 49729
<br>
2025-05-07 00:09:01.187 | 49729
<br>
2025-05-07 00:12:01.95 | 49729
<br>
2025-05-07 00:15:01.512 | 49729
<br>
2025-05-07 00:18:01.292 | 49729
<br>
2025-05-07 00:21:01.123 | 49729
<br>
2025-05-07 00:24:01.956 | 49729
<br>
2025-05-07 00:27:01.824 | 49729
<br>
(10 rows)
<br>
<br>
DEBUG: fetch from memory cache
<br>
DETAIL: query result found in the query cache, SELECT utcts,
tagid FROM tagsdata.tagsrawdata WHERE tagid IN (49729) AND
utcts BETWEEN '2025-05-07 00:00:00' AND '2025-05-07 00:30:00' ;
<br>
postgres@[local]/dynacom=# <br>
<br>
</span>the cache gets used. However if I change the select by
adding date_bin which is an immutable function, I then get : </p>
<p><span style="font-family:monospace"><span
style="color:#000000;background-color:#ffffff;">postgres@[local]/dynacom=#
SELECT date_bin(('2025-05-08 00:00:00'::timestamp -
'2025-05-07 23:59:59')/(100) , utcts, '2025-05-07' ) as utctsb
, utcts, tagid FROM tagsdata.tagsrawdata WH</span><br>
ERE tagid IN (49729) AND utcts BETWEEN '2025-05-07 00:00:00' AND
'2025-05-07 00:30:00' ;
<br>
DEBUG: pool_fetch_from_memory_cache called
<br>
DEBUG: memcache encode key
<br>
DETAIL: username: "postgres" database_name: "dynacom"
<br>
DEBUG: memcache encode key
<br>
DETAIL: query: "SELECT date_bin(('2025-05-08
00:00:00'::timestamp - '2025-05-07 23:59:59')/(100) , utcts,
'2025-05-07' ) as utctsb , utcts, tagid FROM
tagsdata.tagsrawdata WHERE tagid <br>
IN (49729) AND utcts BETWEEN '2025-05-07 00:00:00' AND
'2025-05-07 00:30:00
<br>
message contents do not agree with length in message type "N"
<br>
DEBUG: memcache encode key
<br>
DETAIL: `postgresSELECT date_bin(('2025-05-08
00:00:00'::timestamp - '2025-05-07 23:59:59')/(100) , utcts,
'2025-05-07' ) as utctsb , utcts, tagid FROM
tagsdata.tagsrawdata WHERE tagid<br>
IN (49729) AND utcts BETWEEN '2025-05-07 00:00:00' AND
'2025-05-07 00:30:0
<br>
DEBUG: fetching from cache storage
<br>
DETAIL: search key "2155af6d1c34ce77185fc51404659920"
<br>
DEBUG: fetching from cache storage
<br>
DETAIL: cache not found on shared memory
<br>
message contents do not agree with length in message type "N"
<br>
DEBUG: pool_create_temp_query_cache: cache created:
0x1d5dae8b1cc8
<br>
DEBUG: checking if query has INSERT INTO, FOR SHARE or FOR
UPDATE
<br>
DETAIL: result = 0
<br>
DEBUG: make function name from funccall: funcname:""date_bin""
<br>
DEBUG: non immutable function walker. checking function
""date_bin""
<br>
DEBUG: hit local relation cache
<br>
DETAIL: query:%s
<br>
DEBUG: checking the function volatile property
<br>
DETAIL: search result = 1 (i)
<br>
DEBUG: hit local relation cache
<br>
DETAIL: query:%s
<br>
DEBUG: checking the function matches the given type name
<br>
DETAIL: search result = 1 (function name: "date_bin" type name:
timestamptz)
<br>
DEBUG: checking if SELECT statement contains the IMMUTABLE
function call
<br>
DETAIL: result = 1
<br>
DEBUG: make function name from funccall: funcname:""date_bin""
<br>
DEBUG: function call walker, function name: ""date_bin""
<br>
DEBUG: hit local relation cache
<br>
DETAIL: query:%s
<br>
DEBUG: checking the function volatile property
<br>
DETAIL: search result = 0 (v)
<br>
DEBUG: checking if query has INSERT INTO, FOR SHARE or FOR
UPDATE
<br>
DETAIL: result = 0
<br>
Fpool_query_context.c: decide where to send the query
<br>
DETAIL: destination = 2 for query= "SELECT
date_bin(('2025-05-08 00:00:00'::timestamp - '2025-05-07
23:59:59')/(100) , utcts, '2025-05-07' ) as utctsb , utcts,
tagid FROM tagsdata.tags<br>
rawdata WHERE tagid IN (49729) AND utcts BETWEEN '2025-05-07
00:00:00' AND <br>
Fpool_query_context.c: checking load balance preconditions.
TSTATE:I writing_transaction:0 failed_transaction:0 isolation:2
<br>
DETAIL: destination = 2 for query= "SELECT
date_bin(('2025-05-08 00:00:00'::timestamp - '2025-05-07
23:59:59')/(100) , utcts, '2025-05-07' ) as utctsb , utcts,
tagid FROM tagsdata.tags<br>
rawdata WHERE tagid IN (49729) AND utcts BETWEEN '2025-05-07
00:00:00' AND <br>
DEBUG: system catalog walker, checking relation "tagsrawdata"
<br>
DEBUG: hit local relation cache
<br>
DETAIL: query:SELECT count(*) FROM pg_catalog.pg_class AS c,
pg_catalog.pg_namespace AS n WHERE c.oid =
pg_catalog.to_regclass('"%s"') AND c.relnamespace = n.oid AND
n.nspname = 'pg_catal<br>
og'
<br>
DEBUG: temporary table walker. checking relation "tagsrawdata"
<br>
DEBUG: hit local relation cache
<br>
DETAIL: query:SELECT count(*) FROM pg_catalog.pg_class AS c,
pg_catalog.pg_namespace AS n WHERE c.relname = '%s' AND
c.relnamespace = n.oid AND n.nspname ~ '^pg_temp_'
<br>
DEBUG: make table name from rangevar:
tablename:""tagsdata"."tagsrawdata""
<br>
DEBUG: unlogged table walker. checking relation
""tagsdata"."tagsrawdata""
<br>
DEBUG: hit local relation cache
<br>
DETAIL: query:SELECT count(*) FROM pg_catalog.pg_class AS c
WHERE c.oid = pg_catalog.to_regclass('%s') AND c.relpersistence
= 'u'
<br>
DEBUG: make function name from funccall: funcname:""date_bin""
<br>
DEBUG: function call walker, function name: ""date_bin""
<br>
DEBUG: hit local relation cache
<br>
DETAIL: query:%s
<br>
DEBUG: checking the function volatile property
<br>
DETAIL: search result = 0 (v)
<br>
DEBUG: make function name from funccall: funcname:""date_bin""
<br>
DEBUG: function call walker, function name: ""date_bin""
<br>
DEBUG: hit local relation cache
<br>
DETAIL: query:%s
<br>
DEBUG: checking the function volatile property
<br>
DETAIL: search result = 0 (v)
<br>
DEBUG: waiting for query response
<br>
DETAIL: waiting for backend:0 to complete the query
<br>
DEBUG: make function name from funccall: funcname:""date_bin""
<br>
DEBUG: function call walker, function name: ""date_bin""
<br>
DEBUG: hit local relation cache
<br>
DETAIL: query:%s
<br>
DEBUG: checking the function volatile property
<br>
DETAIL: search result = 0 (v)
<br>
DEBUG: memcache reset buffer
<br>
DETAIL: discard: 0x1d5dae8c5538
<br>
postgres@[local]/dynacom=# <br>
<br>
</span>and it gets sent to the backend <span
style="font-family:monospace">. </span>Why is this happening ?<span
style="font-family:monospace"><br>
</span></p>
<p><span style="font-family:monospace">thank you!<br>
</span></p>
</body>
</html>