Subject: Help with error date_trunc() function.

From: Miguel Angel Prada <[email-masked]>
Date: Fri, 5 Apr 2024 18:23:42 +0200

Hello everyone.

I would need help to know what could be happening to cause the error 
when using the /date_trunc/ function.

I have the following table partitioned by month_year (MM_YYYY)

/postgres=# select tablename from pg_tables where tablename like 'test%';
       tablename
----------------------
  test_mensual
  test_mensual_01_2022
  test_mensual_02_2024
  test_mensual_03_2022
  test_mensual_04_2024
  test_mensual_11_2024
(6 rows)
/

 From the name of table partitions, I want to extract the MM_YYYY and 
with the following query, I want to count the number of monthly 
partitions that are less than a certain date...

/   SELECT count(1)
    FROM (
                SELECT relname,to_date(right 
(relname,7)::text,'MM_YYYY') fecha_part, (current_date - INTERVAL '15 
month') as fecha_max
                FROM pg_catalog.pg_inherits
                      INNER JOIN pg_catalog.pg_class ON 
(pg_inherits.inhrelid = pg_class.oid)
                      INNER JOIN pg_catalog.pg_namespace ON 
(pg_class.relnamespace = pg_namespace.oid)
                   WHERE inhparent = 'test_mensual'::regclass
                   ORDER BY 2
       ) as parts
                WHERE
                date_trunc ('month',fecha_part) < date_trunc 
('month',fecha_max)
/

When I run the above query on a PG 12.6 the following error occurs:

postgres=# select version();
version
----------------------------------------------------------------------------------------------------------
  PostgreSQL 12.16 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 
20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)

/
postgres=#    SELECT count(1)
postgres-#    FROM (
postgres(#                SELECT relname,to_date(right 
(relname,7)::text,'MM_YYYY') fecha_part, (current_date - INTERVAL '15 
month') as fecha_max
postgres(#                FROM pg_catalog.pg_inherits
postgres(#                      INNER JOIN pg_catalog.pg_class ON 
(pg_inherits.inhrelid = pg_class.oid)
postgres(#                      INNER JOIN pg_catalog.pg_namespace ON 
(pg_class.relnamespace = pg_namespace.oid)
postgres(#                   WHERE inhparent = 'test_mensual'::regclass
postgres(#                   ORDER BY 2
postgres(#       ) as parts
postgres-#                WHERE
postgres-#                date_trunc ('month',fecha_part) < date_trunc 
('month',fecha_max)  ;
ERROR:  invalid value "cc" for "MM"
DETAIL:  Value must be an integer./

However, if I run the same query on a PG 15, it runs without a problem.

/postgres=# select version();
version
---------------------------------------------------------------------------------------------------------
  PostgreSQL 15.6 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 
20150623 (Red Hat 4.8.5-44), 64-bit
(1 row)
/

/
/

/postgres=#    SELECT count(1)
    FROM (
                SELECT relname,to_date(right 
(relname,7)::text,'MM_YYYY') fecha_part, (current_date - INTERVAL '15 
month') as fecha_max
                FROM pg_catalog.pg_inherits
                      INNER JOIN pg_catalog.pg_class ON 
(pg_inherits.inhrelid = pg_class.oid)
                      INNER JOIN pg_catalog.pg_namespace ON 
(pg_class.relnamespace = pg_namespace.oid)
                   WHERE inhparent = 'test_mensual'::regclass
                   ORDER BY 2
       ) as parts
                WHERE
                date_trunc ('month',fecha_part) < date_trunc 
('month',fecha_max)  ;
  count
-------
      2
(1 row)
/

/postgres=#    SELECT *
    FROM (
                SELECT relname,to_date(right 
(relname,7)::text,'MM_YYYY') fecha_part, (current_date - INTERVAL '15 
month') as fecha_max
                FROM pg_catalog.pg_inherits
                      INNER JOIN pg_catalog.pg_class ON 
(pg_inherits.inhrelid = pg_class.oid)
                      INNER JOIN pg_catalog.pg_namespace ON 
(pg_class.relnamespace = pg_namespace.oid)
                   WHERE inhparent = 'test_mensual'::regclass
                   ORDER BY 2
       ) as parts
                WHERE
                date_trunc ('month',fecha_part) < date_trunc 
('month',fecha_max)  ;
        relname        | fecha_part |      fecha_max
----------------------+------------+---------------------
  test_mensual_01_2022 | 2022-01-01 | 2023-01-05 00:00:00
  test_mensual_03_2022 | 2022-03-01 | 2023-01-05 00:00:00
(2 rows)
/

The two postgres instances (PG.12 and PG.15) are running on the same 
test server.

/[postgres@multipgsrv1 ~]$ uname -a
Linux multipgsrv1 3.10.0-1160.95.1.el7.x86_64 #1 SMP Mon Jul 24 13:59:37 
UTC 2023 x86_64 x86_64 x86_64 GNU/Linux
[postgres@multipgsrv1 ~]$ cat /etc/redhat-release
CentOS Linux release 7.9.2009 (Core)/

I think the problem may be when executing the function "date_trunc 
('month',fecha_part)" which is performing an incorrect transformation, 
because if I execute the query without the date_trunc. Finishes without 
problem.

/postgres=# SELECT *
    FROM (
                SELECT relname,to_date(right 
(relname,7)::text,'MM_YYYY') fecha_part, (current_date - INTERVAL '15 
month')::date as fecha_max
                FROM pg_catalog.pg_inherits
                      INNER JOIN pg_catalog.pg_class ON 
(pg_inherits.inhrelid = pg_class.oid)
                      INNER JOIN pg_catalog.pg_namespace ON 
(pg_class.relnamespace = pg_namespace.oid)
                   WHERE inhparent = 'test_mensual'::regclass
                   ORDER BY 2
       ) as parts
                WHERE 1=1;
        relname        | fecha_part | fecha_max
----------------------+------------+------------
  test_mensual_01_2022 | 2022-01-01 | 2023-01-05
  test_mensual_03_2022 | 2022-03-01 | 2023-01-05
  test_mensual_02_2024 | 2024-02-01 | 2023-01-05
  test_mensual_04_2024 | 2024-04-01 | 2023-01-05
(4 rows)/

Can you help me find out what the problem and why this is happening?

Thank you so much.

Gretting1

-- 

Miguel Ángel Prada

---

From: Tom Lane <[email-masked]>
Date: Fri, 05 Apr 2024 12:37:47 -0400

> when using the /date_trunc/ function.

EXPLAIN would be informative, but I bet what is happening is that the
date_trunc condition is being pushed down to the scan of pg_class,
since it mentions no variables that aren't available there.  Then it
can get evaluated on tables whose names don't match the pattern you
expect.  I doubt it's more than luck that PG 15 isn't doing the
same thing.

The usual recommendation for fixing this kind of thing is to insert an
optimization fence to keep the WHERE clause from being pushed down.
You could add "OFFSET 0" in the sub-select, or convert it into a
materialized CTE.

			regards, tom lane