public inbox for [email protected]
help / color / mirror / Atom feedWhat is the best way to do this in Postgres
4+ messages / 4 participants
[nested] [flat]
* What is the best way to do this in Postgres
@ 2024-12-08 11:38 kunwar singh <[email protected]>
0 siblings, 2 replies; 4+ messages in thread
From: kunwar singh @ 2024-12-08 11:38 UTC (permalink / raw)
To: [email protected]
Hello Listers,
Looking for your inputs on the most efficient way to do it in Postgres.
What I want to do:
===
I want to spawn 10 concurrent sessions each executing a complex stored
procedure with one of the parameters being the product ID.
Example
CALL process_product(curdate, region, productid=>1) ;
CALL process_product(curdate, region, productid=>2) ;
CALL process_product(curdate, region, productid=>3) ;
CALL process_product(curdate, region, productid=>4) ;
...
CALL process_product(curdate, region, productid=>10) ;
Say I get a list of product IDs by running a big query
Product ID
====
1
2
3
4
..
10
Additional Information
===
Everytime number of product ids will change. At a given time there should
be no more than 10 concurrent sessions of process_product.
I want to trigger these procedure calls once every hour. For a given hour
the number of product IDs could range from 10 to 100 in total.
I am using RDS Postgres v 15.
Question
===
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?
--
Cheers,
Kunwar
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What is the best way to do this in Postgres
@ 2024-12-08 15:26 David G. Johnston <[email protected]>
parent: kunwar singh <[email protected]>
1 sibling, 1 reply; 4+ messages in thread
From: David G. Johnston @ 2024-12-08 15:26 UTC (permalink / raw)
To: kunwar singh <[email protected]>; +Cc: [email protected] <[email protected]>
On Sunday, December 8, 2024, kunwar singh <[email protected]> wrote:
>
> 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?
>
>
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.
David J.
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What is the best way to do this in Postgres
@ 2024-12-08 16:50 Carl Sopchak <[email protected]>
parent: David G. Johnston <[email protected]>
0 siblings, 0 replies; 4+ messages in thread
From: Carl Sopchak @ 2024-12-08 16:50 UTC (permalink / raw)
To: [email protected]
<!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 <<a
href="mailto:[email protected]" moz-do-not-send="true"
class="moz-txt-link-freetext">[email protected]</a>>
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,"Courier New",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>
^ permalink raw reply [nested|flat] 4+ messages in thread
* Re: What is the best way to do this in Postgres
@ 2024-12-08 17:06 Erik Brandsberg <[email protected]>
parent: kunwar singh <[email protected]>
1 sibling, 0 replies; 4+ messages in thread
From: Erik Brandsberg @ 2024-12-08 17:06 UTC (permalink / raw)
To: kunwar singh <[email protected]>; +Cc: pgsql-sql <[email protected]>
Check the extension ph_cron, while I have never used it, it may be good to
use as part of the solution.
On Sun, Dec 8, 2024, 6:39 AM kunwar singh <[email protected]> wrote:
> Hello Listers,
> Looking for your inputs on the most efficient way to do it in Postgres.
>
> What I want to do:
> ===
> I want to spawn 10 concurrent sessions each executing a complex stored
> procedure with one of the parameters being the product ID.
> Example
>
> CALL process_product(curdate, region, productid=>1) ;
> CALL process_product(curdate, region, productid=>2) ;
> CALL process_product(curdate, region, productid=>3) ;
> CALL process_product(curdate, region, productid=>4) ;
> ...
> CALL process_product(curdate, region, productid=>10) ;
>
>
> Say I get a list of product IDs by running a big query
>
> Product ID
> ====
> 1
> 2
> 3
> 4
> ..
> 10
>
>
> Additional Information
> ===
> Everytime number of product ids will change. At a given time there should
> be no more than 10 concurrent sessions of process_product.
> I want to trigger these procedure calls once every hour. For a given hour
> the number of product IDs could range from 10 to 100 in total.
> I am using RDS Postgres v 15.
>
> Question
> ===
> 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?
>
>
>
> --
> Cheers,
> Kunwar
>
^ permalink raw reply [nested|flat] 4+ messages in thread
end of thread, other threads:[~2024-12-08 17:06 UTC | newest]
Thread overview: 4+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2024-12-08 11:38 What is the best way to do this in Postgres kunwar singh <[email protected]>
2024-12-08 15:26 ` David G. Johnston <[email protected]>
2024-12-08 16:50 ` Carl Sopchak <[email protected]>
2024-12-08 17:06 ` Erik Brandsberg <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox