Thread: pg_dump function dumped after required for table definition

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

Reece Hart <reece@in-machina.com> writes:
> 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.

Yeah, this is a well-known problem in pg_dump.  We need to revise it to
pay attention to the dependency information that's now available in
pg_depend.  At the moment your only alternative is to manually fix the
reload order using pg_restore's features for controlling the reload
order ... which features would never have existed in the first place
if we'd had this problem nailed :-(

            regards, tom lane

* Tom Lane (tgl@sss.pgh.pa.us) wrote:
> Reece Hart <reece@in-machina.com> writes:
> > 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.
>=20
> Yeah, this is a well-known problem in pg_dump.  We need to revise it to
> pay attention to the dependency information that's now available in
> pg_depend.  At the moment your only alternative is to manually fix the
> reload order using pg_restore's features for controlling the reload
> order ... which features would never have existed in the first place
> if we'd had this problem nailed :-(

I'm guessing this isn't going to get fixed before 7.4 is released, eh?