PostgreSQL 11 error: column p.proisagg does not exist

Question

Using phpPgAdmin v5.6 and PostgreSQL v11.2 on CentOS v7, when I try to access the Functions tab within the public schema, I get the following error:

ERROR: column p.proisagg does not exist
LINE 18: WHERE NOT p.proisagg
 ^
HINT: Perhaps you meant to reference the column "p.prolang".
Dans l'instruction :
 SELECT
 p.oid AS prooid,
 p.proname,
 p.proretset,
 pg_catalog.format_type(p.prorettype, NULL) AS proresult,
 pg_catalog.oidvectortypes(p.proargtypes) AS proarguments,
 pl.lanname AS prolanguage,
 pg_catalog.obj_description(p.oid, 'pg_proc') AS procomment,
 p.proname || ' (' || pg_catalog.oidvectortypes(p.proargtypes) || ')' AS proproto,
 CASE WHEN p.proretset THEN 'setof ' ELSE '' END || pg_catalog.format_type(p.prorettype, NULL) AS proreturns,
 u.usename AS proowner
 FROM pg_catalog.pg_proc p
 INNER JOIN pg_catalog.pg_namespace n ON n.oid = p.pronamespace
 INNER JOIN pg_catalog.pg_language pl ON pl.oid = p.prolang
 LEFT JOIN pg_catalog.pg_user u ON u.usesysid = p.proowner
 WHERE NOT p.proisagg
 AND n.nspname = 'public'
 ORDER BY p.proname, proresult
Fatal error: Call to a member function recordCount() on integer in /mnt/webdata/websites/applications/pga/classes/Misc.php on line 1949

I get the same error using the \df meta-command in psql (version 10.1 as it turns out):

ts_d=> \df
ERROR: column p.proisagg does not exist
LIGNE 6 : WHEN p.proisagg THEN 'agg'
 ^
ASTUCE : Perhaps you meant to reference the column "p.prolang".
ts_d=>

The error in those queries seems to be due to a reference to proisagg, a column originally from the pg_proc table in the pg_catalog schema, but which no longer exists in PostgreSQL 11. See: https://www.postgresql.org/docs/11/release-11.html

Any way to resolve this?

• mustaccio: I guess you need to open a defect against phpPgAdmin; their claim of support for Postgres 11 seems to be false at least in regard to pg_catalog.pg_proc.
• Sébastien Clément: Right, thanks @mustaccio, I posted the bug there: sourceforge.net/p/phppgadmin/bugs/469
• Sébastien Clément: Seems that this is a PostgreSQL rather than a phpPgAdmin problem. I updated my post.
• Erwin Brandstetter: @mustaccio: If you want backward-compatible, use the information schema. Tools like phpPgAdmin take the fast lane and access system catalogs directly - and should keep their queries up to date ...
• jjanes: @mustaccio Not all changes can be reasonably finagled to be backwards compatible. So the alternative would be to give up on making improvements. If you wish stability at the expense of progress, you can keep using the old version.

Answer 1

In Postgres 11 proisagg was replaced with prokind in the system catalog pg_proc:

prokind | char | f for a normal function, p for a procedure, a for an aggregate function, or w for a window function

The query needs to be adapted. Like:

SELECT ...
FROM pg_catalog.pg_proc p
 ...
WHERE p.prokind = 'f' -- to only get plain functions
...

Related:

• How to drop all of my functions in PostgreSQL?

• PostgreSQL: How to list all stored functions that access specific table

The error you observed from the \df meta-command in psql is most likely due to using an outdated version of psql. psql 11 and up are updated to deal with this change, of course.

• Sébastien Clément: Thanks @Erwin Brandstetter. I guess psql will be corrected to reflect this change?
• Erwin Brandstetter: @SébastienClément: psql is updated, of course. Your version of psql? It's displayed on startup. (Would be instrumental in the question to begin with).
• Sébastien Clément: you are absolutely right. I was using psql 10.1 to connect to the server. I will update it.

Answer 2

In the case of phpPgAdmin, you must modify the faulty queries within \classes\database\Postgres.php.

pg_proc.proisagg (PostgreSQL 10 and before) is a boolean set at TRUE when function is an aggregate function.

pg_proc.prokind (PostgreSQL 11) is a char taking the values 'f', 'p', 'a' or 'w' (see Erwin's answer above).

Thus, for every occurence of WHERE NOT p.proisagg (function is not an aggregate function), replace with WHERE p.prokind <> 'a'.

Answer 3

I had the same issue and ran these 2 commands to fix it

sed -i "s/NOT pp.proisagg/pp.prokind='f'/g" /usr/share/phpPgAdmin/classes/database/Postgres.php
sed -i "s/NOT p.proisagg/p.prokind='f'/g" /usr/share/phpPgAdmin/classes/database/Postgres.php