Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rJdiS-00EcSX-Ci for pgsql-general@arkaria.postgresql.org; Sat, 30 Dec 2023 18:05:45 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1rJdiQ-001jeR-Lo for pgsql-general@arkaria.postgresql.org; Sat, 30 Dec 2023 18:05:42 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rJdiO-001jeH-Ui for pgsql-general@lists.postgresql.org; Sat, 30 Dec 2023 18:05:42 +0000 Received: from out5-smtp.messagingengine.com ([66.111.4.29]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1rJdiH-00CwYn-AU for pgsql-general@lists.postgresql.org; Sat, 30 Dec 2023 18:05:39 +0000 Received: from compute2.internal (compute2.nyi.internal [10.202.2.46]) by mailout.nyi.internal (Postfix) with ESMTP id 4D4FB5C01AD; Sat, 30 Dec 2023 13:05:31 -0500 (EST) Received: from mailfrontend2 ([10.202.2.163]) by compute2.internal (MEProxy); Sat, 30 Dec 2023 13:05:31 -0500 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=aklaver.com; h= cc:cc:content-transfer-encoding:content-type:content-type:date :date:from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to; s=fm1; t=1703959531; x=1704045931; bh=/hzWKWfb+XfJnY72H+j8zerDYVKSkF0cB2osfsc+A0E=; b= iaBf7qIwllhAjdCSOeZPajlQmIyM0PaOXRm3rgS3PV1FdJ9eROwx6Mba4u5APRPj iPR1HEdCaxC8M9Iik7zqxPR3/QwjppMHg/f4w92BAiR/byRxjYHFcUa4KL72GJMC 418lNw62qlgc+Iqn4jjzK9sTWglVDL4+IwoCH9lp5mWfscF3DBDMzLbnbxSQnK6Q f/EyMuqRxA2F3gHafusPgUJCh5VnmSKU7mWNGtbNq00yc29T97WQ4xLRrA0K/AGa 5MnhqHWVooJ+zYV6XnIkTLWq+PxwadgtvvvTDa+I9aak6geSK8WA8YMrn1XyGu73 KkmXKXW6GUs0I45pDIPbzg== DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d= messagingengine.com; h=cc:cc:content-transfer-encoding :content-type:content-type:date:date:feedback-id:feedback-id :from:from:in-reply-to:in-reply-to:message-id:mime-version :references:reply-to:subject:subject:to:to:x-me-proxy:x-me-proxy :x-me-sender:x-me-sender:x-sasl-enc; s=fm2; t=1703959531; x= 1704045931; bh=/hzWKWfb+XfJnY72H+j8zerDYVKSkF0cB2osfsc+A0E=; b=Q N/DW4X6b5SYywbIqQ8g7uZ26dpBJURFZJNTpMO2YtvScG/FeSGMu2ifJ/ggkxoQu aBJPxL9pQJEA9olCoyIKI4CfOhGzyO5cFOui15lcKJVoO1MZ+UXX+o94VCzi/RVW 2KL9xSI3pAUp50v/YiYbD92kAvk6JcoQk2rMhGxOWLiVQn15fvzAhRdLeAErnzdK XJ2PXYAaJ/fTq5mtJg/KBZ7y+7FAdez+AyORFQLdeVevejUmbLEIJEhthZdkHM0O uIpahf6tqpKVXQIbsqH0+Mgnjds4HkU7bbK0ef+qSOrfELLsyoUrUsI45lVtkSv+ sNG7VIA1BnT9ztcl+b3Jg== X-ME-Sender: X-ME-Received: X-ME-Proxy-Cause: gggruggvucftvghtrhhoucdtuddrgedvkedrvdefhedguddutdcutefuodetggdotefrod ftvfcurfhrohhfihhlvgemucfhrghsthforghilhdpqfgfvfdpuffrtefokffrpgfnqfgh necuuegrihhlohhuthemuceftddtnecusecvtfgvtghiphhivghnthhsucdlqddutddtmd enucfjughrpefkffggfgfuvfevfhfhjggtgfesthejredttddvjeenucfhrhhomheptegu rhhirghnucfmlhgrvhgvrhcuoegrughrihgrnhdrkhhlrghvvghrsegrkhhlrghvvghrrd gtohhmqeenucggtffrrghtthgvrhhnpeeuudefjeejgffgtddtgeehgfekueevkeekieet tedtkeffheeivdethfevffegueenucffohhmrghinhepphhoshhtghhrvghsqhhlrdhorh hgnecuvehluhhsthgvrhfuihiivgeptdenucfrrghrrghmpehmrghilhhfrhhomheprggu rhhirghnrdhklhgrvhgvrhesrghklhgrvhgvrhdrtghomh X-ME-Proxy: Feedback-ID: i76984098:Fastmail Received: by mail.messagingengine.com (Postfix) with ESMTPA; Sat, 30 Dec 2023 13:05:29 -0500 (EST) Message-ID: Date: Sat, 30 Dec 2023 10:05:28 -0800 MIME-Version: 1.0 User-Agent: Mozilla Thunderbird Subject: Re: Changing a schema's name with function1 calling function2 Content-Language: en-US To: Wilma Wantren Cc: wolakk@gmail.com, xof@thebuild.com, hjp-pgsql@hjp.at, pgsql-general@lists.postgresql.org References: <09a7102b697d2b2acd81cb12344b0170@mail.eclipso.de> <90ca8707-6f44-47c2-b781-0138947d7483@aklaver.com> <20231224214332.rokhqwkq2xewzobk@hjp.at> <9ed20788-82cd-4eba-a590-7a474ca570ef@aklaver.com> <20231225143726.gfynym24xqcc27tu@hjp.at> <9bb4fd9a3e59adce379c3828e2a73646@mail.eclipso.de> <924F91AE-0597-45EE-B8ED-D857EFDCFB09@thebuild.com> <9402749f7bc1ec4fb28989f72aa50676@mail.eclipso.de> From: Adrian Klaver In-Reply-To: <9402749f7bc1ec4fb28989f72aa50676@mail.eclipso.de> 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/30/23 08:01, Wilma Wantren wrote: > Thank you all, and especially you, Adrian, for your answers. > However, I find the last suggestion too complicated. In Peter's words I had suggested a "magic variable" __function_schema__ which can be set as the search_path of a function to select - when executing the function - the schema the function actually is in. ("when executing", and not "when setting the search_path") > This would have been very easy to use and in the implementation of __function_schema__ it would have been possible to determine and cache the variable value (i.e. the schema of the function) directly when setting the search_path, and to redetermine and cache the variable value only when the function's schema changes. This is still not out of the realm of possibility, it would require getting a developer or developers interested in it. The place to make that argument is the hackers list: https://www.postgresql.org/list/pgsql-hackers/ Though the earliest that could be incorporated into Postgres would be the next major release Fall of 2024. This is dependent on getting the code in before the feature freeze Spring(?) of 2024. > Instead, I should now call the - actually diagnostic - function PG_ROUTINE_OID from the body of my function, with which I get the OID of my function in order to then determine the schema of my function and set it as search_path. I don't think that suits my requirements. > > I will therefore consider using a database change management system instead (e.g. sqitch, suggested by Adrian) and defining there what should happen when the schema name is changed, including the names of all functions whose search_path is to be changed. > > Many thanks again > Wilma > -- Adrian Klaver adrian.klaver@aklaver.com