I am trying to implement a type 2 slowly changing dimension in a PG data warehouse using a trigger function.

The basic idea is that when a new row is inserted, we should check whether NEW is a duplicate (according to a subset of the columns) of an old row. If the row doesn't exists, we insert it. If the row is a full duplicate, we do not insert it.

The tricky part is if the row is a duplicate on the ID columns, but some of the information has changed. In that case I want to fetch the last available date for that customer in the related fact table (named usage), and save it as the transition_date. The transition_date then becomes the end date of the old_row record and the start date of the NEW record.

Easy right?

The issue is that when copying a bunch of new records into the table, I hit a deadlock.

[11309]:ERROR: deadlock detected

[11309]:DETAIL: Process 11309 waits for ShareLock on transaction 23620; blocked by process 31978.
Process 31978 waits for ShareLock on transaction 23603; blocked by process 11309.

Process 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)

Process 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

[11309]:HINT: See server log for query details.

[11309]:CONTEXT: while updating tuple (4048,2) in relation "cust_loc"
SQL statement "UPDATE cust_loc SET cust_loc_info_end_date = transition_date, cust_loc_is_current = FALSE 
WHERE cust_loc_id = old_row.cust_loc_id"
PL/pgSQL function cust_loc_record_update() line 47 at SQL statement
COPY cust_loc, line 147761: "9999,9999,,,,..."

Obviously the issue is with the update, but I don't know enough about PG to say what calls are likely to be locking the row.

It could be another external call trying to access at the same time, but most likely I think I am just locking myself within this function somehow. The fun part is in the ELSE clause if you want to dive in.

CREATE OR REPLACE FUNCTION cust_loc_record_update() RETURNS trigger AS $cust_loc_record_update$

DECLARE old_row cust_loc%ROWTYPE;
DECLARE transition_date DATE;
    -- If the cust_loc record is NOT already in the table
    IF NOT EXISTS (SELECT 1 FROM cust_loc 
                WHERE cust_id_from_utility = NEW.cust_id_from_utility
                AND location_id_from_utility = NEW.location_id_from_utility
                AND utility_name = NEW.utility_name) THEN

        RAISE NOTICE 'Inserting new row by default';
        RETURN NEW;
    -- If the NEW row has the same info as an old one, do not insert it!
    -- Note: the columns listed here are the ones for which we care about detecting changes
                   WHERE (cust_id_from_utility, location_id_from_utility, utility_name, cust_loc_hhsize, cust_loc_irr_area_sf,
                            cust_loc_class_from_utility, cust_loc_has_variance, cust_loc_water_type, cust_loc_apn, 
                            cust_loc_meter_size, cust_loc_pressure_zone, et_zone_from_utility)
                   -- we want null = null to be TRUE
                   IS NOT DISTINCT FROM 
                        (NEW.cust_id_from_utility, NEW.location_id_from_utility, NEW.utility_name, NEW.cust_loc_hhsize, NEW.cust_loc_irr_area_sf,
                            NEW.cust_loc_class_from_utility, NEW.cust_loc_has_variance, NEW.cust_loc_water_type, NEW.cust_loc_apn, 
                            NEW.cust_loc_meter_size, NEW.cust_loc_pressure_zone, NEW.et_zone_from_utility)) THEN
        RAISE NOTICE 'Discarding new row';
        RETURN NULL;
    -- Else a similar outdated record is already in the table and we need to update it
        SELECT * INTO old_row FROM cust_loc 
                WHERE cust_id_from_utility = NEW.cust_id_from_utility
                AND location_id_from_utility = NEW.location_id_from_utility
                AND utility_name = NEW.utility_name
                ORDER BY cust_loc_info_end_date DESC LIMIT 1;

        RAISE NOTICE 'just grabbed old row: %', old_row;

        transition_date := (SELECT max(make_date(usage_year, usage_month, 1)) FROM public.usage 
                                     WHERE cust_id_from_utility = old_row.cust_id_from_utility
                                     AND location_id_from_utility = old_row.location_id_from_utility
                                     AND utility_name = old_row.utility_name);

        RAISE NOTICE 'transition date: %', transition_date;

        -- If the old record's end date must be set
        IF old_row.cust_loc_info_end_date IS NULL THEN
            -- Update the old record
            UPDATE cust_loc SET cust_loc_info_end_date = transition_date, cust_loc_is_current = FALSE 
                WHERE cust_loc_id = old_row.cust_loc_id;
            RAISE NOTICE 'Old row end date was NULL';
        -- Old record already has an end date?          
            UPDATE cust_loc SET cust_loc_is_current = FALSE 
                WHERE cust_loc_id = old_row.cust_loc_id;
            RAISE NOTICE 'Old row end date was already set';
        END IF;

        NEW.cust_loc_info_start_date := transition_date + INTEGER '1';

        RAISE NOTICE 'Inserting new row: %', NEW;
        RETURN NEW;
    END IF; 
$cust_loc_record_update$ LANGUAGE plpgsql;

I have heard that indexes can also cause deadlocks. I have an btree indexes on cust_id_from_utility and location_id_from_utility, in both the cust_loc table (my dimension table) and the usage table (my fact table). The PK for the cust_loc table is cust_loc_id.

  • 2
    It looks like two such processes overlap in time. Can this be the case (i. e. you start a new import before the previous one finishes)? Or is it some other bunch of UPDATEs?
    – dezso
    Oct 4, 2016 at 7:07
  • @dezso I think you are right that it could be a different process that also updates the same table. I was thinking that it could also be due to the use of copy (via PGAdmin "import"). If the copy command inserts all of the rows in a single transaction, then maybe the first time a row is encountered it locks and stays locked until the end of the copy transaction? Then if the same row tries to get updated later on in the transaction there is a deadlock? Really just guessing, lol. Oct 4, 2016 at 19:22


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.