Re: Cloning schemas - Mailing list pgsql-general

On 07/09/2018 09:49 AM, Melvin Davidson wrote:
> 
> 
> On Mon, Jul 9, 2018 at 12:32 PM, DiasCosta <diascosta@diascosta.org 
> <mailto:diascosta@diascosta.org>> wrote:
> 
>     Hi Melvin,
> 
>     Trying run 9.6 clone_schema on a different schema and I get the
>     following error:
> 
>     NOTICE:  search path = {public,pg_catalog}
>     CONTEXT:  PL/pgSQL function clone_schema(text,text,boolean) line 79
>     at RAISE
>     ERROR:  relation "bilhetica_logic_schema.idx_unq_cod_operador_aml"
>     does not exist
>     CONTEXT:  SQL statement "COMMENT ON INDEX
>     bilhetica_logic_schema.idx_unq_cod_operador_aml IS 'garante
>     unicidade do Cod_Operador_AML';"
>     PL/pgSQL function clone_schema(text,text,boolean) line 401 at EXECUTE
>     ********** Error **********
> 
>     ERROR: relation "bilhetica_logic_schema.idx_unq_cod_operador_aml"
>     does not exist
>     SQL state: 42P01
>     Context: SQL statement "COMMENT ON INDEX
>     bilhetica_logic_schema.idx_unq_cod_operador_aml IS 'garante
>     unicidade do Cod_Operador_AML';"
>     PL/pgSQL function clone_schema(text,text,boolean) line 401 at EXECUTE
> 
> 
>     Can you help me, please?
>     Thanks in advance
>     Dias Costa
> 
> 
> Dias
>  > NOTICE:  search path = {public,pg_catalog}
>  >ERROR: relation "bilhetica_logic_schema.idx_unq_cod_operador_aml" does 
> not exist
> 
> This is not related to the clone_schema function. It looks like you may 
> have corruption in your syste catalogs,
> Try reindexing your system_catalogs.

Or from clone_schema.sql:

EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' || 
quote_ident(source_schema) || '.' || quote_ident(object)
         || ' INCLUDING ALL)';

https://www.postgresql.org/docs/10/static/sql-createtable.html

"LIKE source_table [ like_option ... ]

...

Indexes, PRIMARY KEY, UNIQUE, and EXCLUDE constraints on the original 
table will be created on the new table only if INCLUDING INDEXES is 
specified. <*/Names for the new indexes and constraints are chosen 
according to the default rules, regardless of how the originals were 
named. (This behavior avoids possible duplicate-name failures for the 
new indexes.)/*>

...
INCLUDING ALL is an abbreviated form of INCLUDING COMMENTS INCLUDING 
CONSTRAINTS INCLUDING DEFAULTS INCLUDING IDENTITY INCLUDING INDEXES 
INCLUDING STATISTICS INCLUDING STORAGE.

..."

See tagged part(<*/ /*>) part above. I could see where the indexes in 
the new schema have new names while the index comments in the old schema 
refer to the old name. Then you would get the error the OP showed.

> 
> REINDEX VERBOSE SYSTEM  <your_database_name>;
> 
> 
> 
> -- 
> *Melvin Davidson**
> Maj. Database & Exploration Specialist**
> Universe Exploration Command – UXC***
> Employment by invitation only!


-- 
Adrian Klaver
adrian.klaver@aklaver.com