Thread: FK plans cached? Known bug?

I know that in 8.3 we have the new plan caching mechanism which fixes
this, but I am wondering if this particular problem was known:

alvherre=# delete from a where a = 2;
ERROR:  update o delete en «a» viola la llave foránea «b_a_fkey» en «b»
DETAIL:  La llave (a)=(2) todavía es referida desde la tabla «b».

alvherre=# drop index b_a;
DROP INDEX
alvherre=# delete from a where a = 2;
ERROR:  could not open relation with OID 16433
CONTEXT:  sentencia SQL: «SELECT 1 FROM ONLY "public"."b" x WHERE "a" = $1 FOR SHARE OF x»

(This is on 8.2)  What is happening here -- are we caching the plan for
the FK check query?

--
Alvaro Herrera                                http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.

Alvaro Herrera <alvherre@commandprompt.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?

            regards, tom lane

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.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

Alvaro Herrera <alvherre@commandprompt.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

Tom Lane wrote:
> Alvaro Herrera <alvherre@commandprompt.com> writes:

> > 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?

Yeah, we made some experiments and couldn't reproduce it there either.
We wondered about execution of prepared queries not being counted, but
apparently it wasn't that either.

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