Re: FK plans cached? Known bug?

Tom Lane wrote:
> Alvaro Herrera <alvherre(at)commandprompt(dot)com> writes:
> > (This is on 8.2) What is happening here -- are we caching the plan for
> > the FK check query?
>
> 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);

insert into a select generate_series(1, 1000000);
insert into b select generate_series(1, 1000000);

delete from a where a = 2;
-- fails, constraint violated

drop index b_a;

delete from a where a = 2;
-- fails, can't open index

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.

The symptom was that they found that the index had 0 on all counts in
pgstats, so they dropped it; they immediately noticed the load to raise.
This is on 8.1.4; I didn't find any message in the commit logs about
that.

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support