Message-ID: <d8ae733e-8f49-463d-bccc-59754d7796e3@sopchak.me>
Date: Sun, 8 Dec 2024 11:50:21 -0500
MIME-Version: 1.0
User-Agent: Mozilla Thunderbird
Subject: Re: What is the best way to do this in Postgres
To: pgsql-sql@lists.postgresql.org
References: 
 <CAJSrDUoBN0KGdBdQKD_tjUFM2LJipefJuQ5GpQrY2iFm8Y2J3Q@mail.gmail.com>
 <CAKFQuwbkVaAHT+nBfwffZY89pWvqtkK4q0aOmX9PT3Hf7oz2sA@mail.gmail.com>
Content-Language: en-US
From: Carl Sopchak <carl@sopchak.me>
In-Reply-To: 
 <CAKFQuwbkVaAHT+nBfwffZY89pWvqtkK4q0aOmX9PT3Hf7oz2sA@mail.gmail.com>
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable
UI-OutboundReport: notjunk:1;M01:P0:p0vs6BQF+Uc=;kgMbTIiRrUjcK7hXcW7Plx8+nGW
 KI7xDlsDejYnEZTOiMNYhNV98h7NMvHZN9cxSbEiJbJEhmU7N1Yh7IeJg9ztPmzfh2raCRhRF
 eETQhBlJuNF8buh8yDa8iz8Le2fdXvhA2PSkmCOmapvUPu44fAib6S9KmOLoO2jFb4e93yeI3
 Fw9N7zc+c+HbZMcPvk0DqGEQrFrGTwnc4ZHUOF34CA+5J/Qhig02WW5W/FS9tGX6dQ2thxLaH
 ZY6Pb+yCHqw0+NGuGaaKcnJJh3wG0nNxLkrDqTrlrt1S50onELyjbWVnm6hkbpie+awzPsBmz
 Ff/vyr+MKdUcXDImNoKPtrNP+XIAcsT6vcAZGO44+ZM/7GaTFOaNsF3ZT8rUamGPtzY+XXEbx
 gu7L3lU1gb2ZS+g05hXYbGc6XMrpzbX88mf1oLLDjQs2JNSW8abAd1Hqcot8dbdRSSn81SU/X
 1xPQOu/565p08s/FLvvgamJEfKZtezBDsk+/SJXqub0xMZ3xHb5W3zRLBO/BZPjtH8fHAmy6p
 wW0kCVI54WzeMl6DCOw2s/LT3uA8vHfTizh8ZZ+AZpCRUNcUqGe4LICgmEtgOyW1ly/oHToQe
 u1ppAA1u23pgaAAFMX7DGLQOZOLlH5I+2KbveFxU5tPO4cV1C0EnI8O7h323M7Ck7h0rd0LDs
 U0MNwLpayZR7WHBpBQEtQj3NQuWuJiyvSqGczOjq74+GLqKzRKU/5NX06nQwy7nfZ3YFMmtRp
 7+dCfA2xMi6U0EnONFLd1mKwl4pXqm9L7hN1+KJNRYVwYgj9DHlAMrnBdR0yyQJl+nJAOl36d
 CA1hp0KnQVVgDkYvC0hR5PH6gf8q90ohCF1fGhtgFUOOSlrjjxriYFiCHBTgdTjC1sFfoio8g
 yeTRQFPZW2oM1U9pEAzQefYhAPaNfrDNllqkqEzG5Q4zqRlbntJN7MQNyrFIpPvuT9cuDnjjh
 PnZ37qnMnR+fp6Who9V0hExIOco6tDqvI4euFp+VbUZ3Bj7axhYkXbm0xY+cEjdMMLziiUZpk
 WOajspdgLxT77p74ICl7sKyKEpEp3tUZPKN1cec
Archived-At: 
 <https://www.postgresql.org/message-id/d8ae733e-8f49-463d-bccc-59754d7796e3%40sopchak.me>
Precedence: bulk

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv=3D"Content-Type" content=3D"text/html; charset=3DUTF-=
8">
  </head>
  <body>
    You could set up a queuing table to hold the product id's that need
    processing, removing that parameter from the proc (or all of them if
    all columns vary and are in the queuing table).=C2=A0 Then the proc pi=
cks
    one product_id from the queue (using delete and capturing the data
    deleted data using RETURNING), processes it, then loops for the next
    product, terminating when there are none.=C2=A0 A separate job runs to
    add new products needing processing to the queue table.=C2=A0 This
    technique allows you to run as many simultaneous jobs as you need to
    go through all of the products in a timely manner without code
    changes.=C2=A0 Also, if one fails, the others will pick up the slack
    since they all run until the queue is empty.=C2=A0 I've used this
    technique before (although not in Postgres) and it works well.<br>
    <br>
    <br>
    <div class=3D"moz-cite-prefix">On 12/8/24 10:26 AM, David G. Johnston
      wrote:<br>
    </div>
    <blockquote type=3D"cite"
cite=3D"mid:CAKFQuwbkVaAHT+nBfwffZY89pWvqtkK4q0aOmX9PT3Hf7oz2sA@mail.gmail=
.com">
      <meta http-equiv=3D"content-type" content=3D"text/html; charset=3DUT=
F-8">
      On Sunday, December 8, 2024, kunwar singh &lt;<a
        href=3D"mailto:krishsingh.111@gmail.com" moz-do-not-send=3D"true"
        class=3D"moz-txt-link-freetext">krishsingh.111@gmail.com</a>&gt;
      wrote:<br>
      <blockquote class=3D"gmail_quote"
style=3D"margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
        <div dir=3D"ltr">
          <div><br>
          </div>
          <div>
            <div
style=3D"font-family:Menlo,Monaco,&quot;Courier New&quot;,monospace;font-s=
ize:12px;line-height:18px;white-space:pre-wrap"><div><font
            style=3D"background-color:rgb(243,243,243)" color=3D"#0000ff">=
I know I can create a bash script or Python script , but I am wondering if=
 there is a smarter way to do it in Postgres?</font></div></div>
          </div>
          <br>
        </div>
      </blockquote>
      <div><br>
      </div>
      <div>Your concurrency requirement makes doing it in the server
        quite difficult.=C2=A0 Using anything that can launch multiple
        processes/threads and initiate one connect each is your best
        option.=C2=A0 Many things can, so pick one you are familiar with.=
=C2=A0
        There is little complexity here that specialized tooling would
        be needed for.</div>
      <div><br>
      </div>
      <div>David J.=C2=A0<br>
      </div>
    </blockquote>
    <br>
  </body>
</html>