From: | Andrei Lepikhov <lepihov(at)gmail(dot)com> |
---|---|
To: | PostgreSQL mailing lists <pgsql-bugs(at)lists(dot)postgresql(dot)org>, Richard Guo <guofenglinux(at)gmail(dot)com> |
Subject: | Volatile functions under Memoize node |
Date: | 2024-09-19 16:47:30 |
Message-ID: | 7ab4aac3-4790-416f-98b8-2ec0707cceb5@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-bugs |
Hi,
Excuse me if I made noise in vain. After discovering the limits of the
Memoize node, I realized that volatile functions are allowed under the
Memoize. Example:
DROP TABLE IF EXISTS base, other CASCADE;
CREATE TABLE base (x numeric, y text, x1 integer);
INSERT INTO base (x,y,x1) SELECT 1, 'abccccccccccccc'||1,1 FROM
generate_series(1,1E6) AS x;
CREATE TABLE other (x numeric, y text, x1 integer);
INSERT INTO other (x,y,x1) SELECT x, 'abccccccccccccc'||x,1 FROM
generate_series(1,1E4) AS x;
VACUUM ANALYZE base, other;
EXPLAIN (COSTS OFF)
SELECT * FROM base WHERE base.x IN (
SELECT o2.x FROM other o1 LEFT JOIN other o2 ON (o1.x=o2.x) LEFT JOIN
other o3 ON (o2.x=o3.x+random())
WHERE base.x = o1.x GROUP BY o2.x ORDER BY o2.x
);
/*
Nested Loop
-> Seq Scan on base
-> Memoize
Cache Key: base.x
Cache Mode: binary
-> Subquery Scan on "ANY_subquery"
Filter: (base.x = "ANY_subquery".x)
-> Group
Group Key: o2.x
-> Sort
Sort Key: o2.x
-> Nested Loop Left Join
Join Filter: ((o2.x)::double precision
= ((o3.x)::double precision + random()))
-> Nested Loop Left Join
-> Seq Scan on other o1
Filter: (base.x = x)
-> Seq Scan on other o2
Filter: (x = base.x)
-> Seq Scan on other o3
*/
In my mind, any volatile function in any expression should reject the
usage of Memoize, am I wrong? I haven't discovered this case deeply yet,
but maybe someone has a quick and short answer to this question.
--
regards, Andrei Lepikhov
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2024-09-19 21:35:33 | Re: BUG #18545: \dt breaks transaction, calling error when executed in SET SESSION AUTHORIZATION |
Previous Message | Abhishek Mittal | 2024-09-19 13:55:18 | Re: BUG #18624: Memory Leak Issue with PostgreSQL Connection During COPY Command Execution. |