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 1qMHEb-00218c-Ru for pgsql-admin@arkaria.postgresql.org; Thu, 20 Jul 2023 00:09:34 +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 1qMHEZ-00D2Vn-SW for pgsql-admin@arkaria.postgresql.org; Thu, 20 Jul 2023 00:09:31 +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 1qMHEZ-00D2Vf-EV for pgsql-admin@lists.postgresql.org; Thu, 20 Jul 2023 00:09:31 +0000 Received: from mail-yb1-xb35.google.com ([2607:f8b0:4864:20::b35]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_128_GCM_SHA256 (Exim 4.94.2) (envelope-from ) id 1qMHET-000XTD-VI for pgsql-admin@lists.postgresql.org; Thu, 20 Jul 2023 00:09:30 +0000 Received: by mail-yb1-xb35.google.com with SMTP id 3f1490d57ef6-ca9804dc6e4so453359276.0 for ; Wed, 19 Jul 2023 17:09:26 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=20221208; t=1689811764; x=1690416564; 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=LJBH8VDzWoZAoOvdfQCm1RrsVGbOMOwdUCFlxrHZLpY=; b=C4LDoefmWZNtXVbjzRJMWquQA6ZH/Xc7hDDkqFyctlX/DnRvtXaJSf+0E6hpvSP1bX qz9sB/eo7xQHX0lpP/bix5rhJZt5Ek7TgZlfWNfJBPz/+WWJm4S8WgvEuGWWtSp1XUQB fCRpC4MjLOQj/zkzP5XedHlDs1RRqAsuc0rflbn1WYuVI7kh+LZ5rkaarIXaXcUR/Tut dmEsvdMlicHt1LeH11z7I9oVMmRfLjASOEHpmGZOofynyS7d7eUoPTKsrlUaUdbBMK0g Qft2oQUrPga0I5q1CZofHyn+OpoLUYsuoK/9eG6uciVt6gI+JAXEY0eeI4Q25auKYv6O cRuQ== X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=1e100.net; s=20221208; t=1689811764; x=1690416564; 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=LJBH8VDzWoZAoOvdfQCm1RrsVGbOMOwdUCFlxrHZLpY=; b=XNepHuQ13uTCRXOBNWSYpG7IDX9k2oJV9ciaT6BuvkJxZEvQu81e5YrvFakoD8hK+o EzhQOljNbPoxnnVurmA3rT/61qbA0av9SiKfLuSUANulRF++OsAmoywACNyntQQ8gJBw auE4Tr9iAdjXttxi4OFHqDVxf+iZVf9SiPOAER2wg2Y+cJrI4F7y2hbDQ/nTqT4fRdX0 SRD7p76qXJjfjne4fwKM3nc0ambduk7ptuy9bNiaYd/GLSU6zbVouzLWLrc8IqLcL4Vc 5Ari9Fh7P2qjJPxZB4Bkuw5kXLe8WlNQ5cTGD0mDRSAAjp/6xLi+3S1esY/8ZHPPaZZ5 Zd2w== X-Gm-Message-State: ABy/qLaKighEdtHDAHnAtS45EZZA/Bw8pk5D3DdJ/HNTtZ30Tu9XK+V9 RZJfWWGHJ8BWwifqLUrERyUU1RmjbiI= X-Google-Smtp-Source: APBJJlEsgy/Af7/bH9WGwTUixK54nc3T7+G2V8OOUbbad3wIc1OVrkH963gAeqRbp9BnGUy2nHIsng== X-Received: by 2002:a25:c54f:0:b0:ce9:fd0:32b8 with SMTP id v76-20020a25c54f000000b00ce90fd032b8mr4128442ybe.20.1689811763502; Wed, 19 Jul 2023 17:09:23 -0700 (PDT) Received: from ?IPV6:2600:1700:bd2c:b810:60d9:e4b2:59bc:4902? ([2600:1700:bd2c:b810:60d9:e4b2:59bc:4902]) by smtp.googlemail.com with ESMTPSA id z89-20020a25a162000000b00cb2a8f888ebsm1139377ybh.0.2023.07.19.17.09.23 for (version=TLS1_3 cipher=TLS_AES_128_GCM_SHA256 bits=128/128); Wed, 19 Jul 2023 17:09:23 -0700 (PDT) Content-Type: multipart/alternative; boundary="------------TJHaMjiXrdC06quCwJdRu1h3" Message-ID: <02144dd8-cece-b9e9-5903-f9170293d87a@gmail.com> Date: Wed, 19 Jul 2023 19:09:22 -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> 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. --------------TJHaMjiXrdC06quCwJdRu1h3 Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 8bit 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. --------------TJHaMjiXrdC06quCwJdRu1h3 Content-Type: text/html; charset=UTF-8 Content-Transfer-Encoding: 8bit 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.
--------------TJHaMjiXrdC06quCwJdRu1h3--