baB0a4UB

1[11309]:ERROR: deadlock detected
2
3[11309]:DETAIL: Process 11309 waits for ShareLock on transaction 23620; blocked by process 31978.
4Process 31978 waits for ShareLock on transaction 23603; blocked by process 11309.
5
6Process 11309: COPY public.cust_loc(cust_id_from_utility,location_id_from_utility,cust_loc_class,cust_loc_class_from_utility,cust_loc_hhsize,cust_loc_has_variance,cust_loc_water_type,cust_loc_is_master_meter,cust_loc_irr_area_sf,cust_loc_apn,cust_loc_street_num,cust_loc_street_name,cust_loc_city,cust_loc_state,cust_loc_zip,cust_loc_info_start_date,cust_loc_info_end_date,cust_loc_is_current,utility_id,utility_name,census_block_num,et_zone_from_utility,cust_loc_county,cust_loc_latitude,cust_loc_longitude,cust_loc_geocode_accuracy,cust_loc_geocode_date,cust_loc_meter_size,cust_loc_pressure_zone) FROM STDIN (FORMAT 'csv', HEADER)
7
8Process 31978: UPDATE public.cust_loc SET cust_loc_latitude = 99.99999, cust_loc_longitude = -99.99999, cust_loc_geocode_date = '2016-10-03', cust_loc_full_address = 'address' WHERE cust_loc_id = 99999
9
10[11309]:HINT: See server log for query details.
11
12[11309]:CONTEXT: while updating tuple (4048,2) in relation "cust_loc"
13SQL statement "UPDATE cust_loc SET cust_loc_info_end_date = transition_date, cust_loc_is_current = FALSE 
14WHERE cust_loc_id = old_row.cust_loc_id"
15PL/pgSQL function cust_loc_record_update() line 47 at SQL statement
16COPY cust_loc, line 147761: "9999,9999,,,,..."
17	
18CREATE OR REPLACE FUNCTION cust_loc_record_update() RETURNS trigger AS $cust_loc_record_update$
19
20DECLARE old_row cust_loc%ROWTYPE;
21DECLARE transition_date DATE;
22BEGIN
23    -- If the cust_loc record is NOT already in the table
24    IF NOT EXISTS (SELECT 1 FROM cust_loc 
25                WHERE cust_id_from_utility = NEW.cust_id_from_utility
26                AND location_id_from_utility = NEW.location_id_from_utility
27                AND utility_name = NEW.utility_name) THEN
28
29        RAISE NOTICE 'Inserting new row by default';
30        RETURN NEW;
31    -- If the NEW row has the same info as an old one, do not insert it!
32    -- Note: the columns listed here are the ones for which we care about detecting changes
33    ELSIF EXISTS (SELECT 1 FROM cust_loc
34                   WHERE (cust_id_from_utility, location_id_from_utility, utility_name, cust_loc_hhsize, cust_loc_irr_area_sf,
35                            cust_loc_class_from_utility, cust_loc_has_variance, cust_loc_water_type, cust_loc_apn, 
36                            cust_loc_meter_size, cust_loc_pressure_zone, et_zone_from_utility)
37                   -- we want null = null to be TRUE
38                   IS NOT DISTINCT FROM 
39                        (NEW.cust_id_from_utility, NEW.location_id_from_utility, NEW.utility_name, NEW.cust_loc_hhsize, NEW.cust_loc_irr_area_sf,
40                            NEW.cust_loc_class_from_utility, NEW.cust_loc_has_variance, NEW.cust_loc_water_type, NEW.cust_loc_apn, 
41                            NEW.cust_loc_meter_size, NEW.cust_loc_pressure_zone, NEW.et_zone_from_utility)) THEN
42        RAISE NOTICE 'Discarding new row';
43        RETURN NULL;
44    -- Else a similar outdated record is already in the table and we need to update it
45    ELSE 
46        SELECT * INTO old_row FROM cust_loc 
47                WHERE cust_id_from_utility = NEW.cust_id_from_utility
48                AND location_id_from_utility = NEW.location_id_from_utility
49                AND utility_name = NEW.utility_name
50                ORDER BY cust_loc_info_end_date DESC LIMIT 1;
51
52        RAISE NOTICE 'just grabbed old row: %', old_row;
53
54        transition_date := (SELECT max(make_date(usage_year, usage_month, 1)) FROM public.usage 
55                                     WHERE cust_id_from_utility = old_row.cust_id_from_utility
56                                     AND location_id_from_utility = old_row.location_id_from_utility
57                                     AND utility_name = old_row.utility_name);
58
59        RAISE NOTICE 'transition date: %', transition_date;
60
61        -- If the old record's end date must be set
62        IF old_row.cust_loc_info_end_date IS NULL THEN
63            -- Update the old record
64            UPDATE cust_loc SET cust_loc_info_end_date = transition_date, cust_loc_is_current = FALSE 
65                WHERE cust_loc_id = old_row.cust_loc_id;
66            RAISE NOTICE 'Old row end date was NULL';
67        -- Old record already has an end date?          
68        ELSE
69            UPDATE cust_loc SET cust_loc_is_current = FALSE 
70                WHERE cust_loc_id = old_row.cust_loc_id;
71            RAISE NOTICE 'Old row end date was already set';
72        END IF;
73
74        NEW.cust_loc_info_start_date := transition_date + INTEGER '1';
75
76        RAISE NOTICE 'Inserting new row: %', NEW;
77        RETURN NEW;
78    END IF; 
79END;
80$cust_loc_record_update$ LANGUAGE plpgsql;