1

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?

  • 1
    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. – mustaccio May 23 at 14:35
  • Right, thanks @mustaccio, I posted the bug there: sourceforge.net/p/phppgadmin/bugs/469 – Sébastien Clément May 23 at 14:47
  • Seems that this is a PostgreSQL rather than a phpPgAdmin problem. I updated my post. – Sébastien Clément May 23 at 15:06
  • 2
    @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 ... – Erwin Brandstetter May 23 at 16:18
  • 1
    @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. – jjanes May 24 at 15:32
2

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:

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.

  • Thanks @Erwin Brandstetter. I guess psql will be corrected to reflect this change? – Sébastien Clément May 23 at 15:56
  • @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). – Erwin Brandstetter May 23 at 16:14
  • you are absolutely right. I was using psql 10.1 to connect to the server. I will update it. – Sébastien Clément May 23 at 16:16
0

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'.

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.