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

> On 2 Dec 2022, at 15:10, DAVID ROTH <adaptron(at)comcast(dot)net> wrote:
>
> Is there a way to reverse engineer the original code (or its equivalent) from what is saved in the database?

I’m dumping an idea here… Treading back quite a bit with information from later in this thread.

With the original procedure source code under version control and assuming the tokenization converts that source code consistently (since it’s done by a computer), you could store another level of diffs: From the source code you pushed, against the output of pg_get_functiondef.

Assuming that changes to the tokenised code, when converted back to text, only involve renames of database objects, the result of a reversely applied diff could very well be comparable to the original source code.

I suspect that would be sufficient for telling whether a developer is responsible for the changes, or that they were caused by renaming of database artefacts.

You would need to wrap the function creation calls into some automation to generate and store those diffs, comparing it back, etc, but that may be doable. I would also generate new diffs right after major version updates of the database (a before and after of the output of pg_get_functiondef, applied to the stored diff?).

I’m not so sure that would work for auditing, but that seems to have been tackled down-thread.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.