On 12/4/22 11:05, Alban Hertroys wrote:
On 3 Dec 2022, at 20:55, Karsten Hilbert <Karsten.Hilbert@xxxxxxx> 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.
