Re: Cloning schemas

[Adrian Klaver]

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

Re: Cloning schemas

[Melvin Davidson]

Dias,

As an experiment, I commented out the code that creates the comment on
indexes and it still works flawlessly, so that part is redundant.
I have attached the modified function below,
Please retry and see if the problem still exists.
If it does, then please do a schema only pg_dump of the source schema abd
send me the call to clone schema that you are using.

-- 
*Melvin Davidson*
*Maj. Database & Exploration Specialist*
*Universe Exploration Command – UXC*
Employment by invitation only!
-- Function: public.clone_schema(text, text, boolean)

-- DROP FUNCTION public.clone_schema(text, text, boolean);

CREATE OR REPLACE FUNCTION public.clone_schema(
source_schema text,
dest_schema text,
include_recs boolean)
  RETURNS void AS
$BODY$
--  Initial code by Emanuel '3manuek' 
--  Last revision 2017-04-17 by Melvin Davidson
--  Added SELECT REPLACE for schema views
--
--  This function will clone all sequences, tables, indexes, rules, triggers, 
--  data(optional), views & functions from any existing schema to a new schema
-- SAMPLE CALL:
-- SELECT clone_schema('public', 'new_schema', TRUE);

DECLARE
  src_oid  oid;
  tbl_oid  oid;
  func_oid oid;
  con_oid  oid;
  v_path   text;
  v_func   text;
  v_args   text;
  v_connametext;
  v_rule   text;
  v_trig   text;
  object   text;
  buffer   text;
  srctbl   text;
  default_ text;
  v_column text;
  qry  text;
  dest_qry text;
  v_deftext;
  v_stat   integer;
  seqval   bigint;
  sq_last_valuebigint;
  sq_max_value bigint;
  sq_start_value   bigint;
  sq_increment_by  bigint;
  sq_min_value bigint;
  sq_cache_value   bigint;
  sq_log_cnt   bigint;
  sq_is_called boolean;
  sq_is_cycled boolean;
  sq_cycledchar(10);

BEGIN

-- Check that source_schema exists
  SELECT oid INTO src_oid
FROM pg_namespace
   WHERE nspname = quote_ident(source_schema);
  IF NOT FOUND
THEN 
RAISE NOTICE 'source schema % does not exist!', source_schema;
RETURN ;
  END IF;

  -- Check that dest_schema does not yet exist
  PERFORM nspname 
FROM pg_namespace
   WHERE nspname = quote_ident(dest_schema);
  IF FOUND
THEN 
RAISE NOTICE 'dest schema % already exists!', dest_schema;
RETURN ;
  END IF;

  EXECUTE 'CREATE SCHEMA ' || quote_ident(dest_schema) ;

  -- Add schema comment
  SELECT description INTO v_def
FROM pg_description
   WHERE objoid = src_oid
   AND objsubid = 0;
  IF FOUND 
THEN 
  EXECUTE 'COMMENT ON SCHEMA ' || quote_ident(dest_schema) || ' IS ' || 
quote_literal(v_def);
  END IF;

SET search_path TO dest_schema, source_schema, 'public', pg_catalog;  
SELECT current_schemas(TRUE) INTO v_path;
RAISE NOTICE 'search path = %', v_path;

  -- Create sequences
  -- TODO: Find a way to make this sequence's owner is the correct table.
  FOR object IN
SELECT sequence_name::text 
  FROM information_schema.sequences
 WHERE sequence_schema = quote_ident(source_schema)
  LOOP
EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || 
quote_ident(object);
srctbl := quote_ident(source_schema) || '.' || quote_ident(object);

EXECUTE 'SELECT last_value, max_value, start_value, increment_by, 
min_value, cache_value, log_cnt, is_cycled, is_called 
  FROM ' || quote_ident(source_schema) || '.' || 
quote_ident(object) || ';' 
  INTO sq_last_value, sq_max_value, sq_start_value, 
sq_increment_by, sq_min_value, sq_cache_value, sq_log_cnt, sq_is_cycled, 
sq_is_called ; 

IF sq_is_cycled 
  THEN 
sq_cycled := 'CYCLE';
ELSE
sq_cycled := 'NO CYCLE';
END IF;

EXECUTE 'ALTER SEQUENCE '   || quote_ident(dest_schema) || '.' || 
quote_ident(object) 
|| ' INCREMENT BY ' || sq_increment_by
|| ' MINVALUE ' || sq_min_value 
|| ' MAXVALUE ' || sq_max_value
|| ' START WITH '   || sq_start_value
|| ' RESTART '  || sq_min_value 
|| ' CACHE '|| sq_cache_value 
|| sq_cycled || ' ;' ;

buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
IF include_recs 
THEN
EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_last_value || 
', ' || sq_is_called || ');' ; 
ELSE
EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_start_value 
|| ', ' || sq_is_called || ');' ;
END IF;

  -- add sequence comments
