Re: Cloning schemas

DiasCosta <diascosta@xxxxxxxxxxxxx> · Mon, 9 Jul 2018 23:07:41 +0100

    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.klaver@xxxxxxxxxxx>
          wrote:

          On
            07/09/2018 09:49 AM, Melvin Davidson wrote:

                  On Mon, Jul 9, 2018 at 12:32 PM, DiasCosta <diascosta@xxxxxxxxxxxxx
                  <mailto:diascosta@xxxxxxxxxxxxg>>
                  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@xxxxxxxxxxx

        -- 

                  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.