perl and SQL question

[Herwin Weststrate]

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-perl-from-multiple-threads?

-- 
Herwin Weststrate