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

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