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
