note 114822 deleted from pdo.connections by salathe
| From: | salathe@php.net | Date: | Mon, 21 Sep 2020 14:56:48 +0000 |
| Subject: | note 114822 deleted from pdo.connections by salathe | ||
| References: | 1 | Groups: | php.notes |
| Request: | Send a blank email to php-notes+get-215383@lists.php.net to get a copy of this message | ||
Note Submitter: cappytoi at yahoo dot com
----
Using PHP 5.4.26, pdo_pgsql with libpg 9.2.8 (self compiled). As usual PHP never explains some
critical stuff in documentation. You shouldn't expect that your connection is closed when you
set $dbh = null unless all you do is just instantiating PDO class. Try following:
<?php
$pdo = new PDO('pgsql:host=192.168.137.1;port=5432;dbname=anydb', 'anyuser',
'pw');
sleep(5);
$stmt = $pdo->prepare('SELECT * FROM sometable');
$stmt->execute();
$pdo = null;
sleep(60);
?>
Now check your database. And what a surprise! Your connection hangs for another 60 seconds. Now that
might be expectable because you haven't cleared the resultset.
<?php
$pdo = new PDO('pgsql:host=192.168.137.160;port=5432;dbname=platin', 'cappytoi',
'1111');
sleep(5);
$stmt = $pdo->prepare('SELECT * FROM admin');
$stmt->execute();
$stmt->closeCursor();
$pdo = null;
sleep(60);
?>
What teh heck you say at this point? Still same? Here is what you need to do to close that
connection:
<?php
$pdo = new PDO('pgsql:host=192.168.137.160;port=5432;dbname=platin', 'cappytoi',
'1111');
sleep(5);
$stmt = $pdo->prepare('SELECT * FROM admin');
$stmt->execute();
$stmt->closeCursor(); // this is not even required
$stmt = null; // doing this is mandatory for connection to get closed
$pdo = null;
sleep(60);
?>
PDO is just one of a kind because it saves you to depend on 3rd party abstraction layers. But it
becomes annoying to see there is no implementation of a "disconnect" method even though
there is a request for it for 2 years. Developers underestimate the requirement of such a method.
First of all, doing $stmt = null everywhere is annoying and what is most annoying is you cannot
forcibly disconnect even when you set $pdo = null. It might get cleared on script's termination
but this is not always possible because script termination may delayed due to slow client connection
etc.
Anyway here is how to disconnect forcibly using postgresql:
<?php
$pdo = new PDO('pgsql:host=192.168.137.160;port=5432;dbname=platin', 'cappytoi',
'1111');
sleep(5);
$stmt = $pdo->prepare('SELECT * FROM admin');
$stmt->execute();
$pdo->query('SELECT pg_terminate_backend(pg_backend_pid());');
$pdo = null;
sleep(60);
?>
Following may be used for MYSQL: (not guaranteed)
KILL CONNECTION_ID()