0

Database is PostgreSQL15.

I am writing a query that will return a list of table partitions I need to trim. My partitions are named as such: table_YYYYDOY, where YYYY is the year and DOY is the Day of the Year. For example, the partition for today will be named mytable_2023283.

In the query, I would like to filter all partitions which are older than 2 days so I can remove them. I get the list of tables and then I join that list to select the ones which are partitions of my main table. The sample set up is included in the db-fiddle at this link:

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

The query is as follows:

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;

Everything seems to be fine - but when I try and include any form of that calculation in the WHERE clause the query fails with Query Error: error: invalid input syntax for type bigint: "ref_sys".

It seems the system tables which are not partitioned are included in the list as soon as there's a WHERE. I tried putting this in a CTE and operating on the "diff" field after - that does not work either.

Can anyone explain to me what is going on here?

1 Answer 1

0

PostgreSQL takes the liberty of evaluating WHERE conditions before it joins the tables. To prevent that, use a materialized CTE:

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

Then PostgreSQL will evaluate the join before the WHERE condition, since a materialized CTE is an optimizer barrier.

1
  • Thank you, that works flawlessly!
    – EᑎOT
    Oct 11, 2023 at 18:55

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.