agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: Mario Splivalo <mario@splivalo.hr>
To: pgsql-sql@postgresql.org
Subject: Getting the list of foreign keys (for deleting data from the database)
Date: Sun, 02 Aug 2015 17:04:32 +0200
Message-ID: <55BE3180.5020600@splivalo.hr> (raw)
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-sql>
I have a large, in-house built, ERP system that I need to clean up from
old/stale data.
As all the tables are FK-related I could do 'DELETE FROM' from the
top-most table (invoices, or stock documents, or whatever) to remove all
data from all the related tables, but that is, of course, extremely slow
(The datadir is around 20GB in size, and I need to remove 4/5 of the
data from the database - fiscal years 2014, 2013, 2012 and 2011 - only
2015 should remain).
Instead of doing DELETE FROM table WHERE date_created < '2015-01-01' I
was thinking of doing something like this:
SELECT foo_drop_all_constraints();
SELECT * FROM table INTO table_copy WHERE date_created >= '2015-01-01';
DROP TABLE table;
ALTER TABLE table_copy RENAME TO table;
SELECT foo_restore_all_constraints();
Of course, this is simple if I have only one table, but when there is
over 400 tables that are 'linked' with foreign keys, things get a bit
complicated.
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.
Of course, if this is not the best approach I'd appreciate different
views/opinions.
Mario
--
Mario Splivalo
mario@splivalo.hr
"I can do it quick, I can do it cheap, I can do it well. Pick any two."
--
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: <55BE3180.5020600@splivalo.hr>
Permalink: ../55BE3180.5020600@splivalo.hr/
Also on: postgresql.org/message-id/55BE3180.5020600@splivalo.hr
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: mario@splivalo.hr
Subject: Re: Getting the list of foreign keys (for deleting data from the database)
In-Reply-To: <55BE3180.5020600@splivalo.hr>
* 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