Horton

Re: What is the best way to do this in Postgres

Carl Sopchak <carl@sopchak.me>

View thread View on postgr.es
From: Carl Sopchak <carl@sopchak.me>
To: pgsql-sql@lists.postgresql.org
Date: 2024-12-08T16:50:21Z
Lists: pgsql-sql

Attachments

<!DOCTYPE html>
<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-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).  Then the proc picks
    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.  A separate job runs to
    add new products needing processing to the queue table.  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.  Also, if one fails, the others will pick up the slack
    since they all run until the queue is empty.  I've used this
    technique before (although not in Postgres) and it works well.<br>
    <br>
    <br>
    <div class="moz-cite-prefix">On 12/8/24 10:26 AM, David G. Johnston
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAKFQuwbkVaAHT+nBfwffZY89pWvqtkK4q0aOmX9PT3Hf7oz2sA@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      On Sunday, December 8, 2024, kunwar singh &lt;<a
        href="mailto:krishsingh.111@gmail.com" moz-do-not-send="true"
        class="moz-txt-link-freetext">krishsingh.111@gmail.com</a>&gt;
      wrote:<br>
      <blockquote class="gmail_quote"
style="margin:0 0 0 .8ex;border-left:1px #ccc solid;padding-left:1ex">
        <div dir="ltr">
          <div><br>
          </div>
          <div>
            <div
style="font-family:Menlo,Monaco,&quot;Courier New&quot;,monospace;font-size:12px;line-height:18px;white-space:pre-wrap"><div><font
            style="background-color:rgb(243,243,243)" color="#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.  Using anything that can launch multiple
        processes/threads and initiate one connect each is your best
        option.  Many things can, so pick one you are familiar with. 
        There is little complexity here that specialized tooling would
        be needed for.</div>
      <div><br>
      </div>
      <div>David J. <br>
      </div>
    </blockquote>
    <br>
  </body>
</html>