Обсуждение: Cloning schemas

=?UTF-8?Q?=C5=81ukasz_Jarych?= <jaryszek@gmail.com> writes:
> I am trying to use :

> "select * from clone_schema('public','Version8',true) but i am getting
> error:

> "Column "max_value" does not exist.
> LINE 1: SELECT last_value, max_value, start_value, increment_by, min...
> HINT: Maybe you wanted to point to column " "audit_sq.last_value"?
> QUERY: SELECT last_value, max_value, start_value, increment_by, min_value,
> cache_value, log_cnt, is_cycled, is_called FROM public.audit_sq;
> CONTEXT: function PL/pgSQL clone_schema(text,text,boolean), row 66 in
> EXECUTE

I guess audit_sq is a sequence?  It looks to me like this function has
not been taught about the changes in sequence metadata in PG v10.
You need to update it, or talk to its author about an update.

            regards, tom lane

On 07/02/2018 06:57 AM, Łukasz Jarych wrote:
> Hi,
> 
> "
> Strange. "audit_sq" looks like an invalid sequence table. I went 
> here,https://www.postgresql.org/docs/9.6/static/sql-createsequence.html, 
> and checked all the way back to version 7.1 and "maxvalue" has been a 
> column since back then.

What version of Postgres are you actually doing the cloning in?

Per Tom's post:

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

"
Move sequences' metadata fields into a new pg_sequence system catalog 
(Peter Eisentraut)

...

A sequence relation now stores only the fields that can be modified by 
nextval(), that is last_value, log_cnt, and is_called.

...

The main incompatibility introduced by this change is that selecting 
from a sequence relation now returns only the three fields named above. 
To obtain the sequence's other properties, applications must look into 
pg_sequence. The new system view pg_sequences can also be used for this 
purpose; it provides column names that are more compatible with existing 
code.
"

> 
> Maybe skip that table for now? It even says the last value is 1. You 
> should also check the other sequence tables. You can get them by doing:
> 
> select * from information_schema.sequences;"
> 
> Result of select:
> 
> image.png
> 
> Are you sure that I can skip " audit_sq" seq?
> 
> I wrote here because i am newbie and i can not update this. Hope for 
> your help Guys.
> 
> Best,
> Jacek
> 
> 
> 
> 
> 
> 
> 
> pon., 2 lip 2018 o 15:30 Tom Lane <tgl@sss.pgh.pa.us 
> <mailto:tgl@sss.pgh.pa.us>> napisał(a):
> 
>     =?UTF-8?Q?=C5=81ukasz_Jarych?= <jaryszek@gmail.com
>     <mailto:jaryszek@gmail.com>> writes:
>      > I am trying to use :
> 
>      > "select * from clone_schema('public','Version8',true) but i am
>     getting
>      > error:
> 
>      > "Column "max_value" does not exist.
>      > LINE 1: SELECT last_value, max_value, start_value, increment_by,
>     min...
>      > HINT: Maybe you wanted to point to column " "audit_sq.last_value"?
>      > QUERY: SELECT last_value, max_value, start_value, increment_by,
>     min_value,
>      > cache_value, log_cnt, is_cycled, is_called FROM public.audit_sq;
>      > CONTEXT: function PL/pgSQL clone_schema(text,text,boolean), row 66 in
>      > EXECUTE
> 
>     I guess audit_sq is a sequence?  It looks to me like this function has
>     not been taught about the changes in sequence metadata in PG v10.
>     You need to update it, or talk to its author about an update.
> 
>                              regards, tom lane
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

ERROR: BŁĄD: you cannot add to column "TopoToVersion_ID" DETAIL: Column "TopoToVersion_ID" is an identity column defined as GENERATED ALWAYS. HINT: Use OVERRIDING SYSTEM VALUE to override. CONTEXT: wyrażenie SQL "INSERT INTO version4.t_topotoversions SELECT * FROM public.t_topotoversions;" funkcja PL/pgSQL clone_schema(text,text,boolean), wiersz 212 w EXECUTE

Lukasz,

That ERROR is occuring because you choose to copy the data (include_recs = TRUE).

I have added OVERRIDING SYSTEM VALUE to the insert statement and attached revised version.

CAUTION: The value of TopoToVersion_ID and any other IDENTITY columns may be changed.

The revised version is attached.

-- 
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.

On 07/04/2018 03:38 PM, Melvin Davidson wrote:
> 
> 
> On Wed, Jul 4, 2018 at 2:48 PM, DiasCosta <diascosta@diascosta.org 
> <mailto:diascosta@diascosta.org>> wrote:
> 
>     Hi Melvin,
> 
>     I'm new to clone_schema.
>     Can I use it on PostgreSQL 9.6?
> 
>     TIA
>     DCostaployment by invitation only!
> 
> 
>  > Can I use it on PostgreSQL 9.6?
> 
> Yes, but because the developer(s) once again monkeyed with the system 
> catalogs, there are now

Well that is one of the things that distinguish a major release so it 
should be no surprise.

