Re: PostgreSQL 8.4.8 bringing my website down every evening

On Mon, Jun 20, 2011 at 5:08 AM, Amitabh Kant <amitabhkant(at)gmail(dot)com> wrote:
> On Mon, Jun 20, 2011 at 1:43 AM, Alexander Farber
> <alexander(dot)farber(at)gmail(dot)com> wrote:
>>
>> 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
>> >>
>
> <snip>
>>
>> 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
>>
>
> Try setting "set server_reset_query = DISCARD ALL;" in your pgbouncer
> configuration file.

No, in transaction mode the reset_query should actually be empty.

Instead you need to disable use of db-side prepared statements
by adding option PDO::ATTR_EMULATE_PREPARES => true

--
marko