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

On 12/4/22 11:05, Alban Hertroys wrote:
>> On 3 Dec 2022, at 20:55, Karsten Hilbert <Karsten(dot)Hilbert(at)gmx(dot)net> 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 database (a before and after of the output of pg_get_functiondef, applied to the stored diff?).
>> I wonder whether that would tie the sanity check to a particular PG version.
>>
>> 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 reason. The before patch should get you the last ’sane’ situation to get back to the source code. Next, you can diff that to the newly tokenised version after the upgrade.
>
> It is a bit of a hassle, as you need to remember to do that before an upgrade, but at least you’d have something…

I run this cron job every midnight:

#!/bin/bash
PGUSER=postgres
PGHOST=<some_host>
TS=`date +"%Y%m%d_%H%M%S%Z"`

cd /dba/schema

PREVSQL=`ls -1 *sql | tail -n1`
PREV_MD5=`md5sum $PREVSQL | cut -c1-32`
NEWSCHEMA=sides_${TS}${SUFFIX}.sql

pg_dump --schema-only --dbname=<some_db> --clean --create > $NEWSCHEMA

CURRSQL=`ls -1 *sql | tail -n1`
CURR_MD5=`md5sum $CURRSQL | cut -c1-32`

echo "--------"
date +"%F %T, %a"
if [ "$PREV_MD5" = "$CURR_MD5" ];
then
    echo Schema unchanged.  Deleting $NEWSCHEMA
    rm $NEWSCHEMA
else
    ls -aFl $NEWSCHEMA
fi

--
Angular momentum makes the world go 'round.