Foreign key bugs (Re: "New" bug?? Serious - crashes backend.)

ryan <ryan(at)bel(dot)bc(dot)ca> writes:
> I have a database which reliable crashes the database system. When you
> try to do insert/update on the table 'machines' the backend crashes with
> what I think is signal 11.

The problem seems to be that you have a foreign-key trigger on
'machines' which refers to a non-existent primary-key relation:

> CREATE CONSTRAINT TRIGGER "siteid" AFTER INSERT OR UPDATE ON
> "machines" NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
> PROCEDURE "RI_FKey_check_ins" ('siteid', 'machines', 'site',
> 'UNSPECIFIED', 'sites', 'siteid');

The 'site' argument is the name of the referenced table, and you
have no table named site.

There are at least two bugs here: the immediate cause of the crash
is lack of a check for heap_openr() failure in the RI trigger code,
but a larger question is why the system let you drop a table that
is the target of a referential integrity check (which I assume is
what you did to get into this state).

Anyway, dropping the siteid trigger, as well as any others that
refer to gone tables, ought to get you out of trouble for now.
Meanwhile the foreign-key boys have some work to do ...

regards, tom lane