Início / dba / Perguntas / 21760
Accepted
ThinkingMonkey
Asked: 2012-08-01 07:10:17 +0800 CST

PostgreSQL: Isso é devido a bloqueios (nível de linha)?

  • 772

Tenho uma tabela com aproximadamente 2,1 milhões de tuplas. Que tem colunas de latitude e longitude. Estou tentando converter isso em um tipo geográfico (apontar com SRID).

A função (procedimento) que escrevi funciona bem quando limito as entradas (digamos: 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' ;
  • Quando tento executá-lo em toda a tabela, o PostgreSQL parece não fazer nada. Esperei por uma hora e meia.
  • Consome 99% da CPU no núcleo em que está rodando.
  • Não gera nenhuma outra instância do PostgreSQL para utilizar outros núcleos (já que a solicitação é de um único usuário?).
  • Isso é por causa de bloqueios (nível de linha)?
  • Como contornar isso?
postgresql spatial

2 respostas

  1. Best Answer

    Executar uma instrução DML dentro de um loop nunca é uma boa ideia. Você está multiplicando a quantidade de trabalho a ser feito. Bancos de dados relacionais são melhores ao operar em conjuntos, quando você faz um loop, você está operando em uma única linha por vez.

    Você pode conseguir o mesmo fazendo a atualização em uma única instrução:

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

    Não sou 100% sobre a sintaxe para calcular o ponto real, pois não uso material geométrico, mas acho que você entenderá.

    • 6

  2. Sugiro a você que não faça todo o trabalho em apenas uma função, pois função é uma transação no PostgreSQL, tudo feito ou tudo não feito! E se a tabela for muito grande, você pode levar muitas horas para realizar esse trabalho, e durante o processo, toda a linha da tabela está bloqueada ( bloqueio de nível de linha)。

    Então, sugiro o seguinte método, podemos fazer esse trabalho a cada 1.000 ou 10.000 linhas, para que não precisemos bloquear toda a linha da tabela (bloqueio em nível de linha), e isso tem um pequeno impacto no banco de dados de produção。

    --1 cria tabela 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 criar função 

    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

    consultando a tabela tmp_location。

    --3 vi func_file.sql

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

    você precisa escrever muitas linhas do "select convertlatlon ();", se você tiver 2 milhões de linhas na tabela, então você deve escrever 2000 (2000000/1000) linhas no arquivo func_file.sql

    - -4 executa a função

    --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"

    - -função de execução

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

    você deve executar o trabalho em segundo plano, porque leva muito tempo.

    • 0

relate perguntas