From: raf <raf@raf.org>
To: pgsql-general@lists.postgresql.org
Subject: Re: Stored procedure code no longer stored in v14 and v15, changed behaviour
Date: Fri, 2 Dec 2022 11:24:17 +1100
Message-ID: <Y4lFsaF3xz24K0mr@raf.org> (raw)
In-Reply-To: <CAFCRh--K2tahGetbJ_mtjiwvfFb9fF6SzjGnvbyoMKPr92FiTQ@mail.gmail.com>
References: <23DE96C56DFC404096553A22DDC5093B@MARTIJNWS>
<CAKFQuwbYCk0oA3VT+ngEebBTBjdtGsVLEFOdPX2qqQJobA7rtg@mail.gmail.com>
<1579912.1669908194@sss.pgh.pa.us>
<CAFCRh--K2tahGetbJ_mtjiwvfFb9fF6SzjGnvbyoMKPr92FiTQ@mail.gmail.com>
On Thu, Dec 01, 2022 at 04:38:57PM +0100, Dominique Devienne <ddevienne@gmail.com> wrote:
> On Thu, Dec 1, 2022 at 4:23 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > "David G. Johnston" <david.g.johnston@gmail.com> writes:
> > > On Thu, Dec 1, 2022 at 7:59 AM Martijn Tonies (Upscene Productions) <
> > > m.tonies@upscene.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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients from your mail client; the link fills in
the headers below: reply via email
Some clients, webmail especially, drop In-Reply-To and References from
a link, and the reply then starts a new thread: use the mbox below.
To: pgsql-general@postgresql.org
Cc: raf@raf.org
Subject: Re: Stored procedure code no longer stored in v14 and v15, changed behaviour
In-Reply-To: <Y4lFsaF3xz24K0mr@raf.org>
References: <23DE96C56DFC404096553A22DDC5093B@MARTIJNWS> <CAKFQuwbYCk0oA3VT+ngEebBTBjdtGsVLEFOdPX2qqQJobA7rtg@mail.gmail.com> <1579912.1669908194@sss.pgh.pa.us> <CAFCRh--K2tahGetbJ_mtjiwvfFb9fF6SzjGnvbyoMKPr92FiTQ@mail.gmail.com> <Y4lFsaF3xz24K0mr@raf.org>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox