Thread: How to schedule long running SQL job
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 ;
On Jul 19, 2023, at 7:41 PM, M Sarwar <sarwarmd02@outlook.com> wrote:
Hi,Db: PostgresCloud: aws / RDSTool using so far: PgAdminSQL 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
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
On Jul 19, 2023, at 7:41 PM, M Sarwar <sarwarmd02@outlook.com> wrote:
Hi,Db: PostgresCloud: aws / RDSTool using so far: PgAdminSQL 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
P {margin-top:0;margin-bottom:0;} 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,SarwarFrom: 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 jobTry pg_cron. I know later versions support it.Sent from my iPadOn Jul 19, 2023, at 7:41 PM, M Sarwar <sarwarmd02@outlook.com> wrote:Hi,Db: PostgresCloud: aws / RDSTool using so far: PgAdminSQL 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.
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,SarwarFrom: 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 jobTry pg_cron. I know later versions support it.Sent from my iPadOn Jul 19, 2023, at 7:41 PM, M Sarwar <sarwarmd02@outlook.com> wrote:Hi,Db: PostgresCloud: aws / RDSTool using so far: PgAdminSQL 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
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
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,SarwarFrom: 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 jobTry pg_cron. I know later versions support it.Sent from my iPadOn Jul 19, 2023, at 7:41 PM, M Sarwar <sarwarmd02@outlook.com> wrote:Hi,Db: PostgresCloud: aws / RDSTool using so far: PgAdminSQL 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.
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.
Sent: Wednesday, July 19, 2023 8:13 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
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,SarwarFrom: 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 jobTry pg_cron. I know later versions support it.Sent from my iPadOn Jul 19, 2023, at 7:41 PM, M Sarwar <sarwarmd02@outlook.com> wrote:Hi,Db: PostgresCloud: aws / RDSTool using so far: PgAdminSQL 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
You could do a lambda, but it would time out after 15 minutes.
On Jul 19, 2023, at 7:41 PM, M Sarwar <sarwarmd02@outlook.com> wrote:
Hi,Db: PostgresCloud: aws / RDSTool using so far: PgAdminSQL 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
You could do a lambda, but it would time out after 15 minutes.
On Jul 19, 2023, at 7:41 PM, M Sarwar <sarwarmd02@outlook.com> wrote:
Hi,Db: PostgresCloud: aws / RDSTool using so far: PgAdminSQL 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:
On Jul 19, 2023, at 8:39 PM, M Sarwar <sarwarmd02@outlook.com> wrote:
If it is going to time out after 15 mts, it is not a good idea to take this approach.ThanksSarwarSent 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.orgSubject: Re: How to schedule long running SQL jobRun 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 iPhoneOn Jul 19, 2023, at 7:41 PM, M Sarwar <sarwarmd02@outlook.com> wrote:Hi,Db: PostgresCloud: aws / RDSTool using so far: PgAdminSQL 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
Sent: Wednesday, July 19, 2023 8:48 PM
To: M Sarwar <sarwarmd02@outlook.com>
Cc: John Scalia <jayknowsunix@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: How to schedule long running SQL job
On Jul 19, 2023, at 8:34 PM, M Sarwar <sarwarmd02@outlook.com> wrote:
But, of course, you can always spin up a small AWS Linux VM to run psql, scheduled "manual" VACUUM, ANALYZE, etc.
P {margin-top:0;margin-bottom:0;} I thought that something needs to come from AWS side but definitely I can try this approach of psql and Windows Scheduler.Thanks,SarwarFrom: 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 jobInstall 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:p {margin-top:0; margin-bottom:0} 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,SarwarFrom: 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 jobTry pg_cron. I know later versions support it.Sent from my iPadOn Jul 19, 2023, at 7:41 PM, M Sarwar <sarwarmd02@outlook.com> wrote:Hi,Db: PostgresCloud: aws / RDSTool using so far: PgAdminSQL 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.
Born in Arizona, moved to Babylonia.
Sent: Thursday, July 20, 2023 9:26 AM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: How to schedule long running SQL job
But, of course, you can always spin up a small AWS Linux VM to run psql, scheduled "manual" VACUUM, ANALYZE, etc.
I thought that something needs to come from AWS side but definitely I can try this approach of psql and Windows Scheduler.Thanks,SarwarFrom: 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 jobInstall 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,SarwarFrom: 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 jobTry pg_cron. I know later versions support it.Sent from my iPadOn Jul 19, 2023, at 7:41 PM, M Sarwar <sarwarmd02@outlook.com> wrote:Hi,Db: PostgresCloud: aws / RDSTool using so far: PgAdminSQL 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.
Born in Arizona, moved to Babylonia.
P {margin-top:0;margin-bottom:0;} Ron,Yes, my pgAdmin is on my laptop.Can I run pgAdmin any where else in Postgres / RDS / AWS environment?Thanks,SarwarFrom: Ron <ronljohnsonjr@gmail.com>
Sent: Thursday, July 20, 2023 9:26 AM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: How to schedule long running SQL jobPgAdmin is on your laptop, no?
But, of course, you can always spin up a small AWS Linux VM to run psql, scheduled "manual" VACUUM, ANALYZE, etc.On 7/19/23 19:20, M Sarwar wrote:p {margin-top:0; margin-bottom:0} I thought that something needs to come from AWS side but definitely I can try this approach of psql and Windows Scheduler.Thanks,SarwarFrom: 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 jobInstall 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:p {margin-top:0; margin-bottom:0} 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,SarwarFrom: 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 jobTry pg_cron. I know later versions support it.Sent from my iPadOn Jul 19, 2023, at 7:41 PM, M Sarwar <sarwarmd02@outlook.com> wrote:Hi,Db: PostgresCloud: aws / RDSTool using so far: PgAdminSQL 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.--
Born in Arizona, moved to Babylonia.
Born in Arizona, moved to Babylonia.