Problem with pg_dump and functions

============================================================================
                        POSTGRESQL BUG REPORT TEMPLATE
============================================================================


Your name               :  Mark Dalphin
Your email address      :  mdalphin@amgen.com


System Configuration
---------------------
  Architecture (example: Intel Pentium)         : SGI Octane

  Operating System (example: Linux 2.0.26 ELF)  : Irix 6.5

  PostgreSQL version (example: PostgreSQL-6.5.1): PostgreSQL-6.5.1 (patched)

  Compiler used (example:  gcc 2.8.0)           : Native cc


Please enter a FULL description of your problem:
------------------------------------------------

pg_dump of a database outputs CREATE TABLE commands,
followed by CREATE FUNCTION commands.

As a "DEFAULT" condition in some of my tables, I call a
function.  When I restore from a pg_dump, I find that
many CREATE TABLE commands fail as the DEFAULT function
does not exist.

Please describe a way to repeat the problem.   Please try to provide a
concise reproducible example, if at all possible:
----------------------------------------------------------------------

First create a database with a function:

CREATE FUNCTION "gettimestamp" ( ) RETURNS timestamp AS '
DECLARE
        cur_time timestamp;
BEGIN
        cur_time = ''now'';
        RETURN cur_time;
END;
' LANGUAGE 'plpgsql';

CREATE TABLE test (
key int PRIMARY KEY,
theTime timestamp DEFAULT getTimeStamp()
);

Exit the database and dump it:
pg_dump postgres > postgres.dump

Dump looks like this:
\connect - postgres
CREATE TABLE "test" (
        "key" int4 NOT NULL,
        "thetime" timestamp DEFAULT gettimestamp ( ));
CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS
'/usr/compbio/pgsql/lib/plpgsql.so' LANGUAGE 'C';
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler"
LANCOMPILER 'PL/pgSQL';
CREATE FUNCTION "gettimestamp" ( ) RETURNS timestamp AS '
DECLARE
        cur_time timestamp;
BEGIN
        cur_time = ''now'';
        RETURN cur_time;
END;
' LANGUAGE 'plpgsql';
COPY "test" FROM stdin;
\.
CREATE UNIQUE INDEX "test_pkey" on "test" using btree ( "key" "int4_ops" );

Recreate the database and restore the dumped data:
destroydb postgres
createdb postgres
psql -e < postgres.dump

Errors look like this:
\connect - postgres
connecting as new user: postgres
CREATE TABLE "test" (
        "key" int4 NOT NULL,
        "thetime" timestamp DEFAULT gettimestamp ( ));
QUERY: CREATE TABLE "test" (
        "key" int4 NOT NULL,
        "thetime" timestamp DEFAULT gettimestamp ( ));
ERROR:  No such function 'gettimestamp' with the specified attributes
CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS
'/usr/compbio/pgsql/lib/plpgsql.so' LANGUAGE 'C';
QUERY: CREATE FUNCTION "plpgsql_call_handler" ( ) RETURNS opaque AS
'/usr/compbio/pgsql/lib/plpgsql.so' LANGUAGE 'C';
ERROR:  ProcedureCreate: procedure plpgsql_call_handler already exists with same
arguments
CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER "plpgsql_call_handler"
LANCOMPILER 'PL/pgSQL';
QUERY: CREATE TRUSTED PROCEDURAL LANGUAGE 'plpgsql' HANDLER
"plpgsql_call_handler" LANCOMPILER 'PL/pgSQL';
ERROR:  Language plpgsql already exists
CREATE FUNCTION "gettimestamp" ( ) RETURNS timestamp AS '
DECLARE
        cur_time timestamp;
BEGIN
        cur_time = ''now'';
        RETURN cur_time;
END;
' LANGUAGE 'plpgsql';
QUERY: CREATE FUNCTION "gettimestamp" ( ) RETURNS timestamp AS '
DECLARE
        cur_time timestamp;
BEGIN
        cur_time = ''now'';
        RETURN cur_time;
END;
' LANGUAGE 'plpgsql';
CREATE
COPY "test" FROM stdin;
QUERY: COPY "test" FROM stdin;
ERROR:  COPY command failed.  Class test does not exist.
\.
 \?           -- help
 \a           -- toggle field-alignment (currently on)
 \C [<captn>] -- set html3 caption (currently '')
 \connect <dbname|-> <user> -- connect to new database (currently 'postgres')
 \copy table {from | to} <fname>
 \d [<table>] -- list tables and indices, columns in <table>, or * for all
 \da          -- list aggregates
 \dd [<object>]- list comment for table, field, type, function, or operator.
 \df          -- list functions
 \di          -- list only indices
 \do          -- list operators
 \ds          -- list only sequences
 \dS          -- list system tables and indexes
 \dt          -- list only tables
 \dT          -- list types
 \e [<fname>] -- edit the current query buffer or <fname>
 \E [<fname>] -- edit the current query buffer or <fname>, and execute
 \f [<sep>]   -- change field separater (currently '|')
 \g [<fname>] [|<cmd>] -- send query to backend [and results in <fname> or pipe]

 \h [<cmd>]   -- help on syntax of sql commands, * for all commands
 \H           -- toggle html3 output (currently off)
 \i <fname>   -- read and execute queries from filename
 \l           -- list all databases
 \m           -- toggle monitor-like table display (currently off)
 \o [<fname>] [|<cmd>] -- send all query results to stdout, <fname>, or pipe
 \p           -- print the current query buffer
 \q           -- quit
 \r           -- reset(clear) the query buffer
 \s [<fname>] -- print history or save it in <fname>
 \t           -- toggle table headings and row count (currently on)
 \T [<html>]  -- set html3.0 <table ...> options (currently '')
 \x           -- toggle expanded output (currently off)
 \w <fname>   -- output current buffer to a file
 \z           -- list current grant/revoke permissions
 \! [<cmd>]   -- shell escape or command
CREATE UNIQUE INDEX "test_pkey" on "test" using btree ( "key" "int4_ops" );
QUERY: CREATE UNIQUE INDEX "test_pkey" on "test" using btree ( "key" "int4_ops"
);
ERROR:  DefineIndex: test relation not found
EOF

If you know how this problem might be fixed, list the solution below:
---------------------------------------------------------------------
I suspect that re-ordering of the pg_dump output would do it, but I am
not certain about other ramifications of this.  I used an editor to re-order
a larger dump which included some ref-int functions (again in pl/pgsql)
and they also worked.  So, "forward references" to functions in DEFAULT
clauses of TABLE defs don't work, however, "forward references" to
TABLE rows in function definitions (at least pl/pgsql function defs)
do work.

Would there be problems with other kinds of functions if pg_dump output
functions
first? I don't think so.

Cheers,
Mark

PS I don't read this mail-list, so please CC comments and questions to me.

--
Mark Dalphin                          email: mdalphin@amgen.com
Mail Stop: 29-2-A                     phone: +1-805-447-4951 (work)
One Amgen Center Drive                       +1-805-375-0680 (home)
Thousand Oaks, CA 91320                 fax: +1-805-499-9955 (work)