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

On Monday, September 8, 2025, Laurenz Albe <laurenz(dot)albe(at)cybertec(dot)at> wrote:

> On Sun, 2025-09-07 at 15:50 -0700, David G. Johnston wrote:
> > While I agree making it must work would be nice given the presence of
> simple
> > alternatives to use when this functionality is required it's got to be
> easier
> > to detect and prohibit creation.
>
> Perhaps that is the way to go, although it would not make me happy.
> But that still leaves the problem of what to do with a database that
> already contains recursive SQL functions during pg_dump/pg_restore.
>

There are a number of ways for a user to setup a database that cannot be
restored, where our answer is to fix the source database. This is one of
those cases. Usually it’s because they do something we’ve documented as
prohibited - like mis-identifying a stable/volatile function as immutable
and use it in a check constraint. We just haven’t [yet…] explicitly
identified this as prohibited.

Test your restores before you need them for critical reasons. Dumps are
not good backups anyway. Upgrade will fail and they will need to fix the
source.

We have an oversight, but the error is obvious and immediate if the user
takes the necessary precautions. I’m not losing much sleep someone somehow
gets bit by this - especially since we are trying to at least make it
impossible to be bit by it in the future.

David J.