Re: Document efficient self-joins / UPDATE LIMIT techniques. - Mailing list pgsql-hackers

From jian he
Subject Re: Document efficient self-joins / UPDATE LIMIT techniques.
Date
Msg-id CACJufxFA0DyqCf2RFusP5-ArpRaNQAFTDEozgejZZ1EX3M0viQ@mail.gmail.com
Whole thread Raw
In response to Re: Document efficient self-joins / UPDATE LIMIT techniques.  (Corey Huinker <corey.huinker@gmail.com>)
List pgsql-hackers
Hi.
-----------------------------------------
In cases where a DML operation involving many rows must be performed,
and that table experiences numerous other simultaneous DML operations,
a FOR UPDATE clause used in conjunction with SKIP LOCKED can be useful
for performing partial DML operations:

WITH mods AS (SELECT ctid FROM mytable
              WHERE status = 'active' AND retries > 10
              ORDER BY id FOR UPDATE SKIP LOCKED)
UPDATE mytable SET status = 'failed'
FROM mods WHERE mytable.ctid = mods.ctid

This allows the DML operation to be performed in parts, avoiding
locking, until such time as the set of rows that remain to be modified
is small enough that the locking will not affect overall performance,
at which point the same statement can be issued without the SKIP
LOCKED clause to ensure that no rows were overlooked.
----------------------------------
mods found out the ctids to be updated, update mytable actually do the update.
I didn't get "This allows the DML operation to be performed in parts".

omit "at which point", the last sentence still makes sense. so I
didn't get "at which point"?

I am not native english speaker.



pgsql-hackers by date:

Previous
From: Michael Paquier
Date:
Subject: Re: pg_upgrade and logical replication
Next
From: Michael Paquier
Date:
Subject: Re: pg_upgrade and logical replication