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 19:45:55 -0400
Message-ID: <0644DDC9-A86B-411F-A70B-DD2C99D7950F@gmail.com> (raw)
In-Reply-To: <DM4PR19MB59786FDE7F5052955D319F40D339A@DM4PR19MB5978.namprd19.prod.outlook.com>
References: <DM4PR19MB59786FDE7F5052955D319F40D339A@DM4PR19MB5978.namprd19.prod.outlook.com>
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: <0644DDC9-A86B-411F-A70B-DD2C99D7950F@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