On Thu, Dec 1, 2022 at 4:23 PM Tom Lane <tgl@xxxxxxxxxxxxx> wrote: > "David G. Johnston" <david.g.johnston@xxxxxxxxx> writes: > > On Thu, Dec 1, 2022 at 7:59 AM Martijn Tonies (Upscene Productions) < > > m.tonies@xxxxxxxxxxx> 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
