From: Tom Lane <tgl@sss.pgh.pa.us>
To: Miguel Angel Prada <mprada@hoplasoftware.com>
cc: pgsql-sql@postgresql.org
Subject: Re: Help with error date_trunc() function.
In-reply-to: <425370c4-c278-48fc-a1db-ef5aa980da8e@hoplasoftware.com>
References: <425370c4-c278-48fc-a1db-ef5aa980da8e@hoplasoftware.com>
Comments: In-reply-to Miguel Angel Prada <mprada@hoplasoftware.com>
	message dated "Fri, 05 Apr 2024 18:23:42 +0200"
MIME-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
Content-ID: <1344782.1712335067.1@sss.pgh.pa.us>
Date: Fri, 05 Apr 2024 12:37:47 -0400
Message-ID: <1344783.1712335067@sss.pgh.pa.us>
Archived-At: 
 <https://www.postgresql.org/message-id/1344783.1712335067%40sss.pgh.pa.us>
Precedence: bulk

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