Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qMGs3-0020JC-9C for pgsql-admin@arkaria.postgresql.org; Wed, 19 Jul 2023 23:46:15 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1qMGs1-00CfHM-Pk for pgsql-admin@arkaria.postgresql.org; Wed, 19 Jul 2023 23:46:13 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qMGs1-00CfHE-Ac for pgsql-admin@lists.postgresql.org; Wed, 19 Jul 2023 23:46:13 +0000 Received: from mail-qv1-xf2e.google.com ([2607:f8b0:4864:20::f2e]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qMGrx-000XH7-Uz for pgsql-admin@lists.postgresql.org; Wed, 19 Jul 2023 23:46:12 +0000 Received: by mail-qv1-xf2e.google.com with SMTP id 6a1803df08f44-635dd1b52a2so1521246d6.3 for ; Wed, 19 Jul 2023 16:46:10 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1689810368; x=1690415168; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :from:content-transfer-encoding:from:to:cc:subject:date:message-id :reply-to; bh=t8chyyBsiaqAzUADCOA6c8wQzjgq0AbcFM+0C+7/kDk=; b=imMBOr4XeNdmWi2/wuZAYkfQoxS62/tKm/+kRQZUAjDg7EmJf7xeR7j8NGcLNSCzsZ j047LEhFxRKBs7h9ygALo+r4HM/XSoUDB+xk5Vo2wN0UYtwElFeQP2n0HbVaiHcNDWTv y0nluKlS/WArw9yBbQjHRSvGaFo60yYCZCtxy+HBStYPMMryG8cqyAxNT8V2EeHl7beJ lFk/3ZQUEh8hqewOWtnPU5CYVk7j8ORYMRLgI7gHTtLUrFD7nIJU9ndBExZSrfnZPnD7 buZZgQn8fzHWfUy9iSysVh5eBrqB97RBhKeUuLUI7ecx5by+Cm/pizk1vbbtJKp4QNu/ uYhQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1689810368; x=1690415168; h=to:in-reply-to:cc:references:message-id:date:subject:mime-version :from:content-transfer-encoding:x-gm-message-state:from:to:cc :subject:date:message-id:reply-to; bh=t8chyyBsiaqAzUADCOA6c8wQzjgq0AbcFM+0C+7/kDk=; b=NFQ7Aq81vGiqYM2B2QQxqEapXKjfp5YtkxMJHoPWgtEDunHGjCIooyAmv9epGh0lGb OOtXzid76ZfczM+Ich2A4XcmPhigPEhfY3bk+6cl6H77x7HVGogE//H/Ldyp5lrR81C7 8Zjqi+LrfRkQWr7YRBb41Fp9XtSGheBBQaf6NsrM/G9iOpshSlESwDnUuV556awXl30q ciJ5JS9VPYTL/AKWTjI1VcCKReaP83cR/ZUtlSOQ8oTY6Krn07AGz15lRoaFSofPVaw6 x8Yspm2bqAYVKgRpHRLD8N75oAcPZ1mofYQ9ezKHgLOSSVJWu8j6fpiz3rSASCFRPfFQ xH7w== X-Gm-Message-State: ABy/qLZQ5s8VpT9mCzjCsorX2Yb0qBgjdm67PX7JjbjTh4UTqpnODQrH pw7AVBSb4FdiTewIHDESQhA3bMWJ4kI= X-Google-Smtp-Source: APBJJlGYU72kiIt0Q5BhHshizzwxVpFzfAWtr/a8EVLbwQ/QoEQqJHIO1WOjJsyy1PUxmm7dyV1egA== X-Received: by 2002:a0c:db93:0:b0:635:f89a:1d7b with SMTP id m19-20020a0cdb93000000b00635f89a1d7bmr644008qvk.47.1689810367819; Wed, 19 Jul 2023 16:46:07 -0700 (PDT) Received: from smtpclient.apple (h95.164.133.40.dynamic.ip.windstream.net. [40.133.164.95]) by smtp.gmail.com with ESMTPSA id p17-20020a0ccb91000000b006365b23b5dfsm1788485qvk.23.2023.07.19.16.46.05 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 19 Jul 2023 16:46:05 -0700 (PDT) Content-Type: multipart/alternative; boundary=Apple-Mail-97348DC5-F418-4B49-972A-B185052FE694 Content-Transfer-Encoding: 7bit From: John Scalia Mime-Version: 1.0 (1.0) 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> References: Cc: pgsql-admin@lists.postgresql.org In-Reply-To: To: M Sarwar X-Mailer: iPad Mail (20F75) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail-97348DC5-F418-4B49-972A-B185052FE694 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Try pg_cron. I know later versions support it. Sent from my iPad > On Jul 19, 2023, at 7:41 PM, M Sarwar wrote: >=20 > =EF=BB=BF > Hi, > Db: Postgres > Cloud: aws / RDS > Tool using so far: PgAdmin >=20 > 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. >=20 > 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_SERIA= L_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 =3D P1.FILE_ID > AND EXISTS ( SELECT S= UBSTRING(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]+') =3D SUBSTRING(P1.SERIA= L_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 > ; >=20 > I used to use crontab or OEM to schedule these types jobs in the past. Now= it is on RDS/AWS. >=20 >=20 > Thanks, > Sarwar >=20 --Apple-Mail-97348DC5-F418-4B49-972A-B185052FE694 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Try pg_cron. I know later versions support i= t.

Sent from my iPad

On Jul 19, 2023, at 7:41 PM, M Sarwar <sarwarmd02@ou= tlook.com> wrote:

=EF=BB=BF
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,

            &nbs= p;   F.FILENAME,

            &nbs= p;   P1.STAGE,

            &nbs= p;   P1.SERIAL_NUMBER,

            &nbs= p;   P1.TEST_IMAGE_SET_VALUE,

            &nbs= p;   SUBSTRING(P1.SERIAL_NUMBER, '[A-Z][0-9]+') AS MCM_ID_SERIAL_N= UMBER,

            &nbs= p;   P1.RUN_ID,

            &nbs= p;   SPLIT_PART(P1.RUN_ID, ':', 1) TEST_LOT

FROM BRONX.TEST_PART_DETAILS_ALL_MCM P1,

            &nbs= p;   BRONX.FILES_METADATA F

WHERE F.FILE_ID =3D P1.FILE_ID

            &nbs= p;            &n= bsp;      AND EXISTS      =    (           &= nbsp;  SELECT SUBSTRING(P2.SERIAL_NUMBER, '[A-Z][0-9]+') AS MCM_ID2 ,

            &nbs= p;            &n= bsp;            =             &nbs= p;            &n= bsp;            =             &nbs= p;            &n= bsp;          COUNT(DISTINCT SP= LIT_PART(P2.RUN_ID, ':', 1)) AS NUM_DISTINCT_TEST_LOTS

            &nbs= p;            &n= bsp;            =             &nbs= p;            &n= bsp;            =     FROM BRONX.TEST_PART_DETAILS_ALL_MCM P2

            &nbs= p;            &n= bsp;            =             &nbs= p;             &= nbsp;            = ;  WHERE SUBSTRING(P2.SERIAL_NUMBER, '[A-Z][0-9]+') =3D SUBSTRING(P1.SE= RIAL_NUMBER, '[A-Z][0-9]+')

            &nbs= p;            &n= bsp;            =             &nbs= p;            &n= bsp;            =     GROUP BY MCM_ID2

            &nbs= p;            &n= bsp;            =             &nbs= p;            &n= bsp;            =     HAVING COUNT(DISTINCT SPLIT_PART(P2.RUN_ID, ':', 1)) = ; > 1

            &nbs= p;            &n= bsp;            =             &nbs= p;             )=

order BY            &= nbsp;  MCM_ID_SERIAL_NUMBER,

            &nbs= p;            &n= bsp;            =           TEST_LOT

;

I used to use crontab or OEM to schedule these types jobs in the past. Now i= t is on RDS/AWS.


Thanks,
Sarwar

= --Apple-Mail-97348DC5-F418-4B49-972A-B185052FE694--