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 1p1sQt-0000dB-J8 for pgsql-general@arkaria.postgresql.org; Sun, 04 Dec 2022 17:05:39 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p1sQs-0005pq-7w for pgsql-general@arkaria.postgresql.org; Sun, 04 Dec 2022 17:05:38 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p1sQr-0005ph-RU for pgsql-general@lists.postgresql.org; Sun, 04 Dec 2022 17:05:37 +0000 Received: from mail-ej1-x629.google.com ([2a00:1450:4864:20::629]) by makus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_128_GCM_SHA256:128) (Exim 4.92) (envelope-from ) id 1p1sQp-0008Fd-7t for pgsql-general@lists.postgresql.org; Sun, 04 Dec 2022 17:05:36 +0000 Received: by mail-ej1-x629.google.com with SMTP id n21so22644446ejb.9 for ; Sun, 04 Dec 2022 09:05:35 -0800 (PST) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20210112; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:from:to:cc:subject:date :message-id:reply-to; bh=8Gy1dD6TgJnOvXBVqTy2bQBFa9jm4nEotwUXP94+kW4=; b=FDKVPyT3jSRaJ5BytjbHU+keUKSXDFoA0Ah0zF+dV6gy1EBWMV7UoOEgykcXDmk64a 5r0NNFlR1g7INBQcJFLRMMx1/d8/niTNccqjocmnjbMflJO1EwwvFdNn0bp7leJNQinx btrGbsLlcXiKEhQiiphbkJJwVEa3QmVY1rQpyf62pfArqqr1ntIMh/pF+o3U8SoHkyDM PY3ri3fUCUvLktqZrYA0THRuRu/8gwysD90qXNFn32n93Lt8FTb2qZZPugMA35Xb2KdH +tKZTxV+NsATXY20V6tR4DFe2IOrhaCW9aUYhqODYHg4Qc/7969wr59G8uMS5zDwL7+m BjZg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20210112; h=to:references:message-id:content-transfer-encoding:cc:date :in-reply-to:from:subject:mime-version:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=8Gy1dD6TgJnOvXBVqTy2bQBFa9jm4nEotwUXP94+kW4=; b=IS4V3s0qnNiGGy8O/deUWbMidJ/5VCi6ROu76IfLGMIKtQ7gt+ljtVnN+gAPm7XC3w R+bVxFtVpAXitcKZuiUURqrp2YUaP3zhOKObMkZ5YeLMun4mNnnEFDKidsk5DuA+AulF Bs8SwruKlLoCQ0X3Zb9q4HMx90/6R95drNZtwQhSx3qvScOaV1HxnmLRswI/hOBs1OaE /SysYGy0l+om50EV4KPg6MacZDqtqEXKlik8ELRudRRX9sbsXaoseWdqIGVbJg5oJmSe 1nq76TEGdZysApkz8Xy2vDyCvJo0o6mP2mKWFzFl+cEm2LmTNoBmKhf+KLYKXx0DJsWI nbaQ== X-Gm-Message-State: ANoB5pmAWqI+/xDMuX9fFVCrbkuZ9OiJ1evPeOdtIm0RcR6Y/IIQbQAF o4M1oYHEdfVENvLn37w5uGI= X-Google-Smtp-Source: AA0mqf6tYPHv074uFeetHB1jDutWDMUVdMHZ8dWugS+lbpA6M4xtxDDEqRonbssv2dlrZhWSATJJXw== X-Received: by 2002:a17:906:6bd2:b0:7c0:ea28:6976 with SMTP id t18-20020a1709066bd200b007c0ea286976mr2531610ejs.418.1670173533179; Sun, 04 Dec 2022 09:05:33 -0800 (PST) Received: from hollewijn.internal (217-19-30-105.dsl.cambrium.nl. [217.19.30.105]) by smtp.gmail.com with ESMTPSA id b23-20020aa7df97000000b004611c230bd0sm5341232edy.37.2022.12.04.09.05.31 (version=TLS1_2 cipher=ECDHE-ECDSA-AES128-GCM-SHA256 bits=128/128); Sun, 04 Dec 2022 09:05:32 -0800 (PST) Content-Type: text/plain; charset=utf-8 Mime-Version: 1.0 (Mac OS X Mail 13.4 \(3608.120.23.2.7\)) Subject: Re: Stored procedure code no longer stored in v14 and v15, changed behaviour From: Alban Hertroys X-Priority: 3 In-Reply-To: Date: Sun, 4 Dec 2022 18:05:30 +0100 Cc: DAVID ROTH , Dominique Devienne , Tom Lane , Christophe Pettus , Adrian Klaver , Laurenz Albe , "David G. Johnston" , "Martijn Tonies (Upscene Productions)" , pgsql-general@lists.postgresql.org Content-Transfer-Encoding: quoted-printable Message-Id: <5AF91A67-52F3-4D55-9402-CD4939027733@gmail.com> 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> To: Karsten Hilbert X-Mailer: Apple Mail (2.3608.120.23.2.7) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk > On 3 Dec 2022, at 20:55, Karsten Hilbert = wrote: >=20 >> 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?). >=20 > I wonder whether that would tie the sanity check to a particular PG = version. >=20 > I mean, pg_get_functiondef output being a server runtime artifact it = might > well change between server versions, no ? I meant to write: =E2=80=9CI would also generate new diffs right _before = and_ after=E2=80=A6=E2=80=9D, precisely for that reason. The before = patch should get you the last =E2=80=99sane=E2=80=99 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=E2=80=99d have something=E2=80=A6 Alban Hertroys -- If you can't see the forest for the trees, cut the trees and you'll find there is no forest.