Thread: advisory locks in stored procedures

Hello,

I’ve got a large multi-process/multi-threaded VOIP application, that uses UDP ports all over the place (internal communication, SIP ports, RTP ports, etc).

Because of the opportunity for port duplication, we decided to have the ports allocated from a table/stored procedure from our postgres database; and to avoid duplication, we used advisory locks inside the stored procedure.

It’s a simple function; it does a few un-related things, but the meat of it is:

--snip—

create or replace function fc_system_next_session() returns smallint as $$

declare

       u_port smallint;

begin

       perform pg_advisory_lock(1);

       select into u_port id from udp_ports where status = 0 limit 1;

       if not found then

              perform perform pg_advisory_unlock(1);

              return 0;

       end if;

       update udp_ports set status = 1 where id = u_port;

       if not found then

              perform perform pg_advisory_unlock(1);

              return 0;

       end if;

.. do some other stuff here ..

       perform pg_advisory_unlock(1);

       return u_port;

end;

$$ language plpgsql;

--snip—

But this doesn’t seem to work- I end up getting duplicate ports returned when the application starts, and forks()’s off processes.

Changing the “perform pg_advisory_lock(1);“ line to “lock table udp_ports in SHARE ROW EXCLUSIVE mode;“ makes the function work fine.

I realize I can use a select .. for update, but I’d prefer to use advisory locks if possible.

Do advisory locks work inside functions?

Could it be related somehow to PERFORM instead of SELECT?

Any thoughts would be greatly appreciated.

Cheers,

Mike