Deleting records from two-child tables in parallel (concurrently)
0
votes
1
answer
316
views
I have a postgres database where I am using the pg_cron extension to automatically perform some database "maintenance" at night time - Specifically, my database has some "offers" in it with timestamps, so I move those records to archive tables if they are "expired", and then delete the original records from my primary tables.
Anyway, I have two-child tables that both have Foreign Keys that are referencing the Primary Key of the parent-table. Maybe this diagram will help:
pg_cron lets you use these "background_workers", which from my understanding of being a Java Developer, are like Threads and basically let you run SQL scripts (i.e. functions/procedures) in parallel/concurrently. Thus, I was trying to leverage this and perform as many of my INSERTS/DELETES scripts concurrently (if possible).
The problem I'm having now is that when my Cron job triggers some of my DELETE scripts ("script" == Postgres plpgsql procecure) to run for these three tables, I have it coded such that:
1. the child tables delete BEFORE parent (but they can run in-any order AND concurrently)
2. the parent tables
deletes last (ALWAYS)
As far as I know, this avoids any weird FK-exception errors (like trying to delete from the Parent table first).
Anyway, because I am using the background workers, both the child-table delete scripts CAN AND WILL RUN CONCURRENTLY (as I stated above). I thought this was OK, but I see now, one of the scripts will run a delete on one of the child tables just fine - but whichever child table script runs slightly after, ends up never completing (the Job just shows "running" in pg_cron.job_run_details table).
***Am I not able to delete concurrently*** from the child-tables that contain a FK reference to the PK in the same parent-table ?
As I depicted on the diagram, the FK properties for both tables are:
MATCH FULL
ON DELETE NO ACTION
ON UPDATE NO ACTION
Also FYI, I did test altering the FK constraints and set the ON DELETE to "CASCADE" so I can only need 1 plpgsql Procedure but the damn Delete was too damn slow (if anyone has any tips on this maybe I can go back...)
Any help greatly appreciated!
pg_cron lets you use these "background_workers", which from my understanding of being a Java Developer, are like Threads and basically let you run SQL scripts (i.e. functions/procedures) in parallel/concurrently. Thus, I was trying to leverage this and perform as many of my INSERTS/DELETES scripts concurrently (if possible).
The problem I'm having now is that when my Cron job triggers some of my DELETE scripts ("script" == Postgres plpgsql procecure) to run for these three tables, I have it coded such that:
1. the child tables delete BEFORE parent (but they can run in-any order AND concurrently)
2. the parent tables
deletes last (ALWAYS)
As far as I know, this avoids any weird FK-exception errors (like trying to delete from the Parent table first).
Anyway, because I am using the background workers, both the child-table delete scripts CAN AND WILL RUN CONCURRENTLY (as I stated above). I thought this was OK, but I see now, one of the scripts will run a delete on one of the child tables just fine - but whichever child table script runs slightly after, ends up never completing (the Job just shows "running" in pg_cron.job_run_details table).
***Am I not able to delete concurrently*** from the child-tables that contain a FK reference to the PK in the same parent-table ?
As I depicted on the diagram, the FK properties for both tables are:
MATCH FULL
ON DELETE NO ACTION
ON UPDATE NO ACTION
Also FYI, I did test altering the FK constraints and set the ON DELETE to "CASCADE" so I can only need 1 plpgsql Procedure but the damn Delete was too damn slow (if anyone has any tips on this maybe I can go back...)
Any help greatly appreciated!
Asked by PainIsAMaster
(131 rep)
Sep 1, 2021, 12:37 AM
Last activity: Sep 1, 2021, 03:23 AM
Last activity: Sep 1, 2021, 03:23 AM