No partition pruning when initializing query plan with LATERAL JOIN and aggregates
I wonder why partition pruning doesn't work with LATERAL JOIN and aggregates. Below is my example tested on PostgreSQL 12.1 (Ubuntu 12.1-1.pgdg18.04+1): CREATE TABLE demo(key BIGINT) PARTITION BY LIST (key); CREATE TABLE demo_key_1 PARTITION OF demo FOR VALUES IN (1); CREATE TABLE demo_key_2 PARTITION OF demo FOR VALUES IN (2); INSERT INTO demo(key) VALUES (1), (2); ANALYZE demo; CREATE TABLE demo2(key BIGINT) PARTITION BY LIST (key); CREATE TABLE demo2_key_1 PARTITION OF demo2 FOR VALUES IN (1); CREATE TABLE demo2_key_2 PARTITION OF demo2 FOR VALUES IN (2); INSERT INTO demo2(key) VALUES (1), (2); ANALYZE demo2; Now, if there are no aggregates in SELECT under LATERAL JOIN, everything works as expected - only a single partition of each table is scanned: EXPLAIN ANALYZE SELECT * FROM demo JOIN LATERAL ( SELECT key AS key2 FROM demo2 WHERE demo2.key = demo.key ) d ON TRUE WHERE demo.key = 1; QUERY PLAN --- Nested Loop (cost=0.00..2.03 rows=1 width=16) (actual time=0.007..0.008 rows=1 loops=1) -> Seq Scan on demo_key_1 (cost=0.00..1.01 rows=1 width=8) (actual time=0.004..0.005 rows=1 loops=1) Filter: (key = 1) -> Seq Scan on demo2_key_1 (cost=0.00..1.01 rows=1 width=8) (actual time=0.001..0.001 rows=1 loops=1) Filter: (key = 1) Planning Time: 0.191 ms Execution Time: 0.025 ms (7 rows) However, when I try a very similar query that contains an aggregate function, partitions of demo2 are not pruned from the query plan: EXPLAIN ANALYZE SELECT * FROM demo JOIN LATERAL ( SELECT sum(demo2.key) AS sum2 FROM demo2 WHERE demo2.key = demo.key ) d ON TRUE WHERE demo.key = 1; QUERY PLAN --- Nested Loop (cost=2.03..3.07 rows=1 width=40) (actual time=0.018..0.018 rows=1 loops=1) -> Seq Scan on demo_key_1 (cost=0.00..1.01 rows=1 width=8) (actual time=0.005..0.005 rows=1 loops=1) Filter: (key = 1) -> Aggregate (cost=2.03..2.04 rows=1 width=32) (actual time=0.011..0.011 rows=1 loops=1) -> Append (cost=0.00..2.03 rows=2 width=8) (actual time=0.004..0.005 rows=1 loops=1) -> Seq Scan on demo2_key_1 (cost=0.00..1.01 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1) Filter: (key = demo_key_1.key) -> Seq Scan on demo2_key_2 (cost=0.00..1.01 rows=1 width=8) (never executed) Filter: (key = demo_key_1.key) Planning Time: 0.174 ms Execution Time: 0.082 ms (11 rows) Of course, Seq Scan on demo2_key_2 was never executed, but why wasn't it pruned from the query plan? More complex queries with hundreds of partitions are affected badly by that. The workaround is to add a redundant condition to the subquery: EXPLAIN ANALYZE SELECT * FROM demo JOIN LATERAL ( SELECT sum(demo2.key) AS sum2 FROM demo2 WHERE demo2.key = 1 AND demo2.key = demo.key ) d ON TRUE WHERE demo.key = 1; QUERY PLAN --- Nested Loop (cost=1.01..2.05 rows=1 width=40) (actual time=0.011..0.011 rows=1 loops=1) -> Seq Scan on demo_key_1 (cost=0.00..1.01 rows=1 width=8) (actual time=0.004..0.004 rows=1 loops=1) Filter: (key = 1) -> Aggregate (cost=1.01..1.02 rows=1 width=32) (actual time=0.006..0.006 rows=1 loops=1) -> Result (cost=0.00..1.01 rows=1 width=8) (actual time=0.002..0.002 rows=1 loops=1) One-Time Filter: (demo_key_1.key = 1) -> Seq Scan on demo2_key_1 (cost=0.00..1.01 rows=1 width=8) (actual time=0.001..0.002 rows=1 loops=1) Filter: (key = 1) Planning Time: 0.079 ms Execution Time: 0.031 ms (10 rows) -- M.B.
Inconsistent permission enforcement for schemas
Hello, Why can a user access a table from a forbidden schema if the table is explicitly specified in the definition of a view in an allowed schema? And not if the table is accessed by a function used in the view. Example: ``` DROP SCHEMA IF EXISTS forbidden_schema CASCADE; DROP SCHEMA IF EXISTS allowed_schema CASCADE; DROP FUNCTION IF EXISTS func CASCADE; DROP USER IF EXISTS pinky; CREATE SCHEMA forbidden_schema; CREATE TABLE forbidden_schema.demo AS SELECT 1; CREATE SCHEMA allowed_schema; CREATE OR REPLACE FUNCTION func() RETURNS BIGINT AS $$ DECLARE i BIGINT; BEGIN SELECT * INTO i FROM forbidden_schema.demo; RETURN i; END; $$ LANGUAGE plpgsql; CREATE VIEW allowed_schema.can_see AS SELECT * FROM forbidden_schema.demo; CREATE VIEW allowed_schema.cannot_see AS SELECT * FROM func(); CREATE ROLE pinky LOGIN NOSUPERUSER PASSWORD 'pinky'; GRANT ALL PRIVILEGES ON SCHEMA allowed_schema TO pinky; GRANT ALL PRIVILEGES ON ALL TABLES IN SCHEMA allowed_schema TO pinky; ``` And now, as user pinky: ``` q=> SELECT * FROM allowed_schema.can_see; ?column? -- 1 (1 row) q=> SELECT * FROM allowed_schema.cannot_see; ERROR: permission denied for schema forbidden_schema LINE 1: SELECT *FROM forbidden_schema.demo ^ QUERY: SELECT *FROM forbidden_schema.demo CONTEXT: PL/pgSQL function func() line 5 at SQL statement ``` Best regards, Marcin Barczyński
How to change NUMERIC type under a domain without rewriting a table?
Hello! In the following setup: DROP DOMAIN IF EXISTS uint64 CASCADE; DROP TABLE IF EXISTS demo; CREATE DOMAIN uint64 AS NUMERIC(20, 0); CREATE TABLE demo(key uint64); INSERT INTO demo SELECT g FROM generate_series(1, 1000) g; I would like to change the type of "key" column to NUMERIC(40, 0). When I run, ALTER TABLE demo ALTER COLUMN key TYPE NUMERIC(40, 0); the whole table gets rewritten. Due to the table size it's not an option in my case. But, if there was no domain in the middle, and the column type was NUMERIC(20, 0), the command above would complete in-place without rewriting any rows. I attempted to modify the definition uint64 in pg_type table: UPDATE pg_type SET typtypmod = 2621444 WHERE typname = 'uint64'; It seems to work. Is it safe? Does it have any unintended consequences? Or maybe there is another way to achieve this? -- Marcin Barczyński
How to troubleshoot: ERROR: cache lookup failed for type?
Hello! When reindexing a table, PostgreSQL returns the following errors for two of its indexes: Index foo_idx1 on foo failed with error: failed to find parent tuple for heap-only tuple at (1162372,1) in table "foo" Index foo_idx2 on foo failed with error: failed to find parent tuple for heap-only tuple at (1162372,1) in table "foo" When trying to show the tuple, there is another error message: SET enable_indexscan = OFF; SET enable_tidscan = OFF; SELECT ctid, xmin, xmax, * FROM sf.dir_current_part_8 WHERE ctid = '(1162372,1)'; ERROR: cache lookup failed for type 1769235301 Searching for oid=1769235301 in pg_class, pg_type, pg_namespace and pg_proc returned no results. It appears to be data corruption. It's PostgreSQL 13 and unfortunately, data_checksums are turned off. I would appreciate any suggestions for troubleshooting this issue. -- Marcin Barczyński
