Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?
share|improve this question

2 Answers 2

up vote 5 down vote accepted

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 = POINT(longitude,latitude);

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.

share|improve this answer
    
very moronic of me for not thinking of the single statement. –  ThinkingMonkey Jul 31 '12 at 18:24

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.

share|improve this answer
    
A function is not a (single) transaction. –  a_horse_with_no_name Aug 1 '12 at 8:03
    
I've just set up a small test, a small table with 2.1M rows. An UPDATE of every row took about 20 secs (well, the operation was a simple +, a bit simpler than in the question). Not clear from the question whether this is too much time. –  dezso Aug 1 '12 at 8:07
    
@a_horse_with_no_name - Functions and trigger procedures are always executed within a transaction established by an outer query — they cannot start or commit that transaction, since there would be no context for them to execute in. (source) –  dezso Aug 1 '12 at 8:10
    
yes,2.1 M may be small, but if the table is very large such as 100 million ,doing the job in a single update is not a good way. –  francs Aug 1 '12 at 8:14
    
@deszo: that's exactly what I mean. A function will never be a transaction of its own. It is always part of transaction. It never is a (single) transaction. –  a_horse_with_no_name Aug 1 '12 at 8:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.