Обсуждение: How to schedule long running SQL job

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

On Jul 19, 2023, at 7:57 PM, M Sarwar <sarwarmd02@outlook.com> wrote:

From my laptop on powershell and dos prompt, it is saying tht pg_cron is not recognized.

Do  I need anything new installation for this?

Thanks,

Sarwar

---

From: John Scalia <jayknowsunix@gmail.com>
Sent: Wednesday, July 19, 2023 7:45 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: How to schedule long running SQL job

 

Try pg_cron. I know later versions support it.

Sent from my iPad

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



From my laptop on powershell and dos prompt, it is saying tht pg_cron is not recognized.

Do  I need anything new installation for this?

Thanks,

Sarwar

---

From: John Scalia <jayknowsunix@gmail.com>
Sent: Wednesday, July 19, 2023 7:45 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: How to schedule long running SQL job

 

Try pg_cron. I know later versions support it.

Sent from my iPad

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



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

On Jul 19, 2023, at 8:34 PM, M Sarwar <sarwarmd02@outlook.com> wrote:

https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.General.Extensions.Trusted

 



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@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

I thought that something needs to come from AWS side but definitely I can try this approach of psql and Windows Scheduler.

Thanks,

Sarwar

---

From: Ron <ronljohnsonjr@gmail.com>
Sent: Wednesday, July 19, 2023 8:09 PM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: How to schedule long running SQL job

 

Install psql (don't ask me how) on your laptop, then use Windows Task Scheduler to run your query.

On 7/19/23 18:57, M Sarwar wrote:

From my laptop on powershell and dos prompt, it is saying tht pg_cron is not recognized.

Do  I need anything new installation for this?

Thanks,

Sarwar

---

From: John Scalia <jayknowsunix@gmail.com>
Sent: Wednesday, July 19, 2023 7:45 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: How to schedule long running SQL job

 

Try pg_cron. I know later versions support it.

Sent from my iPad

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

--
Born in Arizona, moved to Babylonia.