为了测试我的PostgreSQL热备复制方案中的各种设置,我需要重现以下错误情况:
因此,我尝试将两个进程合并为一个进程,该进程使用其相反值不断更新布尔字段,另一个进程从副本中读取该值。
更新脚本如下(
据我所知,我尝试重现的错误是因为在请求相当陈旧的数据的读副本上执行了 postgresql 的
SQLSTATE[40001]: Serialization failure: 7 ERROR: canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
因此,我尝试将两个进程合并为一个进程,该进程使用其相反值不断更新布尔字段,另一个进程从副本中读取该值。
更新脚本如下(
loopUpdate.php
):$engine = 'pgsql';
$host = 'mydb.c3rrdbjxxkkk.eu-central-1.rds.amazonaws.com';
$database = 'dummydb';
$user = 'dummyusr';
$pass = 'dummypasswd';
$dns = $engine.':dbname='.$database.";host=".$host;
$pdo = new PDO($dns,$user,$pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);
echo "Continious update a field on et_store in order to cause new row version.".PHP_EOL;
while(true)
{
$pdo->exec("UPDATE mytable SET boolval= NOT boolval where id=52");
}
以下是读取脚本(./loopRead.php
)的代码:
$engine = 'pgsql';
$host = 'mydb_replica.c3rrdbjxxkkk.eu-central-1.rds.amazonaws.com';
$database = 'dummydb';
$user = 'dummyusr';
$pass = 'dummypasswd';
$dns = $engine.':dbname='.$database.";host=".$host;
$pdo = new PDO($dns,$user,$pass, [
PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
]);
echo "Continious update a field on et_store in order to cause new row version.".PHP_EOL;
while(true)
{
$value=$pdo->exec("SELECT id, boolval FROM mytable WHERE id=52");
var_dump($value);
echo PHP_EOL;
}
然后我并行执行它们:
# From one shell session
$ php ./loopUpdate.php
# From another one shell session
$ php ./loopRead.php
mydb_replica.c3rrdbjxxkkk.eu-central-1.rds.amazonaws.com
是 mydb.c3rrdbjxxkkk.eu-central-1.rds.amazonaws.com
的热备只读副本。
但是我无法让 loopRead.php
失败并显示以下错误:
SQLSTATE[40001]: Serialization failure: 7 ERROR: canceling statement due to conflict with recovery
DETAIL: User query might have needed to see row versions that must be removed.
据我所知,我尝试重现的错误是因为在请求相当陈旧的数据的读副本上执行了 postgresql 的
VACUUM
操作,而此时正在进行一个活动的读事务。那么,我如何使我的 select 语句选择已经过时的行版本呢?