PHP and pgbouncer in transaction mode: current transaction is aborted

Question

I run a Drupal 7.2 web site embedding a flash game with few custom PHP scripts for the player stats. Using CentOS 5.6/64 bit, PostgreSQL 8.4.8 and PHP 5.3. It is a Quad-Opteron with 4GB RAM.

At the peak times (when there are around 500 players online) my web site used to went down with too many postmaster processes. On the advice of pgsql-general mailing list I have installed pgbouncer 1.3.4 with the following /etc/pgbouncer.ini:

[databases]
pref = host=/tmp user=pref password=XXX dbname=pref

[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
;pool_mode = session

server_check_delay = 10

max_client_conn = 200
default_pool_size = 16

log_connections = 0
log_disconnections = 0
log_pooler_errors = 1

And have increased shared_buffers = 1024MB and decreased max_connections = 50 in postgresql.conf.

This has helped, but I have often a problem that a prepared PDO statement won't be found:

SQLSTATE[26000]: Invalid sql statement name: 7 ERROR: prepared statement "pdo_stmt_00000016" does not exist

• probably because pgbouncer switches the connection between a prepare() and execute().

I can't switch pgbouncer to session mode - my web site will hang.

I've tried adding PDO::ATTR_EMULATE_PREPARES => true - my web site hangs too.

I've added beginTransaction() and commit() around each prepare() and execute() call - but then I get the following error very often:

SQLSTATE[25P02]: In failed sql transaction: 7 ERROR:  current transaction is aborted, commands ignored until end of transaction block

Below is an excerpt of my code failing with that error - it is very straightforward and just calls five SELECT statements:

function fetch_top() {
        $table       = '';
        $top         = '';

        try {
                # throw exception on any errors
                $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);

                # last week's winner
                $db->beginTransaction();
                $sth = $db->prepare("
select u.id,
        u.first_name,
        u.avatar,
        u.female,
        u.city,
        m.money,
        u.login > u.logout as online
from pref_users u, pref_money m where
        m.yw=to_char(current_timestamp - interval '1 week', 'IYYY-IW') and
        u.id=m.id
order by m.money desc
limit 1
");
                $sth->execute();
                $winner = $sth->fetch(PDO::FETCH_OBJ);
                $db->commit();

                $db->beginTransaction();
                $sth = $db->prepare('
select count(id) from (
    select id,
           row_number() over(partition by yw order by money desc) as ranking
    from pref_money
) x
where x.ranking = 1 and id=?
');
                $sth->execute(array($winner->id));
                $winner_medals = $sth->fetchColumn();
                $db->commit();

                # current week leader
                $db->beginTransaction();
                $sth = $db->prepare("
select u.id,
        u.first_name,
        u.avatar,
        u.female,
        u.city,
        m.money,
        u.login > u.logout as online
from pref_users u, pref_money m where
        m.yw=to_char(current_timestamp, 'IYYY-IW') and
        u.id=m.id
order by m.money desc
limit 1
");
                $sth->execute();
                $leader = $sth->fetch(PDO::FETCH_OBJ);
                $db->commit();

                $db->beginTransaction();
                $sth = $db->prepare('
select count(id) from (
    select id,
           row_number() over(partition by yw order by money desc) as ranking
    from pref_money
) x
where x.ranking = 1 and id=?
');
                $sth->execute(array($leader->id));
                $leader_medals = $sth->fetchColumn();
                $db->commit();

                # fetch top players
                $db->beginTransaction();
                $sth = $db->prepare("
select u.id,
        u.first_name,
        u.female,
        u.city,
        m.money,
        u.login > u.logout as online
from pref_users u, pref_money m where
        m.yw=to_char(current_timestamp, 'IYYY-IW') and
        u.id=m.id
order by m.money desc
limit 7
");
                $sth->execute();
                $i = 0;
                while ($player = $sth->fetch(PDO::FETCH_OBJ)) {
                        $top .= user_link($player) . ($i++ > 0 ? '' : '&nbsp;&raquo;') . '<br />';
                }
                $db->commit();

                # create the HTML table
                $table = sprintf('.... skipped for brevity ....');
        } catch (Exception $e) {
                exit('Database problem: ' . $e->getMessage());
        }

        return $table;
}

Answer 1

I do not use PDO, but using prepared statements with pgBouncer in session mode does work for me. I just need to set "server_reset_query = DISCARD ALL" for the preapred statements to work correctly. Can you set pool_mode to session and also set the above mentioned variable?