Re: [GENERAL] Delete all records NOT referenced by Foreign Keys
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] Delete all records NOT referenced by Foreign Keys
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]
Re: [GENERAL] Delete all records NOT referenced by Foreign Keys
On Sat, Dec 13, 2003 at 02:20:15 -0600, D. Dante Lorenso [EMAIL PROTECTED] wrote: I'd like to run a clean up command on my tables to eliminate rows that I'm no longer using in the database. I want to do something like this: DELETE FROM tablename WHERE IS_REFERENCED_BY_FOREIGN_KEY IS FALSE; Does anyone know how something like this could be done in PostgreSQL? I know I can search all the tables that I know refer to this table and see if my primary key exists, but I want a solution that does not require me to rewrite my code every time a new foreign key constraint is added to the database. There must be a way to ask PostgreSQL for a reference count on a given row or something. If you are more concerned about flexibility than speed you can do something like the following: Set all of your foreign key references to the desired table to use an on delete restrict clause. Have your application read all of the key values from the desired table and for each key issue a delete of that key in its own transaction. This will fail for keys that are referenced (because of the restrict clause). A more complicated, less future proof, but more efficient approach would be to have your application find out which tables have references to the table of interest by looking at the system catalog and then write a delete query using appropiate where not exist clauses. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings
Re: [GENERAL] Delete all records NOT referenced by Foreign Keys
Bruno Wolff III wrote: On Sat, Dec 13, 2003 at 02:20:15 -0600, D. Dante Lorenso [EMAIL PROTECTED] wrote: I'd like to run a clean up command on my tables to eliminate rows that I'm no longer using in the database. I want to do something like this: DELETE FROM tablename WHERE IS_REFERENCED_BY_FOREIGN_KEY IS FALSE; Does anyone know how something like this could be done in PostgreSQL? I know I can search all the tables that I know refer to this table and see if my primary key exists, but I want a solution that does not require me to rewrite my code every time a new foreign key constraint is added to the database. There must be a way to ask PostgreSQL for a reference count on a given row or something. If you are more concerned about flexibility than speed you can do something like the following: Set all of your foreign key references to the desired table to use an on delete restrict clause. Have your application read all of the key values from the desired table and for each key issue a delete of that key in its own transaction. This will fail for keys that are referenced (because of the restrict clause). 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. Does anyone know if there is any way to say something like: DELETE FROM tablename IGNORE ERRORS; Where a delete that is possible is performed but ones that throw referencial integrity voilations would silently fail without abandoning the entire transaction? I have the 'on delete restrict' clause on my foreign keys already. A more complicated, less future proof, but more efficient approach would be to have your application find out which tables have references to the table of interest by looking at the system catalog and then write a delete query using appropiate where not exist clauses. ---(end of broadcast)--- TIP 7: don't forget to increase your free space map settings ---(end of broadcast)--- TIP 4: Don't 'kill -9' the postmaster
Re: [GENERAL] Delete all records NOT referenced by Foreign Keys
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. -- Martijn van Oosterhout [EMAIL PROTECTED] http://svana.org/kleptog/ (... have gone from d-i being barely usable even by its developers anywhere, to being about 20% done. Sweet. And the last 80% usually takes 20% of the time, too, right?) -- Anthony Towns, debian-devel-announce pgp0.pgp Description: PGP signature