SELECT oid INTO tbl_oid
  FROM pg_class 
 WHERE relkind = 'S'
   AND relnamespace = src_oid
   AND relname = quote_ident(object);

SELECT description INTO v_def
  FROM pg_description
 WHERE objoid = tbl_oid
   AND objsubid = 0;

IF FOUND
  THEN  
EXECUTE 'COMMENT ON SEQUENCE ' || quote_ident(dest_schema) || '.' || 
quote_ident(object)
 || ' IS 

Re: Cloning schemas

[Adrian Klaver]

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

Re: Cloning schemas

[DiasCosta]

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


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



On Mon, Jul 9, 2018 at 12:32 PM, DiasCosta
mailto:diasco...@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  ;



-- 
*Melvin Davidson**

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



-- 
Adrian Klaver

adrian.kla...@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 

Re: Cloning schemas

[Melvin Davidson]

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 
wrote:

> On 07/09/2018 09:49 AM, Melvin Davidson wrote:
>
>
>>
>> On Mon, Jul 9, 2018 at 12:32 PM, DiasCosta > > 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  ;
>>
>>
>>
>> --
>> *Melvin Davidson**
>> Maj. Database & Exploration Specialist**
>> Universe Exploration Command – UXC***
>> Employment by invitation only!
>>
>
>
> --
> Adrian Klaver
> adrian.kla...@aklaver.com
>



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

Re: Cloning schemas

[Adrian Klaver]

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



On Mon, Jul 9, 2018 at 12:32 PM, DiasCosta > 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  ;



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



--
Adrian Klaver
adrian.kla...@aklaver.com

Re: Cloning schemas

[Melvin Davidson]

On Mon, Jul 9, 2018 at 12:32 PM, DiasCosta  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.

REINDEX VERBOSE SYSTEM  ;



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

Re: Cloning schemas

[DiasCosta]

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




On 07-07-2018 09:32, DiasCosta wrote:

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

Re: Cloning schemas

[Melvin Davidson]

On Mon, Jul 9, 2018 at 5:14 AM, Łukasz Jarych  wrote:

> Hi Melvin,
>
> i am trying to run postgresql 10 cloning schema function but still i am
> getting error...
>
> [image: image.png]
>
> Error: Error in syntax near "SYSTEM"
> Context: Function PL/pgSQL, row 212 in EXECUTE
>
> What is happening?
>
> Best,
> Jacek
>
> > Error: Error in syntax near "SYSTEM"

Jacek,
I have changed the code from OVERRIDING SYSTEM VALUE to OVERRIDING USER
VALUE
and attached the revised version below.

If that does not fix the problem, then I will need you to do a
pgdump -F p -t public.t_cpuinfo

and send the output to me.

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

-- Function: public.clone_schema(text, text, boolean)

-- DROP FUNCTION public.clone_schema(text, text, boolean);

CREATE OR REPLACE FUNCTION public.clone_schema(
source_schema text,
dest_schema text,
include_recs boolean)
  RETURNS void AS
$BODY$
--  Initial code by Emanuel '3manuek' 
--  Last revision 2018-07-03 by Melvin Davidson
--  Added ' OVERRIDING USER VALUE' for INSERT records
--
--  This function will clone all sequences, tables, indexes, rules, triggers, 
--  data(optional), views & functions from any existing schema to a new schema
-- SAMPLE CALL:
-- SELECT clone_schema('public', 'new_schema', TRUE);

DECLARE
  src_oid  oid;
  tbl_oid  oid;
  seq_oid  oid;
  func_oid oid;
  owner_id oid;
  con_oid  oid;
  v_path   text;
  v_func   text;
  v_args   text;
  v_connametext;
  v_rule   text;
  v_trig   text;
  object   text;
  buffer   text;
  srctbl   text;
  default_ text;
  v_column text;
  qry  text;
  dest_qry text;
  v_deftext;
  v_stat   integer;
  seqval   bigint;
  seq_name name;
  sq_type  oid;
  sq_last_valuebigint;
  sq_max_value bigint;
  sq_start_value   bigint;
  sq_increment_by  bigint;
  sq_min_value bigint;
  sq_cache_value   bigint;
  sq_log_cnt   bigint;
--  sq_is_called boolean;
  sq_is_cycled boolean;
  sq_typname   name;
  seq_cycled   text;
  seq_ownername;  
--  sq_cycledchar(10);
  
BEGIN

-- Check that source_schema exists
  SELECT oid INTO src_oid
FROM pg_namespace
   WHERE nspname = quote_ident(source_schema);
   
  IF NOT FOUND
THEN 
  RAISE NOTICE 'source schema % does not exist!', source_schema;
  RETURN ;
  END IF;

  -- Check that dest_schema does not yet exist
  PERFORM nspname 
FROM pg_namespace
   WHERE nspname = quote_ident(dest_schema);
   
  IF FOUND
THEN 
  RAISE NOTICE 'dest schema % already exists!', dest_schema;
  RETURN ;
  END IF;

  EXECUTE 'CREATE SCHEMA ' || quote_ident(dest_schema) ;
   
  -- Add schema comment
  SELECT description INTO v_def
FROM pg_description
   WHERE objoid = src_oid
 AND objsubid = 0;

  IF FOUND 
THEN 
  EXECUTE 'COMMENT ON SCHEMA ' || quote_ident(dest_schema) || ' IS ' || 
quote_literal(v_def);
  END IF;

  SET search_path TO dest_schema, source_schema, 'public', pg_catalog;  
  SELECT current_schemas(TRUE) INTO v_path;
  RAISE NOTICE 'search path = %', v_path;

  -- Create sequences
  -- TODO: Find a way to make this sequence's owner is the correct table.
  FOR object IN
 SELECT sequence_name::text 
  FROM information_schema.sequences
 WHERE sequence_schema = quote_ident(source_schema)

  LOOP

SELECT oid, relowner into seq_oid, owner_id
  FROM pg_class 
 WHERE relname = quote_ident(object)
   AND relkind = 'S';

SELECT seqtypid,
   seqstart,
   seqincrement,
   seqmax,
   seqmin,
   seqcache,
   seqcycle
  INTO sq_type, 
   sq_start_value,
   sq_increment_by,
   sq_max_value,
   sq_min_value,
   sq_cache_value,
   sq_is_cycled
  FROM pg_sequence
 WHERE seqrelid = seq_oid;
 
SELECT typname INTO sq_typname
  FROM pg_type 
 WHERE oid = sq_type;

SELECT rolname INTO seq_owner
  FROM pg_authid WHERE oid = owner_id;

--EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || 
quote_ident(object);
--srctbl := quote_ident(source_schema) || '.' || quote_ident(object);

IF sq_is_cycled 
  THEN 
seq_cycled = ' CYCLE'; 
  ELSE 
seq_cycled = ' NO CYCLE';
END IF;

EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || 
quote_ident(object)
 || ' AS ' || sq_typname
 || ' INCREMENT ' ||  sq_increment_by
 || ' MINVALUE ' || sq_min_value
 || ' MAXVALUE ' || sq_max_value
 || ' START WITH ' 

Re: Cloning schemas

[Łukasz Jarych]

Hi Melvin,

i am trying to run postgresql 10 cloning schema function but still i am
getting error...

[image: image.png]

Error: Error in syntax near "SYSTEM"
Context: Function PL/pgSQL, row 212 in EXECUTE

What is happening?

Best,
Jacek


sob., 7 lip 2018 o 22:20 Melvin Davidson  napisał(a):

>
>
> 2018-07-07 4:32 GMT-04:00 DiasCosta :
>
>> 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 
>> 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.
>>
>>
> You are welcome Dias!
> Good to have positive feedback.
>
>
> --
> *Melvin Davidson*
> *Maj. Database & Exploration Specialist*
> *Universe Exploration Command – UXC*
> Employment by invitation only!
>

Re: Cloning schemas

[Melvin Davidson]

2018-07-07 4:32 GMT-04:00 DiasCosta :

> 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  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.
>
>
You are welcome Dias!
Good to have positive feedback.


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

Re: Cloning schemas

[DiasCosta]

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

Re: Cloning schemas

[Łukasz Jarych]

Melvin,

thank you once again ! Yes and this is working like a charm,
I love your function and file !

Best,
Jacek

czw., 5 lip 2018 o 16:53 Melvin Davidson  napisał(a):

>
>
>
> On Thu, Jul 5, 2018 at 10:38 AM, Łukasz Jarych  wrote:
>
>> You gave me working example.
>>
>> the function from here is not working:
>>
>>
>> https://www.postgresql.org/message-id/CANu8FiyJtt-0q%3DbkUxyra66tHi6FFzgU8TqVR2aahseCBDDntA%40mail.gmail.com
>>
>> Best,
>> Jacek
>>
>> czw., 5 lip 2018 o 16:02 Melvin Davidson 
>> napisał(a):
>>
>>>
>>>
>>> On Thu, Jul 5, 2018 at 1:49 AM, Łukasz Jarych 
>>> wrote:
>>>
 From link function is not working.

>>>
>>> There is no " From link" in PostgreSQL,
>>> Would you please be more specific. Please provide a working example.
>>>
>>>
> >the function from here is not working:
>
>
> >https://www.postgresql.org/message-id/CANu8FiyJtt-0q%3DbkUxyra66tHi6FFzgU8TqVR2aahseCBDDntA%40mail.gmail.com
> 
>
>
> Jacek,
>
> That is NOT the version for PostgreSQL 10. It is for 9.6 and below only!
> I attached the working function for 10 and it is attached again to this
> response.
>
>
> --
> *Melvin Davidson*
> *Maj. Database & Exploration Specialist*
> *Universe Exploration Command – UXC*
> Employment by invitation only!
>

