Re: PostgreSQL 8.4.8 bringing my website down every evening

Hello Cedric and others,

On Sun, Jun 19, 2011 at 9:56 PM, Cédric Villemain
 wrote:
> 2011/6/19 Alexander Farber :
>> [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);
>>
>>        $sth = $db->prepare('
>> select first_name, city, avatar, login > logout as online
>> from pref_users where id=?
>> ');
>>        $sth->execute(array($id));
>>
>> 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