Re: Replacement for tgisconstraint? (Upgrade from 8.4 to 9.0.5)

Not being the author of that view, I confess some ignorance of pg
internals, and just what the intended nuance was.

As a little more explanation, the view is meant to list all the tables that
have a trigger ending in _alert_notify, as created per this function:

CREATE OR REPLACE FUNCTION alert_notify_enable(varchar,varchar) RETURNS
boolean AS $$
if {[info exists 1]} {
set TABLE $1
} else {
elog ERROR "no table passed to alert_notify()"
return false
}
if {[info exists 2]} {
set CUSTOM_COLUMN $2
} else {
set CUSTOM_COLUMN ""
}
set cre_exec "CREATE TRIGGER ${TABLE}_alert_notify
AFTER INSERT OR UPDATE OR DELETE ON ${TABLE}
FOR EACH ROW EXECUTE PROCEDURE table_alert_notify(${CUSTOM_COLUMN})"
spi_exec $cre_exec
return true
$$ LANGUAGE pltcl;

(The second view, about table_logs, is conceptually similar).

Here's the slightly more readable source for the view:

CREATE OR REPLACE VIEW alert_notify_enabled_objects AS

SELECT REPLACE(cc.relname,'tbl_','') AS alert_object_code,
INITCAP(REPLACE(REPLACE(cc.relname,'tbl_',''),'_',' ')) AS
description
FROM pg_catalog.pg_trigger t
LEFT JOIN pg_catalog.pg_class cc ON ( t.tgrelid = cc.oid )
WHERE t.tgname ~ '_alert_notify$'
AND (NOT tgisconstraint OR NOT EXISTS
(SELECT 1 FROM pg_catalog.pg_depend d
JOIN pg_catalog.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' AND c.contype = 'f')
);

If that clarifies the intention, please let me know! Also, what about
question #2--is there an easy/built-in way to edit the
pg_upgrade_dump_db.sql and continue the postgresql-upgrade process?

Thanks!

Ken

On Sat, Nov 19, 2011 at 7:44 AM, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Ken Tanzer <ken(dot)tanzer(at)gmail(dot)com> writes:
> > 1) Can anyone suggest equivalent PG9 replacement for those statements,
> or
> > at least give me some hints?
>
> Per
> http://git.postgresql.org/gitweb/?p=postgresql.git&a=commitdiff&h=9a915e596
>
> I also replaced the tgisconstraint column
> with tgisinternal; the old meaning of tgisconstraint can now be had
> by
> testing for nonzero tgconstraint, while there is no other way to get
> the old meaning of nonzero tgconstraint, namely that the trigger was
> internally generated rather than being user-created.
>
> It's not real clear to me whether your views actually want tgconstraint
> = 0, which would be the exact translation, or NOT tgisinternal, which
> might be a closer approximation to their intention.
>
> regards, tom lane
>