Parallel table update using python multiprocessing (PostGIS/PostgreSQL)

Question

Hi I have been using the multiprocessing approach for updating a huge postgis table from here and it works quite well, but my table doesn't seem to get updated unless I add a commit; statement at the end of

procQuery = 'UPDATE city SET gid_fkey = gid FROM country  WHERE ST_within((SELECT the_geom FROM city WHERE city_id = %s), country.the_geom) AND city_id = %s' % (self.a, self.a)

like

procQuery = 'UPDATE city SET gid_fkey = gid FROM country  WHERE ST_within((SELECT the_geom FROM city WHERE city_id = %s), country.the_geom) AND city_id = %s;commit;' % (self.a, self.a)

what could be the problem here?

In what way does it not make sense to you ? It is an SQL thing to commit an update, insert or delete query. Maybe you think your database transaction object has autocommit set to true. Might be worth it to check this first. — avk
– avk, Commented Jun 8, 2015 at 11:29

ray · Accepted Answer · 2015-06-08 11:56:18Z

0

ok, I solved by setting the connection to autommit = True

class Consumer(multiprocessing.Process):

def __init__(self, task_queue, result_queue):

    multiprocessing.Process.__init__(self)
    self.task_queue = task_queue
    self.result_queue = result_queue
    self.pyConn = psycopg2.connect(**connstring)
    self.pyConn.autocommit = True


def run(self):
    proc_name = self.name
    while True:
        next_task = self.task_queue.get()
        if next_task is None:
            print 'Tasks Complete'
            self.task_queue.task_done()
            break            
        answer = next_task(connection=self.pyConn)
        self.task_queue.task_done()
        self.result_queue.put(answer)
    return

answered Jun 8, 2015 at 11:56

ray

12 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Parallel table update using python multiprocessing (PostGIS/PostgreSQL)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related