Re: BUG #19034: Recursive function with sql_body can replace an existing function but can not be created on it's own

"David G. Johnston" <david.g.johnston@gmail.com> writes:
> Right, because the oid is preserved when the create or replace finishes
> pg_depend must have an entry where objid = refobjid where the oid value is
> that of the originally created function that is now just being altered.
> That situation seems detectable and prohibit-able.

This would not fix the problem for the case of two or more mutually
recursive functions (that is, a() calls b() calls a()).  So I don't
find it to be an attractive answer.

Getting pg_dump to deal with the problem seems more complicated
than I'd first hoped, though:

1. We will need to create two TOC objects, one to represent the
"dummy" CREATE FUNCTION and the other to represent the "CREATE OR
REPLACE" step.  There's really no shortcut that would avoid that,
since the two objects need to be sorted separately by pg_dump's
topological dependency sort.  In otherwise-comparable situations
such as views, there are already two database objects to work with
(e.g., the view's relation and its ON SELECT rule).  Here there are
not, meaning we have only one identifying OID, so it's hard to see
how we'd tell the two TOC objects apart.  And we'd surely not wish
to burden pg_dump with a doppelganger TOC object for every single
function in the database, either, so how do we tell whether two
are needed?  Maybe we can postpone that until we discover the
circular dependency, but I think creating a new TOC object at
that stage would be very messy.

2. Once we do split a function into two TOC objects, we'd have to
figure out which of its dependencies to assign to each object.
The answer is not "they all belong to the second step", because
any user-defined datatypes used in the function's argument list or
result need to still be dependencies of the first step.  (We might
be able to avoid depending on other things like the PL language
and transform types, though: the dummy definition could be made
to always be SQL-language rather than reality.)  Probably pg_dump
can be taught to split the dependencies apart in a valid way, but
it seems a bit messy and outside pg_dump's sphere of knowledge.

(I'm wondering a bit whether this doesn't suggest that we chose
the wrong representation for pg_depend entries for new-style
SQL functions.  Should the backend itself distinguish dependencies
of the function body from those of the function declaration?
Perhaps inventing pg_proc.prosqlbody was the wrong thing and
we should store new-style function bodies in pg_rewrite,
allowing the pg_rewrite OID to be the second OID needed?
It might be too late to change that without breaking a lot
of stuff, though.)

            regards, tom lane