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 1qncC1-00BRqM-S3 for pgsql-general@arkaria.postgresql.org; Tue, 03 Oct 2023 09:59:54 +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 1qncC0-003flL-67 for pgsql-general@arkaria.postgresql.org; Tue, 03 Oct 2023 09:59:52 +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 1qncBz-003fjz-Gu for pgsql-general@lists.postgresql.org; Tue, 03 Oct 2023 09:59:52 +0000 Received: from sonic313-20.consmr.mail.ir2.yahoo.com ([77.238.179.187]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qncBw-007xE4-H8 for pgsql-general@postgresql.org; Tue, 03 Oct 2023 09:59:50 +0000 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1696327185; bh=hFx+c6jXud1hGIcQyz4/Sx/ce6rQLel/aJBuPQXGlR8=; h=Date:From:To:Cc:In-Reply-To:References:Subject:From:Subject:Reply-To; b=fBjkOjO7bbbC6OUKYNcvKTiGF7JEWdCyInwxreLJW6kXMhznS3MXoAkwQt2q0Hug3gaSXEOa0/u3G2D6B/rhamtk7Sp7ytAr5/QE04TZKkP7lR/Z3vEXLeyJ3+j8X9IAB7JKEU8+7ae1BvHCh5P75EnlPbvgysEvEIa/yhtrp27AAZaTUzWs/7LAQLJiFB//uCWBpU90FsV/qojOQurINkZJIKkLZdmEYh6SYYhiXCJjFdUZO+dAjL4tB1up9Wty+1CZ25EaMFK9sO9uDeZ6wnU/W5oiOcEqzMRsBsPbQtXIbFYLvkKUyW+xXtXH6xGpCcEQdP3mgSM06Peq6yVBhQ== X-SONIC-DKIM-SIGN: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yahoo.com; s=s2048; t=1696327185; bh=ej8iMLcypSOp707VUEEWOQr60Od9WAtlHAor9TrAvDK=; h=X-Sonic-MF:Date:From:To:Subject:From:Subject; b=ZStMkzuS4E+9dQ5g/TRGw22N4QnFLwVWOMxJ17j8M+RXMCD7vfASBE4KOWIdZEskDFu89/sxwaHeRLLc/Otd+rfrh2AlTOBesW/tGOi+8QXTAtgzv/wiiT+3xRZHyeJGrjcY0aLWf88NEo4O6b9X4nZvsmTZyIWsSbp5POFHe7OJGT4JiZ39gywVs55B+gB7panXERYm8skvwplsMYThxrWHdm1lniHdNvz2bhWV1X3dFIyfCu2KJ1k7kyqPNlgs3ArOE07bbIp+SPpyGaWTehTI8w3TvB07u9auBu/cNstNzbbf/YVN9mWhLOqAK7EUHQ818XNPWAZToQRO8ds1vA== X-YMail-OSG: 7hODnJcVM1k8jyVMUDg2etBaOxWX8tUh0olsR_vD3erWD76Eg9YTmY.kPAnN4Ms raPX6nJjSkZuG8Gyk37cmT5c_pv7zIwi6eXfMIQ7HTU3GDEjxINpL9XrnQl8Mf.OHFFdOl4EFzez w79NIoc6fu4Sb9iRcdsz11fOUIvyyK7wHA4NdxBQSAphLLBFFwCWFeECjpxAN2.XlBwA7d6pxIcZ APw39q1TRmmWLMtTkA_G33bGeusiizQlENt6DaS1NB0Um14WNQfRqbUJ4AxXllWP4Ckujn0mrHQ5 l3C9AOt6BbW8XFlvVLtQobTT1pYDU5TverqeWmTczvSg.TZhKyzl_GEFNisyhrEeok.JpQDXRQWM _sb7roisDRDeUOIKWu0YxfUe_HXy7kmGD3PhVEmPqeWixwWXGHDQcqQQep5myEbQ9_5ZElVA9xix 0Jv6L5soTrhBUFWz6yJx0Fyq5XKluonh39PGKuxgk9uNT0TDcMoYER70nTS6.GiQRzQD71k6Ht53 yHmsTXdmQox.lG5JAerqd4F8su4UfLgYuhpwfjYc2P4nu4ezl3AWNHSlyLkWuuOgNNKA8fthcrur 3gmY_4hsoBHrS.yf3MTp7bCmnZVlI5NTowbfBN24cDMfcQJc9iULSqqumjg6GK6VUGjF3YKA4iTU hdrDQcQ2lumPEftbwKHYEBgjVZRFwZ.Xiij66qUm6ACmr4paJwsS_Q1t7xAk.w27YH5kEwWh8YDv Fi3xxLURV9uPfUPJe7_yt74qM6IO0PKjqZIgsts3s2JzL9XHbrQBiavPNQPHDRY13UEQo4V4kK_M VfLICxPMCeJajYvwVeEu9OFKDuqaxWpJ33dYGxVzhnbLMgDGJ_ZDpflZsPzQnr3WDRYigj8Xzvoy 3L.0YhRI9ahN4Zzq0hWXimmOXoW8Q59fY2IMsH5_iqjAKgSLhpRTkRvQGMXxMNql.UkklN8JQ8Ky hjhS.qUbhUNclFxgvQI.YWpnJ6sqtv7Npy4vKtK001uhl3a_GpmnoUDF5OYb4mA2uejlRXMsAE9p M6IOSBsynAni46wdtnn6RL8XjwCrfwcBgcdHEjQ2TNnLtJSIovI1jReSY4KoZIAnCXYWQ72oGzSX ORVZh.3T1loqGlcpHxDH9turLifmLwlSD6DOzALXqQiyZ0nADZVdLHFC71pDpjkxqnz6QjHbmpiY wkxHVhzfvcRiftcyubn0Kc1awWQJToESgf7r2Vl91q_PHNhaCij7CW6BvlfqOPlxql6pXIzO7vrc sJvjPqEbeRuCHkMpKsMAffXbEPuelHcu5wylDHEezTd8NXgTd3hox.IC4c4YRvZiGlMEoptL7UUq yFb1CresFzn4qfBOSlleg5y7czRzEZXtSfEogQQ5MVgQfDF40GnPQeC12ao8K98_MVM6BxcM2G3w o9nqQAew5feaVV2MmRku6lyMiDsDUmJpeszkbdOEM52VoaGyCtV62AWOkd7u416ppDUed1ITsEpD 5aiXOXQJeRGfsF7hzlSnHX2YdzQdFYmv2naGUWAI_mA_dcU1fnghCZlJxaUeQRrxeJkosS2ho_Q3 pgQHS9jImlM7MkpeZ17X8Jncx99yhgcaZUQRlzJMfvhubSlaST.YwSaCVfsnaYR0tja6e.O5dS1r oict_v37WCjiXd3YTzSn..Sz3PWRE8hnGNk1DbFq9lJ8_3GPUhIPBsPrYfoGwc0k_ggRQVDWT4lN oU8QA16H.EBoSAaU_W2zbl.PIFllAGFmhptSdKD45qk5ZyLbS9DDNdjB6_LZWeDSp4z00UAkEqlA BHlW05FiquhE4U6jXTJIyCEg22shSVs8IZ5XF6fhOy7mc83Yd05bfGFxUDNMkwrwueys1N9zON3x 3znogqu12nhSBRzhKxXihh62TZDekf0957cz905V5.d9ubrmyIJThA7ZRRV8BIZs5B5tm2tzJ95w ROvfMd2nC4v9_xfx6He2lu1V9ANxDl6mP6JyEDbvm9QZmrJVzIlvO5MaTjYyN5A11ABUvzWqfXV6 b_nQhmilDLBUmvyC4MEHgiT5uBvUsDbuhXTGIIuVBKrNUh9SuDKDDMF7ltLWj7vbOIIjeQ5xEJri HD2HScF_RANb0MyKhWGC6SkMRpim5nJbErG.WSLLf4cMymcYd12RzF6YOg3psKwHZB7TjljnxMv3 MRCaJYGh0zQs0kpNSG4n5Hng6JpWlI98bDnI_0AQun6PEQfxZ X-Sonic-MF: X-Sonic-ID: 4b7bb61c-08c2-44c6-82d5-a6db0ad34a39 Received: from sonic.gate.mail.ne1.yahoo.com by sonic313.consmr.mail.ir2.yahoo.com with HTTP; Tue, 3 Oct 2023 09:59:45 +0000 Date: Tue, 3 Oct 2023 09:59:41 +0000 (UTC) From: Harry Green To: "David G. Johnston" Cc: Adrian Klaver , pgsql-general , "laurenz.albe@cybertec.at" Message-ID: <786972745.7426923.1696327181961@mail.yahoo.com> In-Reply-To: References: <65A32E9A-A82B-4968-9CE9-09C21AAE6CC0@arizona.edu> <103d7895-72a6-98ce-87b3-f23307408244@aklaver.com> <50A7BD11-0BFE-44A3-9F6E-31351CDB9569@arizona.edu> <10104aac-4b99-666f-60d8-4564d2067182@aklaver.com> <1656079583.6535601.1695232905186@mail.yahoo.com> Subject: Re: [EXT]Re: Connection not allowed because of an error 'Not in pg_hba.conf' MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_7426922_2092336411.1696327181959" X-Mailer: WebService/1.1.21797 YMailNorrin Content-Length: 9227 List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk ------=_Part_7426922_2092336411.1696327181959 Content-Type: text/plain; charset=UTF-8 Content-Transfer-Encoding: quoted-printable =20 Dear All,I'd like to thank you for your excellent input on this problem. We= have now resolved this issue and I thought I would mention how. The topic = of the function being used as a check constraint had come up and I had reje= cted this because it was the name given to a function which does some check= ing. Since the function was used elsewhere I did not think it was itself us= ed as a check constraint, but in fact it was (on a different table than the= one that comes up in the error message). So you were right to highlight th= at fact. I should note, however, that it seems to me this is a bug in postg= resql, if not in the narrower sense than in the wider: the issue is that no= where in the process of creating a very simple function that references som= e table and then employed as a check constraint on a different table is the= user warned or stopped from doing so. In many cases, doing this saves time= over creating triggers or alternative mechanisms, so the user is naturally= drawn to employing this technique. Yet when the pg_dump is attempted the p= rocess fails. Furthermore, it is not entirely clear why pg_dump cannot add = the check constraints after all the tables are created, just as it does wit= h triggers. This is why it is worth considering or treating this as a bug t= hat may have a solution=C2=A0 - either by modifying pg_restore (or text equ= ivalent process) or by preventing the user from employing certain types of = functions as check constraints. The problem was resolved not by altering the functions in any way, but by c= reating triggers that employed them through wrap-up functions which used th= e NEW.column_name mechanism in the usual way on inserts and updates. Thank you once again for your valuable feedback. On Wednesday, 20 September 2023 at 22:16:32 BST, David G. Johnston wrote: =20 =20 On Wed, Sep 20, 2023 at 2:06=E2=80=AFPM Harry Green wrote: I attach the text of the entire create function instruction, and also of t= he create table instruction.=C2=A0 The restore is not going to execute functions on its own and you've only sh= own two create statements.=C2=A0 Somewhere else in your dump file the funct= ion check_account_from_bill_items must be referenced in order for it to be = called.=C2=A0 You need to show that.=C2=A0 It is not the function creation = that is going to be illegal, it will be, like Adrian said, something like u= sing a volatile function in a check constraint that is going to be illegal. We are working with version 10.23, and I cannot send you the entire output = of the pg_dump file because it is 3.3 GB, but am happy to send you any part= s that might help.=C2=A0 I doubt a schema-only dump is going to be that large...but you are right th= at you should be trying harder to isolate this down to a reproducible test = case and thus be able to provide more information without it being too much= . David J. =20 ------=_Part_7426922_2092336411.1696327181959 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: quoted-printable

Dear All,
I'd like to thank= you for your excellent input on this problem. We have now resolved this is= sue and I thought I would mention how. The topic of the function being used= as a check constraint had come up and I had rejected this because it was t= he name given to a function which does some checking. Since the function wa= s used elsewhere I did not think it was itself used as a check constraint, = but in fact it was (on a different table than the one that comes up in the = error message). So you were right to highlight that fact. I should note, ho= wever, that it seems to me this is a bug in postgresql, if not in the narro= wer sense than in the wider: the issue is that nowhere in the process of cr= eating a very simple function that references some table and then employed = as a check constraint on a different table is the user warned or stopped fr= om doing so. In many cases, doing this saves time over creating triggers or= alternative mechanisms, so the user is naturally drawn to employing this t= echnique. Yet when the pg_dump is attempted the process fails. Furthermore,= it is not entirely clear why pg_dump cannot add the check constraints afte= r all the tables are created, just as it does with triggers. This is why it= is worth considering or treating this as a bug that may have a solution&nb= sp; - either by modifying pg_restore (or text equivalent process) or by pre= venting the user from employing certain types of functions as check constra= ints.

The problem was resolved not by altering the= functions in any way, but by creating triggers that employed them through = wrap-up functions which used the NEW.column_name mechanism in the usual way= on inserts and updates.

Thank you once again for = your valuable feedback.


=20
=20
On Wednesday, 20 September 2023 at 22:16:32 BST, David = G. Johnston <david.g.johnston@gmail.com> wrote:


On Wed, Sep 20, 2023 at 2:06=E2=80=AFPM Harry Green <= harrygreen91@yahoo.com> wrote:
=
I attach the text of the entire create functi= on instruction, and also of the create table instruction. 

The restore is not going to = execute functions on its own and you've only shown two create statements.&n= bsp; Somewhere else in your dump file the function check_account_from_bill_= items must be referenced in order for it to be called.  You need to sh= ow that.  It is not the function creation that is going to be illegal,= it will be, like Adrian said, something like using a volatile function in = a check constraint that is going to be illegal.

We are working with version= 10.23, and I cannot send you the entire output of the pg_dump file because= it is 3.3 GB, but am happy to send you any parts that might help. 


I doubt a schema-onl= y dump is going to be that large...but you are right that you should be try= ing harder to isolate this down to a reproducible test case and thus be abl= e to provide more information without it being too much.

David J.

------=_Part_7426922_2092336411.1696327181959--