主页 / dba / 问题 / 21760
Accepted
ThinkingMonkey
Asked: 2012-08-01 07:10:17 +0800 CST

PostgreSQL：这是因为锁（行级别）吗？

  • 772

我有一张大约有 210 万个元组的表。其中有纬度和经度列。我正在尝试将其转换为地理类型（带有 SRID 的点）。

我写的函数（过程）在我限制条目时工作正常（比如 : SELECT id,longitude,latitude FROM list_of_location limit 50）。

CREATE OR REPLACE FUNCTION convertlatlon() RETURNS VOID AS $$
DECLARE rec RECORD;
BEGIN
    FOR rec IN SELECT id,longitude,latitude FROM list_of_location
    LOOP
    UPDATE list_of_location SET location= concat('SRID=4326;POINT(',rec.longitude,' ',rec.latitude,')') WHERE id=rec.id;    
    END LOOP;
END;
$$ LANGUAGE 'plpgsql' ;
  • 当我尝试在整个表上运行它时，PostgreSQL 似乎什么都不做。已经等了一个半小时。
  • 在其运行的核心上消耗 99% 的 CPU。
  • 不会启动任何其他 PostgreSQL 实例来利用其他核心（因为请求来自单个用户？）。
  • 这是因为锁（行级别）吗？
  • 如何规避这个？
postgresql spatial

2 个回答

  1. Best Answer

    在循环内运行 DML 语句从来都不是一个好主意。您正在成倍增加要完成的工作量。关系数据库在对集合进行操作时是最好的，当您执行循环时，您一次只对一行进行操作。

    您可以通过在单个语句中执行更新来实现相同的目的：

    UPDATE list_of_location
   SET location = ST_MakePoint(longitude,latitude)::geography;

    我不是 100% 了解计算实际点的语法，因为我不使用几何学的东西，但我想你会明白的。

    • 6

  2. 我建议你不要只在一个函数中完成所有工作，因为函数是 PostgreSQL 中的事务，全部完成或全部不完成！如果表很大，你可能需要花费很多时间来完成这项工作，并且在过程中，表的整行都被锁定（row level lock）。

    所以我建议采用下面的方法，我们可以每隔1000或10000行做一次，这样我们就不需要锁住表的整行（行级锁），而且对生产数据库的影响很小。

    --1 创建tmp表

    create table tmp_location  as select id,longitude,latitude FROM list_of_location;

alter table tmp_location add column flag char(1);
alter table tmp_location add primary key (id);
create index idx_tmp_location_flag on tmp_location using btree (flag);

    --2 创建函数 

    CREATE OR REPLACE FUNCTION convertlatlon() RETURNS VOID AS $$
DECLARE rec RECORD;
BEGIN
    FOR rec IN SELECT ( id,longitude,latitude FROM tmp_location where flag is null limit 1000 )

    LOOP
      UPDATE list_of_location SET location= concat('SRID=4326;POINT(',rec.longitude,' ',rec.latitude,')') WHERE id=rec.id;    
    END LOOP;

    --update flag ,so you can see the  progress of the job
      update tmp_location set flag='Y' where id=rec.id;

END;
$$ LANGUAGE 'plpgsql' ;

From this way ,we commit the job every 1000 rows，and you also can see the process

    通过查询表tmp_location。

    --3 vi func_file.sql

    \timing
select convertlatlon();
select convertlatlon();
..
..
..
select convertlatlon();

    如果表中有 200 万行，你需要写很多行的“select convertlatlon();”，所以你应该在文件 func_file.sql 中写 2000 (2000000/1000) 行

    - -4 执行函数

    --vi 1.sh

    #!/bin/bash

if [ $# != 1 ]; then
echo Usage: ./1.sh arg1
echo   Examples:
echo   1. To run session_wait.sql, enter:
echo '     1.sh  session_wait >session_wait.out'
exit 0
fi

psql -h 127.0.0.1 -d db_name -U role_name -a -c "\i $1"

    - -执行函数

        chmod a+x 1.sh
    nohup 1.sh func_file.sql > func_file.out &

    你应该在后台执行作业，因为它需要很多时间。

    • 0

相关问题