Problem with pg_dump and functions

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

Your name : Mark Dalphin
Your email address : mdalphin(at)amgen(dot)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(at)amgen(dot)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)