Re: How to schedule long running SQL job

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

As per the above document, AWS is not supporting PG_CRON extension.

SELECT * FROM rds.allowed_extensions

The above SQL is giving erorrs but the below SQL is giving 1 row output of *.

SHOW rds.allowed_extensions;

Output:

*

It sounds like I do not have any extensions installed.

I remember like installing 1 extension in the past but I do not remember the details.

Thanks,
Sarwar

________________________________
From: John Scalia <jayknowsunix(at)gmail(dot)com>
Sent: Wednesday, July 19, 2023 8:13 PM
To: M Sarwar <sarwarmd02(at)outlook(dot)com>
Cc: pgsql-admin(at)lists(dot)postgresql(dot)org <pgsql-admin(at)lists(dot)postgresql(dot)org>
Subject: Re: How to schedule long running SQL job

Do: select * from rds.allowed_extensions; and see if it’s in your version support. I don’t quite recall which versions supports it other than v.12.X where X is another value like 4, 5, or 6. If it is in the list, try: create extension pg_cron; note you may need superuser to create it.

Sent from my iPad

On Jul 19, 2023, at 7:57 PM, M Sarwar <sarwarmd02(at)outlook(dot)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(at)gmail(dot)com>
Sent: Wednesday, July 19, 2023 7:45 PM
To: M Sarwar <sarwarmd02(at)outlook(dot)com>
Cc: pgsql-admin(at)lists(dot)postgresql(dot)org <pgsql-admin(at)lists(dot)postgresql(dot)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(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