Search Postgresql Archives

Re: [EXT]Re: Connection not allowed because of an error 'Not in pg_hba.conf'

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



On 10/3/23 02:59, Harry Green wrote:

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 rejected this because it was the name given to a function which does some checking. Since the function was 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, however, that it seems to me this is a bug in postgresql, if not in the narrower sense than in the wider: the issue is that nowhere in the process of creating 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 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 process 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 with triggers. This is why it is worth considering or treating this as a bug that may have a solution  - either by modifying pg_restore (or text equivalent process) or by preventing the user from employing certain types of functions as check constraints.

1) Hijacking another thread is not a good idea.

2) Per my first post on this issue:

https://www.postgresql.org/docs/current/sql-createtable.html

"Currently, CHECK expressions cannot contain subqueries nor refer to
variables other than columns of the current row (see Section 5.4.1). The
system column tableoid may be referenced, but not any other system column."

It is pretty plain you cannot refer to columns outside the table the CHECK is on. Trying to cheat by hiding that in a function, well you see what happens then.

3) The function bodies are opaque to the server. This means there will be no dependency tracking for the dump/restore.





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.


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


On Wed, Sep 20, 2023 at 2:06 PM Harry Green <harrygreen91@xxxxxxxxx <mailto:harrygreen91@xxxxxxxxx>> wrote:

    I attach the text of the entire create function 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.  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 show 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-only dump is going to be that large...but you are right that 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.


--
Adrian Klaver
adrian.klaver@xxxxxxxxxxx






[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
[Index of Archives]     [Postgresql Jobs]     [Postgresql Admin]     [Postgresql Performance]     [Linux Clusters]     [PHP Home]     [PHP on Windows]     [Kernel Newbies]     [PHP Classes]     [PHP Databases]     [Postgresql & PHP]     [Yosemite]

  Powered by Linux