[pgpool-general: 9477] Question about usage of cache
Achilleas Mantzios
a.mantzios at cloud.gatewaynet.com
Fri May 9 00:34:09 JST 2025
Dear list
If I run this :
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' ;
DEBUG: pool_fetch_from_memory_cache called
DEBUG: memcache encode key
DETAIL: username: "postgres" database_name: "dynacom"
DEBUG: memcache encode key
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' ;"
DEBUG: memcache encode key
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
80b2e4ca0c31'
DEBUG: fetching from cache storage
DETAIL: search key "36d88569f66a6a40f63080b2e4ca0c31"
DEBUG: fetching from cache storage
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
DEBUG: memcache: sending cached messages: 'T' len: 54
DEBUG: memcache: sending cached messages: 'D' len: 42
DEBUG: memcache: sending cached messages: 'D' len: 42
DEBUG: memcache: sending cached messages: 'D' len: 42
DEBUG: memcache: sending cached messages: 'D' len: 42
DEBUG: memcache: sending cached messages: 'D' len: 41
DEBUG: memcache: sending cached messages: 'D' len: 42
DEBUG: memcache: sending cached messages: 'D' len: 42
DEBUG: memcache: sending cached messages: 'D' len: 42
DEBUG: memcache: sending cached messages: 'D' len: 42
DEBUG: memcache: sending cached messages: 'D' len: 42
DEBUG: memcache: sending cached messages: 'C' len: 14
utcts | tagid
-------------------------+-------
2025-05-07 00:00:01.151 | 49729
2025-05-07 00:03:01.185 | 49729
2025-05-07 00:06:01.936 | 49729
2025-05-07 00:09:01.187 | 49729
2025-05-07 00:12:01.95 | 49729
2025-05-07 00:15:01.512 | 49729
2025-05-07 00:18:01.292 | 49729
2025-05-07 00:21:01.123 | 49729
2025-05-07 00:24:01.956 | 49729
2025-05-07 00:27:01.824 | 49729
(10 rows)
DEBUG: fetch from memory cache
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' ;
postgres@[local]/dynacom=#
the cache gets used. However if I change the select by adding date_bin
which is an immutable function, I then get :
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
ERE tagid IN (49729) AND utcts BETWEEN '2025-05-07 00:00:00' AND
'2025-05-07 00:30:00' ;
DEBUG: pool_fetch_from_memory_cache called
DEBUG: memcache encode key
DETAIL: username: "postgres" database_name: "dynacom"
DEBUG: memcache encode key
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
IN (49729) AND utcts BETWEEN '2025-05-07 00:00:00' AND '2025-05-07 00:30:00
message contents do not agree with length in message type "N"
DEBUG: memcache encode key
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
IN (49729) AND utcts BETWEEN '2025-05-07 00:00:00' AND '2025-05-07 00:30:0
DEBUG: fetching from cache storage
DETAIL: search key "2155af6d1c34ce77185fc51404659920"
DEBUG: fetching from cache storage
DETAIL: cache not found on shared memory
message contents do not agree with length in message type "N"
DEBUG: pool_create_temp_query_cache: cache created: 0x1d5dae8b1cc8
DEBUG: checking if query has INSERT INTO, FOR SHARE or FOR UPDATE
DETAIL: result = 0
DEBUG: make function name from funccall: funcname:""date_bin""
DEBUG: non immutable function walker. checking function ""date_bin""
DEBUG: hit local relation cache
DETAIL: query:%s
DEBUG: checking the function volatile property
DETAIL: search result = 1 (i)
DEBUG: hit local relation cache
DETAIL: query:%s
DEBUG: checking the function matches the given type name
DETAIL: search result = 1 (function name: "date_bin" type name:
timestamptz)
DEBUG: checking if SELECT statement contains the IMMUTABLE function call
DETAIL: result = 1
DEBUG: make function name from funccall: funcname:""date_bin""
DEBUG: function call walker, function name: ""date_bin""
DEBUG: hit local relation cache
DETAIL: query:%s
DEBUG: checking the function volatile property
DETAIL: search result = 0 (v)
DEBUG: checking if query has INSERT INTO, FOR SHARE or FOR UPDATE
DETAIL: result = 0
Fpool_query_context.c: decide where to send the query
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
rawdata WHERE tagid IN (49729) AND utcts BETWEEN '2025-05-07 00:00:00' AND
Fpool_query_context.c: checking load balance preconditions. TSTATE:I
writing_transaction:0 failed_transaction:0 isolation:2
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
rawdata WHERE tagid IN (49729) AND utcts BETWEEN '2025-05-07 00:00:00' AND
DEBUG: system catalog walker, checking relation "tagsrawdata"
DEBUG: hit local relation cache
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
og'
DEBUG: temporary table walker. checking relation "tagsrawdata"
DEBUG: hit local relation cache
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_'
DEBUG: make table name from rangevar: tablename:""tagsdata"."tagsrawdata""
DEBUG: unlogged table walker. checking relation ""tagsdata"."tagsrawdata""
DEBUG: hit local relation cache
DETAIL: query:SELECT count(*) FROM pg_catalog.pg_class AS c WHERE c.oid
= pg_catalog.to_regclass('%s') AND c.relpersistence = 'u'
DEBUG: make function name from funccall: funcname:""date_bin""
DEBUG: function call walker, function name: ""date_bin""
DEBUG: hit local relation cache
DETAIL: query:%s
DEBUG: checking the function volatile property
DETAIL: search result = 0 (v)
DEBUG: make function name from funccall: funcname:""date_bin""
DEBUG: function call walker, function name: ""date_bin""
DEBUG: hit local relation cache
DETAIL: query:%s
DEBUG: checking the function volatile property
DETAIL: search result = 0 (v)
DEBUG: waiting for query response
DETAIL: waiting for backend:0 to complete the query
DEBUG: make function name from funccall: funcname:""date_bin""
DEBUG: function call walker, function name: ""date_bin""
DEBUG: hit local relation cache
DETAIL: query:%s
DEBUG: checking the function volatile property
DETAIL: search result = 0 (v)
DEBUG: memcache reset buffer
DETAIL: discard: 0x1d5dae8c5538
postgres@[local]/dynacom=#
and it gets sent to the backend . Why is this happening ?
thank you!
-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://www.pgpool.net/pipermail/pgpool-general/attachments/20250508/d0a38d1b/attachment-0001.htm>
More information about the pgpool-general
mailing list