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 1qMWvc-002cYL-8y for pgsql-admin@arkaria.postgresql.org; Thu, 20 Jul 2023 16:55:00 +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 1qMWva-00CJSa-FP for pgsql-admin@arkaria.postgresql.org; Thu, 20 Jul 2023 16:54:58 +0000 Received: from makus.postgresql.org ([2001:4800:3e1:1::229]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1qMWvZ-00CJSN-Se for pgsql-admin@lists.postgresql.org; Thu, 20 Jul 2023 16:54:58 +0000 Received: from mail-yw1-x112d.google.com ([2607:f8b0:4864:20::112d]) by makus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qMWvT-000XH8-17 for pgsql-admin@lists.postgresql.org; Thu, 20 Jul 2023 16:54:56 +0000 Received: by mail-yw1-x112d.google.com with SMTP id 00721157ae682-5704fce0f23so11053617b3.3 for ; Thu, 20 Jul 2023 09:54:50 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1689872090; x=1690476890; 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=PP/Qir1fFakVQOZHsGRpODOmxKr4FGP5UkfJUSGIL3Y=; b=TmZuZVCWFgf73FiAsBSpH+IujvX2deKXGgU7tuB28kls3x/9iiOQo6vB1HOAiJ4E2M L1mB7a8XN+ebzLGgoUQMOMmLQKwdNZUXwFSUNxXvCDwsBRPmy5E0KbJ38XuPY897t13U CrWNCdnGn8+pYIV9g3urchuwd5uyj47D7I8K5bWMQcVVQuLHYNsEHel2UbMciWlc2a1Q 7kXIaSKl6EfxZcZTAMyOyqx5FobWGVJCh1TmmeAir5o8dbf8eJoDRa2M1tAA4uOoftVh K/d4RWvh0VOVUM8g7Je02OItIgLMyMYgMsKYd0htkp1QdslFwrGN/EMAyMiMvA0pIodR /QlQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1689872090; x=1690476890; 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=PP/Qir1fFakVQOZHsGRpODOmxKr4FGP5UkfJUSGIL3Y=; b=kkjSOpUoZ8lf/0UqS4ZSLmqx/BS6ap3vpbWxY5XmJ182eioSHW83LYrqoy74ZCWljz DK5UYKnDx/nD1FjU3LdFyB5Q2nl3HFjmWMMRXHM11khkVwuJh3E60zU9Z83Q6pwKQ2t4 e0czg9T6iRHtDIL29JDGx0FD7ozFPL/km7y4ziCIttYBzzEJxL8ILgLUoCpdJtzDqANa tvp/HO2phKhv410Semjz58WzpipZjzUTpt+72Jc2F0cTJ6aZ7VXfnZUCdHQy8T6QnU1a vFy+agDdoqsjlaZVDNzj7OVIy5zVmk5uHQo+pZF7K/cBUCqETkXVP0EWzDzzFQNkpbhL rNqg== X-Gm-Message-State: ABy/qLboBqQqLgo0NL6yM1BLuW0yXXU/wq9qdKAhI+QIi++sZ/9XjwCB Yk+z/KZC84vywEhZfMRmIKinPLXe2xM= X-Google-Smtp-Source: APBJJlFeeOeVwV4UtYnw2Ci0tRJptrnHo0B2bNe/XaW1Mfhe2cboYh1J9QRF8kXc7d45WfhvgheuEQ== X-Received: by 2002:a0d:f101:0:b0:56f:fba4:9b85 with SMTP id a1-20020a0df101000000b0056ffba49b85mr5863077ywf.7.1689872090019; Thu, 20 Jul 2023 09:54:50 -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 v188-20020a8185c5000000b0056cd3e598d8sm306131ywf.114.2023.07.20.09.54.49 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Thu, 20 Jul 2023 09:54:49 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------VQTbwyhk1JaMh1n1PViwCM8q" Message-ID: <1ffcd55d-b163-f40f-5881-2311d30e6a70@gmail.com> Date: Thu, 20 Jul 2023 11:54:49 -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> <4b29b4fd-db99-4bc2-7973-0d8e744b7674@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. --------------VQTbwyhk1JaMh1n1PViwCM8q Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit PgAdmin *requires* a web server (which comes built-in to the Windows stand-alone version); you don't run it "in" Postgresql. On 7/20/23 09:07, M Sarwar wrote: > Ron, > > Yes, my pgAdmin is on my laptop. > Can I run pgAdmin any where else in Postgres / RDS / AWS environment? > Thanks, > Sarwar > > ---------------------------------------------------------------------------- > *From:* Ron > *Sent:* Thursday, July 20, 2023 9:26 AM > *To:* pgsql-admin@lists.postgresql.org > *Subject:* Re: How to schedule long running SQL job > 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. -- Born in Arizona, moved to Babylonia. --------------VQTbwyhk1JaMh1n1PViwCM8q Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit PgAdmin requires a web server (which comes built-in to the Windows stand-alone version); you don't run it "in" Postgresql.

On 7/20/23 09:07, M Sarwar wrote:
Ron,

Yes, my pgAdmin is on my laptop.
Can I run pgAdmin any where else in Postgres / RDS / AWS environment?
Thanks,
Sarwar

From: Ron <ronljohnsonjr@gmail.com>
Sent: Thursday, July 20, 2023 9:26 AM
To: pgsql-admin@lists.postgresql.org <pgsql-admin@lists.postgresql.org>
Subject: Re: How to schedule long running SQL job
 
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.

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