2011/6/19 Alexander Farber <alexander.farber@xxxxxxxxx>: > Hello Cedric and others, > > On Sun, Jun 19, 2011 at 9:56 PM, Cédric Villemain > <cedric.villemain.debian@xxxxxxxxx> wrote: >> 2011/6/19 Alexander Farber <alexander.farber@xxxxxxxxx>: >>> [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@xxxxxxxxxxxxxx) > 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 -- Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general