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.Hilbert@gmx.net> wrote:
>>
>>> You would need to wrap the function creation calls into some automation to generate and store those diffs,
comparingit 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
patchshould get you the last ’sane’ situation to get back to the source code. Next, you can diff that to the newly
tokenisedversion 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.