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

On Thu, Dec 01, 2022 at 04:38:57PM +0100, Dominique Devienne <ddevienne(at)gmail(dot)com> wrote:

> On Thu, Dec 1, 2022 at 4:23 PM Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us> wrote:
> > "David G. Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> writes:
> > > On Thu, Dec 1, 2022 at 7:59 AM Martijn Tonies (Upscene Productions) <
> > > m(dot)tonies(at)upscene(dot)com> wrote:
> > >> Can you modify the server code to store the original body in proc.prosrc
> > >> again? It would be very helpful.
> >
> > > I seem to recall that this option had been discussed and rejected when this
> > > feature went in. The parsed body is a feature because its contents can be
> > > updated due to, e.g., renaming of objects. The text source of the original
> > > command would be unable to be updated in the same way and so it is possible
> > > the text and the parsed contents would diverge over time, which is a
> > > situation we do not want to have.
> >
> > Indeed. We used to have a similar situation with respect to column
> > default expressions and CHECK constraint expressions. Eventually we got
> > rid of the textual storage of both, because it couldn't be maintained
> > in a reasonable way.
> >
> > I think the answer here is "don't use the new syntax if you want the
> > function body stored textually". You can have one set of benefits,
> > or the other set, but not both at once.
>
> FWIW, we have a custom schema introspection and diff'ing ad-hoc framework,
> and the fact the original SQL is not conserved as-is has also created
> issues for us.
>
> On Oracle, our SQL was preserved as-is, so could be compared reliably. While on
> PostgreSQL, some names-in-SQL are rewritten, the text reformatted,
> etc... So this
> byte-perfect comparison is no longer possible, and we must rely on heuristics
> (a.k.a, hacks...) to do a fuzzy-compare (since we lack a real PG-compatible SQL
> parsers to do an infoset-comparison instead, at the AST level for example).
>
> So it's not just a matter of browsing the schema. For us, it's a
> *functional* issue. --DD

Same here. Accessing the loaded stored procedure source
is how I audit the state of stored procedures in the
database against the code in the code repository.
Without the ability to make that comparison, there is
no way to audit the stored procedures, and the only way
to make sure that the database is up to date with the
stored procedures would be to reload every single
stored procedure. I might have to alter the procedure
loading program to store its own copy of the source code
in the database somewhere, and just hope that nobody
loads stored procedures using any other tool. Even so,
it will slow down loading old database backups and
bringing their schema and code up to date. But that's
probably OK compared with the time to load the backup
itself.

cheers,
raf