PostgreSQL: Possible causes for "deadlock detected" error

Question

we are currently getting deadlocks on the following trigger, which is being called concurrently by several threads at time (don't know how to give an exact thread count).

Any hints would be appreciated, either on discovering the lock cause, or even better, on solving the issue.-

This is running on Enterprise DB 9.2.1.3 (PostgreSQL) on RHEL 6.4

Current code:

CREATE OR replace FUNCTION reports.update_cobertura
  () returns TRIGGER
AS
  $body$declare has_signal BOOLEAN;
  ip inet;
  id_proveedor INTEGER;
  mejora       BOOLEAN;
  thislat      NUMERIC;
  thislng      NUMERIC;
BEGIN
  has_signal := NEW.tipo='R';
  SELECT avl_instantaneo.ip
  INTO   ip
  FROM   avl_instantaneo
  WHERE  imei=NEW.imei
  AND    avl_instantaneo.ip<>'';

  IF ip IS NOT NULL THEN
    SELECT reports.proveedor_red.id_proveedor
    INTO   id_proveedor
    FROM   reports.proveedor_red
    WHERE  ip<<= red;

    IF id_proveedor IS NOT NULL THEN
      FOR decimales IN 0..4
      LOOP
        BEGIN
          thislat := round(NEW.latitud:: NUMERIC,decimales);
          thislng := round(NEW.longitud::NUMERIC,decimales);
          IF has_signal THEN
            INSERT INTO reports.cobertura
                        (
                                    lat,
                                    lng,
                                    ONLINE,
                                    id_provider,
                                    zoom
                        )
                        VALUES
                        (
                                    thislat,
                                    thislng,
                                    1,
                                    id_proveedor,
                                    decimales
                        );

          ELSE
            INSERT INTO reports.cobertura
                        (
                                    lat,
                                    lng,
                                    OFFLINE,
                                    id_provider,
                                    zoom
                        )
                        VALUES
                        (
                                    thislat,
                                    thislng,
                                    1,
                                    id_proveedor,
                                    decimales
                        );

          END IF;
        EXCEPTION
        WHEN integrity_constraint_violation THEN
          BEGIN
            thislat := round(NEW.latitud:: NUMERIC,decimales);
            thislng := round(NEW.longitud::NUMERIC,decimales);
            IF has_signal THEN
              UPDATE reports.cobertura
              SET    ONLINE=ONLINE+1
              WHERE  id_provider=id_proveedor
              AND    lat=thislat
              AND    lng=thislng
              AND    zoom=decimales;

            ELSE
              UPDATE reports.cobertura
              SET    OFFLINE=OFFLINE+1
              WHERE  id_provider=id_proveedor
              AND    lat=thislat
              AND    lng=thislng
              AND    zoom=decimales;

            END IF;
          EXCEPTION
          WHEN OTHERS THEN
            RAISE warning 'unknown exception on update: % / %',SQLERRM, SQLSTATE;
          END;
        WHEN restrict_violation THEN
          RAISE warning 'restrict_violation: % / %',SQLERRM, SQLSTATE;
        WHEN unique_violation THEN
          RAISE warning 'unique_violation: % / %',SQLERRM, SQLSTATE;
        WHEN check_violation THEN
          RAISE warning 'check_violation: % / %',SQLERRM, SQLSTATE;
        WHEN exclusion_violation THEN
          RAISE warning 'exclusion_violation: % / %',SQLERRM, SQLSTATE;
        WHEN deadlock_detected THEN
          RAISE warning 'deadlock_detected: % / %',SQLERRM, SQLSTATE;
          --raise;
        WHEN OTHERS THEN
          RAISE warning 'unknown exception: % / %',SQLERRM, SQLSTATE;
        END;
      END LOOP;
    END IF;
  END IF;
  RETURN NEW;
END;
$body$ LANGUAGE plpgsql volatile cost 100;

Referenced table:

CREATE TABLE reports.cobertura
         (
                      lat DOUBLE PRECISION NOT NULL,
                      lng DOUBLE PRECISION NOT NULL,
                      id_provider INTEGER NOT NULL,
                      zoom        INTEGER NOT NULL,
                      online      BIGINT DEFAULT 0,
                      OFFLINE     BIGINT DEFAULT 0,
                      CONSTRAINT cobertura_pkey PRIMARY KEY (lat, lng, id_provider),
                      CONSTRAINT cobertura_id_provider_fkey FOREIGN KEY (id_provider) REFERENCES reports.proveedor_movil (id_proveedor) match simple ON
         UPDATE no action
         ON
         DELETE no action
         )
         WITH
         (
                oids=FALSE
         );

  -- Index: reports.cobertura_update
  -- DROP INDEX reports.cobertura_update;CREATE INDEX cobertura_update
  ON reports.cobertura
  using        btree
               (
                            lat,
                            lng,
                            id_provider,
                            zoom
               );

Answer 1

You may find this query useful to identify the deadlocking transactions:

with locks as (
select
  pg_stat_activity.datname, application_name,
  substr(pg_class.relname, 1, 32) relname, page, tuple,
  pg_locks.transactionid as txid, 
  pg_locks.virtualtransaction as vtxid,
  locktype, pg_locks.mode, pg_locks.granted, pg_stat_activity.usename,
  substr(pg_stat_activity.query, 1, 255), pg_stat_activity.query_start,
  age(now(), pg_stat_activity.query_start) as "age",
  pg_stat_activity.pid -- , pg_locks.*
from pg_stat_activity, pg_locks
left outer join pg_class on (pg_locks.relation=pg_class.oid)  
where
  pg_locks.pid=pg_stat_activity.pid
  and pg_stat_activity.pid != pg_backend_pid()
  and (not granted OR pg_locks.mode like '%Exclusive%')
order by query_start
)
select * from locks a where not granted
or exists (select * from locks b where not b.granted
  and COALESCE(a.relname, '')=COALESCE(b.relname, '')
  and COALESCE(a.page, 0)=COALESCE(b.page, 0)
  and COALESCE(a.tuple, 0)=COALESCE(b.tuple, 0)
  and COALESCE(a.txid, '0'::xid)=COALESCE(b.txid, '0'::xid)
  -- and COALESCE(a.vtxid, '')=COALESCE(b.vtxid, '')
);