Re: Cloning schemas

[Melvin Davidson]

On Thu, Jul 5, 2018 at 10:38 AM, Łukasz Jarych  wrote:

> You gave me working example.
>
> the function from here is not working:
>
> https://www.postgresql.org/message-id/CANu8FiyJtt-0q%
> 3DbkUxyra66tHi6FFzgU8TqVR2aahseCBDDntA%40mail.gmail.com
>
> Best,
> Jacek
>
> czw., 5 lip 2018 o 16:02 Melvin Davidson 
> napisał(a):
>
>>
>>
>> On Thu, Jul 5, 2018 at 1:49 AM, Łukasz Jarych  wrote:
>>
>>> From link function is not working.
>>>
>>
>> There is no " From link" in PostgreSQL,
>> Would you please be more specific. Please provide a working example.
>>
>>
>the function from here is not working:

>https://www.postgresql.org/message-id/CANu8FiyJtt-0q%
3DbkUxyra66tHi6FFzgU8TqVR2aahseCBDDntA%40mail.gmail.com



Jacek,

That is NOT the version for PostgreSQL 10. It is for 9.6 and below only!
I attached the working function for 10 and it is attached again to this
response.


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

-- Function: public.clone_schema(text, text, boolean)

-- DROP FUNCTION public.clone_schema(text, text, boolean);

CREATE OR REPLACE FUNCTION public.clone_schema(
source_schema text,
dest_schema text,
include_recs boolean)
  RETURNS void AS
$BODY$
--  Initial code by Emanuel '3manuek' 
--  Last revision 2018-07-03 by Melvin Davidson
--  Added ' OVERRIDING SYSTEM VALUE' for INSERT records
--
--  This function will clone all sequences, tables, indexes, rules, triggers, 
--  data(optional), views & functions from any existing schema to a new schema
-- SAMPLE CALL:
-- SELECT clone_schema('public', 'new_schema', TRUE);

DECLARE
  src_oid  oid;
  tbl_oid  oid;
  seq_oid  oid;
  func_oid oid;
  owner_id oid;
  con_oid  oid;
  v_path   text;
  v_func   text;
  v_args   text;
  v_connametext;
  v_rule   text;
  v_trig   text;
  object   text;
  buffer   text;
  srctbl   text;
  default_ text;
  v_column text;
  qry  text;
  dest_qry text;
  v_deftext;
  v_stat   integer;
  seqval   bigint;
  seq_name name;
  sq_type  oid;
  sq_last_valuebigint;
  sq_max_value bigint;
  sq_start_value   bigint;
  sq_increment_by  bigint;
  sq_min_value bigint;
  sq_cache_value   bigint;
  sq_log_cnt   bigint;
--  sq_is_called boolean;
  sq_is_cycled boolean;
  sq_typname   name;
  seq_cycled   text;
  seq_ownername;  
--  sq_cycledchar(10);
  
BEGIN

-- Check that source_schema exists
  SELECT oid INTO src_oid
FROM pg_namespace
   WHERE nspname = quote_ident(source_schema);
   
  IF NOT FOUND
THEN 
  RAISE NOTICE 'source schema % does not exist!', source_schema;
  RETURN ;
  END IF;

  -- Check that dest_schema does not yet exist
  PERFORM nspname 
FROM pg_namespace
   WHERE nspname = quote_ident(dest_schema);
   
  IF FOUND
THEN 
  RAISE NOTICE 'dest schema % already exists!', dest_schema;
  RETURN ;
  END IF;

  EXECUTE 'CREATE SCHEMA ' || quote_ident(dest_schema) ;
   
  -- Add schema comment
  SELECT description INTO v_def
FROM pg_description
   WHERE objoid = src_oid
 AND objsubid = 0;

  IF FOUND 
THEN 
  EXECUTE 'COMMENT ON SCHEMA ' || quote_ident(dest_schema) || ' IS ' || 
quote_literal(v_def);
  END IF;

  SET search_path TO dest_schema, source_schema, 'public', pg_catalog;  
  SELECT current_schemas(TRUE) INTO v_path;
  RAISE NOTICE 'search path = %', v_path;

  -- Create sequences
  -- TODO: Find a way to make this sequence's owner is the correct table.
  FOR object IN
 SELECT sequence_name::text 
  FROM information_schema.sequences
 WHERE sequence_schema = quote_ident(source_schema)

  LOOP

SELECT oid, relowner into seq_oid, owner_id
  FROM pg_class 
 WHERE relname = quote_ident(object)
   AND relkind = 'S';

SELECT seqtypid,
   seqstart,
   seqincrement,
   seqmax,
   seqmin,
   seqcache,
   seqcycle
  INTO sq_type, 
   sq_start_value,
   sq_increment_by,
   sq_max_value,
   sq_min_value,
   sq_cache_value,
   sq_is_cycled
  FROM pg_sequence
 WHERE seqrelid = seq_oid;
 
SELECT typname INTO sq_typname
  FROM pg_type 
 WHERE oid = sq_type;

SELECT rolname INTO seq_owner
  FROM pg_authid WHERE oid = owner_id;

--EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || 
quote_ident(object);
--srctbl := quote_ident(source_schema) || '.' || quote_ident(object);

IF sq_is_cycled 
  THEN 
seq_cycled = ' 

Re: Cloning schemas

[Melvin Davidson]

On Thu, Jul 5, 2018 at 1:49 AM, Łukasz Jarych  wrote:

> From link function is not working.
>

There is no " From link" in PostgreSQL,
Would you please be more specific. Please provide a working example.

Re: Cloning schemas

[Łukasz Jarych]

>From link function is not working.

czw., 5 lip 2018 o 07:49 Łukasz Jarych  napisał(a):

> Hi Melvin.
>
> folks wrote only that it is old version and didnt care :)
>
> Tahnk you very much,
> Best,
> Jacek
>
> czw., 5 lip 2018 o 01:09 Melvin Davidson 
> napisał(a):
>
>>
>> >The folks that wanted transactional ALTER SEQUENCE might disagree:):
>> Ah, so you mean the previous version was not working or sufficient?
>>
>> https://www.postgresql.org/docs/9.6/static/sql-altersequence.html
>>
>> --
>> *Melvin Davidson*
>> *Maj. Database & Exploration Specialist*
>> *Universe Exploration Command – UXC*
>> Employment by invitation only!
>>
>

Re: Cloning schemas

[Łukasz Jarych]

Hi Melvin.

folks wrote only that it is old version and didnt care :)

Tahnk you very much,
Best,
Jacek

czw., 5 lip 2018 o 01:09 Melvin Davidson  napisał(a):

>
> >The folks that wanted transactional ALTER SEQUENCE might disagree:):
> Ah, so you mean the previous version was not working or sufficient?
>
> https://www.postgresql.org/docs/9.6/static/sql-altersequence.html
>
> --
> *Melvin Davidson*
> *Maj. Database & Exploration Specialist*
> *Universe Exploration Command – UXC*
> Employment by invitation only!
>

Re: Cloning schemas

[Melvin Davidson]

>The folks that wanted transactional ALTER SEQUENCE might disagree:):
Ah, so you mean the previous version was not working or sufficient?

https://www.postgresql.org/docs/9.6/static/sql-altersequence.html

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

Re: Cloning schemas

[Adrian Klaver]

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

Re: Cloning schemas

[Melvin Davidson]

On Wed, Jul 4, 2018 at 6:48 PM, Adrian Klaver 
wrote:

> On 07/04/2018 03:38 PM, Melvin Davidson wrote:
>
>>
>>
>> On Wed, Jul 4, 2018 at 2:48 PM, DiasCosta > > 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.
>
>
>
The problem is, AFAICS, none of the changes induced were really necessary
or increased performance.


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

Re: Cloning schemas

[Adrian Klaver]

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



On Wed, Jul 4, 2018 at 2:48 PM, DiasCosta > 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.kla...@aklaver.com

Re: Cloning schemas

[Melvin Davidson]

On Wed, Jul 4, 2018 at 2:48 PM, DiasCosta  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.
-- Function: public.clone_schema(text, text, boolean)

-- DROP FUNCTION public.clone_schema(text, text, boolean);

CREATE OR REPLACE FUNCTION public.clone_schema(
source_schema text,
dest_schema text,
include_recs boolean)
  RETURNS void AS
$BODY$
--  Initial code by Emanuel '3manuek' 
--  Last revision 2017-04-17 by Melvin Davidson
--  Added SELECT REPLACE for schema views
--
--  This function will clone all sequences, tables, indexes, rules, triggers, 
--  data(optional), views & functions from any existing schema to a new schema
-- SAMPLE CALL:
-- SELECT clone_schema('public', 'new_schema', TRUE);

DECLARE
  src_oid  oid;
  tbl_oid  oid;
  func_oid oid;
  con_oid  oid;
  v_path   text;
  v_func   text;
  v_args   text;
  v_connametext;
  v_rule   text;
  v_trig   text;
  object   text;
  buffer   text;
  srctbl   text;
  default_ text;
  v_column text;
  qry  text;
  dest_qry text;
  v_deftext;
  v_stat   integer;
  seqval   bigint;
  sq_last_valuebigint;
  sq_max_value bigint;
  sq_start_value   bigint;
  sq_increment_by  bigint;
  sq_min_value bigint;
  sq_cache_value   bigint;
  sq_log_cnt   bigint;
  sq_is_called boolean;
  sq_is_cycled boolean;
  sq_cycledchar(10);

