Replacement for tgisconstraint? (Upgrade from 8.4 to 9.0.5)

From: Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Replacement for tgisconstraint? (Upgrade from 8.4 to 9.0.5)
Date: 2011-11-19 10:58:17
Message-ID: CAD3a31V9259RNkY3uL0Evu_2S=3QbW8Bn_Y1mtji4W4x7afohg@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

I just upgraded from Fedora 14 to Fedora 15. (Which is Postgres 8.4.? to
9.0.5)

I tried starting Postgres, and then as directed, I installed the
postgresql-upgrade package. (My postgresql packages are all current, at
version 9.0.5-1.fc15.x86_64)

I then ran "service postgresql upgrade", which chugged away for a while,
and then failed, with this in the log:

Resetting WAL archives ok
Setting frozenxid counters in new cluster ok
Creating databases in the new cluster ok
Adding support functions to new cluster ok
Restoring database schema to new cluster
psql:/var/lib/pgsql
/pg_upgrade_dump_db.sql:4333: ERROR: column t.tgisconstraint does not
exist
LINE 2: ...RE ((t.tgname ~ '_alert_notify$'::text) AND ((NOT t.tgiscons...

So upon closer inspection I've got two statements in my databases that
reference tgisconstraint. My 2 questions are:

1) Can anyone suggest equivalent PG9 replacement for those statements, or
at least give me some hints?

CREATE VIEW alert_notify_enabled_objects AS
SELECT replace((cc.relname)::text, 'tbl_'::text, ''::text) AS
alert_object_code, initcap(replace(replace((cc.relname)::text,
'tbl_'::text, ''::text), '_'::text, ' '::text)) AS description FROM
(pg_trigger t LEFT JOIN pg_class cc ON ((t.tgrelid = cc.oid))) WHERE
((t.tgname ~ '_alert_notify$'::text) AND ((NOT t.tgisconstraint) OR (NOT
(EXISTS (SELECT 1 FROM (pg_depend d JOIN pg_constraint c ON (((d.refclassid
= c.tableoid) AND (d.refobjid = c.oid)))) WHERE ((((d.classid = t.tableoid)
AND (d.objid = t.oid)) AND (d.deptype = 'i'::"char")) AND (c.contype =
'f'::"char")))))));

CREATE VIEW table_log_enabled_tables AS
SELECT cc.relname AS "table" FROM (pg_trigger t LEFT JOIN pg_class cc
ON ((t.tgrelid = cc.oid))) WHERE ((t.tgname ~ '_log_chg$'::text) AND ((NOT
t.tgisconstraint) OR (NOT (EXISTS (SELECT 1 FROM (pg_depend d JOIN
pg_constraint c ON (((d.refclassid = c.tableoid) AND (d.refobjid =
c.oid)))) WHERE ((((d.classid = t.tableoid) AND (d.objid = t.oid)) AND
((d.deptype)::text = ('i'::character(1))::text)) AND ((c.contype)::text =
('f'::character(1))::text)))))));

and,

2) Assuming I get the offending statements fixed and edit the
pg_upgrade_dump_db.sql, is there a way to continue the postgresql-upgrade
process?

Thanks in advance!

Ken Tanzer

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Tomas Vondra 2011-11-19 12:29:33 Re: Postgres 9.0.4 replication issue: FATAL: requested WAL segment 0000000100000B110000000D has already been removed
Previous Message Raymond O'Donnell 2011-11-19 10:23:51 Re: How to install latest stable postgresql on Debian