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 1qMHIv-0021Nx-O3 for pgsql-admin@arkaria.postgresql.org; Thu, 20 Jul 2023 00:14:01 +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 1qMHIt-00D9L9-SH for pgsql-admin@arkaria.postgresql.org; Thu, 20 Jul 2023 00:13:59 +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 1qMHIt-00D9L1-Ek for pgsql-admin@lists.postgresql.org; Thu, 20 Jul 2023 00:13:59 +0000 Received: from mail-qv1-xf2b.google.com ([2607:f8b0:4864:20::f2b]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qMHIq-000XVv-62 for pgsql-admin@lists.postgresql.org; Thu, 20 Jul 2023 00:13:58 +0000 Received: by mail-qv1-xf2b.google.com with SMTP id 6a1803df08f44-635eb5b0320so1587446d6.3 for ; Wed, 19 Jul 2023 17:13:57 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1689812035; x=1690416835; 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=4LnEF/+UWcY3+Po3a6hTk3hqznpProdo4DbBLEvZgvc=; b=WcKevzPwZj49bxgWweG7McUGWppzzvaHA8y63mU1a6MWTIyq2fuyEv3ga3IF817Q4z 5uCW8Q4whMgnOPgeFbAsE+uLu2g5qxyFsujBACVtojBt2EFv5ZxrBT1yIQjXzGuLe9K7 7SkrpsgHKDr7uvBF6fQyqJ0CTox0HFEVy3Of4zroa3PTe2cuBWV4QvLWZWjjLwGO7P6l sWfKaxVAGp+KBxHyppw0tCKR4spSN6UlJnnRfj/s7moQ0J1Y771YBjzWjyvkhk9DF2qi /t75ygw6ibcnYltx4qAU0x7faX2U093AVXbkxjMuGfhqXCM9Sde/uUG/dYsLnIOy53bt NEBg== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1689812035; x=1690416835; 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=4LnEF/+UWcY3+Po3a6hTk3hqznpProdo4DbBLEvZgvc=; b=BLJEFbW/ZNcTdQO/UCQv1kl3ZXyIFXnkXNQojmWfkhkmbW8UP0KVGIps6X+SnOekcg rutagE1iBDHOE8bC0BchPDoEYd8jDBZ00XesZxTweOEFR5ef2wmNhEPyemapMSD+kXcC hoA7VEDIFFvt335BEjsPFtRWIQYpp7102FlN7UvLJL+HXtWQ8+i+nN2m5pO27WGcq/4P tem0deeTk//Fub6PP4AXNu/xdsrycEq4w/JehsDzRH05fRKgbR41OxsHW3YM0JG2SDdn cskGRzqQmJNZ9uirns85lSKf2hCdCLNBz+Zza8sTIJK8ApZ9G2V7U1wKGEWeUPRnwo+q pNpw== X-Gm-Message-State: ABy/qLawIIFicDsqFmUn4gWNXImTG+7IoOHWPRNCteQlYl8oIp8tU5kU DOiNNUJiUuXfFxEbwCCfV/JsuSDQI3g= X-Google-Smtp-Source: APBJJlGisTf5rYzlKSj2VEGhqs+Uu4fSEZSCJZ4Jtar5ZBB5O3pKL+wJU2Yn/mSKTzB9YBAkZZsNeA== X-Received: by 2002:a0c:c548:0:b0:636:e4f:6b89 with SMTP id y8-20020a0cc548000000b006360e4f6b89mr17351787qvi.9.1689812035278; Wed, 19 Jul 2023 17:13:55 -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 i4-20020a0c9c84000000b0063612e03433sm1811756qvf.101.2023.07.19.17.13.54 (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 19 Jul 2023 17:13:54 -0700 (PDT) Content-Type: multipart/alternative; boundary=Apple-Mail-EB7F9DA6-1D5F-499A-A065-498CD3C28106 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 20:13:44 -0400 Message-Id: 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-EB7F9DA6-1D5F-499A-A065-498CD3C28106 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable Do: select * from rds.allowed_extensions; and see if it=E2=80=99s in your ve= rsion support. I don=E2=80=99t 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 wrote: >=20 > =EF=BB=BF > =46rom 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 >=20 > From: John Scalia > Sent: Wednesday, July 19, 2023 7:45 PM > To: M Sarwar > Cc: pgsql-admin@lists.postgresql.org > Subject: Re: How to schedule long running SQL job > =20 > Try pg_cron. I know later versions support it. >=20 > Sent from my iPad >=20 >>> 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_SERI= AL_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.SERI= AL_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. No= w it is on RDS/AWS. >>=20 >>=20 >> Thanks, >> Sarwar >>=20 --Apple-Mail-EB7F9DA6-1D5F-499A-A065-498CD3C28106 Content-Type: text/html; charset=utf-8 Content-Transfer-Encoding: quoted-printable Do: select * from rds.allowed_extensions; a= nd see if it=E2=80=99s in your version support. I don=E2=80=99t 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:

=EF=BB=BF
=46rom my laptop on powershell and dos prompt, it is saying tht pg_cron is n= ot recognized.
Do  I need anything new installation for this?
Thanks,
Sarwar

From: John Scalia <jayknow= sunix@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 <sarwarmd= 02@outlook.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-EB7F9DA6-1D5F-499A-A065-498CD3C28106--