4 SELECT evergreen.upgrade_deps_block_check('1133', :eg_version);
6 \qecho Applying a unique constraint to action.transit_copy. This will
7 \qecho only effect newly created transits. Admins are encouraged to manually
8 \qecho remove any existing duplicate transits by applying values for cancel_time
9 \qecho or dest_recv_time, or by deleting the offending transits. Below is a
10 \qecho query to locate duplicate transits. Note dupes may exist accross
11 \qecho parent (action.transit_copy) and child tables (action.hold_transit_copy,
12 \qecho action.reservation_transit_copy)
14 \qecho WITH dupe_transits AS (
15 \qecho SELECT COUNT(*), target_copy FROM action.transit_copy
16 \qecho WHERE dest_recv_time IS NULL AND cancel_time IS NULL
17 \qecho GROUP BY 2 HAVING COUNT(*) > 1
19 \qecho FROM dupe_transits
20 \qecho JOIN action.transit_copy atc USING (target_copy)
21 \qecho WHERE dest_recv_time IS NULL AND cancel_time IS NULL;
25 Unique indexes are not inherited by child tables, so they will not prevent
26 duplicate inserts on action.transit_copy and action.hold_transit_copy,
27 for example. Use check constraints instead to enforce unique-per-copy
28 transits accross all transit types.
31 -- Create an index for speedy check constraint lookups.
32 CREATE INDEX active_transit_for_copy
33 ON action.transit_copy (target_copy)
34 WHERE dest_recv_time IS NULL AND cancel_time IS NULL;
36 -- Check for duplicate transits across all transit types
37 CREATE OR REPLACE FUNCTION action.copy_transit_is_unique()
38 RETURNS TRIGGER AS $func$
40 PERFORM * FROM action.transit_copy
41 WHERE target_copy = NEW.target_copy
42 AND dest_recv_time IS NULL
43 AND cancel_time IS NULL;
45 RAISE EXCEPTION 'Copy id=% is already in transit', NEW.target_copy;
49 $func$ LANGUAGE PLPGSQL STABLE;
51 -- Apply constraint to all transit tables
52 CREATE CONSTRAINT TRIGGER transit_copy_is_unique_check
53 AFTER INSERT ON action.transit_copy
54 FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
56 CREATE CONSTRAINT TRIGGER hold_transit_copy_is_unique_check
57 AFTER INSERT ON action.hold_transit_copy
58 FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
60 CREATE CONSTRAINT TRIGGER reservation_transit_copy_is_unique_check
61 AFTER INSERT ON action.reservation_transit_copy
62 FOR EACH ROW EXECUTE PROCEDURE action.copy_transit_is_unique();
66 DROP TRIGGER transit_copy_is_unique_check ON action.transit_copy;
67 DROP TRIGGER hold_transit_copy_is_unique_check ON action.hold_transit_copy;
68 DROP TRIGGER reservation_transit_copy_is_unique_check ON action.reservation_transit_copy;
69 DROP INDEX action.active_transit_for_copy;