Re: Further thoughts about warning for costly FK checks

Bruce Momjian <pgman(at)candle(dot)pha(dot)pa(dot)us> writes:
> Looking at what we have, we know every table will get some inserts, and
> we know every insert will have to check the primary key. What we don't
> know is if there will be any modifications or deletes to the primary
> key.

Yeah. It's possible that the user has deliberately omitted the index
because he knows that PK deletions will be nonexistent, or at least so
infrequent that it's not worth maintaining an index on the FK column.
So there are use-cases for it, it's just not the normal case. (This
is also the reason not to automatically create the supporting FK index
during ADD CONSTRAINT, which is an idea I'd toyed with briefly.)

> Call me crazy, but maybe we have to throw a message for primary
> key lookups on foreign key tables without indexes. I hate to throw a
> message on update/delete rather than create table, but I don't see
> another way.

I don't think that will fly. It's too noisy/repetitive, and it's
complaining at the wrong time (in production rather than when you're
setting up the DB schema). Imagine how annoying it would be to get
such warnings if you were a user without the privileges needed to create
the requested index.

regards, tom lane