Início / user-317381

snorris's questions

Martin Hope
snorris
Asked: 2024-11-22 02:33:45 +0800 CST
  • 5

A primeira nota de incompatibilidade para notas PG17:

Funções usadas por índices de expressão e visualizações materializadas que precisam referenciar esquemas não padrão devem especificar um caminho de pesquisa durante a criação da função.

A recriação de visualizações materializadas (que funcionavam bem no PG 14-16) usando funções que dependem da ltreeextensão instalada publicfalha no PG17 com:

ERROR:  operator does not exist: public.ltree <@ public.ltree
LINE 3:   code_b <@ code_a

CONTEXT:  SQL function "myfunc" during inlining

Se eu adicionar set search_path = publicà definição da função conforme sugerido pelas notas de incompatibilidade, a visualização materializada poderá ser criada.

Mas, uma vez set search_pathadicionada, a função não pode mais ser usada: o planejador não coleta índices nos ltreetipos.

Existe uma solução alternativa para obter funções usadas em uma visualização materializada para pegar os ltreeoperadores em public? Ou para cutucar o planejador para usar os índices? Ou estou fazendo algo errado?

Para reproduzir, em um novo banco de dados PG 17.0 (e praticamente o mesmo em dockerizado PostgreSQL 17.1 (Debian 17.1-1.pgdg110+1)):

select version();
                                                           version
------------------------------------------------------------------------------------------------------------------------------
 PostgreSQL 17.0 (Homebrew) on aarch64-apple-darwin24.1.0, compiled by Apple clang version 16.0.0 (clang-1600.0.26.4), 64-bit

-- check the default search path
SELECT setting FROM pg_settings WHERE name = 'search_path';
     setting
-----------------
 "$user", public
(1 row)


-- setup test data
create extension if not exists ltree;

create schema test;

create table test.table
(id serial primary key,
 code ltree);

insert into test.table (code)
select
  concat_ws('.',
    rpad(trunc(random() * 10 + 1)::text, 3, '0'),
    rpad(trunc(random() * 10 + 1)::text, 3, '0'),
    rpad(trunc(random() * 100 + 1)::text, 3, '0'),
    rpad(trunc(random() * 100 + 1)::text, 3, '0'),
    rpad(trunc(random() * 100 + 1)::text, 3, '0')
)::ltree as code
from generate_series(1, 1000000) s(i);

create index on test.table using gist (code);
create index on test.table using btree (code);

-- create test function as per pg16 (no search path)
create or replace function test.myfunc(
  code_a ltree,
  code_b ltree)
RETURNS boolean language sql AS $$
SELECT
  code_b <@ code_a
$$
immutable parallel safe;

-- test the function - it picks up the index
explain analyze
select
  a.id
from test.table a
inner join (select * from test.table limit 10) as b
on test.myfunc(a.code, b.code);
                                                               QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.41..15541.90 rows=100000 width=4) (actual time=0.105..1.078 rows=11 loops=1)
   ->  Limit  (cost=0.00..0.20 rows=10 width=36) (actual time=0.015..0.019 rows=10 loops=1)
         ->  Seq Scan on "table"  (cost=0.00..20310.00 rows=1000000 width=36) (actual time=0.014..0.016 rows=10 loops=1)
   ->  Index Scan using table_code_idx on "table" a  (cost=0.41..1454.16 rows=10000 width=36) (actual time=0.086..0.104 rows=1 loops=10)
         Index Cond: (code @> "table".code)
 Planning Time: 1.475 ms
 Execution Time: 1.175 ms
(7 rows)

-- try and use the function in a materialized view,
-- (works fine in PG14,PG16, fails on PG17)
create materialized view test.myview as
select
  a.id
from test.table a
inner join (select * from test.table limit 10) as b
on test.myfunc(a.code, b.code);
ERROR:  operator does not exist: public.ltree <@ public.ltree
LINE 3:   code_b <@ code_a
                 ^
HINT:  No operator matches the given name and argument types. You might need to add explicit type casts.
QUERY:
SELECT
  code_b <@ code_a

CONTEXT:  SQL function "myfunc" during inlining

-- try specifying search_path as recommended in
-- PG17 release notes
create or replace function test.myfunc(
  code_a ltree,
  code_b ltree)
RETURNS boolean set search_path = public language sql AS $$
SELECT
  code_b <@ code_a
$$
immutable parallel safe;

-- test the new function - it works, but does not pick up the index
explain analyze
select
  a.id
from test.table a
inner join (select * from test.table limit 10) as b
on test.myfunc(a.code, b.code);
                                                             QUERY PLAN
-------------------------------------------------------------------------------------------------------------------------------------
 Nested Loop  (cost=0.00..2645310.33 rows=3333333 width=4) (actual time=0.335..6668.876 rows=11 loops=1)
   Join Filter: test.myfunc(a.code, b.code)
   Rows Removed by Join Filter: 9999989
   ->  Seq Scan on "table" a  (cost=0.00..20310.00 rows=1000000 width=36) (actual time=0.034..46.823 rows=1000000 loops=1)
   ->  Materialize  (cost=0.00..0.35 rows=10 width=32) (actual time=0.000..0.000 rows=10 loops=1000000)
         ->  Subquery Scan on b  (cost=0.00..0.30 rows=10 width=32) (actual time=0.008..0.012 rows=10 loops=1)
               ->  Limit  (cost=0.00..0.20 rows=10 width=36) (actual time=0.008..0.011 rows=10 loops=1)
                     ->  Seq Scan on "table"  (cost=0.00..20310.00 rows=1000000 width=36) (actual time=0.006..0.007 rows=10 loops=1)
 Planning Time: 0.313 ms
 Execution Time: 6668.912 ms
(10 rows)

-- Try using the updated func in materialized view
-- The view is successfully created (on this small sample), 
-- but is impractical on larger data because it doesn't use the index
create materialized view test.myview as
select
  a.id
from test.table a
inner join (select * from test.table limit 10) as b
on test.myfunc(a.code, b.code);
SELECT 11
postgresql