postgresql用的是9.4版本,驱动也用到9.4版本,jdk用的1.7
我们的系统前端有一个删除项目的操作,后台用的spring boot+spring mvc+mybatis,删除操作实际调用一个postgresql函数,函数部分执行语句如下:
CREATE OR REPLACE FUNCTION delete_eplus_project_cascade(projectid character varying)
RETURNS void AS
$BODY$
BEGIN
--1. delete task_item_contents
delete from eplus_task_item_contents etic using eplus_task_item eti, eplus_task et
where etic.task_item_id = eti.id and eti.task_id = et.id and et.project_id = projectid;
--2. delete task_item
delete from eplus_task_item eti using eplus_task et where eti.task_id = et.id and et.project_id = projectid;
--3. delete eplus_repair_record_item
delete from eplus_repair_record_item erri using eplus_repair_record err,eplus_task et
where erri.repair_id = err.id and err.task_id = et.id and et.project_id = projectid;
--4. delete eplus_repair_record
delete from eplus_repair_record err using eplus_task et where err.task_id = et.id and et.project_id = projectid;
--5. delete eplus_task
delete from eplus_task et where et.project_id = projectid;
........
现象是前端点击删除后,后台执行较慢,可能是数据比较多,或者index设置不合适?虽然执行较慢,但有的项目可以删除成功,有的报下面的错,随机性的:
Error querying database. Cause: org.postgresql.util.PSQLException: ERROR: canceling statement due to user request
Where: SQL statement "DELETE FROM ONLY "public"."eplus_task_item_contents" WHERE $1 OPERATOR(pg_catalog.=) "task_item_id""
SQL statement "delete from eplus_task_item eti using eplus_task et where eti.task_id = et.id and et.project_id = projectid"
PL/pgSQL function delete_eplus_project_cascade(character varying) line 8 at SQL statement
网上看到类似的问题求助帖,发现9.2的版本貌似出过这个错误,但现在我用的9.4版本已经修复了这个问题,而且我的数据库设置基本都是默认的,更没有改过statement-timeout这个属性,业务处调用代码如下:
@Transactional()
public void delete(String projectId) {
projectDao.delete(projectId);
//standardService.deleteByProjectId(projectId);
}