From: Stephan Szabo <sszabo@megazone.bigpanda.com>
To: Kenneth Gonsalves <lawgon@thenilgiris.com>
Cc: Phil Endecott <spam_from_postgresql_sql@chezphil.org>
Cc: pgsql-sql@postgresql.org
Subject: Re: finding if a foreign key is in use
Date: Wed, 30 Jun 2004 23:54:16 -0700 (PDT)
Message-ID: <20040630232712.P15907@megazone.bigpanda.com> (raw)
In-Reply-To: <0407010743031D.02481@thenilgiris.com>
References: <0406290618410W.02481@thenilgiris.com>
<7679232@chezphil.org>
<0407010743031D.02481@thenilgiris.com>
On Thu, 1 Jul 2004, Kenneth Gonsalves wrote:
> On Tuesday 29 June 2004 07:19 pm, Phil Endecott wrote:
> > Kenneth Gonsalves <lawgon@thenilgiris.com> wrote:
> > > in my app i have a table where the id serves as a foreign key for
> > > one or more other tables. if i want to delete a row in the table,
> > > i currently search the other tables where this table is referenced
> > > to see if the row is in use - and then, if not in use, permit
> > > deletion.
>
> > > Now if i want the delete button in my app to be disabled whenever
> > > a row that is in use is selected, searching the database every time
> > > would dramatically slow down the app.
> >
> > Basically you do have to do this search. But it won't be too slow if you
> > create an index on the foreign key.
>
> pity. thought postgres would have some function like 'in_use' to tell when a
> row that is used as a foreign key is in actual use and hence cannot be
> deleted. surely, in a database of millions of records, it wouldnt have search
> them all to find if the row is in use? in my case the id field serves as a
Well, it's kind of a losing proposition either way.
One way (the one we take right now) involves checking rows on the fktable
on pk update/delete which is hoped to be fast if the user creates
appropriate indexes but has issues if that plan isn't fast.
The other would probably involve trying to keep track of known in_use.
There are some problems there however in knowing what that value should
be. For example after a delete of a row in the fktable, do you know what
in_use is on its referenced row without finding all things that reference
it and checking them (and what about seeing or locking stuff for
concurrent transactions?) You can use a known in_use vs unknown in_use vs
known not in use style value to get around that, but then I think there'd
still be issues in guaranteeing the correct semantics and I think rows
may often end up in unknown state at which point you scan anyway.
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, lawgon@thenilgiris.com, spam_from_postgresql_sql@chezphil.org
Subject: Re: finding if a foreign key is in use
In-Reply-To: <20040630232712.P15907@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