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 1p0nYs-00068S-LV for pgsql-general@arkaria.postgresql.org; Thu, 01 Dec 2022 17:41:27 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.92) (envelope-from ) id 1p0nYr-0001ss-1t for pgsql-general@arkaria.postgresql.org; Thu, 01 Dec 2022 17:41:25 +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 1p0nYp-0001sD-RU for pgsql-general@lists.postgresql.org; Thu, 01 Dec 2022 17:41:24 +0000 Received: from out3-smtp.messagingengine.com ([66.111.4.27]) by magus.postgresql.org with esmtps (TLS1.3:ECDHE_RSA_AES_256_GCM_SHA384:256) (Exim 4.92) (envelope-from ) id 1p0nYm-0006XV-S2 for pgsql-general@lists.postgresql.org; Thu, 01 Dec 2022 17:41:23 +0000 Received: from compute4.internal (compute4.nyi.internal [10.202.2.44]) by mailout.nyi.internal (Postfix) with ESMTP id F11295C00BC; Thu, 1 Dec 2022 12:41:18 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute4.internal (MEProxy); Thu, 01 Dec 2022 12:41:18 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:date:date:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to; s=fm3; t=1669916478; x= 1670002878; bh=rSNlIeWD3r/ATlk40lA7JXwHW4bUJkObGy1oyyYKuns=; b=n sQNw3VFJhq9/F/ydC3xjxKWKimPrkkts4URIXf9X9aAAcezPvMf0KKIbBpyJ5G3I Y7qufaJoFLDNrkVoBXvi2DTNk/hYC6akEEAxJOxRieOK7Vlb/T2JL8ZMoYPcV85q /qr+TcKwZARfWb5pwa3HhyehxwxlnExonMJuJ52YaWx6uXQEOXQi4gKmgjFD3I+y pK2jr1NMq5gOYdXIb1NNhaQLB9mFG48yy4qifGRDkjqxn4vfFACYEFqCjxV0u2og ijld8hQv5AVBWNO9oSnp/jpp/GvVm6KGwru+/bHb7HipoTMoSQodeNvZigvuhjWu 3KLprGgfJj/QfL99TDInQ== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:date:date:feedback-id:feedback-id:from:from :in-reply-to:in-reply-to:message-id:mime-version:references :reply-to:sender:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm1; t=1669916478; x= 1670002878; bh=rSNlIeWD3r/ATlk40lA7JXwHW4bUJkObGy1oyyYKuns=; b=c 8OLDGLz4hVup9zLmh3LqhzXA9xE9HQiYCG8dNVw/pXtQqBQd1yL9cnF6rDDVt6m+ vN5h9oCku0ISdDg3uAIDwe9opju1TMTT26taZMDHFd3Ka2d2hnf9i+BF1e61KSIL zEfk5Way7BVa1cvWNLv26VFP7yYsVrTKNmXc/+i+tSQLxJ4jua/pSrMD07NQxOS+ knkqJI+9SYn86yYfDi8UydO4SiT3gQ8bY+uLIEoSpfNqWB7P81iTUSsc0S2nJFTC 0MwpKBdVaMpDupIb/rRCjMmS/xVB7j/AADnNlIch4u/yZgAxrDRa3rup9MEvhjCH XkkP/zdVwKjRIFYGH7DhQ== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvhedrtdehgddutdefucetufdoteggodetrfdotf fvucfrrhhofhhilhgvmecuhfgrshhtofgrihhlpdfqfgfvpdfurfetoffkrfgpnffqhgen uceurghilhhouhhtmecufedttdenucesvcftvggtihhpihgvnhhtshculddquddttddmne cujfgurhepkfffgggfuffvvehfhfgjtgfgsehtjeertddtfeejnecuhfhrohhmpeetughr ihgrnhcumfhlrghvvghruceorggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtg homheqnecuggftrfgrthhtvghrnhepjeegtdfhuefhtefgffdvheduleetgeejudevffek teegjeeiheeuudellefgffffnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpe hmrghilhhfrhhomheprggurhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Thu, 1 Dec 2022 12:41:17 -0500 (EST) Message-ID: Date: Thu, 1 Dec 2022 09:41:17 -0800 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.5.1 Subject: Re: Stored procedure code no longer stored in v14 and v15, changed behaviour Content-Language: en-US To: Dominique Devienne , Laurenz Albe Cc: Tom Lane , "David G. Johnston" , "Martijn Tonies (Upscene Productions)" , pgsql-general@lists.postgresql.org References: <23DE96C56DFC404096553A22DDC5093B@MARTIJNWS> <1579912.1669908194@sss.pgh.pa.us> From: Adrian Klaver In-Reply-To: Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk On 12/1/22 09:24, Dominique Devienne wrote: > On Thu, Dec 1, 2022 at 5:10 PM Laurenz Albe wrote: >> On Thu, 2022-12-01 at 16:38 +0100, Dominique Devienne wrote: >>> FWIW, we have a custom schema introspection and diff'ing ad-hoc framework, >> >> This is arguable, but my opinion is that this is not a robust way to >> do development. You should use a schema versioning tool like Liquibase, >> develop schema migration scripts and maintain the SQL code in a source >> repository like other software. > > We don't maintain SQL. We maintain a *Logical* model, and generate the > physical model from it. > FKs, NKs, Enums, CHECK constraints, indexes, etc.. all that > "implementation details" > is programmatically generated, and always consistent, from a much > higher-level and simpler model. > And you also get auto-upgrade most of the time, greatly increasing > development velocity too. > > I would argue that NOT doing it this way, is the non-robust way myself :) > We've been refactoring a large data-model maintained manually like you advocate, > and I can't tell you how many anomalies we've discovered and had to fix, > using the more robust formalism of using a high-level logical model > and (DDL) code gen. > > I guess is a DBA-versus-Developer point-of-view difference. --DD What this points to is that there are multiple ways to handle this, many external to the server itself. My take is that the system catalogs are there for the proper operation of the server and that is their task, first and foremost. If you can piggyback of that then great, but with the knowledge that the information may change to meet the needs of the server not external users. -- Adrian Klaver adrian.klaver@aklaver.com