Duplicate calendar events

[LnLcFlx]

After #426 was fixed, many duplicates of movie release dates started appearing. First some movies only had the same release date twice, then triple ...

I don't know if these are actually dates on TMDB or just the same redundant dates.

[IgnisDa]

Thanks for reporting this. Happened on my instance too, but I did not notice it. I will fix it.

[IgnisDa]

Blocked by SeaQL/sea-query#478 or SeaQL/sea-orm#1938.

Note to self:

create unique index on calendar_event (date, metadata_id, coalesce(metadata_extra_information, 'i')); should work.

Create 2 partial indices:

• when metadata_extra_information is NULL
• when it is NOT NULL

If the above PR is not merged, I will have to go the hacky way of inserting an empty string into the metadata_extra_information for non shows/podcasts. This utility might be useful.

[IgnisDa]

@LnLcFlx This will likely take some time to be fixed since it depends on an upstream problem. For the time being, if it is really bothering you, you can run this SQL to remove duplicate elements.

It worked on my instance, but make sure you backup your DB just in case.

CREATE OR REPLACE FUNCTION process_event_ids() RETURNS VOID AS $$
DECLARE
    rec record; id2 integer;
BEGIN
    FOR rec IN
        SELECT date, metadata_id, ARRAY_AGG(id) AS event_ids
        FROM calendar_event
        WHERE metadata_extra_information IS NULL
        GROUP BY date, metadata_id
        HAVING COUNT(*) > 1
    LOOP
        FOREACH id2 IN ARRAY rec.event_ids[2:]
        LOOP
            RAISE NOTICE 'Processing event_id %', id2;
            DELETE FROM calendar_event where id = id2;
        END LOOP;
    END LOOP;
END;
$$ LANGUAGE plpgsql;
SELECT process_event_ids();
DROP FUNCTION process_event_ids;

[IgnisDa]

@LnLcFlx can you confirm if everything works as expected now?

[LnLcFlx]

Prior to updating, I have deleted all duplicates using your SQL.
But so far I did not encounter any new duplicates.
Will report if it still happens.