Postgres-on conflict do 引起的core宕问题

最新推荐文章于 2024-01-10 10:03:20 发布

pigcoffee

最新推荐文章于 2024-01-10 10:03:20 发布

阅读量408

点赞数

分类专栏： PostgreSQL 文章标签：数据库

本文链接：https://blog.csdn.net/hqwang4/article/details/125610958

版权

PostgreSQL 专栏收录该内容

12 篇文章 0 订阅

订阅专栏

问题描述

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($$%s$$,$$%s$$,$$%s$$,$$%s$$,$$%s$$)',_preName,_schema,_cuName,_cuKey,_cuId,_value)) into flag;