Cloning schemas

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 <vnoagbodji@amplify-nation.com>
napisał(a):

=?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

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 <tgl@sss.pgh.pa.us> 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 <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

Hi,

i see. thank you

I am using:

[image: image.png]

Best,
Jacek

pon., 2 lip 2018 o 16:03 Adrian Klaver <adrian.klaver@aklaver.com>
napisał(a):

On Mon, Jul 2, 2018 at 10:14 AM, Łukasz Jarych <jaryszek@gmail.com> wrote:

Hi,

i see. thank you

I am using:

[image: image.png]

Best,
Jacek

pon., 2 lip 2018 o 16:03 Adrian Klaver <adrian.klaver@aklaver.com>
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 <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

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!

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 <melvin6925@gmail.com> 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.

Hi Melvin,

thank you very much. Awesome!!!

Best,
Jacek

wt., 3 lip 2018 o 15:34 Melvin Davidson <melvin6925@gmail.com> napisał(a):

On Wed, Jul 4, 2018 at 2:27 PM, Łukasz Jarych <jaryszek@gmail.com> wrote:

Hi Melvin,

Best,
Jacek

wt., 3 lip 2018 o 15:34 Melvin Davidson <melvin6925@gmail.com> 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!

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.

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.

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 Wed, Jul 4, 2018 at 6:48 PM, Adrian Klaver <adrian.klaver@aklaver.com>
wrote:

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.

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!

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

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!

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 <melvin6925@gmail.com> napisał(a):

From link function is not working.

czw., 5 lip 2018 o 07:49 Łukasz Jarych <jaryszek@gmail.com> napisał(a):

On Thu, Jul 5, 2018 at 1:49 AM, Łukasz Jarych <jaryszek@gmail.com> 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.