Where to look for possible deadlock causes? We've been having several problems the past days in our production environment, and some of them seem to boil down to database issues.

Just realized we are having deadlocks (68 actually), on database "waypoint":



Can anyone please provide hints on where / how to search for possible deadlock causes?

select version();
=&gt; PostgreSQL 9.4.5 on x86_64-unknown-linux-gnu, compiled by gcc (Debian 4.9.2-10) 4.9.2, 64-bit


Found this evidence in log:

2016-03-06 06:58:39 UTC [11807-1] waypointtx@waypoint ERROR:  deadlock detected
2016-03-06 06:58:39 UTC [11807-2] waypointtx@waypoint DETAIL:  Process 11807 waits for ShareLock on transaction 370917997; blocked by process 11788.
    Process 11788 waits for ShareLock on transaction 370917865; blocked by process 11807.
    Process 11807: select * from telemetria_data.insert_tabla_instantaneo($1,$2,$3,$4,$5,$6 ) as result
    Process 11788: select * from telemetria_data.insert_tabla_instantaneo($1,$2,$3,$4,$5,$6 ) as result
2016-03-06 06:58:39 UTC [11807-3] waypointtx@waypoint HINT:  See server log for query details.
2016-03-06 06:58:39 UTC [11807-4] waypointtx@waypoint CONTEXT:  while updating tuple (2836,88) in relation "instantaneo"
    SQL statement "UPDATE telemetria_data.instantaneo SET utc = '2016-03-06 06:58:34', registro=now(), power=0, voltaje = 0, valor ='{2147}'::double precision [] WHERE imei=354676055362536 and clase=40;"
    PL/pgSQL function telemetria_data.insert_tabla_instantaneo(bigint,timestamp without time zone,integer,double precision[],integer,double precision) line 13 at EXECUTE statement
2016-03-06 06:58:39 UTC [11807-5] waypointtx@waypoint STATEMENT:  select * from telemetria_data.insert_tabla_instantaneo($1,$2,$3,$4,$5,$6 ) as result


So it seems that the following part is the problem:

UPDATE telemetria_data.instantaneo ....... WHERE imei=354676055362536 and clase=40;


Am I right?

Possible offending function:

CREATE OR REPLACE FUNCTION telemetria_data.insert_tabla_instantaneo(
    bigint,
    timestamp without time zone,
    integer,
    double precision[],
    integer,
    double precision)
  RETURNS boolean AS
$BODY$
DECLARE 
    imei ALIAS FOR $1;
    utc ALIAS FOR $2;
    clase ALIAS FOR $3;
    valor ALIAS FOR $4;
    power ALIAS FOR $5;
    voltaje ALIAS FOR $6;
    num_rows int;

BEGIN

    EXECUTE 'UPDATE telemetria_data.instantaneo SET utc = ''' || utc || ''', registro=now(), power='|| power ||', voltaje = '|| voltaje ||', valor =''{' || array_to_string(valor,',')  ||'}''::double precision [] WHERE imei='|| imei ||' and clase=' || clase || ';';


    GET DIAGNOSTICS num_rows = ROW_COUNT;
    IF num_rows &gt; 0 THEN
    RETURN TRUE;
    ELSE

    EXECUTE 'insert into telemetria_data.enabled_units (imei,clase) values ('|| imei ||','|| clase ||');';
    EXECUTE 'insert into telemetria_data.instantaneo (imei,utc,clase,valor,registro,power,voltaje) values ('|| imei ||',''' || utc || ''','|| clase ||',''{' || array_to_string(valor,',')  ||'}''::double precision [],now(),'|| power ||','|| voltaje ||');'; 

    RETURN TRUE;
    END IF;


    EXCEPTION
    WHEN UNIQUE_VIOLATION THEN
        EXECUTE 'insert into telemetria_data.instantaneo (imei,utc,clase,valor,registro,power,voltaje) values ('|| imei ||',''' || utc || ''','|| clase ||',''{' || array_to_string(valor,',')  ||'}''::double precision [],now(),'|| power ||','|| voltaje ||');'; 
        RETURN TRUE;

    WHEN foreign_key_violation THEN
        RETURN FALSE;







END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


Related table structure:

CREATE TABLE telemetria_data.instantaneo
(
  imei bigint NOT NULL,
  utc timestamp without time zone NOT NULL,
  clase integer NOT NULL,
  valor double precision[],
  registro timestamp without time zone DEFAULT now(),
  power integer DEFAULT (-1),
  voltaje double precision DEFAULT 0.0,
  CONSTRAINT pk_telemetria_data_instantaeo PRIMARY KEY (imei, clase),
  CONSTRAINT fk_telemetria_data_clase_id FOREIGN KEY (clase)
      REFERENCES telemetria_data.clase (id) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=FALSE
);
Seem to have resulved the issue adding a SELECT FOR UPDATE to pre-lock the affected rows, as follows:

CREATE OR REPLACE FUNCTION telemetria_data.insert_tabla_instantaneo(
    bigint,
    timestamp without time zone,
    integer,
    double precision[],
    integer,
    double precision)
  RETURNS boolean AS
$BODY$
DECLARE 
    imei ALIAS FOR $1;
    utc ALIAS FOR $2;
    clase ALIAS FOR $3;
    valor ALIAS FOR $4;
    power ALIAS FOR $5;
    voltaje ALIAS FOR $6;
    num_rows int;
dummy INTEGER;
BEGIN

    SELECT 1 into dummy FROM telemetria_data.instantaneo a WHERE a.imei=$1 and a.clase=$3 FOR UPDATE;
    EXECUTE 'UPDATE telemetria_data.instantaneo SET utc = ''' || utc || ''', registro=now(), power='|| power ||', voltaje = '|| voltaje ||', valor =''{' || array_to_string(valor,',')  ||'}''::double precision [] WHERE imei='|| imei ||' and clase=' || clase || ';';


    GET DIAGNOSTICS num_rows = ROW_COUNT;

    IF num_rows &gt; 0 THEN
    RETURN TRUE;
    ELSE

    EXECUTE 'insert into telemetria_data.enabled_units (imei,clase) values ('|| imei ||','|| clase ||');';
    EXECUTE 'insert into telemetria_data.instantaneo (imei,utc,clase,valor,registro,power,voltaje) values ('|| imei ||',''' || utc || ''','|| clase ||',''{' || array_to_string(valor,',')  ||'}''::double precision [],now(),'|| power ||','|| voltaje ||');'; 

    RETURN TRUE;
    END IF;


    EXCEPTION
    WHEN UNIQUE_VIOLATION THEN
        EXECUTE 'insert into telemetria_data.instantaneo (imei,utc,clase,valor,registro,power,voltaje) values ('|| imei ||',''' || utc || ''','|| clase ||',''{' || array_to_string(valor,',')  ||'}''::double precision [],now(),'|| power ||','|| voltaje ||');'; 
        RETURN TRUE;

    WHEN foreign_key_violation THEN
        RETURN FALSE;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;


There are not that many deadlocks, but they are still appearing as you can see in the following graph: