Re: Re: Getting the list of foreign keys (for deleting data from the database)

On 08/02/2015 05:20 PM, Thomas Kellerer wrote:
> Mario Splivalo schrieb am 02.08.2015 um 17:04:
>> Suppose I have a table_detail that has column table_id which is FK
>> pointing to table(id), I would need to do something like this:
>>
>> SELECT foo_drop_all_constraints();
>> SELECT * FROM table INTO table_copy WHERE date_created >= '2015-01-01';
>> SELECT table_detail.* INTO table_detail_copy FROM table_detail JOIN
>> table_copy ON table_detail.table_id = table_copy.id
>> DROP TABLE table;
>> DROP TABLE table_copy
>> ALTER TABLE table_copy RENAME TO table;
>> ALTER TABLE table_detail_copy TO table_detail;
>> SELECT foo_restore_all_constraints();
>>
>> Now, what am I asking is - is there a tool which would help me find all
>> the _detail tables? I know I could query pg_constraints and similar
>> views but before I go onto hacking into those I'm wondering if there is
>> something that could aid me in doing so.
>
> The SQL tool I maintain (http:://www.sql-workbench.net) has such a feature.
>
> It supports a (SQL Workbench specific) command that generates
> (recursively) the delete
> statements starting with the "root" table given a condition on the root
> table:
>
> http://www.sql-workbench.net/manual/wb-commands.html#command-gendelete

Oho! Thank you, I will check this out immediately!
>
> The generated SQL script honors the FKs and thus there is no need to
> drop all constraints.

The main reason for dropping FKs is because of the speed. It is WAY
faster to copy non-deleting data to a new (temporary) table, then drop
originating table and then rename the temporary table.

>
> In your case it would be something like:
>
> WbGenerateDelete -table=root_table -columnValue="date_created >=
> '2015-01-01'";
>
> The output is a script with the DELETEs in the right order - or at least
> it _should_.
>
> I have to admit that I had to deal with one or two really large schemas
> (> 700 tables) where the
> delete statements where not ordered properly, especially if there are
> multiple FKs to/from the
> same table.
>
> Note that the generated statements are not pretty and far from being
> efficient.

We'll see. If I can adapt/change those so that they INSERT INTO instead
of DELETE, then I'm 'riding on horse' (I'm on donkey now).

>
>> Of course, if this is not the best approach I'd appreciate different
>> views/opinions.
>
> In my experience, setting all the FKs to "on delete cascade" and
> properly indexing the FK
> columns is very often faster than doing the deletes all "manually".

All the constraints are set to 'on delete cascade' - deleting data just
from the top-most tables currently takes over 3 days to complete.

> Another option (if you need to do that very often) is to partition the
> tables by e.g. year.
> Then getting rid of all the data for a year is as simple as dropping the
> partitions for that year.

Yup, this is a very good suggestion! But for now I first need to get rid
of 'unneeded' data from the database.

> However partitioning and foreign key constraints don't work together in
> Postgres, which is a real shame.

+1

Mario

--
Mario Splivalo
mario(at)splivalo(dot)hr

"I can do it quick, I can do it cheap, I can do it well. Pick any two."