如何手动触发“由于恢复错误与冲突,取消语句”错误以进行PostgreSQL复制方案?

3
为了测试我的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.commydb.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 语句选择已经过时的行版本呢?
2个回答

4
在待机状态下,将max_standby_streaming_delay设置为0,将hot_standby_feedback设置为off
然后在备用节点上启动一个事务:
SELECT *, pg_sleep(10) FROM atable;

在主服务器上从atable中删除行并使用VACUUM(VERBOSE)清理它。确保已删除一些行。

然后,您应该能够观察到复制冲突。


0
为了引发错误,您需要通过使用 pg_delay PostgreSQL 函数在选择查询本身中放置一个巨大的延迟,从而将查询更改为以下内容:
SELECT id, boolval, pg_sleep(1000000000) FROM mytable  WHERE id=52

因此,在单个事务中,您有一个“重”查询,并最大化了导致PostgreSQL序列化错误的可能性。

尽管细节会有所不同:

DETAIL:  User was holding shared buffer pin for too long.

在这种情况下,请尝试将pg_delay值从1000000000减少到10

网页内容由stack overflow 提供, 点击上面的
可以查看英文原文,
原文链接