BEGIN

-- Check that source_schema exists
  SELECT oid INTO src_oid
FROM pg_namespace
   WHERE nspname = quote_ident(source_schema);
  IF NOT FOUND
THEN 
RAISE NOTICE 'source schema % does not exist!', source_schema;
RETURN ;
  END IF;

  -- Check that dest_schema does not yet exist
  PERFORM nspname 
FROM pg_namespace
   WHERE nspname = quote_ident(dest_schema);
  IF FOUND
THEN 
RAISE NOTICE 'dest schema % already exists!', dest_schema;
RETURN ;
  END IF;

  EXECUTE 'CREATE SCHEMA ' || quote_ident(dest_schema) ;

  -- Add schema comment
  SELECT description INTO v_def
FROM pg_description
   WHERE objoid = src_oid
   AND objsubid = 0;
  IF FOUND 
THEN 
  EXECUTE 'COMMENT ON SCHEMA ' || quote_ident(dest_schema) || ' IS ' || 
quote_literal(v_def);
  END IF;

SET search_path TO dest_schema, source_schema, 'public', pg_catalog;  
SELECT current_schemas(TRUE) INTO v_path;
RAISE NOTICE 'search path = %', v_path;

  -- Create sequences
  -- TODO: Find a way to make this sequence's owner is the correct table.
  FOR object IN
SELECT sequence_name::text 
  FROM information_schema.sequences
 WHERE sequence_schema = quote_ident(source_schema)
  LOOP
EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || 
quote_ident(object);
srctbl := quote_ident(source_schema) || '.' || quote_ident(object);

EXECUTE 'SELECT last_value, max_value, start_value, increment_by, 
min_value, cache_value, log_cnt, is_cycled, is_called 
  FROM ' || quote_ident(source_schema) || '.' || 
quote_ident(object) || ';' 
  INTO sq_last_value, sq_max_value, sq_start_value, 
sq_increment_by, sq_min_value, sq_cache_value, sq_log_cnt, sq_is_cycled, 
sq_is_called ; 

IF sq_is_cycled 
  THEN 
sq_cycled := 'CYCLE';
ELSE
sq_cycled := 'NO CYCLE';
END IF;

EXECUTE 'ALTER SEQUENCE '   || quote_ident(dest_schema) || '.' || 
quote_ident(object) 
|| ' INCREMENT BY ' || sq_increment_by
|| ' MINVALUE ' || sq_min_value 
|| ' MAXVALUE ' || sq_max_value
|| ' START WITH '   || sq_start_value
|| ' RESTART '  || sq_min_value 
|| ' CACHE '|| sq_cache_value 
|| sq_cycled || ' ;' ;

