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 1qMTgD-002V1j-02 for pgsql-admin@arkaria.postgresql.org; Thu, 20 Jul 2023 13:26:53 +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 1qMTgA-007V8O-T1 for pgsql-admin@arkaria.postgresql.org; Thu, 20 Jul 2023 13:26:50 +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 1qMTgA-007V8C-Dw for pgsql-admin@lists.postgresql.org; Thu, 20 Jul 2023 13:26:50 +0000 Received: from mail-yb1-xb29.google.com ([2607:f8b0:4864:20::b29]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qMTg2-000egN-HQ for pgsql-admin@lists.postgresql.org; Thu, 20 Jul 2023 13:26:49 +0000 Received: by mail-yb1-xb29.google.com with SMTP id 3f1490d57ef6-cae0ad435b6so774235276.0 for ; Thu, 20 Jul 2023 06:26:43 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1689859601; x=1690464401; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:from:to:cc:subject:date:message-id :reply-to; bh=ZA9ga1XzZrefTvxF8CZv45klcO/ckSOZUPFIOm3g4s4=; b=nNOgi40IEyVLxWNZffuxWS2H9ahChmFUFxLvRpZMk8l4C3I+/sxKo9Nbh5i3ZnxpB8 8OP1AOMFSUppIOAhz92ELEbmjCdQ5ebod/RGoEUyT5h1i0uAcnor5SR+DQSmVWojHtW/ 9gg3Uvh9qOWa3W6jf4KqSe8Y721OFmt0q9wqHqsLVQl0CS00Yjxua1Lk+84xkOE87uyJ QpWccGSPFXJmjnfpXloNcHJS/AqQ3yyFpbCNBTBigbSJOE2z3IYUnUzDGN9xebP4ADpS iZlVlLB1JCwpek25vNp5BCcjqGlizlMECwdfk5Y7TflQ3H6araRdwIP2A6ioDk4pdjca B0bw== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1689859601; x=1690464401; h=in-reply-to:from:references:to:content-language:subject:user-agent :mime-version:date:message-id:x-gm-message-state:from:to:cc:subject :date:message-id:reply-to; bh=ZA9ga1XzZrefTvxF8CZv45klcO/ckSOZUPFIOm3g4s4=; b=LG4lVGxLYaZpzHsxaqfhvwhHi7xXv5xsjyGXg+xCGdSNuxihASS37VbYJiYjBmGsgE ARBnceb3tWjF1TGVw9j+D4NPGjy8L0lF4TfZwhOE+3KYXfHI6vkAt8J5jX6D4PJQL7iL HZWm6akV2J+Krh9Vp0l969IB9OOdEvRHO5UE3cG5B1YNNrQezXZZGUBKs3D4tajzQmuw qsPkrJqfHPJs11EYJ3gWl5m8clje0jhq0Pv/uisKVPr03AZsghWnVaR+8b+YSy5M2Psl qJJ9bFi2jaJC8XTzBT+7ylhXgJE4X0mjETeNqcFQizASirE1SD/YRxn6okYmv6iO6tim 8OWA== X-Gm-Message-State: ABy/qLYYfRoYbbFi2PgJEiVXTD59JjpTVG9bO2M/JiKGLF6y1AGp5GBZ dWUZ5HLClzerf900c6k2WJBg00l3wgI= X-Google-Smtp-Source: APBJJlFnNIu4pZRwCpesjeUE4UN9sMoeRFN9qghyMn3oiDoOVwqCvDynohGSt78PCWyraacyZeTjSg== X-Received: by 2002:a25:4f8a:0:b0:c93:8507:b448 with SMTP id d132-20020a254f8a000000b00c938507b448mr1907888ybb.15.1689859601413; Thu, 20 Jul 2023 06:26:41 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:64d0:3aec:6103:5398? ([2600:1700:bd2c:b810:64d0:3aec:6103:5398]) by smtp.googlemail.com with ESMTPSA id a3-20020a259383000000b00cec105e03d1sm191989ybm.38.2023.07.20.06.26.40 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 20 Jul 2023 06:26:40 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------w1Vci70UuRN0u0Cou0CqvnMG" Message-ID: <4b29b4fd-db99-4bc2-7973-0d8e744b7674@gmail.com> Date: Thu, 20 Jul 2023 08:26:39 -0500 MIME-Version: 1.0 User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:102.0) Gecko/20100101 Thunderbird/102.11.0 Subject: Re: How to schedule long running SQL job Content-Language: en-US To: "pgsql-admin@lists.postgresql.org" References: <0644DDC9-A86B-411F-A70B-DD2C99D7950F@gmail.com> <02144dd8-cece-b9e9-5903-f9170293d87a@gmail.com> From: Ron In-Reply-To: List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk This is a multi-part message in MIME format. --------------w1Vci70UuRN0u0Cou0CqvnMG Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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 > *Sent:* Wednesday, July 19, 2023 8:09 PM > *To:* 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 >> *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 >> Try pg_cron. I know later versions support it. >> >> Sent from my iPad >> >>> On Jul 19, 2023, at 7:41 PM, M Sarwar >>> 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. --------------w1Vci70UuRN0u0Cou0CqvnMG Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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>
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.

--
Born in Arizona, moved to Babylonia.
--------------w1Vci70UuRN0u0Cou0CqvnMG--