Re: PostgreSQL 8.4.8 bringing my website down every evening

2011/6/19 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>:
> Hello Cedric and others,
>
> On Sun, Jun 19, 2011 at 9:56 PM, Cédric Villemain
> <cedric(dot)villemain(dot)debian(at)gmail(dot)com> wrote:
>> 2011/6/19 Alexander Farber <alexander(dot)farber(at)gmail(dot)com>:
>>> [pgbouncer]
>>> logfile = /var/log/pgbouncer.log
>>> pidfile = /var/run/pgbouncer/pgbouncer.pid
>>> listen_port = 6432
>>> unix_socket_dir = /tmp
>>> auth_type = md5
>>> auth_file = /var/lib/pgsql/data/global/pg_auth
>>> pool_mode = transaction
>>> server_check_delay = 10
>>> max_client_conn = 200
>>> default_pool_size = 20
>
>>> My php script displaying player stats:
>>> http://preferans.de/user.php?id=OK493430777441
>>> will sometimes exit with the PDO error:
>>>
>>>  SQLSTATE[26000]: Invalid sql statement name:
>>>  7 ERROR: prepared statement
>>>  "pdo_stmt_00000016" does not exist
>>>
>>> try {
>>>        # enable persistent connections and throw exception on any errors
>>>        $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION,
>>> PDO::ATTR_PERSISTENT => true);
>>>        $db = new PDO('pgsql:host=' . DBHOST . ' port=6432; dbname=' .
>>> DBNAME, DBUSER, DBPASS, $options);
>>>

You have your first request here :

>>>        $sth = $db->prepare('
>>> select first_name, city, avatar, login > logout as online
>>> from pref_users where id=?
>>> ');

then the second:

>>>        $sth->execute(array($id));

in auto-commit, each request/transaction will probably be affected to
a different connection with pgbouncer in mode transaction. So you need
to have a begin/commit before/after them to be sure everything is
executed together.

>>>
>>> and so on - a dozen SELECT statements.
>>>
>> Ooops, I would have been more explicit here!
>> You need to add begin/commit to build a transaction.
>> From PHP::PDO doc:
>> http://www.php.net/manual/en/pdo.begintransaction.php
>> <?
>> /* Begin a transaction, turning off autocommit */
>> $dbh->beginTransaction();
>>
>> /* Change the database schema */
>> $sth = $dbh->exec("DROP TABLE fruit");
>>
>> /* Commit the changes */
>> $dbh->commit();
>>
>> /* Database connection is now back in autocommit mode */
>> ?>
>>
>> An alternative can be to use pre_prepare:
>> https://github.com/dimitri/preprepare
>>
>> Please read the README carefully for this one if you intend to use it.
>
> why add a begin/commit if I only
> have SELECT statements
> there (in the default mode) and
> the data isn't critical to me
> (just some player statistics and
> notes by other players - i.e.
> a statistic or note is ok to be lost
> occasionally)?
>
> Also I've changed my PHP-script
> to non-persistent connections:
>
>       $options = array(PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION);
>        $db = new PDO(sprintf('pgsql:host=%s port=%u; dbname=%s',
>                DBHOST, DBPORT, DBNAME), DBUSER, DBPASS, $options);
>
> and restarted Apache 2.2.3,
> but that error is still there:
>
> SQLSTATE[26000]: Invalid sql
> statement name: 7 ERROR: prepared
> statement "pdo_stmt_0000000a" does not exist
>
> Regards
> Alex
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

--
Cédric Villemain               2ndQuadrant
http://2ndQuadrant.fr/     PostgreSQL : Expertise, Formation et Support