perl and SQL question

[Anton]

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




-- 
Anton,
инженер отдела управления сетью связи,
ООО "ИКА" (Томика) 634050 г. Томск
пр. Ленина 55, оф. 101
Тел: 701-855