[prev in list] [next in list] [prev in thread] [next in thread]
List: postgresql-general
Subject: [GENERAL] advisory locks in stored procedures
From: "Mike" <mike () fonolo ! com>
Date: 2008-08-29 23:09:17
Message-ID: 00ed01c90a2c$42dda2f0$c898e8d0$ () com
[Download RAW message or body]
This is a multipart message in MIME format.
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
[Attachment #3 (text/html)]
<html xmlns:v="urn:schemas-microsoft-com:vml" \
xmlns:o="urn:schemas-microsoft-com:office:office" \
xmlns:w="urn:schemas-microsoft-com:office:word" \
xmlns:m="http://schemas.microsoft.com/office/2004/12/omml" \
xmlns="http://www.w3.org/TR/REC-html40">
<head>
<meta http-equiv=Content-Type content="text/html; charset=us-ascii">
<meta name=Generator content="Microsoft Word 12 (filtered medium)">
<style>
<!--
/* Font Definitions */
@font-face
{font-family:Calibri;
panose-1:2 15 5 2 2 2 4 3 2 4;}
@font-face
{font-family:"Lucida Console";
panose-1:2 11 6 9 4 5 4 2 2 4;}
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
{margin:0in;
margin-bottom:.0001pt;
font-size:11.0pt;
font-family:"Calibri","sans-serif";}
a:link, span.MsoHyperlink
{mso-style-priority:99;
color:blue;
text-decoration:underline;}
a:visited, span.MsoHyperlinkFollowed
{mso-style-priority:99;
color:purple;
text-decoration:underline;}
span.EmailStyle17
{mso-style-type:personal-compose;
font-family:"Calibri","sans-serif";
color:windowtext;}
.MsoChpDefault
{mso-style-type:export-only;}
@page Section1
{size:8.5in 11.0in;
margin:1.0in 1.0in 1.0in 1.0in;}
div.Section1
{page:Section1;}
-->
</style>
<!--[if gte mso 9]><xml>
<o:shapedefaults v:ext="edit" spidmax="1026" />
</xml><![endif]--><!--[if gte mso 9]><xml>
<o:shapelayout v:ext="edit">
<o:idmap v:ext="edit" data="1" />
</o:shapelayout></xml><![endif]-->
</head>
<body lang=EN-US link=blue vlink=purple>
<div class=Section1>
<p class=MsoNormal>Hello,<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>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). <o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>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.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>It’s a simple function; it does a few un-related
things, but the meat of it is:<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'>--snip—<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'>create or replace function fc_system_next_session() returns
smallint as $$<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'>declare<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'> u_port \
smallint;<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'>begin<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'> perform \
pg_advisory_lock(1);<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'> select into u_port id from
udp_ports where status = 0 limit 1;<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'> if not found \
then<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'> \
perform perform pg_advisory_unlock(1);<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'> \
return 0;<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'> end if;<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'> update udp_ports set status
= 1 where id = u_port;<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'> if not found \
then<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'> \
perform perform pg_advisory_unlock(1);<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'> \
return 0;<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'> end if;<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal style='text-indent:.5in'><span style='font-size:8.0pt;
font-family:"Lucida Console";color:#1F497D'>.. do some other stuff here \
..<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'> perform \
pg_advisory_unlock(1);<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'> return \
u_port;<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'>end;<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'>$$ language plpgsql;<o:p></o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'><o:p> </o:p></span></p>
<p class=MsoNormal><span style='font-size:8.0pt;font-family:"Lucida Console";
color:#1F497D'>--snip—<o:p></o:p></span></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>But this doesn’t seem to work- I end up getting
duplicate ports returned when the application starts, and forks()’s off
processes.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Changing the “<span style='font-size:8.0pt;font-family:
"Lucida Console";color:#1F497D'>perform pg_advisory_lock(1);</span>“ line
to “<span style='font-size:8.0pt;font-family:"Lucida \
Console";color:#1F497D'>lock table udp_ports in SHARE ROW EXCLUSIVE \
mode;</span>“ makes the function work fine.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>I realize I can use a select .. for update, but I’d
prefer to use advisory locks if possible.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Do advisory locks work inside functions? <o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Could it be related somehow to PERFORM instead of \
SELECT?<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Any thoughts would be greatly appreciated.<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Cheers,<o:p></o:p></p>
<p class=MsoNormal><o:p> </o:p></p>
<p class=MsoNormal>Mike<o:p></o:p></p>
</div>
</body>
</html>
[prev in list] [next in list] [prev in thread] [next in thread]
Configure |
About |
News |
Add a list |
Sponsored by KoreLogic