Re: Cloning schemas

Hi Melvin,

I followed your recommendation and it did not work.

Since I was in a rush I did try to understand where the function crashed
and commenting in the function the creation of
comments for indexes, as follows, was sufficient for the function work.

    IF FOUND
      THEN
--        EXECUTE 'COMMENT ON INDEX ' || quote_ident(dest_schema) || '.'
|| quote_ident(object)
--                 || ' IS ''' || v_def || ''';';

Problem is I don't usually comment indexes.

Thanks and
Keep good working
Dias Costa

On 09-07-2018 22:50, Melvin Davidson wrote:
>
> Adrian,
> The code that CREATES the TABLE is
>
> EXECUTE 'CREATE TABLE ' || buffer || ' (LIKE ' ||
> quote_ident(source_schema) || '.' || quote_ident(object)
>         || ' INCLUDING ALL)';
>
> The schema names are supposed to be changed!
>
> This function HAS been tested and does WORK. Please do not muddle the
> problem without testing yourself.
>
>
> On Mon, Jul 9, 2018 at 2:56 PM, Adrian Klaver
> <adrian(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>> wrote:
>
> On 07/09/2018 09:49 AM, Melvin Davidson wrote:
>
>
>
> On Mon, Jul 9, 2018 at 12:32 PM, DiasCosta
> <diascosta(at)diascosta(dot)org <mailto:diascosta(at)diascosta(dot)org>
> <mailto:diascosta(at)diascosta(dot)org
> <mailto:diascosta(at)diascosta(dot)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
> <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(dot)klaver(at)aklaver(dot)com <mailto:adrian(dot)klaver(at)aklaver(dot)com>
>
>
>
>
> --
> *Melvin Davidson**
> Maj. Database & Exploration Specialist**
> Universe Exploration Command – UXC*
> Employment by invitation only!

--
J. M. Dias Costa
Telef. 214026948 Telem. 939307421

Se divulgar esta mensagem por terceiros, por favor:
1. Apague o meu endereço de correio electrónico e o meu nome.
2. Apague também os endereços dos seus amigos antes de distribuir.
3. Enderece como cópia oculta (Cc ou Bcc) para os SEUS destinatários.
Agindo deste modo, dificultará a disseminação de "vírus", "spams" e
"banners" e contribuirá para manter a privacidade de todos e cada um.
Obrigado.

Nota: Não se deverá ao acaso a ocorrência de palavras na minha escrita que não respeitem o
malfadado acordo ortográfico.