PHP and pgbouncer in transaction mode: current transaction is aborted

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;
}

Any help please? Alex

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?

Transaction pooling

To make prepared statements work in this mode would need PgBouncer to keep track of them internally, which it does not do. So only way to keep using PgBouncer in this mode is to disable prepared statements completely.

1. Configure pgbouncer to use transaction pooling
2. Write a PL function that creates your PREPAREed statements
3. Have your PL function check the pg_prepared_statements system view and generate all of your prepared statements if any are missing.
4. Change your SQL command execution to be:
   1. BEGIN
   2. SELECT create_prepared_statements();
   3. /* Do whatever it is that you would normally do */
   4. COMMIT

The reason you need to call this yet-to-be-written create_prepared_statements() PL function is because you do not know which backend your connection is being dispatched to or whether or not the backend you are talking to was freshly spawned and has no PREPAREed statements.

Depending on how you're using your PREPARE'ed statements, look at VIEW's or PL functions since they automatically generate and cache PREPARE'ed statements. I'd suggest making more aggressive use of PL/pgsql functions, however since that's the easiest method to maintain.