Hi. I have next code in perl module to communicate with postgresql: ... our $pgclientsn = "DBI:Pg:dbname=mydb;host=10.14.2.66;port=5432;"; ... sub CLONE { ... # remote psql $pgclientdbh = DBI->connect($pgclientsn, 'username', 'password',{AutoCommit => 1,pg_server_prepare => 1}); # accoutning stop insert (?) $astopins = $pgclientdbh->prepare("INSERT INTO telephonecallacct (username,acctstarttime,acctstoptime,acctsessiontime,callingstationid,calledstationid,calldirection) VALUES (?,TO_TIMESTAMP(?),TO_TIMESTAMP(?),?,?,?,?)"); ... # check service status for tel.number -- check number status $numstatcheck = $pgclientdbh->prepare("SELECT s.enabled FROM telephony_numbers AS tn, service AS s WHERE number = ? AND tn.sid = s.id"); ... } today postgres was unaccessible for 10 minits (some pg processes were core dumped). freeradius started responding "Invalid user" and stayed in this state until the freeradius restart. How should I modify perl code to restart sql connection automatically or periodically or something else to prevent sql connection problems ? If this possible at all ?
On 04-04-17 10:13, Anton wrote:
Hi.
I have next code in perl module to communicate with postgresql:
... our $pgclientsn = "DBI:Pg:dbname=mydb;host=10.14.2.66;port=5432;"; ... sub CLONE { ... # remote psql $pgclientdbh = DBI->connect($pgclientsn, 'username', 'password',{AutoCommit => 1,pg_server_prepare => 1});
# accoutning stop insert (?) $astopins = $pgclientdbh->prepare("INSERT INTO telephonecallacct (username,acctstarttime,acctstoptime,acctsessiontime,callingstationid,calledstationid,calldirection) VALUES (?,TO_TIMESTAMP(?),TO_TIMESTAMP(?),?,?,?,?)"); ... # check service status for tel.number -- check number status $numstatcheck = $pgclientdbh->prepare("SELECT s.enabled FROM telephony_numbers AS tn, service AS s WHERE number = ? AND tn.sid = s.id"); ... }
today postgres was unaccessible for 10 minits (some pg processes were core dumped). freeradius started responding "Invalid user" and stayed in this state until the freeradius restart.
How should I modify perl code to restart sql connection automatically or periodically or something else to prevent sql connection problems ? If this possible at all ?
You probably shouldn't. The CLONE sub is executed every time a thread is spawned [1], so the code snippet you posted is probably wrong (it might be cropped too heavy, but it looks as if the database inserts are performed inside the CLONE sub). You *might* be able to move $pgclientdbh to an "our" variable in the outer scope of the file and do something like "reconnect() unless $pgclientdbh->ping" I have no idea if it works or not. But looking at your code once again: I don't see anything here that can't be done by the sql module. And even if it has, you can still make it more robust by moving the select and insert statements to specific sql-modules and storing results in temporary variables. This removes the database handling from the perl script. [1] http://perldoc.perl.org/perlguts.html#Should-I-do-anything-special-if-I-call... -- Herwin Weststrate
Herwin, I have "->prepare" in CLONE, so INSERTs and SELECTs are not executed in CLONE functions. They are only prepared on sql-server for next execution in more effective way. There is only one place I know for "sql prepare" -- CLONE function. Thanks for You reply. Spawned theads did not help me for some unknown reason. On Tue, 4 Apr 2017 10:23:55 +0200 Herwin Weststrate <herwin@quarantainenet.nl> wrote:
On 04-04-17 10:13, Anton wrote:
Hi.
I have next code in perl module to communicate with postgresql:
... our $pgclientsn = "DBI:Pg:dbname=mydb;host=10.14.2.66;port=5432;"; ... sub CLONE { ... # remote psql $pgclientdbh = DBI->connect($pgclientsn, 'username', 'password',{AutoCommit => 1,pg_server_prepare => 1});
# accoutning stop insert (?) $astopins = $pgclientdbh->prepare("INSERT INTO telephonecallacct (username,acctstarttime,acctstoptime,acctsessiontime,callingstationid,calledstationid,calldirection) VALUES (?,TO_TIMESTAMP(?),TO_TIMESTAMP(?),?,?,?,?)"); ... # check service status for tel.number -- check number status $numstatcheck = $pgclientdbh->prepare("SELECT s.enabled FROM telephony_numbers AS tn, service AS s WHERE number = ? AND tn.sid = s.id"); ... }
today postgres was unaccessible for 10 minits (some pg processes were core dumped). freeradius started responding "Invalid user" and stayed in this state until the freeradius restart.
How should I modify perl code to restart sql connection automatically or periodically or something else to prevent sql connection problems ? If this possible at all ?
You probably shouldn't. The CLONE sub is executed every time a thread is spawned [1], so the code snippet you posted is probably wrong (it might be cropped too heavy, but it looks as if the database inserts are performed inside the CLONE sub).
You *might* be able to move $pgclientdbh to an "our" variable in the outer scope of the file and do something like "reconnect() unless $pgclientdbh->ping" I have no idea if it works or not.
But looking at your code once again: I don't see anything here that can't be done by the sql module. And even if it has, you can still make it more robust by moving the select and insert statements to specific sql-modules and storing results in temporary variables. This removes the database handling from the perl script.
[1] http://perldoc.perl.org/perlguts.html#Should-I-do-anything-special-if-I-call...
-- Anton, инженер отдела управления сетью связи, ООО "ИКА" (Томика) 634050 г. Томск пр. Ленина 55, оф. 101 Тел: 701-855
participants (2)
-
Anton -
Herwin Weststrate