If it is going to time out after 15 mts, it is not a good idea to take this approach.
Thanks
Sarwar
-------- Original message --------
From: Doug Reynolds <mav@wastegate.net>
Date: 7/19/23 8:35 PM (GMT-05:00)
To: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: How to schedule long running SQL job
Run a postgres container on Fargate, dumping CSV from psql to a file, upload to S3. Kick job from CloudWatch cron trigger.
You could do a lambda, but it would time out after 15 minutes.
Sent from my iPhone
On Jul 19, 2023, at 7:41 PM, M Sarwar <sarwarmd02@outlook.com> wrote:
Hi,
Db: Postgres
Cloud: aws / RDS
Tool using so far: PgAdmin
SQL got aborted after running for 1 hr 40 minutes from pgadmin.
I need CSV file output.
What is the recommended approach for this issue.
SQL:
SELECT P1.FILE_ID,
F.FILENAME,
P1.STAGE,
P1.SERIAL_NUMBER,
P1.TEST_IMAGE_SET_VALUE,
SUBSTRING(P1.SERIAL_NUMBER, '[A-Z][0-9]+') AS MCM_ID_SERIAL_NUMBER,
P1.RUN_ID,
SPLIT_PART(P1.RUN_ID, ':', 1) TEST_LOT
FROM BRONX.TEST_PART_DETAILS_ALL_MCM P1,
BRONX.FILES_METADATA F
WHERE F.FILE_ID = P1.FILE_ID
AND EXISTS ( SELECT SUBSTRING(P2.SERIAL_NUMBER, '[A-Z][0-9]+') AS MCM_ID2 ,
COUNT(DISTINCT SPLIT_PART(P2.RUN_ID, ':', 1)) AS NUM_DISTINCT_TEST_LOTS
FROM BRONX.TEST_PART_DETAILS_ALL_MCM P2
WHERE SUBSTRING(P2.SERIAL_NUMBER, '[A-Z][0-9]+') = SUBSTRING(P1.SERIAL_NUMBER, '[A-Z][0-9]+')
GROUP BY MCM_ID2
HAVING COUNT(DISTINCT SPLIT_PART(P2.RUN_ID, ':', 1)) > 1
)
order BY MCM_ID_SERIAL_NUMBER,
TEST_LOT ;
I used to use crontab or OEM to schedule these types jobs in the past. Now it is on RDS/AWS.
Thanks,
Sarwar