CODEWITHSUNDEEP
databasepostgresqlgeospatialpostgispostgresql-9.1
ThinkingMonkey
ThinkingMonkey

Reputation: 12727

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

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' ;

P.S. I am pretty sure this will get closed as off topic. But, I have to look for answers.

Upvotes: 1

Views: 146

Answers (1)

hgmnz
hgmnz

Reputation: 13306

I don't know what would cause it, but it sounds like there may be locks acquired in this operation. You can verify this easily though:

SELECT * FROM pg_locks;

will tell you what locks are currently taken. And

SELECT * FROM pg_stat_activities WHERE waiting;

will tell you, if there are locks, which transactions are sitting there idle waiting for a lock to be released. That should point you on the right direction.

Does not spring any other instance of PostgreSQL to utilize other cores(since the request is from a single user?).

This is how postgres works. A single session will spin off one backend. A single query does not do any sort multi-process or concurrent operation.

How to circumvent this?

Why are you looping? I think you can do this in one shot. Why not do:

UPDATE list_of_location 
SET location = ST_GeogFromText('POINT(' || longitude || ' ' || latitude || ')');

(assuming that gives you the correct result)

Upvotes: 4

Related Questions