Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
To: pgsql-performance@postgresql.org
References: <3fa6fd38-1f92-98ab-0055-3e0eb7e4e772@ultimeth.com>
 <2a1b34f4-73d4-585b-f4fc-3bd9b9ba248e@aklaver.com>
 <88255b75-e7e3-b29c-0e75-f989ea320570@mailpen.com>
 <70edb767-6ba0-1065-2f74-f01440e3caea@gmail.com>
 <d73e2731-31ba-df14-9b9c-974342200ae8@mailpen.com>
 <2b7b9a25-e3c7-b980-a1e3-d21d8d91d34d@gmail.com>
 <988e2438-c322-c1d5-ca88-683943a80087@mailpen.com>
 <1a2f01ce-b3da-3066-0642-3e311587ece3@aklaver.com>
 <94ed2da7-7622-e57b-1562-3f7caf40b4a8@mailpen.com>
 <5d3aa25f-a886-a3e8-ba1c-9d31c30a21c3@dunslane.net>
From: "Dean Gibson (DB Administrator)" <postgresql@mailpen.com>
Organization: UltiMeth Systems
Message-ID: <79489b9a-608e-4a96-076a-963e7217fc3c@mailpen.com>
Date: Sun, 6 Jun 2021 16:49:24 -0700
User-Agent: Mozilla/5.0 (Windows NT 10.0; Win64; x64; rv:78.0) Gecko/20100101
 Thunderbird/78.11.0
MIME-Version: 1.0
In-Reply-To: <5d3aa25f-a886-a3e8-ba1c-9d31c30a21c3@dunslane.net>
Content-Type: multipart/alternative;
 boundary="------------CA2B35953432F82FEF57F61D"
Content-Language: en-US
Archived-At: 
 <https://www.postgresql.org/message-id/79489b9a-608e-4a96-076a-963e7217fc3c%40mailpen.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------CA2B35953432F82FEF57F61D
Content-Type: text/plain; charset=utf-8; format=flowed
Content-Transfer-Encoding: 8bit

On 2021-05-29 13:35, Andrew Dunstan wrote:
> On 5/29/21 3:59 PM, Dean Gibson (DB Administrator) wrote:
>> Meanwhile, I've been doing some checking.  If I remove "CAST(
>> license_status AS CHAR ) = 'A'", the problem disappears.  Changing the
>> JOIN to a RIGHT JOIN, & replacing WHERE with ON, also "solves" the
>> problem, but there is an extra row where license_status is NULL, due
>> to the RIGHT JOIN.  Currently trying to figure that out (why did the
>> CAST ... match 'A', if it is null?)...
> Why are you using this expression? It's something you almost never want
> to do in my experience. Why not use the substr() function to get the
> first character?
>
> cheers
>
> andrew
>
> --
> Andrew Dunstan
> EDB: https://www.enterprisedb.com

Although it doesn't matter in this case, I do it because in general, it 
changes the type of the value from CHAR to bptext or whatever it is, & 
that has causes comparison issues in the past.  It's just a matter of 
habit for me when working with CHAR() types.

But this case, where it doesn't matter, I'd use LEFT().

--------------CA2B35953432F82FEF57F61D
Content-Type: text/html; charset=utf-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 2021-05-29 13:35, Andrew Dunstan
      wrote:<br>
    </div>
    <blockquote type="cite"
      cite="mid:5d3aa25f-a886-a3e8-ba1c-9d31c30a21c3@dunslane.net">
      <pre class="moz-quote-pre" wrap="">On 5/29/21 3:59 PM, Dean Gibson (DB Administrator) wrote:
</pre>
      <blockquote type="cite">
        <pre class="moz-quote-pre" wrap="">Meanwhile, I've been doing some checking.  If I remove "CAST(
license_status AS CHAR ) = 'A'", the problem disappears.  Changing the
JOIN to a RIGHT JOIN, &amp; replacing WHERE with ON, also "solves" the
problem, but there is an extra row where license_status is NULL, due
to the RIGHT JOIN.  Currently trying to figure that out (why did the
CAST ... match 'A', if it is null?)...
</pre>
      </blockquote>
      <pre class="moz-quote-pre" wrap="">Why are you using this expression? It's something you almost never want
to do in my experience. Why not use the substr() function to get the
first character?

cheers

andrew

--
Andrew Dunstan
EDB: <a class="moz-txt-link-freetext" href="https://www.enterprisedb.com">https://www.enterprisedb.com</a>
</pre>
    </blockquote>
    <br>
    Although it doesn't matter in this case, I do it because in general,
    it changes the type of the value from CHAR to bptext or whatever it
    is, &amp; that has causes comparison issues in the past.  It's just
    a matter of habit for me when working with CHAR() types.<br>
    <br>
    But this case, where it doesn't matter, I'd use LEFT().<br>
  </body>
</html>

--------------CA2B35953432F82FEF57F61D--