From: | John Scalia <jayknowsunix(at)gmail(dot)com> |
---|---|
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 |
Date: | 2023-07-19 23:45:55 |
Message-ID: | 0644DDC9-A86B-411F-A70B-DD2C99D7950F@gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-admin |
Try pg_cron. I know later versions support it.
Sent from my iPad
> 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
>
From | Date | Subject | |
---|---|---|---|
Next Message | M Sarwar | 2023-07-19 23:57:15 | Re: How to schedule long running SQL job |
Previous Message | M Sarwar | 2023-07-19 23:41:23 | How to schedule long running SQL job |