buffer := quote_ident(dest_schema) || '.' || quote_ident(object);
IF include_recs 
THEN
EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_last_value || 
', ' || sq_is_called || ');' ; 
ELSE
EXECUTE 'SELECT setval( ''' || buffer || ''', ' || sq_start_value 
|| ', ' || sq_is_called || ');' ;
END IF;

  -- add sequence comments
SELECT oid INTO tbl_oid
  FROM pg_class 
 WHERE relkind = 'S'
   AND relnamespace = src_oid
   AND relname = quote_ident(object);

SELECT description INTO v_def
  FROM pg_description
 WHERE objoid = tbl_oid
   AND objsubid = 0;

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


  END LOOP;

-- Create tables 
  FOR object IN
SELECT 

Re: Cloning schemas

[DiasCosta]

Hi Melvin,

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

TIA
DCosta



On 03-07-2018 14:34, Melvin Davidson wrote:




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.

Re: Cloning schemas

[Melvin Davidson]

On Wed, Jul 4, 2018 at 2:27 PM, Łukasz Jarych  wrote:

> Hi Melvin,
>
>
>
> Best,
> Jacek
>
>
> wt., 3 lip 2018 o 15:34 Melvin Davidson  napisał(a):
>
>>
>>
>>
>>> 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.
>>
>>
> thank you very much. Awesome!!!

Jacek,
You are quite welcome.



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

Re: Cloning schemas

[Łukasz Jarych]

Hi Melvin,

thank you very much. Awesome!!!

Best,
Jacek


wt., 3 lip 2018 o 15:34 Melvin Davidson  napisał(a):

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

Re: Cloning schemas

[Melvin Davidson]

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

-- Function: public.clone_schema(text, text, boolean)

-- DROP FUNCTION public.clone_schema(text, text, boolean);

CREATE OR REPLACE FUNCTION public.clone_schema(
source_schema text,
dest_schema text,
include_recs boolean)
  RETURNS void AS
$BODY$
--  Initial code by Emanuel '3manuek' 
--  Last revision 2018-07-03 by Melvin Davidson
--  Added ' OVERRIDING SYSTEM VALUE' for INSERT records
--
--  This function will clone all sequences, tables, indexes, rules, triggers, 
--  data(optional), views & functions from any existing schema to a new schema
-- SAMPLE CALL:
-- SELECT clone_schema('public', 'new_schema', TRUE);

DECLARE
  src_oid  oid;
  tbl_oid  oid;
  seq_oid  oid;
  func_oid oid;
  owner_id oid;
  con_oid  oid;
  v_path   text;
  v_func   text;
  v_args   text;
  v_connametext;
  v_rule   text;
  v_trig   text;
  object   text;
  buffer   text;
  srctbl   text;
  default_ text;
  v_column text;
  qry  text;
  dest_qry text;
  v_deftext;
  v_stat   integer;
  seqval   bigint;
  seq_name name;
  sq_type  oid;
  sq_last_valuebigint;
  sq_max_value bigint;
  sq_start_value   bigint;
  sq_increment_by  bigint;
  sq_min_value bigint;
  sq_cache_value   bigint;
  sq_log_cnt   bigint;
--  sq_is_called boolean;
  sq_is_cycled boolean;
  sq_typname   name;
  seq_cycled   text;
  seq_ownername;  
--  sq_cycledchar(10);
  
BEGIN

-- Check that source_schema exists
  SELECT oid INTO src_oid
FROM pg_namespace
   WHERE nspname = quote_ident(source_schema);
   
  IF NOT FOUND
THEN 
  RAISE NOTICE 'source schema % does not exist!', source_schema;
  RETURN ;
  END IF;

  -- Check that dest_schema does not yet exist
  PERFORM nspname 
FROM pg_namespace
   WHERE nspname = quote_ident(dest_schema);
   
  IF FOUND
THEN 
  RAISE NOTICE 'dest schema % already exists!', dest_schema;
  RETURN ;
  END IF;

  EXECUTE 'CREATE SCHEMA ' || quote_ident(dest_schema) ;
   
  -- Add schema comment
  SELECT description INTO v_def
FROM pg_description
   WHERE objoid = src_oid
 AND objsubid = 0;

  IF FOUND 
THEN 
  EXECUTE 'COMMENT ON SCHEMA ' || quote_ident(dest_schema) || ' IS ' || 
quote_literal(v_def);
  END IF;

  SET search_path TO dest_schema, source_schema, 'public', pg_catalog;  
  SELECT current_schemas(TRUE) INTO v_path;
  RAISE NOTICE 'search path = %', v_path;

  -- Create sequences
  -- TODO: Find a way to make this sequence's owner is the correct table.
  FOR object IN
 SELECT sequence_name::text 
  FROM information_schema.sequences
 WHERE sequence_schema = quote_ident(source_schema)

  LOOP

SELECT oid, relowner into seq_oid, owner_id
  FROM pg_class 
 WHERE relname = quote_ident(object)
   AND relkind = 'S';

SELECT seqtypid,
   seqstart,
   seqincrement,
   seqmax,
   seqmin,
   seqcache,
   seqcycle
  INTO sq_type, 
   sq_start_value,
   sq_increment_by,
   sq_max_value,
   sq_min_value,
   sq_cache_value,
   sq_is_cycled
  FROM pg_sequence
 WHERE seqrelid = seq_oid;
 
SELECT typname INTO sq_typname
  FROM pg_type 
 WHERE oid = sq_type;

SELECT rolname INTO seq_owner
  FROM pg_authid WHERE oid = owner_id;

--EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || 
quote_ident(object);
--srctbl := quote_ident(source_schema) || '.' || quote_ident(object);

IF sq_is_cycled 
  THEN 
seq_cycled = ' CYCLE'; 
  ELSE 
seq_cycled = ' NO CYCLE';
END IF;

EXECUTE 'CREATE SEQUENCE ' || quote_ident(dest_schema) || '.' || 
quote_ident(object)
 || ' AS ' || sq_typname
 || ' INCREMENT ' ||  sq_increment_by
 || ' MINVALUE ' || sq_min_value
 || ' MAXVALUE ' || sq_max_value
 || ' START WITH ' || sq_start_value 
 || ' CACHE ' || 

Re: Cloning schemas

[Łukasz Jarych]

Hi Melvin,

I understand this but i can not update function by myself. Thnak you very
much !

Still errors here.

Something like:

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 SQL
state: 428C9

pon., 2 lip 2018 o 16:22 Melvin Davidson  napisał(a):

>
>
> On Mon, Jul 2, 2018 at 10:14 AM, Łukasz Jarych  wrote:
>
>>
>> Hi,
>>
>> i see. thank you
>>
>> I am using:
>>
>> [image: image.png]
>>
>> Best,
>> Jacek
>>
>> pon., 2 lip 2018 o 16:03 Adrian Klaver 
>> napisał(a):
>>
>>> 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 >> > > napisał(a):
>>> >
>>> > =?UTF-8?Q?=C5=81ukasz_Jarych?= >> > > 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.kla...@aklaver.com
>>>
>>
> Lukas,
>
> Once again, in Version 10, the developers have changed the system
> catalogs. Please use the attached clone_schema_10.sql which
> has been adjusted for PostgreSQL 10.
>
>
>
> --
> *Melvin Davidson*
> *Maj. Database & Exploration Specialist*
> *Universe Exploration Command – UXC*
> Employment by invitation only!
>

Re: Cloning schemas

[Melvin Davidson]

On Mon, Jul 2, 2018 at 10:14 AM, Łukasz Jarych  wrote:

>
> Hi,
>
> i see. thank you
>
> I am using:
>
> [image: image.png]
>
> Best,
> Jacek
>
> pon., 2 lip 2018 o 16:03 Adrian Klaver 
> napisał(a):
>
>> 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 > > > napisał(a):
>> >
>> > =?UTF-8?Q?=C5=81ukasz_Jarych?= > > > 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.kla...@aklaver.com
>>
>
Lukas,

Once again, in Version 10, the developers have changed the system catalogs.
Please use the attached clone_schema_10.sql which
has been adjusted for PostgreSQL 10.



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

-- Function: public.clone_schema(text, text, boolean)

-- DROP FUNCTION public.clone_schema(text, text, boolean);

CREATE OR REPLACE FUNCTION public.clone_schema(
source_schema text,
dest_schema text,
include_recs boolean)
  RETURNS void AS
$BODY$
--  Initial code by Emanuel '3manuek' 
--  Last revision 2017-04-17 by Melvin Davidson
--  Added SELECT REPLACE for schema views
--
--  This function will clone all sequences, tables, indexes, rules, triggers, 
--  data(optional), views & functions from any existing schema to a new schema
-- SAMPLE CALL:
-- SELECT clone_schema('public', 'new_schema', TRUE);

DECLARE
  src_oid  oid;
  tbl_oid  oid;
  seq_oid  oid;
  func_oid oid;
  owner_id oid;
  con_oid  oid;
  v_path   text;
  v_func   text;
  v_args   text;
  v_connametext;
  v_rule   text;
  v_trig   text;
  object   text;
  buffer   text;
  srctbl   text;
  default_ text;
  v_column text;
  qry  text;
  dest_qry text;
  v_deftext;
  v_stat   integer;
  seqval   bigint;
  seq_name name;
  sq_type  oid;
  sq_last_valuebigint;
  sq_max_value bigint;
  sq_start_value   bigint;
  sq_increment_by  bigint;
  sq_min_value bigint;
  sq_cache_value   bigint;
  sq_log_cnt   bigint;
--  sq_is_called boolean;
  sq_is_cycled boolean;
  sq_typname   name;
  seq_cycled   text;
  seq_ownername;  
--  sq_cycledchar(10);
  
BEGIN

-- Check that source_schema exists
  SELECT oid INTO src_oid
FROM pg_namespace
   WHERE nspname = 

Re: Cloning schemas

[Łukasz Jarych]

Hi,

i see. thank you

I am using:

[image: image.png]

Best,
Jacek

pon., 2 lip 2018 o 16:03 Adrian Klaver 
napisał(a):

> 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  > > napisał(a):
> >
> > =?UTF-8?Q?=C5=81ukasz_Jarych?=  > > 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.kla...@aklaver.com
>

Re: Cloning schemas

[Adrian Klaver]

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 > napisał(a):


=?UTF-8?Q?=C5=81ukasz_Jarych?= mailto:jarys...@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.kla...@aklaver.com

Re: Cloning schemas

[Łukasz Jarych]

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.

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: 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  napisał(a):

> =?UTF-8?Q?=C5=81ukasz_Jarych?=  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
>

Re: Cloning schemas

[Tom Lane]

=?UTF-8?Q?=C5=81ukasz_Jarych?=  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

Re: Cloning schemas

[Łukasz Jarych]

Hi ,

thank you !

You have right:

[image: image.png]

Hmm i thought that i am creating this table " audit_sq " within  clone
schema and this function is complete.

How can i fix this?

Best,
Jacek



pon., 2 lip 2018 o 13:51 Victor Noagbodji 
napisał(a):

> Hello,
>
> Can you check if the table "audit_sq" has the column "max_value"?
>
> On Jul 2, 2018, at 7:30 AM, Łukasz Jarych  wrote:
>
> Hi,
>
> i am trying to use postgresql clone schema function:
>
>
> https://www.postgresql.org/message-id/CANu8FiwiBiAjYgdehYBkJcSRFd6ZFFnN5kDJE7TG4rad5BNXZQ%40mail.gmail.com
>
> I created function clone_schema in public schema:
>
> 
>
>
> 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
> SQL state: 42703
> Can anyone help?
>
> Best,
> Jacek
>
>
>
>