Início / dba / Perguntas / 332020
Accepted
EᑎOT
Asked: 2023-10-11 04:42:47 +0800 CST

Comportamento estranho de consulta no PostgreSQL

  • 772

O banco de dados é PostgreSQL15.

Estou escrevendo uma consulta que retornará uma lista de partições de tabela que preciso cortar. Minhas partições são nomeadas da seguinte forma: table_YYYYDOY, onde YYYY é o ano e DOY é o dia do ano. Por exemplo, a partição de hoje será denominada mytable_2023283.

Na consulta, gostaria de filtrar todas as partições com mais de 2 dias para poder removê-las. Recebo a lista de tabelas e depois entro nessa lista para selecionar aquelas que são partições da minha tabela principal. A configuração de amostra está incluída no db-fiddle neste link:

https://www.db-fiddle.com/#&togetherjs=QLQyTaPQkO

A consulta é a seguinte:

select  t.table_schema as schema_name,
        t.table_name,
        right(t.table_name, 7)::int8 as partition_postfix,
        (date_part('year', now()) * 1000 + date_part('doy', now() - interval '2 days'))::int8 as threshold,
        right(t.table_name, 7)::int8 - (date_part('year', now()) * 1000 + date_part('doy', now() - interval '2 days'))::int8 as diff
    from information_schema.tables t
    join pg_inherits i on i.inhrelid = (t.table_schema || '.' || t.table_name)::regclass
    where t.table_schema = 'public'
--      and right(t.table_name, 7)::int8 < (date_part('year', now()) * 1000 + date_part('doy', now() - interval '2 days'))::int8;

Tudo parece estar bem - mas quando tento incluir qualquer forma desse cálculo na cláusula WHERE, a consulta falha com Query Error: error: invalid input sintaxe for type bigint: "ref_sys" .

Parece que as tabelas do sistema que não estão particionadas são incluídas na lista assim que houver um WHERE. Tentei colocar isso em um CTE e operar no campo "diff" depois - também não funcionou.

Alguém pode me explicar o que está acontecendo aqui?

postgresql

1 respostas

  1. Best Answer

    O PostgreSQL toma a liberdade de avaliar WHEREas condições antes de juntar as tabelas. Para evitar isso, use um CTE materializado:

    WITH cte AS MATERIALIZED (
   SELECT ...
   FROM information_schema.tables t
      JOIN pg_inherits i ON ...
)
SELECT ...
FROM cte
WHERE ...

    Então o PostgreSQL avaliará a junção antes da WHEREcondição, uma vez que um CTE materializado é uma barreira do otimizador.

    • 0

relate perguntas