Обсуждение: Problem with schemas, possibly oids?

Hello.
We have some weird errors preventing us from using a postgres database. Software version is PostgreSQL 11.4. on 64-bit Debian Stretch (9.9), installed from the upstream apt repo.

We're using the clone_schema function from a postgres mailing list (converted to a procedure now) to create new schemas from a template, and so far we have created about 170 schemas that way. Now we have encountered a problem which prevents us from creating new schemas:

CALL clone_schema('klijent_tpl', 'test-2019')
Although this has worked perfectly until now, the call results in this error:

ERROR: relation "test-2019.bc_mob_pr_pnd" does not exist CONTEXT: SQL statement "COMMENT ON TRIGGER brisi_stavku ON "test-2019".bc_mob_pr_pnd IS 'Briši stavke ponude prije brisanja zaglavlja ponude'" PL/pgSQL function clone_schema(text,text,boolean) line 444 at EXECUTE SQL state: 42P01

Just to note that "test-2019" schema doesn't exist, ant the template schema "klijent_tpl" contains NO relation named bc_mob_pr_pnd nor the trigger mentioned in the error message. I see that as a clue it references the wrong  schema, obviously one of the existing schemas which DO contain a table named bc_mob_pr_pnd and a trigger.

Moreover, trying to clone some of the existing schemas also fails:
call clone_schema ('HR16101766338-2018', 'test')

NOTICE: source schema HR16101766338-2018 does not exist!

The source schema DOES exist.

There are other weird errors that have started happening. For example, if I try any select query from within the PGAdmin4 Query Tool, there is no Data Output, and I get only 'table_oid' (together with single quotes) in the     Messages pane (although View/Edit from the context menu on a table works, so do the queries from psql shell). This has also worked fine before.

Another error happens if I try to backup (from PGAdmin4 too). The command it generates is:

/usr/bin/pg_dump --file "/var/lib/pgadmin/storage/postgres_hostname.domain.tld/test" --host "/var/run/postgresql" --port "5432" --username "postgres" --no-password --verbose --format=c --blobs --schema "HR00000000001-2019" "nubes-test"

It fails with:

1. pg_dump: last built-in OID is 16383
2. pg_dump: no matching schemas were found

I'm not actually sure if this has something to do with the other problems, for I've already seen pg_dump fail just because of the schema name. However, since OID is mentioned, I'd say it may have something to do with this.

As I understand, every database object name is mapped to its OID, so it seems likely to me that mentioned problems where objects are referenced by name and mysteriously found nonexistent, would have something to do with OIDs...? Especially since OIDs are explicitly mentioned in some of the error messages. However, I have no clue how to fix this.

I have VACUUMed and REINDEXed the database for a good measure, but nothing has changed.

Could someone help with this, please? TIA

On Thu, Aug 8, 2019 at 11:04 AM Arni Kromić <Arni.Kromic@bios-ict.hr> wrote:
> Moreover, trying to clone some of the existing schemas also fails:
> call clone_schema ('HR16101766338-2018', 'test')
>
> NOTICE: source schema HR16101766338-2018 does not exist!
>
> The source schema DOES exist.

This could lead to a catalog corruption, even if it is a lot strange!
What does:
SELECT oid, nspname FROM pg_namespace WHERE nspname = quote_ident(
'HR16101766338-2018');

return? And what about

SELECT oid, nspname FROM pg_namespace WHERE nspname  like
'%HR16101766338-2018%';

Luca

On 8/8/19 4:04 AM, Arni Kromić wrote:
> Hello.
> We have some weird errors preventing us from using a postgres database. 
> Software version is PostgreSQL 11.4. on 64-bit Debian Stretch (9.9), 
> installed from the upstream apt repo.
>
> We're using the clone_schema function from a postgres mailing list 
> (converted to a procedure now) to create new schemas from a template, and 
> so far we have created about 170 schemas that way. Now we have encountered 
> a problem which prevents us from creating new schemas:
>
> CALL clone_schema('klijent_tpl', 'test-2019')
> Although this has worked perfectly until now, the call results in this error:
>> ERROR: relation "test-2019.bc_mob_pr_pnd" does not exist CONTEXT: SQL 
>> statement "COMMENT ON TRIGGER brisi_stavku ON "test-2019".bc_mob_pr_pnd 
>> IS 'Briši stavke ponude prije brisanja zaglavlja ponude'" PL/pgSQL 
>> function clone_schema(text,text,boolean) line 444 at EXECUTE SQL state: 42P01
> Just to note that "test-2019" schema doesn't exist, ant the template 
> schema "klijent_tpl" contains NO relation named bc_mob_pr_pnd nor the 
> trigger mentioned in the error message. I see that as a clue it references 
> the wrong  schema, obviously one of the existing schemas which DO contain 
> a table named bc_mob_pr_pnd and a trigger.
>
> Moreover, trying to clone some of the existing schemas also fails:
> call clone_schema ('HR16101766338-2018', 'test')

Maybe there's a bug in clone_schema or it's wants some assumptions that you 
aren't fulfilling.

What if you manually execute the contents ofclone_schema to generate DDL and 
then run that DDL?


-- 
Angular momentum makes the world go 'round.

Thank you for your response.

On 08/08/2019 14.08, Luca Ferrari wrote:
> On Thu, Aug 8, 2019 at 11:04 AM Arni Kromić <Arni.Kromic@bios-ict.hr> wrote:
>> Moreover, trying to clone some of the existing schemas also fails:
>> call clone_schema ('HR16101766338-2018', 'test')
>>
>> NOTICE: source schema HR16101766338-2018 does not exist!
>>
>> The source schema DOES exist.
> This could lead to a catalog corruption, even if it is a lot strange!
> What does:
> SELECT oid, nspname FROM pg_namespace WHERE nspname = quote_ident(
> 'HR16101766338-2018');
>
> return? 
That returns nothing...

> And what about
>
> SELECT oid, nspname FROM pg_namespace WHERE nspname  like
> '%HR16101766338-2018%';
>
> Luca
>
>
>
This works! I get:
 oid  |      nspname
-------+--------------------
 16385 | HR16101766338-2018
(1 row)

I get the same thing if I try with '%HR16101766338-2018'. So it seems as
if there were some characters prepended to the name... I've checked for
spaces, but there are none. What could this mean?

Thank you for the response.

On 08/08/2019 14.14, Ron wrote:
> On 8/8/19 4:04 AM, Arni Kromić wrote:
>> ...snip...
>
> Maybe there's a bug in clone_schema or it's wants some assumptions
> that you aren't fulfilling.
I'm not sure, but clone_schema has been successfully used to create
about 170 new schemas before this...

>
> What if you manually execute the contents ofclone_schema to generate
> DDL and then run that DDL?
>
>
At the first glance it looks like a lot of work to modify the script to
generate DDL instead of executing it directly. I may try that if no
solution is found, but I'm afraid that wouldn't make me any smarter...

On 8/8/19 7:30 AM, Arni wrote:
> Thank you for the response.
>
> On 08/08/2019 14.14, Ron wrote:
>> On 8/8/19 4:04 AM, Arni Kromić wrote:
>>> ...snip...
>> Maybe there's a bug in clone_schema or it's wants some assumptions
>> that you aren't fulfilling.
> I'm not sure, but clone_schema has been successfully used to create
> about 170 new schemas before this...

I also mentioned "or it's wants some assumptions that you aren't fulfilling" 
(which can also be considered a bug).

-- 
Angular momentum makes the world go 'round.

On 08/08/2019 14.33, Ron wrote:
> On 8/8/19 7:30 AM, Arni wrote:
>> Thank you for the response.
>>
>> On 08/08/2019 14.14, Ron wrote:
>>> On 8/8/19 4:04 AM, Arni Kromić wrote:
>>>> ...snip...
>>> Maybe there's a bug in clone_schema or it's wants some assumptions
>>> that you aren't fulfilling.
>> I'm not sure, but clone_schema has been successfully used to create
>> about 170 new schemas before this...
>
> I also mentioned "or it's wants some assumptions that you aren't
> fulfilling" (which can also be considered a bug).
>
So far I can't identify anything like that. Also considering that it has
worked flawlessly until now and that some other quirks (like PGAdmin's
message) have occured, I believe the culprit might be somewhere else in
the database...

On 8/8/19 7:39 AM, Arni wrote:
> On 08/08/2019 14.33, Ron wrote:
>> On 8/8/19 7:30 AM, Arni wrote:
>>> Thank you for the response.
>>>
>>> On 08/08/2019 14.14, Ron wrote:
>>>> On 8/8/19 4:04 AM, Arni Kromić wrote:
>>>>> ...snip...
>>>> Maybe there's a bug in clone_schema or it's wants some assumptions
>>>> that you aren't fulfilling.
>>> I'm not sure, but clone_schema has been successfully used to create
>>> about 170 new schemas before this...
>> I also mentioned "or it's wants some assumptions that you aren't
>> fulfilling" (which can also be considered a bug).
>>
> So far I can't identify anything like that. Also considering that it has
> worked flawlessly until now and that some other quirks (like PGAdmin's
> message) have occured, I believe the culprit might be somewhere else in
> the database...

Which is why I suggested, "What if you manually execute the contents 
ofclone_schema to generate DDL and then run that DDL?"  Then you might find 
where the problem is.


-- 
Angular momentum makes the world go 'round.

On Thu, Aug 8, 2019 at 2:21 PM Arni <Arni.Kromic@bios-ict.hr> wrote:
> This works! I get:
>  oid  |      nspname
> -------+--------------------
>  16385 | HR16101766338-2018
> (1 row)
>
> I get the same thing if I try with '%HR16101766338-2018'. So it seems as
> if there were some characters prepended to the name... I've checked for
> spaces, but there are none. What could this mean?
>

Can you inspect output of
SELECT oid, nspname, quote_ident( nspname ) FROM pg_namespace WHERE
nspname = '%HR1610%'

and see if there's a character provoking this (could it need to be
redirect to a file and inspect with an editor).
I suspect this is the problem why your schema cannot be "named" as it is.
On a desperate approach, I would try to update pg_namespace to force
the naming of such schema, but I don't tend to work against the
catalog in production...and since you don't seem to have a backup I
would do a few more experiments first.

Luca

Pozdrav,
Arni

On 08/08/2019 14.46, Luca Ferrari wrote:
> On Thu, Aug 8, 2019 at 2:21 PM Arni <Arni.Kromic@bios-ict.hr> wrote:
>> This works! I get:
>>  oid  |      nspname
>> -------+--------------------
>>  16385 | HR16101766338-2018
>> (1 row)
>>
>> I get the same thing if I try with '%HR16101766338-2018'. So it seems as
>> if there were some characters prepended to the name... I've checked for
>> spaces, but there are none. What could this mean?
>>
> Can you inspect output of
> SELECT oid, nspname, quote_ident( nspname ) FROM pg_namespace WHERE
> nspname = '%HR1610%'
>
> and see if there's a character provoking this (could it need to be
> redirect to a file and inspect with an editor).
> I suspect this is the problem why your schema cannot be "named" as it is.
I've rechecked and I see that
SELECT oid, nspname FROM pg_namespace WHERE nspname = 'HR16101766338-2018';
...actually works as it should, returns the name and the oid.
However, clone_schema claims it doesn't exist,  and so does pg_dump.

I've base64 encoded the name and decoded it into a file which I
inspected in hex. All character bytes are ok.

> On a desperate approach, I would try to update pg_namespace to force
> the naming of such schema, but I don't tend to work against the
> catalog in production...and since you don't seem to have a backup I
> would do a few more experiments first.
I do have a backup, and I have restored it to another database for
experimenting. I'd accept any dangerous ideas. :)

And I still don't get why PGAdmin4 doesn't work or why it returns that
'table_oid' message.
> Luca
>
>
>
--
Arni

=?UTF-8?Q?Arni_Kromi=c4=87?= <Arni.Kromic@Bios-ICT.hr> writes:
> We're using the clone_schema function from a postgres mailing list
> (converted to a procedure now) to create new schemas from a template,
> and so far we have created about 170 schemas that way. Now we have
> encountered a problem which prevents us from creating new schemas:

> CALL clone_schema('klijent_tpl', 'test-2019')
> Although this has worked perfectly until now, the call results in this
> error:
>> ERROR: relation "test-2019.bc_mob_pr_pnd" does not exist

> Moreover, trying to clone some of the existing schemas also fails:
> call clone_schema ('HR16101766338-2018', 'test')
>> NOTICE: source schema HR16101766338-2018 does not exist! 
> The source schema DOES exist.

Given that both of these troublesome schemas have dashes in their
names, the most obvious theory is that clone_schema() has some
oversight about double-quoting schema names everywhere necessary.

            regards, tom lane

On Thu, Aug 8, 2019 at 4:25 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Given that both of these troublesome schemas have dashes in their
> names, the most obvious theory is that clone_schema() has some
> oversight about double-quoting schema names everywhere necessary.

According to this implementation
<https://www.postgresql.org/message-id/CANu8FiyJtt-0q%3DbkUxyra66tHi6FFzgU8TqVR2aahseCBDDntA%40mail.gmail.com>
the first query is performed with quote_ident and that's the initial
failure. However, the original post claimed it has been converted from
a function to a procedure, so it could be the quote_ident part has
been removed.
What is the source of clone_schema?

Luca

On 08/08/2019 16.38, Luca Ferrari wrote:
> On Thu, Aug 8, 2019 at 4:25 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
>> Given that both of these troublesome schemas have dashes in their
>> names, the most obvious theory is that clone_schema() has some
>> oversight about double-quoting schema names everywhere necessary.
> According to this implementation
> <https://www.postgresql.org/message-id/CANu8FiyJtt-0q%3DbkUxyra66tHi6FFzgU8TqVR2aahseCBDDntA%40mail.gmail.com>
> the first query is performed with quote_ident and that's the initial
> failure. However, the original post claimed it has been converted from
> a function to a procedure, so it could be the quote_ident part has
> been removed.
> What is the source of clone_schema?
The clone_schema is the one from the above quoted post, i.e. the last
version by Melvin Davidson. Although it has been converted to a
procedure, the code itself has not been changed. If you wish, I could
attach it but the "$BODY$" block is identical to the original.
> Luca
>
>
>
Thank you for your responses!

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!

The other (main) problem is not solved yet, but we have gotten around it
by simply removing the comment on triggers, which obviously confused the
script. Without the comment, it works again; however it is obvious that
the script still has problems which need to be addressed to be fully
functional.

I guess the script works fine with simpler names, but it seems the
capital letters in our schema names (which require quoting where
necessary) cause all the problems. Similar problem is present when
PGAdmin tries to back such a schema up; the schema names require quoting
(but that's a PGAdmin's problem, which I'll report elsewhere.)

Thanks,
A.K.

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.

Luca

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
>
>
>

On Fri, Aug 9, 2019 at 12:04 PM Arni <Arni.Kromic@bios-ict.hr> wrote:
> 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.

And this is the behaviour I was expecting by quote_ident and
format('%I'), so I still don't see the case for the bug here. Unless
you are mixing upper and lower cases passing arguments to the
function, such as clone_schema( 'foo') and expecting it will clone
"FOO".

That's my fault, I cannot see the problem with quote_ident as it has
been applied consistently.

Luca

On 09/08/2019 12.19, Luca Ferrari wrote:
> On Fri, Aug 9, 2019 at 12:04 PM Arni <Arni.Kromic@bios-ict.hr> wrote:
>> 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.
> And this is the behaviour I was expecting by quote_ident and
> format('%I'), so I still don't see the case for the bug here. Unless
> you are mixing upper and lower cases passing arguments to the
> function, such as clone_schema( 'foo') and expecting it will clone
> "FOO".
>
> That's my fault, I cannot see the problem with quote_ident as it has
> been applied consistently.
Obviously there is a problem if it works _only_ with schemas without
capitals!
You said before:
> the first query is performed with quote_ident and that's the initial
> failure. 
So I did according to whay _you_ said - removed that quote_ident - and
now it works (thanks!)