Re: Issue with pg_get_functiondef

On Wed, 13 Dec 2023 at 00:26, Edouard Tollet <edouard(dot)tollet(at)stoik(dot)io> wrote:
> I'm having trouble understanding the following, I apologize in advance if it is not a bug.

> select * from (
> select proname, prokind, pg_get_functiondef(oid) as def
> from pg_proc
> where pg_proc.prokind = 'f'
> ) def
> where def is not null;
> ERROR: "array_agg" is an aggregate function

The EXPLAIN output shows you what's going on here:

QUERY PLAN
-------------------------------------------------------------------------------
Seq Scan on pg_proc (cost=0.00..155.49 rows=3124 width=97)
Filter: ((pg_get_functiondef(oid) IS NOT NULL) AND (prokind = 'f'::"char"))

You might think you're forcing the prokind = 'f' to be evaluated
before the "def is not null", but the query planner has optimisations
built-in and is able to pull up the subquery into the top-level query.
The planner also thinks evaluating the qual prokind = 'f' qual last is
more efficient.

This isn't a bug... or at least if we were to "fix it", many more
people would complain as we'd have to do something quite heavy-handed
like stop pulling up simple subqueries. Even something less
heavy-handed like evaluating quals from deeper (original) levels first
could cause quite significant performance regressions.

The best solution for you is likely just to add an OFFSET 0 to the
subquery. That'll prevent the query planner from pulling up the
subquery into the top-level query.

David