Re: BUG #6299: pg_dump, pg_dumpall - Problem with the order of backup functions

On 11/19/2011 12:19 AM, lindebg@gmail.com wrote:
 > Thanks, in this case works :-) However, this does not solve all cases.
 > Unlikely, but possible to create, cyclic case can not be restored:
 >

Another example with more functions:


$ psql

create database test;

\connect test

create function fn1(param1 int)
returns int as
$$ select $1 $$
language sql immutable;

create function fn2(param1 int default fn1(8))
returns int as
$$ select $1 $$
language sql immutable;

create function fn3(param1 int default fn2(5))
returns int as
$$ select $1 $$
language sql immutable;

create or replace function fn1(param1 int default fn3(3))
returns int as
$$ select $1 $$
language sql immutable;


\df
                              List of functions
  Schema | Name | Result data type |      Argument data types      |  Type
--------+------+------------------+-------------------------------+--------
  public | fn1  | integer          | param1 integer DEFAULT fn3(3) | normal
  public | fn2  | integer          | param1 integer DEFAULT fn1(8) | normal
  public | fn3  | integer          | param1 integer DEFAULT fn2(5) | normal
(3 rows)

\q


$ pg_dump -F c test > test.backup
pg_dump: [sorter] WARNING: could not resolve dependency loop among these
items:
pg_dump: [sorter]   FUNCTION fn2  (ID 173 OID 16403)
pg_dump: [sorter]   FUNCTION fn1  (ID 175 OID 16402)
pg_dump: [sorter]   FUNCTION fn3  (ID 174 OID 16404)


$ psql -c "drop database test"
$ psql -c "create database test"

$ pg_restore -d test test.backup
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 173; 1255 16403
FUNCTION fn2(integer) postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  function
fn1(integer) does not exist
LINE 1: CREATE FUNCTION fn2(param1 integer DEFAULT fn1(8)) RETURNS i...
                                                    ^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.
     Command was: CREATE FUNCTION fn2(param1 integer DEFAULT fn1(8))
RETURNS integer
     LANGUAGE sql IMMUTABLE
     AS $_$ select $1 $_$;



pg_restore: [archiver (db)] could not execute query: ERROR:  function
public.fn2(integer) does not exist
     Command was: ALTER FUNCTION public.fn2(param1 integer) OWNER TO
postgres;


pg_restore: [archiver (db)] Error from TOC entry 174; 1255 16404
FUNCTION fn3(integer) postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  function
fn2(integer) does not exist
LINE 1: CREATE FUNCTION fn3(param1 integer DEFAULT fn2(5)) RETURNS i...
                                                    ^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.
     Command was: CREATE FUNCTION fn3(param1 integer DEFAULT fn2(5))
RETURNS integer
     LANGUAGE sql IMMUTABLE
     AS $_$ select $1 $_$;



pg_restore: [archiver (db)] could not execute query: ERROR:  function
public.fn3(integer) does not exist
     Command was: ALTER FUNCTION public.fn3(param1 integer) OWNER TO
postgres;


pg_restore: [archiver (db)] Error from TOC entry 175; 1255 16402
FUNCTION fn1(integer) postgres
pg_restore: [archiver (db)] could not execute query: ERROR:  function
fn3(integer) does not exist
LINE 1: CREATE FUNCTION fn1(param1 integer DEFAULT fn3(3)) RETURNS i...
                                                    ^
HINT:  No function matches the given name and argument types. You might
need to add explicit type casts.
     Command was: CREATE FUNCTION fn1(param1 integer DEFAULT fn3(3))
RETURNS integer
     LANGUAGE sql IMMUTABLE
     AS $_$ select $1 $_$;



pg_restore: [archiver (db)] could not execute query: ERROR:  function
public.fn1(integer) does not exist
     Command was: ALTER FUNCTION public.fn1(param1 integer) OWNER TO
postgres;


WARNING: errors ignored on restore: 6



$ psql test

\df
                        List of functions
  Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)