Postgres Pro
Downloads
Home   >   mailing lists

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

What is the best way to do this in Postgres

From
kunwar singh
Date:
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

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

From
"David G. Johnston"
Date:
On Sunday, December 8, 2024, kunwar singh <krishsingh.111@gmail.com> 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. 

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

From
Erik Brandsberg
Date:
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 <krishsingh.111@gmail.com> 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