Re: Referential integrity broken (8.0.3), sub-select help

On Tue, 21 Mar 2006 ogjunk-pgjedan(at)yahoo(dot)com wrote:

> I mistakenly swapped the tables in my email. Here they are, corrected:
>
> Table "url":
> id SERIAL
> CONSTRAINT pk_url_id PRIMARY KEY
>
> Table "bookmark":
> url_id INTEGER
> CONSTRAINT fk_url_id REFERENCES url(id)
>
> I see my questions got chopped off from this email below, so let me restate them:
>
>
> Problem #1: Strange that PG allowed this to happen. Maybe my DDL above
> allows this to happen and needs to be tightened? I thought the above
> would ensure referential integrity, but maybe I need to specify
> something else?

That seems like it should have worked. I don't know of any cases that'd
fail without referential actions (there are some cases with actions and
before triggers or rules), so if you have any leads, that'd be useful.

> Problem #2: I'd like to find all rows in B that point to non-existent
> rows in U. I can do it with the following sub-select, I believe, but
> it's rather inefficient (EXPLAIN shows both tables would be sequentially
> scanned):
>
> SELECT * FROM bookmark WHERE id NOT IN (SELECT b.id FROM bookmark b, url u WHERE b.url_id=u.id);
>
> Is there a more efficient way to get the rows from "bookmark"?

I think something like the following would work

SELECT * FROM bookmark WHERE url_id NOT IN (SELECT id FROM url u);

Raising work_mem may help get a better plan as well.