I take it that no answer means that this is a strange problem, and within the last 24 hours or so it has turned even stranger...

Doing a backup and restore make the fields from the dropped table disappear from RDB$RELATION_FIELDS and apparently the database works correctly for a while. CREATEing and DROPping a new table a couple of times, then makes IB_SQL complain about multiple rows in singleton select when attempting to browse the database, whereas FlameRobin doesn't complain at all. A bit further investigation reveals that whilst the fields are gone from RDB$RELATION_FIELDS, they are still present in RDB$RELATION_CONSTRAINTS (i.e. we have a PK constraint that doesn't belong to any table!). gfix doesn't report anything. After another restore of the backedup database, the records in question can be deleted from RDB$RELATION_CONSTRAINTS (by now, we're working on a test database) and after this deletion everything appears to work as desired.

Now for the real question which I hope someone can answer within the next 36 hours (Thursday we intend to try it on our production database unless advised otherwise): Is it safe to simply delete stray records from RDB$RELATION_CONSTRAINTS???

Hoping for someone to enlighten us,
Set

Svein Erling Tysvær wrote:

> On one of our databases,
>
> SELECT rf.RDB$RELATION_NAME, rf.RDB$FIELD_NAME
> FROM RDB$RELATION_FIELDS rf
> WHERE NOT EXISTS(SELECT * FROM RDB$RELATIONS r
> WHERE r.RDB$RELATION_NAME = rf.RDB$RELATION_NAME)
>
> returns two records. These two records were the fields of a table referred to from a procedure
> that we recently "renamed" (we don't remember exactly how things were done, but we used ordinary
> DDL through IB_SQL to drop and recreate the table/procedure, no fiddling with system tables).
>
> Having these two records in RDB$RELATION_FIELDS makes DB Workbench complain loudly, refusing to
> show the table and view names of existing tables/views in the database (hint: a warning is more
> pleasant than an error, Martijn).
>
> Now, two questions:
>
> 1) How come this table could be dropped without dropping its fields?
> 2) Is it safe to just delete the two records in question from RDB$RELATION_FIELDS or is there a
> safer way to fix the problem?
>
> Set

[Non-text portions of this message have been removed]