Delete referenced entries on update

All we need is an easy explanation of the problem, so here it is.

Let’s say I have a bunch of trains and each train has a schedule. A schedule is basically a list of stops and some metadata. So in JSON I would have something like

{
    "train_no": 12345,
    "schedule": [
        {"stop_id": 54321, "station": "foo"},
        {"stop_id": 54322, "station": "bar"}
    ]
}

I want to have this data in postgres, so I create two tables:

CREATE TABLE trains (
    train_no integer PRIMARY KEY,
    schedule_id integer UNIQUE
);
CREATE TABLE stops (
    stop_id integer PRIMARY KEY,
    schedule_id integer REFERENCES trains (schedule_id) ON DELETE CASCADE,
    station text
);

Now I insert a bunch of trains and their schedules, and when a train gets cancelled, I just delete the train, e.g. with DELETE FROM trains WHERE train_no = 12345 and all the stops that were associated with that train are being deleted too. Great!

But what happens when a train is delayed and the schedule changes? If I insert the new schedule with

INSERT INTO trains VALUES (12345, 54323) ON CONFLICT (train_no) 
DO UPDATE SET schedule_id = EXCLUDED.schedule_id

I would expect the stops from the old schedule to be dropped, since the schedule_id has changed. But that doesn’t seem to work. When I add ON UPDATE CASCADE to the stops table, it doesn’t delete the now deprecated schedule, instead it changes the schedule_id to the new value (which is wrong, I want to insert a different schedule instead).

This problem seems so simple when you look at it in terms of objects: just replace the list of stops which is a property of the train. How do I solve this problem in postgres?

How to solve :

I know you bored from this bug, So we are here to help you! Take a deep breath and look at the explanation of your problem. We have many solutions to this problem, But we recommend you to use the first method because it is tested & true method that will 100% work for you.

Method 1

If you are happy with this kind of implementation of schedule replacement, where the old schedule is gone forever, then I can see no problem with – nor, indeed, a better option other than – doing the replacement in two steps: first deleting the row with the old schedule and then inserting a new one with the new schedule.

DELETE FROM trains WHERE train_no = 12345;
INSERT INTO trains VALUES (12345, 54323);

Wrap them both in a single transaction to ensure atomicity of the operation. The first statement will cause removal of the dependent stops rows, as defined by the foreign key, and the second one will introduce the new schedule, ready to be referenced by new stops, which presumably are inserted immediately afterwards (and it would make sense for those inserts to be in the same transaction too).

Method 2

I think ist os best to add to more fileds.

  1. Delayed

    here you can the minutes ot a time that is expected, that is btter because the people still can search for start and end and know that it is delayed and how much

  2. Canceledd

    here also, rthe people can still search and know, that it is canceled

I would also recomend a scheduled event, that deletes everything that is older than 2 days, so the users can stll search for a short time all the information there is about it

Note: Use and implement method 1 because this method fully tested our system.
Thank you 🙂

All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

Leave a Reply