From: John Scalia <jayknowsunix@gmail.com>
To: M Sarwar <sarwarmd02@outlook.com>
Cc: pgsql-admin@lists.postgresql.org
Subject: Re: How to schedule long running SQL job
Date: Wed, 19 Jul 2023 20:13:44 -0400
Message-ID: <C50F0B6C-B557-4139-93F5-9B18D58ACF4F@gmail.com> (raw)
In-Reply-To: <DM4PR19MB597858D95BA6A938AC76EF8BD339A@DM4PR19MB5978.namprd19.prod.outlook.com>
References: <DM4PR19MB597858D95BA6A938AC76EF8BD339A@DM4PR19MB5978.namprd19.prod.outlook.com>
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
>>
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-admin@postgresql.org
Cc: jayknowsunix@gmail.com, sarwarmd02@outlook.com, pgsql-admin@lists.postgresql.org
Subject: Re: How to schedule long running SQL job
In-Reply-To: <C50F0B6C-B557-4139-93F5-9B18D58ACF4F@gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
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