Question 328499 in PostgreSQL Question posted in
The official documentation can be found here.

Postgresql – Force partition pruning with IN statement and select for update in Postgres

Would be glad if you could help me out:
Got a query that is supposed to take some task from the database (Postgres) partitioned table (partitioned by execute_ts) and take them to work thread-safely (with several workers in the service taking the tasks of the DB concurrently)

UPDATE tasks
SET    status = 'IN_WORK' 
WHERE  execute_ts BETWEEN (now() - INTERVAL '2 day') AND now() 
    AND id IN (
     SELECT id
     FROM   tasks
     WHERE  status IN ('INITIAL', 'TO_BE_RETRIED')
      AND execute_ts BETWEEN (now() - INTERVAL '2 day') AND now()
LIMIT  1000
 FOR    UPDATE SKIP LOCKED
    )
RETURNING *;

Is there a way to avoid repeating execute_ts BETWEEN (now() - INTERVAL '2 day') AND now() conditions, because otherwise it traverses all partitions.

Tags:

2

Answers


  1. 0

    Try with CTE, like the following

    WITH filtered_tasks AS (
    SELECT id
    FROM   tasks
    WHERE  status IN ('INITIAL', 'TO_BE_RETRIED')
      AND  execute_ts BETWEEN (now() - INTERVAL '2 day') AND now()
    LIMIT  1000
    FOR    UPDATE SKIP LOCKED
)
UPDATE tasks
SET    status = 'IN_WORK' 
FROM   filtered_tasks
WHERE  tasks.id = filtered_tasks.id
RETURNING tasks.*;
    Login or Signup to reply.
  2. 0

    The tuple identifier ctid is a good way to achieve an efficient self-join. However, according to this question on DBA.SE one needs to combine it with the tableoid to work correctly in partitioned tables, and one should use a CTE instead of a subquery:

    WITH work AS (
  SELECT tableoid, ctid
  FROM   tasks
  WHERE  status IN ('INITIAL', 'TO_BE_RETRIED')
    AND  execute_ts BETWEEN (now() - INTERVAL '2 day') AND now()
  LIMIT  1000
  FOR    UPDATE SKIP LOCKED
)
UPDATE tasks
SET    status = 'IN_WORK'
WHERE  (tableoid, ctid) IN (TABLE work)
RETURNING *;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search