Skip site navigation (1) Skip section navigation (2)

Re: FK plans cached? Known bug?

From: Alvaro Herrera <alvherre(at)commandprompt(dot)com>
To: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
Cc: Bugs <pgsql-bugs(at)postgresql(dot)org>
Subject: Re: FK plans cached? Known bug?
Date: 2007-05-18 17:52:01
Message-ID: 20070518175201.GB15691@alvh.no-ip.org (view raw or flat)
Thread:
Lists: pgsql-bugs
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

In response to

Responses

pgsql-bugs by date

Next:From: Tom LaneDate: 2007-05-18 18:08:42
Subject: Re: FK plans cached? Known bug?
Previous:From: Tom LaneDate: 2007-05-18 17:44:46
Subject: Re: FK plans cached? Known bug?

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group