Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p1uhQ-0007EL-I3 for pgsql-general@arkaria.postgresql.org; Sun, 04 Dec 2022 19:30:52 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p1uhO-00051Q-Rk for pgsql-general@arkaria.postgresql.org; Sun, 04 Dec 2022 19:30:50 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p1uhO-00051H-F2 for pgsql-general@lists.postgresql.org; Sun, 04 Dec 2022 19:30:50 +0000 Received: from mail-oa1-x36.google.com ([2001:4860:4864:20::36]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p1uhI-0001ty-Kz for pgsql-general@lists.postgresql.org; Sun, 04 Dec 2022 19:30:49 +0000 Received: by mail-oa1-x36.google.com with SMTP id 586e51a60fabf-1447c7aa004so3057251fac.11 for ; Sun, 04 Dec 2022 11:30:44 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :from:to:cc:subject:date:message-id:reply-to; bh=E7TkJ5X5B193LPOZ5t+k7JXPmB6qjujvIjQkFhyH8/0=; b=YFpCt3X9KzqaNsXr4oAuFfdc964vWOpYQ1j5gJcq538okU1rtdqUWKTg45AMATYWpv 3mDpOuA5xvaCBAtECVdoMiQKwmbvCYTnXVIMb1bqjbK3uYwuhCnpr9VvNINUkjVm8nr6 G7EB6hhmy+ChJGJJSPi1Ajplf4o/rWmoG91vJGNAP+mW2+jigk8K9SyANHYORyVRn3La q/kgCDWmknBPTb7FaAcIHLjxzReX0IaNFb4kfFvAprXPBNdZDSA7tdcgrb31BwdRkjVU LobgnkkvTsra/HcR5nHIbh/G+Wjz3Fd1TkgiX36Z3cWZOSMUWvYs0ot338+P9GMnHrp2 NN2g== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=content-transfer-encoding:in-reply-to:from:references:to :content-language:subject:user-agent:mime-version:date:message-id :x-gm-message-state:from:to:cc:subject:date:message-id:reply-to; bh=E7TkJ5X5B193LPOZ5t+k7JXPmB6qjujvIjQkFhyH8/0=; b=MsBgMyFGJN7r/xsmpeGeJav46LbYqshz4x6tKZEhOlK0TLwXIP2YeZu5LYAZivzaXE sfPRi7MfvgAORtY6i/WotmLoJsM0uLKuOpjgazLO/0ju+qI/yDDm+h2C4WBVP0ZYrT5g ZSqmL56cQpIfSL/458CC0biMOAJTsfFGB6km4hjEahtFf2nIpsVKghW5yeqzC2FL5VHX QZ+hlwlMwBf0JDm84RD03ejNzukc21mmWRYL8cRwJNeTAleyw5JYHjv9WcFQvKxVGCwu sadB6LXPS2rn2SuIcwzuc67y4GqmKMTeH9a/sB0l3Iz40rdOKBMCLA3FblVeDAC2JaCK r+6A== X-Gm-Message-State: ANoB5pnUL7aIMX6JZPD3/JrHg8eeqzVVtWHJ6/qpuCZbUyQA8gWW3ASp Rjrn4hbgA8ZOXpFKj5KIfsFmCdfKKWBjrA== X-Google-Smtp-Source: AA0mqf6n7ImE7NRD6ax7MCflP+fzC+l6edWsUxtmm4iR1/7OeaS8Zwt76CsFMKl3BRFcnOQs4ZP/QQ== X-Received: by 2002:a05:6870:ac26:b0:144:a164:1ccb with SMTP id kw38-20020a056870ac2600b00144a1641ccbmr644890oab.260.1670182242104; Sun, 04 Dec 2022 11:30:42 -0800 (PST) Received: from ?IPV6:2600:1700:bd2c:b810::43? ([2600:1700:bd2c:b810::43]) by smtp.googlemail.com with ESMTPSA id m40-20020a4a952b000000b0049ee88e86f9sm5663415ooi.10.2022.12.04.11.30.41 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Sun, 04 Dec 2022 11:30:41 -0800 (PST) Message-ID: Date: Sun, 4 Dec 2022 13:30:40 -0600 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.2.2 Subject: Re: Stored procedure code no longer stored in v14 and v15, changed behaviour Content-Language: en-US To: pgsql-general@lists.postgresql.org References: <23DE96C56DFC404096553A22DDC5093B@MARTIJNWS> <1579912.1669908194@sss.pgh.pa.us> <0730410A-9C79-4EE2-B21B-3502445A8700@thebuild.com> <1697225.1669924271@sss.pgh.pa.us> <1258414512.930087.1669990257195@connect.xfinity.com> <5AF91A67-52F3-4D55-9402-CD4939027733@gmail.com> From: Ron In-Reply-To: <5AF91A67-52F3-4D55-9402-CD4939027733@gmail.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk 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 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= 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= --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.