The following bug has been logged on the website:
Bug reference: 12106
Logged by: OndÅej Bouda
Email address: bouda@edookit.com
PostgreSQL version: 9.3.5
Operating system: Windows 7
Description:
A function may reference itself via default values of some of its arguments.
It is rather tricky to create one, but it is possible:
-- first, define the function without argument defaults
CREATE FUNCTION self_loop(a INT) RETURNS INT
AS $$
SELECT 2*a;
$$ LANGUAGE SQL;
-- then, replace it with the argument defaults
CREATE OR REPLACE FUNCTION self_loop(a INT = self_loop(1)) RETURNS INT
AS $$
SELECT 2*a;
$$ LANGUAGE SQL;
Although the real usage is questionable, such a function is perfectly legal
both in terms of documented limitations and the actual implementation.
The problem is that pg_dump does not handle the circular dependency:
...
pg_dump: reading dependency data
pg_dump: [sorter] WARNING: could not resolve dependency loop among these
items:
pg_dump: [sorter] FUNCTION self_loop (ID 183 OID 41120)
...
Indeed, in the resulting dump, it emits a straight CREATE statement with
defaults right away - the same as the CREATE OR REPLACE above - which does
not succeed, obviously:
ERROR: function self_loop(integer) does not exist
LINE 1: CREATE FUNCTION self_loop(a integer DEFAULT self_loop(1)) RE...
^
HINT: No function matches the given name and argument types. You might need
to add explicit type casts.
Even worse, pg_dump returns 0 as the exit code, even though the dump is not
usable. It merely issues a warning on output.
Generally, the problem might occur with multiple functions, creating a
non-trivial circular dependency (e.g., foo(int = bar(1)) and bar(int =
foo(2))).
I suggest fixing pg_dump such that in case it finds a circular dependency,
it cuts it by ignoring some of the attributes in the cycle (argument
default, in this case) when emitting the CREATE statement, then emitting
ALTER/REPLACE statements which will fix the ignored attributes. I don't
know, however, whether it is even possible - currently, pg_depend does not
contain information about the attribute causing the dependency; it could be
extended (not sure about complexity, though).
I will analyze whether this is the only problem of this kind, or whether
there are more similar situations.
Tested with pg_dump 9.3.5 and 9.4rc1 (which is not in the selectbox in the
web form for reporting bugs, by the way).
