Postgresql – Delete data from multiple related tables

deletepostgresql

I am using a PostgreSQL server. Suppose I have 200 values in a master table that need to be deleted. There are 9 other tables referencing this master table. The other 9 tables not only reference the master table, but reference other tables also. The 200 values in the master table are spread out in other 9 tables.

I want to find out if any of the 200 values are present in all the 10 tables and delete them.

Suppose the master table has id = 1 and this record needs to be deleted. Apart from deleting the value in this table, I need to check the remaining 9 tables whether this value is present or not and delete those records also.

For example, if I need to delete the second table, the second table references 3 tables. One is the master table and other two are different tables. That is why I am in a fix.

How can I do this?

Some of the tables have been declared with on delete no action. I am not in a position to change that. Need to find another way without doing that.

Also, when I use foreign keys and delete the other tables, the other tables have been referenced by some other tables also. It is like a chain.

Best Answer

You can use Jailer tool, it will find all rows of child tables that reference to master table.

Example: I have 4 tables : employee, employee_detail, phone_address, relationship. I want to delete employee which name="JOHN". With Jailer, it will find rows in employee_detail & relationship which FK to "JOHN" (by id). And because of phone_address reference to employee_detail, so Jailer will find rows in phone_address.

tb_employee ----(1,n)----tb_employee_detail----(1,n)----tb_phone_address 
            ----(1,n)----tb_relationship

CONSIDERATION #1

Using DELETE FROM tbl ... LIMIT 5000 is not replication-safe because the 5000 rows being deleted may not be the same order or the same set of 5000 rows on a slave. See MySQL Documentation (Replication and LIMIT). Should a DELETE on a Master get interrupted, it has to rollback. In that event, it could be remotely possible that the number of times the DELETE FROM tbl ... LIMIT 5000 is called may not be the same. Extra calls of the DELETE on a Slave is not critical. Too few calls could be bad. This should not happen.

SUGGESTION : Make sure the row count for RTesAll is identical on the Master and all Slaves.

CONSIDERATION #2

Since you are running a Stored Procedure on the Master, the DELETE command will not begin its execution on a Slave until the completion of the DELETE on the Master and the DELETE command is posted in the Master's Binary Logs. With one Master and 4 Slaves (5 DB Servers), there are a certain of calls to DELETE multiplied by 5.

SUGGESTION #1 : You should add set sql_log_bin = 0; to the Stored Procedure

DROP PROCEDURE IF EXISTS PurgeOlderData;
DELIMITER $$
CREATE PROCEDURE `PurgeOlderData`(In StartDate DATETIME ,In EndDate DATETIME,In NoOfRecordsToDelete BIGINT,In TableName CHAR(50))
BEGIN
    SET sql_log_bin             = 0;
    SET @delete_counter             = 0;
    SET @table_name             = TableName;
    SET @number_of_records_to_delete    = NoOfRecordsToDelete;
    SET @start_date             = StartDate;
    SET @end_date               = EndDate;
    
    WHILE @delete_counter < @number_of_records_to_delete DO
        SET @varSQL = CONCAT('DELETE FROM ', @table_name,' WHERE recordDate BETWEEN \'',@start_date ,'\' AND \'', @end_date ,'\' LIMIT 5000;');
        PREPARE stmt FROM @varSQL;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        /*SELECT @varSQL;*/
        SET @delete_counter = @delete_counter + 5000;
    END WHILE;          
    SET sql_log_bin             = 1;
END $$
DELIMITER ;