> two versions. One for 10 and one for 9.6 and below. I've attached the 
> 9.6 version for you.
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 07/04/2018 03:53 PM, Melvin Davidson wrote:
> 
> 

> 
> 
> The problem is, AFAICS, none of the changes induced were really 
> necessary or increased performance.

The folks that wanted transactional ALTER SEQUENCE might disagree:):

https://www.postgresql.org/docs/10/static/release-10.html
"Move sequences' metadata fields into a new pg_sequence system catalog 
(Peter Eisentraut)

A sequence relation now stores only the fields that can be modified by 
nextval(), that is last_value, log_cnt, and is_called. Other sequence 
properties, such as the starting value and increment, are kept in a 
corresponding row of the pg_sequence catalog. ALTER SEQUENCE updates are 
now fully transactional, implying that the sequence is locked until 
commit. The nextval() and setval() functions remain nontransactional.

...
"

> 
> 
> *Melvin Davidson**
> Maj. Database & Exploration Specialist**
> Universe Exploration Command – UXC***
> Employment by invitation only!


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On Wed, Jul 4, 2018 at 2:48 PM, DiasCosta <diascosta@diascosta.org> wrote:

Hi Melvin,

I'm new to clone_schema.
Can I use it on PostgreSQL 9.6?

TIA
DCostaployment by invitation only!

> Can I use it on PostgreSQL 9.6?

Yes, but because the developer(s) once again monkeyed with the system catalogs, there are now

two versions. One for 10 and one for 9.6 and below. I've attached the 9.6 version for you.

 

-- 
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.

Hi Melvin,

Thank you.

Dias Costa

On 04-07-2018 23:38, Melvin Davidson wrote:

On Wed, Jul 4, 2018 at 2:48 PM, DiasCosta <diascosta@diascosta.org> wrote:

Hi Melvin,

I'm new to clone_schema.
Can I use it on PostgreSQL 9.6?

TIA
DCostaployment by invitation only!

> Can I use it on PostgreSQL 9.6?

Yes, but because the developer(s) once again monkeyed with the system catalogs, there are now

two versions. One for 10 and one for 9.6 and below. I've attached the 9.6 version for you.

 

-- 
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.

-- 
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.

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

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@aklaver.com> wrote:

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

--

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.

On 07/09/2018 02:50 PM, 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.
> 

create table public.idx_test (id int, fld_1 varchar);
create index test_idx on idx_test (id);

test_(postgres)# \d idx_test
                    Table "public.idx_test"
  Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
  id     | integer           |           |          |
  fld_1  | character varying |           |          |
Indexes:
     "test_idx" btree (id)

create table sch_test.idx_test (like public.idx_test including all);

test_(postgres)# \d sch_test.idx_test
                   Table "sch_test.idx_test"
  Column |       Type        | Collation | Nullable | Default
--------+-------------------+-----------+----------+---------
  id     | integer           |           |          |
  fld_1  | character varying |           |          |
Indexes:
     "idx_test_id_idx" btree (id)

When you look up the comments you do:

  SELECT oid
       FROM pg_class
      WHERE relkind = 'i'
        AND relnamespace = src_oid

Where src_oid is the source namespace/schema. So in this case:

test_(postgres)# SELECT oid, relname
       FROM pg_class
      WHERE relkind = 'i'
        AND relnamespace = 'public'::regnamespace AND oid=2089851;
    oid   | relname
---------+----------
  2089851 | test_idx

You then do:

  SELECT relname INTO object ..

EXECUTE 'COMMENT ON INDEX ' || quote_ident(dest_schema) || '.' || 
quote_ident(object)
                  || ' IS ''' || v_def || ''';';

The problem is that the relname/object has changed in the new schema. In 
this case from text_idx --> idx_test_id_idx. So this happens:

test_(postgres)# comment on index sch_test.test_idx is 'test';
ERROR:  relation "sch_test.test_idx" does not exist


Just some muddling do with it what you will:)


-- 
Adrian Klaver
adrian.klaver@aklaver.com

On 07/09/2018 03:23 PM, Adrian Klaver wrote:
> On 07/09/2018 02:50 PM, Melvin Davidson wrote:
>>
>> Adrian,

> 
> The problem is that the relname/object has changed in the new schema. In 
> this case from text_idx --> idx_test_id_idx. So this happens:
> 
> test_(postgres)# comment on index sch_test.test_idx is 'test';
> ERROR:  relation "sch_test.test_idx" does not exist
> 
> 
> Just some muddling do with it what you will:)
> 

Should have added to the above that INCLUDING ALL encompasses INCLUDING 
COMMENTS:

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

"Comments for the copied columns, constraints, and indexes will be 
copied only if INCLUDING COMMENTS is specified. The default behavior is 
to exclude comments, resulting in the copied columns and constraints in 
the new table having no comments."

So the COMMENT ON INDEX code is redundant anyway.

-- 
Adrian Klaver
adrian.klaver@aklaver.com