Re: [GENERAL] Replacement for tgisconstraint? (Upgrade from 8.4 to 9.0.5)
Just to follow up on this, I went with istgconstraint which seems to work
OK.
I didn't see any way to edit the generated restore files and then continue
the process. It would be nice if there was option to start (or stop) at
the time the files are written.
Although it ended up being quite simple, it took me a while to figure out
how to restore the old databases. I couldn't tell if editing the files and
feeding them into psql would be a bad idea, since they were geared for
binary copy and has frozenxids and such.
So now I know it's just starting up the old 8.4 (included in
postgresql-upgrade), dumping the database, editing accordingly, shutting
down the old 8.4, starting the new and restore. That info _can_ be found
elsewhere, but it would be really nice if this were documented somewhere
where half-frantic people are likely to find them. Like in the
postgresql-upgrade package, perhaps under the heading if you carelessly
screw up your system.
I ended up doing roughly this:
/usr/lib64/pgsql/postgresql-8.4/bin/pg_ctl -D /var/lib/pgsql.old start
'pg_dumpall old_dump.sql
(edit old_dump.sql)
/usr/lib64/pgsql/postgresql-8.4/bin/pg_ctl -D /var/lib/pgsql.old stop
service postgresql start
psql -f old_dump.sql postgres(?)
Hope this didn't belabor the obvious too badly!
Cheers,
Ken
On Sat, Nov 19, 2011 at 8:01 AM, Ken Tanzer ken.tan...@gmail.com wrote:
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 t...@sss.pgh.pa.us wrote:
Ken Tanzer ken.tan...@gmail.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.gita=commitdiffh=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
[GENERAL] Replacement for tgisconstraint? (Upgrade from 8.4 to 9.0.5)
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
Re: [GENERAL] Replacement for tgisconstraint? (Upgrade from 8.4 to 9.0.5)
Ken Tanzer ken.tan...@gmail.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.gita=commitdiffh=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 -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] 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 t...@sss.pgh.pa.us wrote:
Ken Tanzer ken.tan...@gmail.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.gita=commitdiffh=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
