Обсуждение: Issue with pg_get_functiondef
Dear PostgreSQL developers,
I'm having trouble understanding the following, I apologize in advance if it is not a bug.
The following query works and lists the functions name and definitions as set in my database:
select * from (
select proname, prokind, pg_get_functiondef(oid) as def
from pg_proc
where pg_proc.prokind = 'f'
) def
select proname, prokind, pg_get_functiondef(oid) as def
from pg_proc
where pg_proc.prokind = 'f'
) def
however, if I add the filter where def is not null, it returns an error:
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
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
(with VERBOSITY set to verbose I get:
ERREUR: 42809: « array_agg » est une fonction d'agrégat
EMPLACEMENT : pg_get_functiondef, ruleutils.c : 2926)
EMPLACEMENT : pg_get_functiondef, ruleutils.c : 2926)
I understand that somehow the query above is calling pg_get_functiondef on array_agg where I believe it shouldn't since array_agg has prokind set to a.
Filtering for pg_proc.prokind <> 'a' makes the query works again:
select * from (
select proname, prokind, pg_get_functiondef(oid) as def
from pg_proc
where pg_proc.prokind <> 'a'
) def
where def is not null;
select proname, prokind, pg_get_functiondef(oid) as def
from pg_proc
where pg_proc.prokind <> 'a'
) def
where def is not null;
I get an error if I write:
select * from (
select proname, prokind, pg_get_functiondef(oid) as def
from pg_proc
where pg_proc.prokind in ('f')
) def
where def is not null;
ERROR: "array_agg" is an aggregate function
select proname, prokind, pg_get_functiondef(oid) as def
from pg_proc
where pg_proc.prokind in ('f')
) def
where def is not null;
ERROR: "array_agg" is an aggregate function
but it works (and lists both functions and procedures) if I do:
select * from (
select proname, prokind, pg_get_functiondef(oid) as def
from pg_proc
where pg_proc.prokind in ('f', 'p')
) def
where def is not null;
select proname, prokind, pg_get_functiondef(oid) as def
from pg_proc
where pg_proc.prokind in ('f', 'p')
) def
where def is not null;
I was able to reproduce these steps on a clean, up to date, Debian 12 install, without any data or user defined function.
SELECT version();
version
-------------------------------------------------------------------------------------------------------------------
PostgreSQL 15.5 (Debian 15.5-0+deb12u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
version
-------------------------------------------------------------------------------------------------------------------
PostgreSQL 15.5 (Debian 15.5-0+deb12u1) on x86_64-pc-linux-gnu, compiled by gcc (Debian 12.2.0-14) 12.2.0, 64-bit
Thanks in advance,
Best regards
--
Edouard Tollet
Data scientist | Stoïk
On Wed, 13 Dec 2023 at 00:26, Edouard Tollet <edouard.tollet@stoik.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
On Tue, 2023-12-12 at 10:33 +0100, Edouard Tollet wrote:
> I'm having trouble understanding the following, I apologize in advance if it is not a bug.
> The following query works and lists the functions name and definitions as set in my database:
>
> select * from (
> select proname, prokind, pg_get_functiondef(oid) as def
> from pg_proc
> where pg_proc.prokind = 'f'
> ) def
>
> however, if I add the filter where def is not null, it returns an error:
> 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
PostgreSQL evaluates the function before the WHERE clause.
Try this:
WITH cte AS MATERIALIZED (
SELECT proname, oid
FROM pg_proc
WHERE prokind = 'f'
)
SELECT proname, pg_get_functiondef(oid) AS def
FROM cte;
Yours,
Laurenz Albe