Re: How to schedule long running SQL job

A lambda would time out and not be appropriate. A Fargate container would not time out. I wanted to mention that since everything on medium loves to promote using lambdas for everything.

Doug

Sent from my iPhone

> On Jul 19, 2023, at 8:39 PM, M Sarwar <sarwarmd02(at)outlook(dot)com> wrote:
>
> 
> If it is going to time out after 15 mts, it is not a good idea to take this approach.
> Thanks
> Sarwar
>
>
>
>
> Sent from my Galaxy
>
>
>
> -------- Original message --------
> From: Doug Reynolds <mav(at)wastegate(dot)net>
> Date: 7/19/23 8:35 PM (GMT-05:00)
> To: M Sarwar <sarwarmd02(at)outlook(dot)com>
> Cc: pgsql-admin(at)lists(dot)postgresql(dot)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(at)outlook(dot)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
>>