Re: FK plans cached? Known bug?

Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> Tom Lane wrote:
>> Yeah, we've always done so. I'm a bit surprised though that it's
>> letting you drop the index --- isn't that index required for the FK
>> constraint? Exactly what is the constraint anyway?

> create table a (a int primary key);
> create table b (a bigint not null references a);
> create index b_a on b(a);

Oh, OK, that index is on the referencing side. The index that the
constraint depends on is just a's pkey:

regression=# alter table a drop constraint a_pkey;
NOTICE: constraint b_a_fkey on table b depends on index a_pkey
ERROR: cannot drop constraint a_pkey on table a because other objects depend on it
HINT: Use DROP ... CASCADE to drop the dependent objects too.

Anyway, this is just one of the reasons we've needed plan invalidation
for so long. There's basically nothing to be done about it pre-8.3.

> The problem I was actually investigating was that pgstats does not seem
> to count certain scans of the index on the b table. I haven't been able
> to reproduce the bug.

Strange. It could easily be version-specific though --- did you try the
same version they're using?

regards, tom lane