pg_dump function dumped after required for table definition - Mailing list pgsql-bugs

platform: PostgreSQL 7.3.4, RedHat 7.3, linux 2.4.18

I've recently tried to restore my database and get this:

pg_restore: [archiver (db)] could not execute query: ERROR: Function pftype_id_lookup(text) does not exist
        Unable to identify a function that satisfies the given argument types
        You may need to add explicit typecasts


In the pg_dump (plain-text format) output:
line 570:

        --
        -- TOC entry 81 (OID 28990)
        -- Name: paprospect2; Type: TABLE; Schema: unison; Owner: unison
        --

        CREATE TABLE paprospect2 (
            pftype_id integer DEFAULT pftype_id_lookup('prospect2'::text),
            "start" integer NOT NULL,


Then, line 673:

        --
        -- TOC entry 750 (OID 29001)
        -- Name: pftype_id_lookup (text); Type: FUNCTION; Schema: unison; Owner: unison
        --

        CREATE FUNCTION pftype_id_lookup (text) RETURNS integer
            AS 'select pftype_id from pftype where upper(name) = upper($1)'
            LANGUAGE sql STABLE STRICT;


I get the same behavior if I pg_dump with -Fc. I've seen scant reports
of similar behavior elsewhere, but all long ago. Is this a known
problem?

I assume the work-around is to load functions used to define tables
first, then reload. I had previously noticed that pg_restore permits
reordering the archive, but there's no mention of why one would want to
do this or that it's required in some cases.

I have a hunch about why this happens and is rare. The pftype_id_lookup
function was created AFTER some of the tables using 'alter table set
default ...'. Thus, the function's OID is greater than the OID of the
tables which use them. This is probably not typical. While dumping in
OID order (which pg_dump apparently does) will usually mean that
dependencies are dumped first, 'alter table...set default' potentially
breaks such an assumption.

Thanks,
Reece
--
Reece Hart, Ph.D.                       rkh@gene.com, http://www.gene.com/
Genentech, Inc.                         650/225-6133 (voice), -5389 (fax)
Bioinformatics and Protein Engineering
1 DNA Way, MS-93                        http://www.in-machina.com/~reece/
South San Francisco, CA  94080-4990     reece@in-machina.com, GPG: 0x25EC91A0