Re: [GENERAL] Delete all records NOT referenced by Foreign Keys

[D. Dante Lorenso]

Martijn van Oosterhout wrote:

On Sat, Dec 13, 2003 at 09:48:16PM -0600, D. Dante Lorenso wrote:
 

This is something very ugly indeed and is what I'll have to resort to unless
I can find something cleaner.  Ideally, I would be able to run this cleanup
on a subset of the table data after an insert into the table.  I would like
the query to be fast, though. 
   

What about just:

delete from a where a.id not in (select id from b);

or the equivalent exists query.
 

You missed the previous part of the thread.  I have N tables that
have a foreign key to the table in question.  Tomorrow there may be
more or fewer foreign key references.  Without having to know which
tables have foreign keys on my table, I want to delete all rows
that are not used by any any other table.
PG already can block a delete when it knows that foreign key exists, so
why can't I perform a query that says...
   DELETE FROM tablename
   WHERE FOREIGN_KEY_EXISTS(oid) IS FALSE;
You see?  Something like what I seek never requires ME the developer or
DBA to know about foreign key relationships because I know that PostgreSQL
already does.
To NOT have this functionality does not cause problems, but it does cause
me to waste disk space on rows that are no longer in use.  I just want to
do some automated cleanup on tables and just leave that process running
in a crontab nightly or something.  I don't want to have to re-write the
cleanup process every time a new dependency is introduced or removed.
I think Bruno had a good idea about using the system tables to determine
relationships, but how to do that is beyond my PostgreSQL expertise at
the moment.  I just think there's gotta be an easier way, though...something
like what I describe above.
Dante

--
D. Dante Lorenso
[EMAIL PROTECTED]


---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]

Re: [GENERAL] make error Mac OS X (ar: illegal option -- s)

[Mike Coleman]

> I also have 7.4 running on 10.3 and didn't have any trouble installing it.

Same here. I built and installed REL7_4_STABLE from CVS without any problem
on 10.3. Maybe the problem starts with a later release?

-mc

-- 
Mike Coleman <[EMAIL PROTECTED]>
Five Bats Incorporated, Portland Oregon

---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
  subscribe-nomail command to [EMAIL PROTECTED] so that your
  message can get through to the mailing list cleanly

Re: [GENERAL] Delete all records NOT referenced by Foreign Keys

[Stephan Szabo]

On Sun, 14 Dec 2003, D. Dante Lorenso wrote:

> PG already can block a delete when it knows that foreign key exists, so
> why can't I perform a query that says...
>
> DELETE FROM tablename
> WHERE FOREIGN_KEY_EXISTS(oid) IS FALSE;

That's fairly different from the checks that are performed for the foreign
keys which happen after the action has happened and errors to prevent the
action from being visible.  The where clause happens long before that.  If
the above has to check each referencing table for matching rows for each
row in tablename, I'd also expect it to perform poorly.

---(end of broadcast)---
TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]