Re: [EXT]Re: Connection not allowed because of an error 'Not in pg_hba.conf' - Mailing list pgsql-general

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@gmail.com> wrote:
> 
> 
> On Wed, Sep 20, 2023 at 2:06 PM Harry Green <harrygreen91@yahoo.com 
> <mailto:harrygreen91@yahoo.com>> 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@aklaver.com