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.
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
<[email protected]> wrote:
On Wed, Sep 20, 2023 at 2:06 PM Harry Green <[email protected]> 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.
