I have to randomly match about 20 million rows in two tables without using the same row twice using PostgreSQL 9.3.5. Both tables have the following structure

CREATE TABLE de_sim_points_end
-- Inherited from table de_sim_points:  id integer NOT NULL DEFAULT nextval('de_sim_points_id_seq'::regclass),
-- Inherited from table de_sim_points:  parent_geometry character varying(12),
-- Inherited from table de_sim_points:  used boolean DEFAULT false,
-- Inherited from table de_sim_points:  geom geometry(Point,900913),
  CONSTRAINT de_sim_points_end_pkey PRIMARY KEY (id)
INHERITS (de_sim_points)
ALTER TABLE de_sim_points_end
  OWNER TO benjamin;

CREATE INDEX de_sim_points_end_geom_idx
  ON de_sim_points_end
  USING gist
ALTER TABLE de_sim_points_end CLUSTER ON de_sim_points_end_geom_idx;

CREATE INDEX de_sim_points_end_parent_relation_idx
  ON de_sim_points_end
  USING btree
  (parent_geometry COLLATE pg_catalog."default")

CREATE INDEX de_sim_points_end_used_idx
  ON de_sim_points_end
  USING btree

The SQL Query and Python Code used to match the rows can be found at https://github.com/boerngen-schmidt/commuter-simulation/blob/master/code/builder/process_point_mass_matcher.py (I will not post it here due to its length). But basically what I do is:

  1. SELECT rows, randomly ordered from start point table
  2. JOIN rows, randomly ordered from destination point table
  3. Take the result, iterate over it and update the points in start and destination table as used
  4. Save the result in another table (done by another thread)

When I was using multiple processes with SELECT ... FOR UPDATE I ran into deadlocks (currently I just use one process, so the FOR UPDATE statement is currently omitted). So far I have read a bit about SERIALIZABLE and so forth locking mechanisms, but yet I fail to fully grasp them.

Is it possible to make the matching multiprocessing compatible? How whould the statements look like?

  • "multiprocessing compatible". In what sense? What concurrent transactions would be happening? Do you want to permit concurrent inserts/updates/deletes? What? The simplest way to make it "multiprocessing compatible" is to add a LOCK TABLE. – Craig Ringer Dec 23 '14 at 23:13
  • Well the problem is, that the query to select the start and endpoints takes quite some time to execute, but once it has selected the rows and marked them for update the rest of the programm can update the selected rows and insert their Ids into another table. What obviously can happen and happend are deadlocks and that another process selects the same endpoint (startpoints can not be selected twice due to app logic). So what I'm looking for is a way to not run into deadlock and if the same endpoint is selected to just skip updating and inserting the start,end tuple. – Benjamin Dec 24 '14 at 1:43

As I dug deeper into the matter at hand trying various approaches I found out that there is no solution to this problem with PostgreSQL versions <= 9.4.

PostgreSQL 9.5 (current development version) has a new feature called SKIP LOCKED which does skip rows that have been locked by a query using SELECT * ... FOR UPDATE

For more information on the new feature have a look at http://www.depesz.com/2014/10/10/waiting-for-9-5-implement-skip-locked-for-row-level-locks/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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