pg_restore to a newer PostgreSQL: column d.adsrc does not exist and other errors-CodePudding

As in title, on running pg_restore to a newer version of postgresql (13 ), there are many errors when it attempts to create views and functions, as also mentioned here.

The full command is

CREATE VIEW aco_columns AS
    SELECT c.relname AS tablename, a.attnum AS ordinal, a.attname AS columnname, CASE WHEN (t.typname = 'int4'::name) THEN (CASE WHEN ((SELECT d.adsrc FROM pg_attrdef d WHERE ((d.adrelid = c.oid) AND (d.adnum = a.attnum))) ~~ 'nextval%'::text) THEN 'serial'::text ELSE 'int'::text END)::name WHEN (t.typname = 'int2'::name) THEN 'smallint'::name WHEN (t.typname = 'int8'::name) THEN 'bigint'::name WHEN (t.typname = 'float4'::name) THEN 'real'::name WHEN (t.typname = 'float8'::name) THEN 'float'::name WHEN (t.typname = 'varchar'::name) THEN (((('varchar'::text || '('::text) || (a.atttypmod - 4)) || ')'::text))::name ELSE t.typname END AS basetype, d.description, a.attnotnull AS required, t.typname AS sqltype, (SELECT CASE WHEN (d.adsrc ~~ 'nextval%'::text) THEN ''::text ELSE d.adsrc END AS adsrc FROM pg_attrdef d WHERE ((d.adrelid = c.oid) AND (d.adnum = a.attnum))) AS default_value FROM (((pg_class c JOIN pg_attribute a ON ((c.oid = a.attrelid))) JOIN pg_type t ON ((a.atttypid = t.oid))) LEFT JOIN pg_description d ON (((c.oid = d.objoid) AND (a.attnum = d.objsubid)))) WHERE (a.attnum > 0) ORDER BY c.relname, a.attnum;

How can I fix this command so that it will work on my database? Also bringing up errors are t.consrc and p.proisagg, and the variable 'grouping' in CREATE FUNCTION aco_display_tab_divn_wrap(location text, grouping integer) RETURNS void. t.consrc error pops up for below command:

CREATE VIEW aco_constraints AS
    SELECT t.conname AS name, c.relname AS tablename, CASE WHEN (lower((t.contype)::text) = 'c'::text) THEN CASE WHEN ("substring"(t.consrc, 'aco_check_enum'::text) IS NOT NULL) THEN 'ENUM'::text ELSE 'CHECK'::text END WHEN (lower((t.contype)::text) = 'p'::text) THEN 'PKEY'::text WHEN (lower((t.contype)::text) = 'f'::text) THEN 'FOREIGN_KEY'::text WHEN (lower((t.contype)::text) = 'u'::text) THEN 'UNIQUE'::text ELSE lower((t.contype)::text) END AS contype, CASE WHEN (t.conname ~~ '%_parent'::text) THEN 'parent'::text WHEN (t.conname ~~ '%_ref'::text) THEN 'ref'::text WHEN (t.conname ~~ '%_one'::text) THEN 'one'::text ELSE 'unknown'::text END AS relation_type, CASE WHEN (t.confupdtype = 'c'::"char") THEN 'CASCADE'::text WHEN (t.confupdtype = 'r'::"char") THEN 'RESTRICT'::text ELSE 'IGNORE'::text END AS on_update, CASE WHEN (t.confdeltype = 'c'::"char") THEN 'CASCADE'::text WHEN (t.confdeltype = 'r'::"char") THEN 'RESTRICT'::text ELSE 'IGNORE'::text END AS on_delete, t.consrc AS column_expression, aco_get_columns_by_ordinal_array((c.relname)::text, (t.conkey)::integer[]) AS key_columns, (SELECT f.relname FROM pg_class f WHERE (f.oid = t.confrelid)) AS fkey_table, aco_get_columns_by_ordinal_array(((SELECT ff.relname FROM pg_class ff WHERE (ff.oid = t.confrelid)))::text, (t.confkey)::integer[]) AS fkey_columns, (SELECT d.description FROM pg_description d WHERE (d.objoid = t.oid)) AS description FROM pg_constraint t, pg_class c WHERE (t.conrelid = c.oid);

CodePudding user response：

The system catalog columns pg_attrdef.adsrc and pg_constraint.consrc where removed in Postgres version 12 per Release Notes.:

Remove obsolete pg_attrdef.adsrc column (Peter Eisentraut)

This column has been deprecated for a long time, because it did not update in response to other catalog changes (such as column renamings). The recommended way to get a text version of a default-value expression from pg_attrdef is pg_get_expr(adbin, adrelid).

Remove obsolete pg_constraint.consrc column (Peter Eisentraut)

This column has been deprecated for a long time, because it did not update in response to other catalog changes (such as column renamings). The recommended way to get a text version of a check constraint's expression from pg_constraint is pg_get_expr(conbin, conrelid). pg_get_constraintdef() is also a useful alternative.

If you are going to use system catalogs directly you need to follow the release notes for changes.