pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedHow to schedule long running SQL job
15+ messages / 5 participants
[nested] [flat]
* How to schedule long running SQL job
@ 2023-07-19 23:41 M Sarwar <sarwarmd02@outlook.com>
2023-07-19 23:45 ` Re: How to schedule long running SQL job John Scalia <jayknowsunix@gmail.com>
2023-07-20 00:35 ` Re: How to schedule long running SQL job Doug Reynolds <mav@wastegate.net>
0 siblings, 2 replies; 15+ messages in thread
From: M Sarwar @ 2023-07-19 23:41 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to schedule long running SQL job
2023-07-19 23:41 How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
@ 2023-07-19 23:45 ` John Scalia <jayknowsunix@gmail.com>
2023-07-19 23:57 ` Re: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
1 sibling, 1 reply; 15+ messages in thread
From: John Scalia @ 2023-07-19 23:45 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: pgsql-admin@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to schedule long running SQL job
2023-07-19 23:41 How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-19 23:45 ` Re: How to schedule long running SQL job John Scalia <jayknowsunix@gmail.com>
@ 2023-07-19 23:57 ` M Sarwar <sarwarmd02@outlook.com>
2023-07-20 00:09 ` Re: How to schedule long running SQL job Ron <ronljohnsonjr@gmail.com>
2023-07-20 00:13 ` Re: How to schedule long running SQL job John Scalia <jayknowsunix@gmail.com>
0 siblings, 2 replies; 15+ messages in thread
From: M Sarwar @ 2023-07-19 23:57 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to schedule long running SQL job
2023-07-19 23:41 How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-19 23:45 ` Re: How to schedule long running SQL job John Scalia <jayknowsunix@gmail.com>
2023-07-19 23:57 ` Re: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
@ 2023-07-20 00:09 ` Ron <ronljohnsonjr@gmail.com>
2023-07-20 00:20 ` Re: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
1 sibling, 1 reply; 15+ messages in thread
From: Ron @ 2023-07-20 00:09 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org
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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to schedule long running SQL job
2023-07-19 23:41 How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-19 23:45 ` Re: How to schedule long running SQL job John Scalia <jayknowsunix@gmail.com>
2023-07-19 23:57 ` Re: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-20 00:09 ` Re: How to schedule long running SQL job Ron <ronljohnsonjr@gmail.com>
@ 2023-07-20 00:20 ` M Sarwar <sarwarmd02@outlook.com>
2023-07-20 13:26 ` Re: How to schedule long running SQL job Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: M Sarwar @ 2023-07-20 00:20 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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><mailto:jayknowsunix@gmail.com>
Sent: Wednesday, July 19, 2023 7:45 PM
To: M Sarwar <sarwarmd02@outlook.com><mailto:sarwarmd02@outlook.com>
Cc: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org> <pgsql-admin@lists.postgresql.org><mailto: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><mailto: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.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to schedule long running SQL job
2023-07-19 23:41 How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-19 23:45 ` Re: How to schedule long running SQL job John Scalia <jayknowsunix@gmail.com>
2023-07-19 23:57 ` Re: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-20 00:09 ` Re: How to schedule long running SQL job Ron <ronljohnsonjr@gmail.com>
2023-07-20 00:20 ` Re: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
@ 2023-07-20 13:26 ` Ron <ronljohnsonjr@gmail.com>
2023-07-20 14:07 ` Re: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 15+ messages in thread
From: Ron @ 2023-07-20 13:26 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
PgAdmin 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:
> 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> <mailto:jayknowsunix@gmail.com>
>> *Sent:* Wednesday, July 19, 2023 7:45 PM
>> *To:* M Sarwar <sarwarmd02@outlook.com> <mailto:sarwarmd02@outlook.com>
>> *Cc:* pgsql-admin@lists.postgresql.org
>> <mailto:pgsql-admin@lists.postgresql.org>
>> <pgsql-admin@lists.postgresql.org> <mailto: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>
>>> <mailto: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.
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to schedule long running SQL job
2023-07-19 23:41 How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-19 23:45 ` Re: How to schedule long running SQL job John Scalia <jayknowsunix@gmail.com>
2023-07-19 23:57 ` Re: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-20 00:09 ` Re: How to schedule long running SQL job Ron <ronljohnsonjr@gmail.com>
2023-07-20 00:20 ` Re: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-20 13:26 ` Re: How to schedule long running SQL job Ron <ronljohnsonjr@gmail.com>
@ 2023-07-20 14:07 ` M Sarwar <sarwarmd02@outlook.com>
2023-07-20 16:54 ` Re: How to schedule long running SQL job Ron <ronljohnsonjr@gmail.com>
0 siblings, 1 reply; 15+ messages in thread
From: M Sarwar @ 2023-07-20 14:07 UTC (permalink / raw)
To: Ron <ronljohnsonjr@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Ron,
Yes, my pgAdmin is on my laptop.
Can I run pgAdmin any where else in Postgres / RDS / AWS environment?
Thanks,
Sarwar
________________________________
From: 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 job
PgAdmin 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:
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><mailto:ronljohnsonjr@gmail.com>
Sent: Wednesday, July 19, 2023 8:09 PM
To: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org> <pgsql-admin@lists.postgresql.org><mailto: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><mailto:jayknowsunix@gmail.com>
Sent: Wednesday, July 19, 2023 7:45 PM
To: M Sarwar <sarwarmd02@outlook.com><mailto:sarwarmd02@outlook.com>
Cc: pgsql-admin@lists.postgresql.org<mailto:pgsql-admin@lists.postgresql.org> <pgsql-admin@lists.postgresql.org><mailto: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><mailto: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.
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to schedule long running SQL job
2023-07-19 23:41 How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-19 23:45 ` Re: How to schedule long running SQL job John Scalia <jayknowsunix@gmail.com>
2023-07-19 23:57 ` Re: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-20 00:09 ` Re: How to schedule long running SQL job Ron <ronljohnsonjr@gmail.com>
2023-07-20 00:20 ` Re: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-20 13:26 ` Re: How to schedule long running SQL job Ron <ronljohnsonjr@gmail.com>
2023-07-20 14:07 ` Re: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
@ 2023-07-20 16:54 ` Ron <ronljohnsonjr@gmail.com>
0 siblings, 0 replies; 15+ messages in thread
From: Ron @ 2023-07-20 16:54 UTC (permalink / raw)
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
PgAdmin *requires* a web server (which comes built-in to the Windows
stand-alone version); you don't run it "in" Postgresql.
On 7/20/23 09:07, M Sarwar wrote:
> Ron,
>
> Yes, my pgAdmin is on my laptop.
> Can I run pgAdmin any where else in Postgres / RDS / AWS environment?
> Thanks,
> Sarwar
>
> ----------------------------------------------------------------------------
> *From:* 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 job
> PgAdmin 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:
>> 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> <mailto:ronljohnsonjr@gmail.com>
>> *Sent:* Wednesday, July 19, 2023 8:09 PM
>> *To:* pgsql-admin@lists.postgresql.org
>> <mailto:pgsql-admin@lists.postgresql.org>
>> <pgsql-admin@lists.postgresql.org> <mailto: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> <mailto:jayknowsunix@gmail.com>
>>> *Sent:* Wednesday, July 19, 2023 7:45 PM
>>> *To:* M Sarwar <sarwarmd02@outlook.com> <mailto:sarwarmd02@outlook.com>
>>> *Cc:* pgsql-admin@lists.postgresql.org
>>> <mailto:pgsql-admin@lists.postgresql.org>
>>> <pgsql-admin@lists.postgresql.org> <mailto: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>
>>>> <mailto: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.
>
> --
> Born in Arizona, moved to Babylonia.
--
Born in Arizona, moved to Babylonia.
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to schedule long running SQL job
2023-07-19 23:41 How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-19 23:45 ` Re: How to schedule long running SQL job John Scalia <jayknowsunix@gmail.com>
2023-07-19 23:57 ` Re: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
@ 2023-07-20 00:13 ` John Scalia <jayknowsunix@gmail.com>
2023-07-20 00:34 ` Re: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
1 sibling, 1 reply; 15+ messages in thread
From: John Scalia @ 2023-07-20 00:13 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: pgsql-admin@lists.postgresql.org
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@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
>>
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to schedule long running SQL job
2023-07-19 23:41 How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-19 23:45 ` Re: How to schedule long running SQL job John Scalia <jayknowsunix@gmail.com>
2023-07-19 23:57 ` Re: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-20 00:13 ` Re: How to schedule long running SQL job John Scalia <jayknowsunix@gmail.com>
@ 2023-07-20 00:34 ` M Sarwar <sarwarmd02@outlook.com>
2023-07-20 00:48 ` Re: How to schedule long running SQL job Jonathan Katz <jonathan.katz@excoventures.com>
0 siblings, 1 reply; 15+ messages in thread
From: M Sarwar @ 2023-07-20 00:34 UTC (permalink / raw)
To: John Scalia <jayknowsunix@gmail.com>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.Gene...
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@gmail.com>
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
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@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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to schedule long running SQL job
2023-07-19 23:41 How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-19 23:45 ` Re: How to schedule long running SQL job John Scalia <jayknowsunix@gmail.com>
2023-07-19 23:57 ` Re: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-20 00:13 ` Re: How to schedule long running SQL job John Scalia <jayknowsunix@gmail.com>
2023-07-20 00:34 ` Re: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
@ 2023-07-20 00:48 ` Jonathan Katz <jonathan.katz@excoventures.com>
2023-07-20 01:25 ` Re: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
0 siblings, 1 reply; 15+ messages in thread
From: Jonathan Katz @ 2023-07-20 00:48 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: John Scalia <jayknowsunix@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
> 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.Gene... <https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.Gene...;
>
This URL is a list of extensions that are marked as “trusted,” which means
that they can be installed by users without elevated privileges. A list of
extensions that are available on RDS can be found here. “pg_cron” is
included in this list:
https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html
It includes a link that provides guidance for how to use pg_cron on RDS:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html
Thanks,
Jonathan
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to schedule long running SQL job
2023-07-19 23:41 How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-19 23:45 ` Re: How to schedule long running SQL job John Scalia <jayknowsunix@gmail.com>
2023-07-19 23:57 ` Re: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-20 00:13 ` Re: How to schedule long running SQL job John Scalia <jayknowsunix@gmail.com>
2023-07-20 00:34 ` Re: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-20 00:48 ` Re: How to schedule long running SQL job Jonathan Katz <jonathan.katz@excoventures.com>
@ 2023-07-20 01:25 ` M Sarwar <sarwarmd02@outlook.com>
0 siblings, 0 replies; 15+ messages in thread
From: M Sarwar @ 2023-07-20 01:25 UTC (permalink / raw)
To: Jonathan Katz <jonathan.katz@excoventures.com>; +Cc: John Scalia <jayknowsunix@gmail.com>; pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Katz,
This is a good article. I am going try these.
Thanks,
Sarwar
________________________________
From: Jonathan Katz <jonathan.katz@excoventures.com>
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<mailto:sarwarmd02@outlook.com>> wrote:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_PostgreSQL.html#PostgreSQL.Concepts.Gene...
This URL is a list of extensions that are marked as “trusted,” which means
that they can be installed by users without elevated privileges. A list of
extensions that are available on RDS can be found here. “pg_cron” is
included in this list:
https://docs.aws.amazon.com/AmazonRDS/latest/PostgreSQLReleaseNotes/postgresql-extensions.html
It includes a link that provides guidance for how to use pg_cron on RDS:
https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/PostgreSQL_pg_cron.html
Thanks,
Jonathan
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to schedule long running SQL job
2023-07-19 23:41 How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
@ 2023-07-20 00:35 ` Doug Reynolds <mav@wastegate.net>
2023-07-20 00:39 ` RE: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
1 sibling, 1 reply; 15+ messages in thread
From: Doug Reynolds @ 2023-07-20 00:35 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: pgsql-admin@lists.postgresql.org
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
>
^ permalink raw reply [nested|flat] 15+ messages in thread
* RE: How to schedule long running SQL job
2023-07-19 23:41 How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-20 00:35 ` Re: How to schedule long running SQL job Doug Reynolds <mav@wastegate.net>
@ 2023-07-20 00:39 ` M Sarwar <sarwarmd02@outlook.com>
2023-07-20 00:50 ` Re: How to schedule long running SQL job Doug Reynolds <mav@wastegate.net>
0 siblings, 1 reply; 15+ messages in thread
From: M Sarwar @ 2023-07-20 00:39 UTC (permalink / raw)
To: Doug Reynolds <mav@wastegate.net>; +Cc: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
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
^ permalink raw reply [nested|flat] 15+ messages in thread
* Re: How to schedule long running SQL job
2023-07-19 23:41 How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-20 00:35 ` Re: How to schedule long running SQL job Doug Reynolds <mav@wastegate.net>
2023-07-20 00:39 ` RE: How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
@ 2023-07-20 00:50 ` Doug Reynolds <mav@wastegate.net>
0 siblings, 0 replies; 15+ messages in thread
From: Doug Reynolds @ 2023-07-20 00:50 UTC (permalink / raw)
To: M Sarwar <sarwarmd02@outlook.com>; +Cc: pgsql-admin@lists.postgresql.org
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@outlook.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@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
>>
^ permalink raw reply [nested|flat] 15+ messages in thread
end of thread, other threads:[~2023-07-20 16:54 UTC | newest]
Thread overview: 15+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-19 23:41 How to schedule long running SQL job M Sarwar <sarwarmd02@outlook.com>
2023-07-19 23:45 ` John Scalia <jayknowsunix@gmail.com>
2023-07-19 23:57 ` M Sarwar <sarwarmd02@outlook.com>
2023-07-20 00:09 ` Ron <ronljohnsonjr@gmail.com>
2023-07-20 00:20 ` M Sarwar <sarwarmd02@outlook.com>
2023-07-20 13:26 ` Ron <ronljohnsonjr@gmail.com>
2023-07-20 14:07 ` M Sarwar <sarwarmd02@outlook.com>
2023-07-20 16:54 ` Ron <ronljohnsonjr@gmail.com>
2023-07-20 00:13 ` John Scalia <jayknowsunix@gmail.com>
2023-07-20 00:34 ` M Sarwar <sarwarmd02@outlook.com>
2023-07-20 00:48 ` Jonathan Katz <jonathan.katz@excoventures.com>
2023-07-20 01:25 ` M Sarwar <sarwarmd02@outlook.com>
2023-07-20 00:35 ` Doug Reynolds <mav@wastegate.net>
2023-07-20 00:39 ` M Sarwar <sarwarmd02@outlook.com>
2023-07-20 00:50 ` Doug Reynolds <mav@wastegate.net>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox