pg_advisory_locks in a multithreaded application context

Hello,

I'm having some trouble trying to use postgresql locks. After trying
several options and reading the postgresql online documentation, I still
can't figure out what I'm doing wrong. Here's the use case:

A multithreaded application collecting adapter firmwares on a network
loads data into the following table:

CREATE TABLE firmware (
id SERIAL NOT NULL,
type CHARACTER VARYING(32),
version CHARACTER VARYING(30),
build_id INTEGER,
date CHARACTER VARYING(25),
ps_id CHARACTER VARYING(25)
);

Typically, there are a few hundred adapters, but only 5 firmwares (many
adapters should have the same firmware if the sysadmins did their jobs
properly ;-) ).

Only a single entry is required per firmware (many separate adapters can
share the same firmware by pointing to the appropriate firmware id field).
To make sure that only one entry is created per firmware, I use the
following trigger:

CREATE TRIGGER firmware_pre_insert_trigger
BEFORE INSERT ON firmware
FOR EACH ROW
EXECUTE PROCEDURE firmware_pre_insert_trigger_cb();

CREATE FUNCTION firmware_pre_insert_trigger_cb() RETURNS TRIGGER
AS $_$
DECLARE
fw_id INT;
BEGIN

SELECT fw.id FROM firmware fw INTO fw_id
WHERE (fw.type = new.type AND
fw.version = new.version AND
fw.build_id = new.build_id AND
fw.date = new.date AND
fw.ps_id = new.ps_id);

IF fw_id IS NULL THEN
-- create the non-existing firmware
RETURN new;
ELSE
-- skip firmware which already exists
RETURN NULL;
END IF;

END;
$_$
LANGUAGE PLPGSQL;

When a thread wishes to add a firmware after discovering one adapter, it
executes the following code:

PERFORM pg_advisory_lock(1);

INSERT INTO firmware (type, version, build_id, date, ps_id)
VALUES (chip_type, firm_version, firm_build_id, firm_date, firm_ps_id);

PERFORM pg_advisory_unlock(1);

Unfortunately, I still get duplicated entries using advisory locks, and
they don't seem to lock anything at all...

On the other hand, If I use builtin locks as follows without the
trigger, I get deadlocks in the server logs:

LOCK TABLE firmware IN SHARE MODE;

SELECT f.id INTO firmware_id FROM firmware f
WHERE (f.type = chip_type AND
f.version = firm_version AND
f.build_id = firm_build_id AND
f.date = firm_date AND
f.ps_id = firm_ps_id)
LIMIT 1;

IF firmware_id IS NULL THEN
INSERT INTO firmware (type, version, build_id, date, ps_id)
VALUES (chip_type, firm_version, firm_build_id, firm_date, firm_ps_id);
END IF;

Here's the log:

DETAIL: Process 7643 waits for RowExclusiveLock on relation 21060 of
database 20535; blocked by process 7593.
Process 7593 waits for RowExclusiveLock on relation 21060 of
database 20535; blocked by process 7643.
Process 7643: SELECT
add_firmware('0x08003800013731aa','hca','512.1792.0',0,'0920-10-06','BL_0010030001000');
Process 7593: SELECT
add_firmware('0x08003800013734b0','hca','512.1792.0',0,'0920-10-06','BL_0010030001000');
HINT: See server log for query details.
QUERY: INSERT INTO firmware (type, version, build_id, date, ps_id)
VALUES (chip_type, firm_version, firm_build_id, firm_date,
firm_ps_id)

Looking at #13.3.3. Deadlocks on
http://www.postgresql.org/docs/9.0/interactive/explicit-locking.html, I
guess I'm probably in the same situation, but I can't figure out how
that applies to threads and more importantly, how to deal with it.
Note that each of the 16 threads uses its own connection to the database
backend (postgresql 9.0.4)

Serializing calls to add_firmware() on the client side by putting them
in the same thread does not help much either, as there is not guarantee
that the requests will be executed in a serialized manner on the server
side. In this case, I also get deadlocks, although less frequently...

Any idea is more than welcome ;-)

Thanks

Vincent