Also, we've *never* had storage of the original text for views.
I'm a little mystified by people claiming they use original
text for vetting functions when they clearly have no equivalent
ability for views ... or are your applications completely
view-free?
Well, I would say that's annoying too!
: Friday, December 2, 2022 3:10 PM
To: pgsql-general@lists.postgresql.org
Subject: Re: Stored procedure code no longer stored in v14 and v15, changed
behaviour
From a user point of view, can also be seen as a "regression",
when an observable property of the system change
Am Sun, Dec 04, 2022 at 06:05:30PM +0100 schrieb Alban Hertroys:
> > I mean, pg_get_functiondef output being a server runtime artifact it might
> > well change between server versions, no ?
>
> I meant to write: “I would also generate new diffs right
> _before and_ after…”, precisely for that
On 12/4/22 11:05, Alban Hertroys wrote:
On 3 Dec 2022, at 20:55, Karsten Hilbert wrote:
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
> On 3 Dec 2022, at 20:55, Karsten Hilbert wrote:
>
>> 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
> 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
On 12/2/2022 9:05 AM, Peter J. Holzer wrote:
I don't know how reproducable that tokenization process is. Can you just
do it again and compere the results?
Right.
You can compare passwords, even though you don't store the original.
You might be able to run your virgin copy through the
> On 2 Dec 2022, at 15:10, DAVID ROTH 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
On Fri, Dec 02, 2022 at 11:02:18AM -0500, Tom Lane wrote:
> Also, we've *never* had storage of the original text for views.
> I'm a little mystified by people claiming they use original
> text for vetting functions when they clearly have no equivalent
> ability for views ... or are your
> If you want an audit comparison point, I'd suggest capturing
> the result of pg_get_functiondef or one of its sibling functions
> just after creating your function. "pg_dump -s" is another
> pretty credible mechanism for capturing schema details.
That's very good info, thank you. I guess
> On Dec 2, 2022, at 08:02, Tom Lane wrote:
>
> Pasi Oja-Nisula writes:
>> I would be perfectly satisfied, if the sql that produced the procedure
>> would be stored "as is" read-only copy when it was compiled.
> If you want an audit comparison point, I'd suggest capturing
> the result of
Pasi Oja-Nisula writes:
> I would be perfectly satisfied, if the sql that produced the procedure
> would be stored "as is" read-only copy when it was compiled.
As I mentioned upthread, we used to have that behavior (store both
compiled form and original text) for column default expressions and
On 2022-12-02 14:02:37 +0100, Daniel Gustafsson wrote:
> > On 2 Dec 2022, at 14:00, Pasi Oja-Nisula wrote:
> >
> > On Fri, 2 Dec 2022 at 02:24, raf wrote:
> >> Same here. Accessing the loaded stored procedure source
> >> is how I audit the state of stored procedures in the
> >> database against
On Fri, 2 Dec 2022 at 15:47, raf wrote:
> If you're concerned about tampering by
> customers/users/developers, you can either set
> permissions to prevent it in some cases, and when you
> can't prevent it, make it tamper-evident by logging
> actions to somewhere remote and monitoring for what
>
Is there a way to reverse engineer the original code (or its equivalent) from
what is saved in the database?
> On 12/02/2022 8:48 AM Dominique Devienne wrote:
>
>
> On Thu, Dec 1, 2022 at 8:51 PM Tom Lane wrote:
> > Do you really fail to see the contradictions in this? You want the
> >
>
> From a user point of view, can also be seen as a "regression",
> when an observable property of the system changes to a new
> different / incompatible way, to some extent. I'm not saying it is,
> still it is a change one discovers too late, creates pain to some,
> and is both worth reporting
On Thu, Dec 1, 2022 at 8:51 PM Tom Lane wrote:
> Do you really fail to see the contradictions in this? You want the
> database to preserve the original DDL, but you also want it to update
> in response to subsequent alterations. You can't have both those
Hi. I probably didn't express myself
On Fri, Dec 02, 2022 at 03:00:09PM +0200, Pasi Oja-Nisula wrote:
> On Fri, 2 Dec 2022 at 02:24, raf wrote:
> > 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.
>
> Exactly. If
On Fri, Dec 2, 2022 at 1:37 PM Laurenz Albe wrote:
> Great; then go ahead and use those databases, if it is important for you.
Now come on. We all love PostgreSQL.
But that doesn't mean we can't disagree on some decisions.
Especially when you are a USER of PostgreSQL, not a DEV of it,
and it's
On Fri, 2 Dec 2022 at 15:02, Daniel Gustafsson wrote:
> How do you today prove that for other compiled programs in your system?
Good question. I guess I never considered database to be compiled.
How do you debug this kind of system? What if the you suspect that there is
a wrong version of
> On 2 Dec 2022, at 14:00, Pasi Oja-Nisula wrote:
>
> On Fri, 2 Dec 2022 at 02:24, raf wrote:
>> 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.
>
> Exactly. If our software is
On Fri, 2 Dec 2022 at 02:24, raf wrote:
> 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.
Exactly. If our software is audited, how can I reliably prove to auditor
that the running
On Fri, 2022-12-02 at 08:49 +0100, Martijn Tonies (Upscene Productions) wrote:
> So do InterBase, Firebird, SQL Server, MySQL (except for Views, strangely
> enough),
> MariaDB, NexusDB, SQL Anywhere, and, frankly, all others I know of.
>
> And this is used all the time by database developers.
>
On 12/1/22 09:24, Dominique Devienne wrote:
> I guess is a DBA-versus-Developer point-of-view difference. --DD
What this points to is that there are multiple ways to handle this, many
external to the server itself. My take is that the system catalogs are
there for the proper operation of the
On Thu, Dec 01, 2022 at 04:38:57PM +0100, Dominique Devienne
wrote:
> On Thu, Dec 1, 2022 at 4:23 PM Tom Lane wrote:
> > "David G. Johnston" writes:
> > > On Thu, Dec 1, 2022 at 7:59 AM Martijn Tonies (Upscene Productions) <
> > > m.ton...@upscene.com> wrote:
> > >> Can you modify the server
On 2 Dec 2022, at 6:51, Tom Lane wrote:
> Dominique Devienne writes:
>> On Thu, Dec 1, 2022 at 8:09 PM Christophe Pettus wrote:
>>> Since this is a custom-built system, there is nothing keeping you from
>>> creating your own table in the database that stores the original text of
>>> the
Dominique Devienne writes:
> On Thu, Dec 1, 2022 at 8:09 PM Christophe Pettus wrote:
>> Since this is a custom-built system, there is nothing keeping you from
>> creating your own table in the database that stores the original text of the
>> function.
> That's not the point. If a DBA updates
On Thu, Dec 1, 2022 at 8:09 PM Christophe Pettus wrote:
> > On Dec 1, 2022, at 11:05, Dominique Devienne wrote:
> > I see. Still, Oracle preserves SQL as-is. SQLite preserve SQL as-is.
> > Would be nice if PostgreSQL did too. That's all I'm saying.
>
> Since this is a custom-built system, there
> On Dec 1, 2022, at 11:05, Dominique Devienne wrote:
>
> I see. Still, Oracle preserves SQL as-is. SQLite preserve SQL as-is.
> Would be nice if PostgreSQL did too. That's all I'm saying.
Since this is a custom-built system, there is nothing keeping you from creating
your own table in the
On Thu, Dec 1, 2022 at 6:41 PM Adrian Klaver wrote:
> On 12/1/22 09:24, Dominique Devienne wrote:
> > I guess is a DBA-versus-Developer point-of-view difference. --DD
>
> What this points to is that there are multiple ways to handle this, many
> external to the server itself. My take is that the
On 12/1/22 09:24, Dominique Devienne wrote:
On Thu, Dec 1, 2022 at 5:10 PM Laurenz Albe wrote:
On Thu, 2022-12-01 at 16:38 +0100, Dominique Devienne wrote:
FWIW, we have a custom schema introspection and diff'ing ad-hoc framework,
This is arguable, but my opinion is that this is not a
On Thu, Dec 1, 2022 at 5:10 PM Laurenz Albe wrote:
> On Thu, 2022-12-01 at 16:38 +0100, Dominique Devienne wrote:
> > FWIW, we have a custom schema introspection and diff'ing ad-hoc framework,
>
> This is arguable, but my opinion is that this is not a robust way to
> do development. You should
On Thu, 2022-12-01 at 16:38 +0100, Dominique Devienne wrote:
> 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
On Thu, Dec 1, 2022 at 7:59 AM Martijn Tonies (Upscene Productions)
wrote:
Since version 14, the source code for a stored procedure or function written
in plain (compound) SQL, a new feature, is no longer stored in
pg_proc.prosrc, instead, there’s an additional column prosqlbody which
On Thu, Dec 1, 2022 at 4:23 PM Tom Lane wrote:
> "David G. Johnston" writes:
> > On Thu, Dec 1, 2022 at 7:59 AM Martijn Tonies (Upscene Productions) <
> > m.ton...@upscene.com> wrote:
> >> Can you modify the server code to store the original body in proc.prosrc
> >> again? It would be very
"David G. Johnston" writes:
> On Thu, Dec 1, 2022 at 7:59 AM Martijn Tonies (Upscene Productions) <
> m.ton...@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
On Thu, Dec 1, 2022 at 7:59 AM Martijn Tonies (Upscene Productions) <
m.ton...@upscene.com> wrote:
>
> Since version 14, the source code for a stored procedure or function
> written
> in plain (compound) SQL, a new feature, is no longer stored in
> pg_proc.prosrc, instead, there’s an additional
Hi all,
In PosgreSQL version 13, the source code for a stored procedure or function
in SQL/plpgsql/etc was stored in pg_proc.prosrc. This column would hold the
original procedure or function body, verbatim.
Since version 14, the source code for a stored procedure or function written
in
38 matches
Mail list logo
