Thread: [MASSMAIL]Help with error date_trunc() function.
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 Angel Prada <mprada@hoplasoftware.com> writes:
> I would need help to know what could be happening to cause the error
> 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