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
Date: Tue, 21 Mar 2006 10:44:11 -0800 (PST)
Message-ID: <20060321103843.D76370@megazone.bigpanda.com> (raw)
In-Reply-To: <20060321181140.35234.qmail@web50303.mail.yahoo.com>
References: <20060321181140.35234.qmail@web50303.mail.yahoo.com>
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: sszabo@megazone.bigpanda.com, ogjunk-pgjedan@yahoo.com
Subject: Re: Referential integrity broken (8.0.3), sub-select help
In-Reply-To: <20060321103843.D76370@megazone.bigpanda.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox