Subject: Re: Problem with schemas, possibly oids?
To: pgsql-admin@lists.postgresql.org
References: <3cabefc3-0713-7f5d-27c5-c0dab55cd8c6@Bios-ICT.hr>
 <19010.1565274302@sss.pgh.pa.us>
 <CAKoxK+7XXr4rSNV8NRzPpj+pHMdfwTtzjMRRBBK+HWkKArTPxA@mail.gmail.com>
 <49e6cc59-5e8f-78c0-389d-f4c48e49f4ab@Bios-ICT.hr>
 <CAKoxK+7v-nAxVBxbMD8J7ws1ehOAHgz1vaBTrOeKZNDESF-b1w@mail.gmail.com>
From: Arni <Arni.Kromic@Bios-ICT.hr>
Openpgp: preference=signencrypt
Autocrypt: addr=Arni.Kromic@Bios-ICT.hr; prefer-encrypt=mutual; keydata=
 xsFNBFq+khoBEACdC7vnOWflosSNLuIZrzGNh4ADaQbLSGWQVBal0etZB+HZhLK86dKLoMPd
 ZQdKg3L8hTSj8mnRawDupUoi/CQeWpnhVH9QWXPtCpf7WD9u4VDK/g9i1WTOIKw+tcN/KGzC
 CxIXyvZgwNGy5oxTmSxwbHS9De4p9/LxceXmVIKpcqMnw213rPCN4i4x9G/owZ0cUmymdzTm
 +bXDlJ+LICpzDCyUSLFCYp4IXIFxSYUV8EmSqmnxcC7JZ0y+XgusnHZtfQrBrePxv0GVqJnI
 4oqji+phi1i9Xq3ZytPxelsmiIXbmlhXrICBlZyE/pZZ9GqMUEw3l61kg4KcRAkSvqDkxjtl
 Ta3GUf7yCrCGQV/lLfFKymkdWSJtuvp9lI00JoIC+uPfhGa3RU7IP4J78KnCB1DjiPAJ+8Jo
 TneTZTqgTgwS8WKL8zoHqygIaI1JYNfNeJLW/bXtHsaVX9/vYRHw5kGbgjPBaPusy7EcHFnT
 n4RprQxjNy3CQ01L1El+QFjAg652f08Q74zDxVPJ/gql9A1lpBo73lpQ2C69xoVIQEJqs/oo
 hWPyRNN1gFHOty4hJn6yIJ57dUlFNVN4s080hYgwstSqKu+MFVbfQ365SHfj87OyJa0ElviC
 lsD2de5YSd5jFTMUCF/sCLac0jtD5iZsicaGh5m8FgyCQvCnCwARAQABzSZBcm5pIEtyb21p
 xIcgPGFybmkua3JvbWljQGJpb3MtaWN0LmhyPsLBlAQTAQgAPhYhBFrGWVRUJ9AMiVAUCX3z
 nnWrztCCBQJavpIaAhsjBQkJZgGABQsJCAcCBhUKCQgLAgQWAgMBAh4BAheAAAoJEH3znnWr
 ztCCYm4P/1Y7W1NjRq2GGZxlfeFI6v7sWJA5U8bpJZa+FhdHA/8XmqGvWIYCcCiXYEIi+4Id
 CcWfuSwkSSwZ2iJdNyvR7C7nOpYq2SWTNXRn5jE2JzSBsU+tLaoA51lTvo10Ql93OvG4ZmxH
 TGUPu4mXoHyLkhERdijlrzOp+cte/2++yoJpb5o0TbbeOodXRJbid0Ywo/qgSzXSn2jPoWgU
 bp1/gpisC70fmlo7en+KwG0ZLWI3bv0zAkGf6e7FGqBYGfOIQ/wsGIvMs27QI8T80axHuSFx
 VAdfElSj2oG/aicEZIj0Y40r4nAe5YHdClHl+XGTpu5rfTRb7lPQA/c6eTDgQdFEBP3gxdPC
 C5PrpzzlQ80Hkh2QoAXvrb1K3ZDrzIAznhazxdcFGSxy8TTer9BNVU7Vm84SYfEOZNMKm5Tw
 UUWIyzNe/pgVru+awTSpwztKHWhLv3EXC3kgDMkl0WBilQ2ggeyCsVMgK80xAi/osiXKVs/N
 yARWEykNypoeVl6LTl9Ajcxzod8D/fc84dQgiFC+tJdcnOa5fzBze6FsCWJ6Wp60Lbi9jz+P
 9erwqc7eKC8xic1Sz69k9r4oqJx1ze+Gxd5zvPkRgmBcHK9xPECgRuormE+HYPjY5HBquRfj
 OkMM1QrbvIh6snyPXiHYNL8uWtahRqlLM01Iv9su3kzmzsFNBFq+khoBEACxJcgeuQYA/1A1
 A+IdSsPBF7ZL7cqBGV1BROZSy1ugCWI88yF29CQENEBkpJ2QihIx7u1SA/8qYMGw5Nda7bUZ
 DfbYenznvh3lEn2eBVKeg1UMVjmrD1ZxWpy1mbzpSgjPu4AkdLG2YkbO8spjw/gd5btcZ/dP
 gjL9D7WwuMlCKUyyzOakdjZtD1ry2Ppps/LFPHcYBMJRWYARZruKAawI+cLTeohtFuQPxNzQ
 2hVNAqZLBl7miYt6kG4nF2gYvw1eOa6XSjlvvNqTRUiZUzZOhZAE+l/tDiguu7qsud0JdBx5
 FMX8nd9aS9aXajJZ54vQd/7jwikrRaqfVg7uKL6sxWggPNgxZCm7aeJZxfi5EJndgaBCoacQ
 K6QfMblE495DvEeVCjH/lcyW7Nx/dw4xD7kKt8SW+0hKADW9cVW0eMxeu7pz3XBBBDMNnM7c
 gODiTC0You5d8zCnePwXS8aWqvTuCR/yh9sKi2NQX3ogJKEPvcMhGYvRSXi+LpcUKFCcfgkt
 ErktPuDeqYpqe3Q0WnIsEnSKzZITm3ZLhuNdTwW0t94Wh8c/tGHS+kXQeurN2N9gg4C55F92
 LIyXp2IHAqdvcoMfSRpzaQ88MDghR/N7+u5fcX7mHgcakXZdBdNgaHIeeWm55X9ntxD/1rVI
 SpSN0Bh8FMhqVJCGnCExBwARAQABwsF8BBgBCAAmFiEEWsZZVFQn0AyJUBQJffOedavO0IIF
 Alq+khoCGwwFCQlmAYAACgkQffOedavO0IIjfQ/7Bh5bViTTu6V2MtUkuGX3r5sbWv25PJOJ
 u2fMLhB/2FfbseP6HmuHcXx7hCPVMYKqClGtKlJJzyyTsQNw7yyfZzR1YFl1gmn6i6kWOQzj
 RJgEJBF1BybdGvYRW0AnrlsMKJwo9yRi0y/dd6Tqwz70/LrpdjufM5OKKfTBRS97zeY7di4Y
 G0iZEcCFGS+y0DQn6lT6DsJIQWattI4yKnWss7rJhLYNLa5mZIsyTceT88DicDwZLK7T/bJs
 LZ5iqqDjb3XQeXxpI654Z3QAjrtQ01Q+oiEZTvyYVbTaIi3UgSnNT1zp58c+lPxxjZgOhZAz
 7HCCgtsQlBQW05O1t2D96EekIkKQFoFIB+wgc5EazDpRSZHKe9e+76yqWuNafRGjevNy60UI
 TTx05+po7kNWxd24C/MUj+otdyALGr1qmPbtbePs4LcOuQr/vhVFG9Kf4swxzx1C+Yc/icRz
 bblJQJTfGnHI7iQQNxMN7l0HFtlhtnyCk41cTBbM7eCDHxZNyK/Yhw+u74woiSElmhhkpIiW
 iXvy0/k/XoiKhGY4erYee5dDIY/DFuOv8C1g8snZ/XBEmp3u6tkZXmG5JKC0iJ87Hg623fvU
 SKjtNgnUGhTCURtzrIPjthErfOcIPaQdpa1h1076+2N6An7uqWnJBR6/ztqQlVP+IGemNP6y 6w0=
Organization: BIOS ICT d.o.o. Split
Message-ID: <7b555c9d-8d50-eced-53e8-b64bd35b883a@Bios-ICT.hr>
Date: Fri, 9 Aug 2019 12:04:46 +0200
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:60.0) Gecko/20100101
 Thunderbird/60.8.0
MIME-Version: 1.0
In-Reply-To: 
 <CAKoxK+7v-nAxVBxbMD8J7ws1ehOAHgz1vaBTrOeKZNDESF-b1w@mail.gmail.com>
Content-Type: text/plain; charset=utf-8
Content-Transfer-Encoding: 8bit
Content-Language: hr-HR
Precedence: bulk


Pozdrav,
Arni

On 09/08/2019 11.53, Luca Ferrari wrote:
> On Fri, Aug 9, 2019 at 11:39 AM Arni <Arni.Kromic@bios-ict.hr> wrote:
>> On 08/08/2019 16.38, Luca Ferrari wrote:
>>> the first query is performed with quote_ident and that's the initial
>>> failure.
>> Oh now I get it, that was the reason why it wouldn't clone an existing
>> schema! My schema names are always quoted and that's why it fails. I
>> removed that quote_indent and it sort of works now (even though it still
>> fails trying to do something on a nonexistant column!) I wonder how
>> nobody noticed this before, while so many people discussed the script.
>> As I'm not so good with procedural SQL, I  wouldn't have caught it too.
>> Many thanks!
> I didn't get this: your schema name requires double quotes because it
> has uppercase letters, and you fixed it removing quote_ident?
> Do you have the time and patient to reproduce every single query done
> by the script and see what exactly does not work? Because it seems to
> me that the schema is always quote_ident-ed.
Always quote_ident-ed... well, yes and no :)
The confusion seems to spring from the way quote_ident works! Let me
illustrate with two examples:

postgres=# select quote_ident('HR0000');
 quote_ident
-------------
 "HR0000"
(1 row)

postgres=# select quote_ident('hr0000');
 quote_ident
-------------
 hr0000
(1 row)

Apparently, postgres thinks the identifier should be quoted if it
contains capitals, and not if there are none (or any other characters it
finds offensive!) That inevitably leads to different behaviors for
different types of identifier names, which may introduce subtle bugs as
is the case here.

> Luca
>
>
>