From: Dean Gibson (DB Administrator) <postgresql@mailpen.com>
To: pgsql-performance@postgresql.org
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
Date: Mon, 7 Jun 2021 10:27:13 -0700
Message-ID: <bd23d5ce-0e67-05c2-cfb2-0d925ae88d54@mailpen.com> (raw)
In-Reply-To: <e837378b-7820-068b-f71c-5ea7dc1ca80c@dunslane.net>
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>
<79489b9a-608e-4a96-076a-963e7217fc3c@mailpen.com>
<e837378b-7820-068b-f71c-5ea7dc1ca80c@dunslane.net>
On 2021-06-07 04:52, Andrew Dunstan wrote:
> On 6/6/21 7:49 PM, Dean Gibson (DB Administrator) wrote:
>> On 2021-05-29 13:35, Andrew Dunstan wrote:
>>> On 5/29/21 3:59 PM, Dean Gibson (DB Administrator) wrote:
>>>> ... If I remove "CAST( license_status AS CHAR ) = 'A'", ...
>>> 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?
>> 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 caused 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().
>>
>>
>> That raises the issue of why you're using CHAR(n) fields. Just about every consultant I know advises simply avoiding them. :-)
>>
>> cheers, andrew
>>
>> --
>> Andrew Dunstan
>> EDB: https://www.enterprisedb.com
As I mentioned earlier, both the data & the table definitions come from
the FCC, the latter in the form of text files containing their formal
SQL definitions. These often change (like two weeks ago). There are 18
tables currently of interest to me, with between 30 & 60 fields in each
table. Further, the entire data set is replaced every Sunday, with
daily updates during the week. About 1/6th of the text fields are
defined as VARCHAR; the rest are CHAR. All of the text fields that are
used as indexes, are CHAR.
Being mindful of the fact that trailing blanks are significant in CHAR
fields, I find it easier to keep the original FCC table definitions, &
remap them to VIEWs containing the fields I am interested in. I've been
doing this with the FCC data for over 15 years, starting with PostgreSQL
7.3.
As far as needing a consultant in DB design, the FCC is planning a new
DB architecture "soon", & they sorely need one. When they export the
data to the public (delimited by "|"), they don't escape some characters
like "|", "\", & <cr>. That makes it fun ...
-- Dean
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: postgresql@mailpen.com
Subject: Re: AWS forcing PG upgrade from v9.6 a disaster
In-Reply-To: <bd23d5ce-0e67-05c2-cfb2-0d925ae88d54@mailpen.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox