Problem with schemas, possibly oids?

Поиск
Список
Период
Сортировка
От Arni Kromić
Тема Problem with schemas, possibly oids?
Дата
Msg-id 3cabefc3-0713-7f5d-27c5-c0dab55cd8c6@Bios-ICT.hr
обсуждение исходный текст
Ответы Re: Problem with schemas, possibly oids?  (Luca Ferrari <fluca1978@gmail.com>)
Re: Problem with schemas, possibly oids?  (Ron <ronljohnsonjr@gmail.com>)
Re: Problem with schemas, possibly oids?  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-admin
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
--
Kind regards
-----------------------------------------------
Arni Kromić
[ IT system engineer ]
arni.kromic@bios-ict.hr
Tel: +385 21 490 599
Mob: +385 95 659 5 659
----------------------------------------------

Mažuranićevo šet. 14
21000 Split, Croatia
Tel: +385 21 344 349
Fax: +358 21 490 599
http://podrska.bios-ict.hr
http://www.bios-ict.hr
----------------------------------------------

В списке pgsql-admin по дате отправления:

Предыдущее
От: Azimuddin Mohammed
Дата:
Сообщение: please unscbscribe
Следующее
От: Luca Ferrari
Дата:
Сообщение: Re: Problem with schemas, possibly oids?