I get a weird message when opening a PostgreSQL database

Databases
1

When I open a database in pgAdmin, I get the following error message. Does anyone know how to fix this? I just went from V94. to V11

Thanks,
TIm

12:40:20 AM: Error: ERROR: column “proisagg” does not exist
LINE 9: WHERE proisagg = FALSE AND pronamespace = 2200::oid
^
HINT: Perhaps you meant to reference the column “pr.prolang”.

2

Don’t know exactly, but maybe mismatched versions?

3

Did you migrate the data folder? PostgreSQL only directly supports updates within the same major version (e.g. 9.x to 9.x) without migrating the data. See here for more info and how to handle switching major versions.

4

You need to update your queries.

proisagg don’t exist anymore, it was replaced with prokind in the system catalog

See: pg_proc:

image
image722×53 4.37 KB

Queries using it needs to be adapted.

That can be expressed also as

WHERE  NOT proisagg    -- Not aggregate function

is now

WHERE pg_proc.prokind <> 'a'
5

Is pgAdmin updated?

6

I just went from V94. to V11

Btw, support (end of life) of PG v11 (which was released in 2018) ended 3 weeks ago. I am not even sure modern versions of PGAdmin work with this ancient version. The current version of PG is 16.1.

7

Hello all and thank you for your responses!

I was using 9.4 but wanted to upgrade the use of pgAdmin and wanted the server version on my notebook to match the version on the Raspberry Pi’s we use.

I looked at instructions and they said to
Backup existing database

Install V11.
Create a new DB
Restore from the backup made on 9.4.

What I really need to know is how to correct the issue??

I never created queries for these so have no idea what they are how to fix them.

I did find on line that using the following command

SELECT 
FROM pg_catalog.pg_proc p
WHERE p.prokind = 'f'

would fix it. It does appear to work now without error.

8

While on the subject of versions, if my development windows pc has a higher version of PostgreSQL like 16.x would that interfere with the operation of the same database on V11 that is in my Raspberry Pi?

Thanks again everyone!
Tim

9

Again.

The way you reported seemed that you are using an old pgAdmin doing improper deprecated queries. Upgrading it should solve the internal queries pgAdmin is issuing.

10

Hi Rick,

pgAdmin is at version 8.

Tim

11

So, a pgAdmin 8.x, connecting to a V11 migrated from 9.4 PostgreSQL, fires an error:
column “proisagg” does not exist ?

If yes, this is new to me.

12

By migrate I did the following:

  1. Backup db in V9.4
  2. Turn off V9.4
  3. Install V11.x
  4. Create new DB in V11.x using pgAdmin V8
  5. Restore the backup file to the new Db in V11 using pgAdmin V8
13

This appeared to fix it, BUT not sure what that command does, other than select a bunch of records - I do not see what/where a command to make a change is…
Tim

14

If I’m understanding correctly, you dumped the DB in v9.4
and in some places of the SQL text there are parts like

WHERE proisagg = FALSE AND pronamespace = ...

in the SQL.

The proisagg = FALSE says “just non-aggregate functions” in v9
but it does not exist in v11 anymore.

So when restoring you get errors… that’s what I suppose.

The equivalent in v11 to

proisagg = FALSE

is

prokind <> 'a'

I guess you may be starting in to diving in complex things that may be better to discuss in postgresql forums so experts could guide you better. Maybe they even can get tools for it for you.

15

Hi Rick,

The thing is, I did not create that. It was created automatically by Postgre in V9.4

I’ll try restoring again, and pay closer attention to any errors that may arise.

Tim

16

I know, I said that.

I would try another way, using a Linux vm, data files, and using tools for upgrade. But it is too much and you should get support from those guys.

17

OK, will try for sure.
Thank you!

Tim

1 Like