问题描述

postgres进程异常core宕，日志信息如下

user=dbuser_monitor,db=cptest,app=dn11,client=10.4.135.57 LOG:  connection authorized: user=dbuser_monitor database=cptest

2022-07-02 16:29:44 CST [139186]: [12] user=,db=,app=,client= LOG:  server process (PID 188687) was terminated by signal 11: Segmentation fault

2022-07-02 16:29:44 CST [139186]: [13] user=,db=,app=,client= DETAIL:  Failed process was running: select upsert($1,$2,$3,$4,$5,$6)

2022-07-02 16:29:44 CST [139186]: [14] user=,db=,app=,client= LOG:  terminating any other active server processes

2022-07-02 16:29:44 CST [257355]: [1] user=[unknown],db=[unknown],app=dn11,client=10.4.135.58 LOG:  connection received: host=10.4.135.58 port=46828

2022-07-02 16:29:44 CST [257355]: [2] user=repuser,db=[unknown],app=dn11,client=10.4.135.58 FATAL:  the database system is in recovery mode

2022-07-02 16:29:44 CST [139186]: [15] user=,db=,app=,client= LOG:  all server processes terminated; reinitializing

 问题解决

分析core文件

 

 分析upsert函数

CREATE OR REPLACE FUNCTION upsert(_schema character varying, _cuname character 
BEGIN
raise info 'cu 11111 ';
vsql := 'insert into cu_'||_shardid||' (schema, cuName, cuKey, cuId, value) values ($1, $2, $3, $4, $5) on conflict (schema, cuName, cuKey, cuId) do update set value = $5 returning insertFlag'; 

_preName := cu.prepare_exe(vsql, '(text,text,text,int,text)');

execute (format('EXECUTE %s(

,,,,)',_preName,_schema,_cuName,_cuKey,_cuId,_value)) into flag;

upsert

upsert功能是PostgreSQL 9.5 引入的一项新功能：当插入遇到约束错误时可直接返回，或者改为执行UPDATE。

PostgreSQL upsert功能(insert on conflict do)的用法_DemonHunter211的博客-CSDN博客

问题解决

将on conflict功能去掉，core宕问题解决