Thread: Get constrrelid for fk constraints that lost it

I believe this should allow create constraint trigger
to look up the constrrelid out of the fk constraint
arguments for those cases where the FROM clause
was lost if we want to go that route.


It determines if it should look up args and which
argument to use based on the function name and won't
probably handle delimited names nicely. I figured
that since the point of it is to read old dumps,
this is okay.  If the table doesn't exist or there
aren't enough args to intuit a table name, it currently
elog errors.  Is that reasonable behavior, or should
it be taking the constraint and letting it fail at
runtime?

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> ... If the table doesn't exist or there
> aren't enough args to intuit a table name, it currently
> elog errors.  Is that reasonable behavior, or should
> it be taking the constraint and letting it fail at
> runtime?

I was inclined to think it should take the constraint and let the
failure occur at runtime.  A NOTICE would be okay but not an ERROR.
(We can tweak the RI triggers to make the runtime failure message be
more helpful than "Relation 0 not found".)  ISTM the point of having
this hack is to allow old dump files to be loaded, and an ERROR would
get in the way of that.

            regards, tom lane

On Mon, 30 Sep 2002, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> > ... If the table doesn't exist or there
> > aren't enough args to intuit a table name, it currently
> > elog errors.  Is that reasonable behavior, or should
> > it be taking the constraint and letting it fail at
> > runtime?
>
> I was inclined to think it should take the constraint and let the
> failure occur at runtime.  A NOTICE would be okay but not an ERROR.

That's easy enough to do (changing a a false to true and an ERROR
to NOTICE I believe)

> (We can tweak the RI triggers to make the runtime failure message be
> more helpful than "Relation 0 not found".)  ISTM the point of having

Are we mostly concerned about the case where it's 0 or all cases
where the constrrelid relation doesn't open?

> this hack is to allow old dump files to be loaded, and an ERROR would
> get in the way of that.

Yeah, I'd been thinking that they weren't in a transaction so
an error would just not make the trigger, but that's not a safe
assumption.

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
>> (We can tweak the RI triggers to make the runtime failure message be
>> more helpful than "Relation 0 not found".)  ISTM the point of having

> Are we mostly concerned about the case where it's 0 or all cases
> where the constrrelid relation doesn't open?

I'd only special-case OID 0; we can reasonably assume that came from
a broken CREATE CONSTRAINT TRIGGER definition.  Any other failure is
harder to guess at the cause of, so we should just let the generic
error reporting do its thing.

            regards, tom lane

On Mon, 30 Sep 2002, Tom Lane wrote:

> Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
> >> (We can tweak the RI triggers to make the runtime failure message be
> >> more helpful than "Relation 0 not found".)  ISTM the point of having
>
> > Are we mostly concerned about the case where it's 0 or all cases
> > where the constrrelid relation doesn't open?
>
> I'd only special-case OID 0; we can reasonably assume that came from
> a broken CREATE CONSTRAINT TRIGGER definition.  Any other failure is

What do we want to tell the user?  I was thinking we could give something
like:

 Trigger "blah" has no target table. You will need to remove the
triggers for this foreign key constraint and add the constraint again
using ALTER TABLE ADD CONSTRAINT.

Stephan Szabo <sszabo@megazone23.bigpanda.com> writes:
>> I was inclined to think it should take the constraint and let the
>> failure occur at runtime.  A NOTICE would be okay but not an ERROR.

> That's easy enough to do (changing a a false to true and an ERROR
> to NOTICE I believe)

OK, applied this second version of the patch (with some minor cleanups).

If CREATE TRIGGER is unable to resolve the missing FROM clause, you get
something like

psql:r72.dump:81: NOTICE:  Unable to find table for constraint "<unnamed>"

while loading the dump, and then

z=# insert into zot values(22);
ERROR:  No target table given for trigger "RI_ConstraintTrigger_149654" on "zot"
        Remove these RI triggers and do ALTER TABLE ADD CONSTRAINT
z=#

when the trigger is fired (instead of "Relation 0 unknown").

If anyone's got an idea for a better one-liner hint for that error message,
I'm open to suggestions.

            regards, tom lane