Date: Tue, 21 Mar 2006 10:44:11 -0800 (PST)
From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: ogjunk-pgjedan@yahoo.com
Cc: pgsql-sql@postgresql.org
Subject: Re: Referential integrity broken (8.0.3), sub-select help
In-Reply-To: <20060321181140.35234.qmail@web50303.mail.yahoo.com>
Message-ID: <20060321103843.D76370@megazone.bigpanda.com>
References: <20060321181140.35234.qmail@web50303.mail.yahoo.com>
MIME-Version: 1.0
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Tue, 21 Mar 2006 ogjunk-pgjedan@yahoo.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.