Re: Stored procedure code no longer stored in v14 and v15, changed behaviour

On 2 Dec 2022, at 6:51, Tom Lane wrote:

> Dominique Devienne <ddevienne(at)gmail(dot)com> writes:
>> On Thu, Dec 1, 2022 at 8:09 PM Christophe Pettus <xof(at)thebuild(dot)com> wrote:
>>> Since this is a custom-built system, there is nothing keeping you from creating your own table in the database that stores the original text of the function.
>
>> That's not the point. If a DBA updates one of our triggers or proc or
>> whatever else,
>> the recorded info in a custom table won't be affected. We are diff'ing
>> the server-side
>> schema, against the expected in-memory model of the physical model.
>
>> Thus the dictionaries are the only source of truth we can trust for
>> the current state of the schema.
>> And beside minor syntactic differences, and some more troublesome
>> object-name rewrites, this is
>> exactly what we want. The system itself needs to preserve the original
>> DDL IMHO. --DD
>
> Do you really fail to see the contradictions in this? You want the
> database to preserve the original DDL, but you also want it to update
> in response to subsequent alterations. You can't have both those
> things.
>
At the risk of stating the (over) obvious…

NEW: the Pg system has a parsed/tokenised version of functions and such like, with the excellent feature that these will be kept synchronised with any name change of objects referenced
OLD: easily available text version of functions, etc. with comments and layout (but not easily updatable in the event referenced objects get renamed) — sysadmin usage

The contradiction is obvious but both needs can be justified…

NEXT(?): human readable version of function, etc definitions be generated from the parsed version, with the addition of tokens within this parsed version that allow programer’s comments to be reinstated. Leave the layout style conventions to the system and promise to keep this style consistent and maintained as part of the feature. Putting this generated (and updated as needed) text into pg_proc.prosrc would seem to be the least change needed to allow existing usage to move with the new Pg versions (and maybe help pgAdmin as well)

Regards

Gavan Schneider
——
Gavan Schneider, Sodwalls, NSW, Australia
Explanations exist; they have existed for all time; there is always a well-known solution to every human problem — neat, plausible, and wrong.
— H. L. Mencken, 1920