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

On Fri, 5 Sept 2025 at 16:18, Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:

> Oh, if you are defining the problem as "pg_dump doesn't cope after
> I make this function in two steps" rather than "the server should
> allow this to be done in one step", that seems more defensible.
>
> One could expect that that'd let pg_dump also cope with cases
> involving mutual recursion between two or more such functions,
> which is something we'd certainly not try to make the server
> allow without help.
>

It seems I was a bit too concise with my bug report. Sorry about this.

What I left out is this:
I have stumbled about this by accident. I had just been refactoring a
bunch of old sql functions and thought they might go well with sql_body.
The server didn't complain and all tests were passed until we came to
"dump and restore". That would have been a nasty surprise in an emergency.

I don't think it is a good idea to allow the creation of such a function
one way but not the other. This leads to confusion and unexpected
outcomes. Either allow sql_body with recursion or don't. There is already
a check if the objects exist that a function want's to use. So I thought
it
must be easy to also check if a function calls itself. Changing how pg_dump
copes with this would at least lessen the impact of this situation.

Regards Katja