timestamp AS blacklist_timestamp FROM blacklist INNER JOIN suspicious_activity ON blacklist. created ) AS latest_suspicious_activity, blacklist. customer_id AS customer_id, MAX ( suspicious_activity. new_expiry_timestamp FROM ( - subquery 3: get new expiration timestamps based on a chunk number SELECT row_num, row_batched, customer_id, NOW ():: TIMESTAMP + row_batched * INTERVAL '1 day' AS new_expiry_timestamp, latest_suspicious_activity, blacklist_timestamp FROM ( - subquery 2: get chunk numbers based on a row number SELECT row_num, (( row_num - 1 ) / 10 ) + 1 AS row_batched, customer_id, latest_suspicious_activity, blacklist_timestamp FROM ( - subquery 1: get row numbers SELECT row_number () OVER ( ORDER BY latest_suspicious_activity DESC NULLS LAST ) AS row_num, customer_id, latest_suspicious_activity, blacklist_timestamp FROM ( - subquery 0: order blacklist SELECT blacklist. main: update expiry timestamp column in chunks UPDATE blacklist SET expiry = subquery. But in this case there’s nothing that a modern RDBMS couldn’tĪchieve by itself, without writing any extra code. Satisfy the condition, then iterate over batches of size 10 and We can do it like in the example above: select all the rows that Have to do it in chunks of the constant size. In other words we want to update all rows in the table where someĬonditions met ( expiry value equals to NULL in this case). The activity log defined in another database table. The rows so that the customers with the most recent suspiciousĪctivity come first. When splitting into chunks, we want to order Problem: we want to set an expiration date to all permanentlyīlacklisted customers so that they get removed from the list inīatches of size 10 a day. (possibly by a cronjob task), once an expiration timestamp is less Otherwise a row will be removed from the blacklist table If the value is NULL then a client considered permanentelyīlacklisted. In the database may have an optional value in expiration timestampĬolumn. Problem: update blacklist expiration timestamps in chunks
0 kommentar(er)