to prevent all DELETEs from replicating. If you are deleting 611992998 rows, deleting every 5000 rows requires executing DELETE ... LIMIT 5000 122399 times (That's 611992998 / 5000 rounded up to next integer).

This will prevent having to write to the binlogs 122399 times.

SUGGESTION #2 : Run the Stored Procedure on the Master and Slaves at the same time.

This prevents replication lag
This gets rid of all the rows at the same time
This is best done during maintenance cycles or slow read times

CONSIDERATION #3

Make sure autocommit is enabled on Master and Slaves. Otherwise, all the deletes will pile up in the undo logs inside ibdata1 before either doing all the deletes at once as a single transaction or a huge rollback.

Give it a Try !!!

PostgreSQL – Cascade Deletes from Multiple Tables

Some clarifications:

REFERENCES is the key word used for a FOREIGN KEY constraint (which allows to cascade DELETE or UPDATE).
Your database design seems to have logical flaws. rating seems like a detail of the main table restaurant. Since you have a 1:1 relationship, you could just include the "rating" columns in the main table. If you need a separate table, you would include a restaurant_id in the rating table rather than the other way round.
Your "rating" columns average, vote_count and vote_sum indicate another table vote and these values are derived aggregates. A MATERIALIZED VIEW would be the typical solution for that. Either as separate rating table or in combination with columns from each main table ...

The clean way would be to have a separate rating table for each main table. Then you can have a FK constraint on each with ON DELETE CASCADE.

If you still need your current design, I have two ideas:

1. Multiple FK columns in one `rating` table

With FK constraints reversed to point in the right direction, the table could look like this:

CREATE TABLE rating (
  rating_id  serial PRIMARY KEY
, vote_count int
, vote_sum   int
, average    float8
, restaurant_id int UNIQUE REFERENCES restaurant(restaurant_id)
                    ON UPDATE CASCADE ON DELETE CASCADE
, picture_id int UNIQUE REFERENCES picture(picture_id)
                 ON UPDATE CASCADE ON DELETE CASCADE
-- more references to other tables
);

The UNIQUE constraints enforce your 1:1 requirement. Each row in each master table can have at most one row in rating. Also creates very useful indexes automatically.

Alternatively, if you have several main tables, you might want to create partial unique indexes instead to exclude irrelevant rows from each index:

CREATE UNIQUE INDEX rating_restaurant_id ON rating (restaurant_id)
WHERE restaurant_id IS NOT NULL;
-- etc.

To enforce that exactly one FK column per row is NOT NULL:

ALTER TABLE rating ADD CONSTRAINT exactly_one_fk CHECK (
      (restaurant_id IS NOT NULL)::int
    + (picture_id    IS NOT NULL)::int = 1);  -- add more ...

With a couple of master tables, it may look like a lot of wasted storage but it really is not. A bunch of NULL columns hardly cost anything. NULL storage is very cheap:

How do completely empty columns in a large table affect performance?

2. Inheritance

Since your motivation is to avoid repeating this rating schema, inheritance could be a good solution for you. A limitation of table inheritance is that foreign keys of the parent table are not inherited - which turns into an advantage for your case:

CREATE TABLE rating (
  rating_id  serial PRIMARY KEY
, vote_count int
, vote_sum   int
, average    float8
);

CREATE TABLE restaurant_rating (
   restaurant_id int PRIMARY KEY REFERENCES restaurant(restaurant_id)
                                 ON UPDATE CASCADE ON DELETE CASCADE
) INHERITS (rating);

CREATE TABLE picture_rating (
   picture_id int PRIMARY KEY REFERENCES picture (picture_id)
                              ON UPDATE CASCADE ON DELETE CASCADE
) INHERITS (rating);

-- more?

Now you have to define the basic schema only once and inherit from it.
You still have a common PK column for all ratings with a single attached sequence.
Each child table adds the ID of the master table as PK and FK, thus enforcing your 1:1 relationship and providing the important index on the column automatically.
You can query the master table to get all ratings at once. If you need to know from which child table each row originates:
```
SELECT tableoid::regclass::text AS origin, *
FROM   rating;
```
- Get all partition names for a table
- Retrieve all records from multiple schemas
You might want to add a rule or trigger to disallow inserts into the master table rating directly.

SQL Fiddle.

Best Answer

Related Solutions

Mysql – Delete data from huge InnoDB table from MySQL Master and Slaves