agora inbox for pgsql-sql@postgresql.org  

help / color / mirror / Atom feed

From: Thomas Kellerer <spam_eater@gmx.net>
To: pgsql-sql@postgresql.org
Subject: Re: Getting the list of foreign keys (for deleting data from the database)
Date: Sun, 2 Aug 2015 17:20:08 +0200
Message-ID: <mplcf8$fgf$1@ger.gmane.org> (raw)
In-Reply-To: <55BE3180.5020600@splivalo.hr>
References: <55BE3180.5020600@splivalo.hr>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>

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

The generated SQL script honors the FKs and thus there is no need to drop all constraints.

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.
  
> 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".

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.

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

Thomas





-- 
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql

view thread (5+ messages)  latest in thread

Message-ID: <mplcf8$fgf$1@ger.gmane.org>
Permalink:  ../mplcf8$fgf$1@ger.gmane.org/
Also on:    postgresql.org/message-id/mplcf8$fgf$1@ger.gmane.org

reply

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: spam_eater@gmx.net
  Subject: Re: Getting the list of foreign keys (for deleting data from the database)
  In-Reply-To: <mplcf8$fgf$1@ger.gmane.org>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox