Re: Cloning schemas
От | DiasCosta |
---|---|
Тема | Re: Cloning schemas |
Дата | |
Msg-id | c029dd3a-efcc-94ef-acb8-c6826353420c@diascosta.org обсуждение исходный текст |
Ответ на | Re: Cloning schemas (Melvin Davidson <melvin6925@gmail.com>) |
Список | pgsql-general |
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:
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 isEXECUTE '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@aklaver.com> wrote:On 07/09/2018 09:49 AM, Melvin Davidson wrote:Or from clone_schema.sql:
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.
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
--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.
В списке pgsql-general по дате отправления: