PHP和pgbouncer处于事务模式：当前事务被中止

Question

我运行一个 Drupal 7.2 网站，其中嵌入 一个 Flash 游戏，并为玩家提供了一些自定义 PHP 脚本统计数据。使用 CentOS 5.6/64 位、PostgreSQL 8.4.8 和 PHP 5.3。它是一款配备 4GB RAM 的四核处理器。

在高峰时期（大约有 500 名玩家在线），我的网站曾经因过多的 postmaster 进程而崩溃。根据 pgsql-general 邮件列表的建议，我已经安装了 pgbouncer 1.3 .4 具有以下 /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

并增加了shared_buffers = 1024MB并减少了max_connections = 50 postgresql.conf。

这很有帮助，但我经常遇到一个问题，即找不到准备好的 PDO 语句：

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

• 可能是因为 pgbouncer 在prepare() 和execute() 之间切换连接。

我无法将 pgbouncer 切换到会话模式 - 我的网站将挂起。

我尝试添加 PDO::ATTR_EMULATE_PREPARES =>; true - 我的网站也挂了。

我在每个prepare()和execute()调用周围添加了beginTransaction()和commit() - 但随后我经常收到以下错误：

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

下面是我的代码因该错误而失败的摘录 - 它非常简单且简单调用五个 SELECT 语句：

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

我不使用 PDO，但在会话模式下使用 pgBouncer 准备好的语句对我来说确实有用。我只需要设置“server_reset_query = DISCARD ALL”即可使 preapred 语句正常工作。您可以将 pool_mode 设置为 session 并设置上述变量吗？

Answer 2

事务池

要使准备好的语句在此模式下工作，需要 PgBouncer
在内部跟踪它们，但它不会这样做。 所以唯一的办法
在此模式下继续使用 PgBouncer 是为了禁用准备好的语句
完全。

Answer 3

1. 配置 pgbouncer 来使用事务池
2. 编写一个 PL 函数来创建您的 PREPAREed 语句
3. 让您的 PL 函数检查 pg_prepared_statements 系统视图和生成所有准备好的语句（如果有遗漏）。
4. 将 SQL 命令执行更改为：
   1. 开始
   2. SELECT create_prepared_statements();
   3. /* 做你通常会做的任何事情 */
   4. 提交

您需要调用这个尚未编写的 create_prepared_statements() PL 函数的原因是您不知道您的连接将被分派到哪个后端，也不知道您是否是后端说to 是新生成的，没有 PREPARE 语句。

根据您使用 PREPARE 语句的方式，查看 VIEW 或 PL 函数，因为它们会自动生成和缓存 PREPARE”编的声明。我建议更积极地使用 PL/pgsql 函数，但因为这是最容易维护的方法。