PostgreSQL : Is this because of locks(row level)?

Question

I have a table with approximately 2.1 million tuples. Which has latitude and longitude columns. I am trying to convert this into a geographic type (point with SRID).

The function(procedure) I have written, works fine when I limit the entries (say : 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' ;

---

• When I try to run it on the entire table, PostgreSQL seems to do nothing. Have waited for an hour and a half.
• Consumes 99% of CPU on the core it is running.
• Does not spring any other instance of PostgreSQL to utilize other cores(since the request is from a single user?).
• Is this because of locks(row level)?
• How to circumvent this?

Answer 1

Running a DML statement inside a loop is never a good idea. You are multiplying the amount of work to be done. Relational databases are best when operating on sets, when you do a loop you are operating on a single row at a time.

You can achieve the same by doing the update in a single statement:

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

I'm not 100% about the syntax for calculating the actual point, as I don't use geometric stuff, but I guess you'll get the idea.

Answer 2

I suggest you that not do all the job in only a function , because a function is a transaction in PostgreSQL,all done or all not done ！ And if the table is very big, you may take many hours to achieve this job, and during the process, the entire row of the table is locked ( row level lock)。

So i suggets the follwing method ， we can do that job in every 1000 or 10000 rows，so that we not need to lock entire row of the table (row level lock)，and it has the small impact in the production database。

--1 create tmp table

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 function

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 

by query the table tmp_location。

--3 vi func_file.sql

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

you man need write lots of row of the "select convertlatlon();", if you have 2 million rows in the table, so you should write 2000 (2000000/1000) rows in the file func_file.sql

--4 execute the function

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

--execute function

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

you should execute the job in backgroud, because it's takes much time.