Thread
-
Problem with pg_dump and functions
Mark Dalphin <mdalphin@amgen.com> — 1999-09-29T19:09:34Z
============================================================================ 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)