|
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 |
