Thread: BUG #6299: pg_dump, pg_dumpall - Problem with the order of backup functions
The following bug has been logged online:
Bug reference: 6299
Logged by: lindebg
Email address: lindebg@gmail.com
PostgreSQL version: 9.0, 9.1
Operating system: Linux, Windows
Description: pg_dump, pg_dumpall - Problem with the order of backup
functions
Details:
1. create database test and functions fn2, fn1:
$ psql -c "create database test"
$ psql test
test=# create function fn2()
returns int as
$$ select 1 $$
language sql immutable;
create function fn1(param1 int default fn2())
returns int as
$$ select $1 $$
language sql immutable;
\q
2. backup:
pg_dump -F c > test.backup
3. clear database:
psql -c "drop database test"
psql -c "create database test"
4. restore database:
pg_restore -d test test.backup
Errors:
pg_restore: [archiver (db)] Error while PROCESSING TOC:
pg_restore: [archiver (db)] Error from TOC entry 221; 1255 21043 FUNCTION
fn1(integer) postgres
pg_restore: [archiver (db)] could not execute query: ERROR: function fn2()
does not exist
LINE 1: CREATE FUNCTION fn1(param1 integer DEFAULT fn2()) RETURNS in...
^
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 fn2()) 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: 2
"lindebg" <lindebg@gmail.com> writes: > Description: pg_dump, pg_dumpall - Problem with the order of backup > functions Yeah, this was reported last month --- CREATE FUNCTION neglects to mark the function with the default expression as dependent on the other one. It'll be fixed in the next set of update releases, or if you're in a big hurry you can get the patch here: http://archives.postgresql.org/pgsql-committers/2011-10/msg00007.php regards, tom lane
Re: BUG #6299: pg_dump, pg_dumpall - Problem with the order of backup functions
From
lindebg@gmail.com
Date:
Thanks, in this case works :-) However, this does not solve all cases.
Unlikely, but possible to create, cyclic case can not be restored:
$ 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 or replace function fn1(param1 int = fn2(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 fn2(3) | normal
public | fn2 | integer | param1 integer DEFAULT fn1(8) | normal
(2 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 16398)
pg_dump: [sorter] FUNCTION fn1 (ID 174 OID 16397)
$ 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 16398 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 16397 FUNCTION
fn1(integer) postgres
pg_restore: [archiver (db)] could not execute query: ERROR: function
fn2(integer) does not exist
LINE 1: CREATE FUNCTION fn1(param1 integer DEFAULT fn2(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 fn2(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: 4
$ psql test
\df
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+------+------------------+---------------------+------
(0 rows)
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)
lindebg <lindebg@gmail.com> writes:
> 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:
Color me skeptical. Under what conceivable use-case could you have
functions that were mutually dependent in that way? And actually did
something useful (not recurse till stack overflow) when called?
regards, tom lane
On 11/19/2011 04:34 AM, Tom Lane wrote: > Color me skeptical. Under what conceivable use-case could you have > functions that were mutually dependent in that way? And actually did > something useful (not recurse till stack overflow) when called? > > regards, tom lane > Does this mean that this situation will not be handled by pg_dump / pg_restore? These functions do not cause a stack overflow: select fn1(); 3 select fn2(); 8 select fn3(); 5 select fn3(10); 10 It is not about to find now a practical example of use. There is always the possibility of finding another solution, without cyclical. But since PostgreSQL allows you to create such cyclically dependent functions, it should handle it in any case, also the pg_dump / pg_restore, or block the ability to create cycle-dependent functions. It's just my opinion. PostgreSQL is very good. I wish it was the most perfect. PS: Sorry if it hurt the language. I'm using Google Translate.
lindebg <lindebg@gmail.com> writes:
> On 11/19/2011 04:34 AM, Tom Lane wrote:
>> Color me skeptical. Under what conceivable use-case could you have
>> functions that were mutually dependent in that way? And actually did
>> something useful (not recurse till stack overflow) when called?
> Does this mean that this situation will not be handled by pg_dump /
> pg_restore?
If you can convince me that there is some actual real-world use case for
a situation like this, I might think about complicating pg_dump to the
point where it would handle it. If it's only an artificial corner case,
I don't think that the added complexity (and ensuing long-term
maintenance costs, plus risk of new bugs) is worth it.
regards, tom lane