I am using the below function in a bulk insert, usually the bulk insert has around 60 rows and each row will have the below function. But from time to time I get PG::TRDeadlockDetected: ERROR: deadlock detected (see the full error below).
How can I avoid this? Can I add a EXCEPTION which will return 0 in case this happens, I will prefer not to, but the bulk insert is very convenient for me.
CREATE OR REPLACE FUNCTION "univ"."gc_title_desc"(IN _title text, IN _desc text, OUT result_id int4) RETURNS "int4"
AS $BODY$BEGIN
LOOP
BEGIN
WITH sel AS (
SELECT id
FROM univ.results
WHERE title = _title AND description = _desc
)
, ins AS (
INSERT INTO univ.results (title, description)
SELECT _title, _desc
WHERE NOT EXISTS (SELECT 1 FROM sel)
RETURNING id
)
SELECT id
FROM sel NATURAL FULL OUTER JOIN ins
INTO result_id;
EXCEPTION WHEN UNIQUE_VIOLATION THEN -- inserted in concurrent session.
RAISE NOTICE 'It actually happened!'; -- hardly ever happens
END;
EXIT WHEN result_id IS NOT NULL;
END LOOP;
END
$BODY$
LANGUAGE plpgsql
COST 100
CALLED ON NULL INPUT
SECURITY INVOKER
VOLATILE;
Error: 2015-02-25T15:11:15.078Z 3564 TID-oti3yx4ms WARN: PG::TRDeadlockDetected: ERROR: deadlock detected DETAIL: Process 15507 waits for ShareLock on transaction 14912613; blocked by process 15690. Process 15690 waits for ShareLock on transaction 14912617; blocked by process 15507. HINT: See server log for query details. CONTEXT: while inserting index tuple (12728,26) in relation "uniq_search_results_title_description" SQL statement "WITH sel AS ( SELECT id FROM univ.search_results WHERE title = _title AND description = _desc ) , ins AS ( INSERT INTO univ.search_results (title, description) SELECT _title, _desc WHERE NOT EXISTS (SELECT 1 FROM sel) RETURNING id ) SELECT id FROM sel NATURAL FULL OUTER JOIN ins" PL/pgSQL function univ.gc_title_desc(text,text) line 5 at SQL statement
Aucun commentaire:
Enregistrer un commentaire