Search Postgresql Archives

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

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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.tanzer@xxxxxxxxx> 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 = "" 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@xxxxxxxxxxxxx> wrote:
Ken Tanzer <ken.tanzer@xxxxxxxxx> 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



[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Books]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]
  Powered by Linux