clone_schema function

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Melvin Davidson
Sent: Wednesday, September 09, 2015 12:31 PM
To: pgsql-general@postgresql.org
Subject: [GENERAL] clone_schema function

I noted there was an inquiry as to how to copy or clone_schema
an entire schema. The standard method for doing that is to
1. pg_dump the schema in plain format
2. edit the dump file and change all occurrences of the schema name
3. reload the dump into the new schema.

The attached function is an alternate method for doing that.
It is a revision of the clone_schema by by Emanuel '3manuek'
from https://wiki.postgresql.org/wiki/Clone_schema

Originally, it did not copy views, functions or data from
the source schema despite the claim that it "copies everything".

I've added error checking and verified that it now copies the
current sequnce values, table data, views and functions.

As always, use with caution.
--
Melvin Davidson

I assume you are aware that this script does not produce complete copy of the source schema.
Foregn Key constraints are not recreated along with the tables.

Regards,
Igor Neyman

Thanks Igor,

hmm, apparently the "INCLUDING CONSTRAINTS' option of "CREATE TABLE' has a
glitch and only includes the primary key.
I also noticed that INCLUDING ALL generates an error, so I'll have to
report that also.

I'll go eat some crow and work on a fix to add all constraints in the
meantime.

On Wed, Sep 9, 2015 at 3:43 PM, Igor Neyman <ineyman@perceptron.com> wrote:

*From:* pgsql-general-owner@postgresql.org [mailto:
pgsql-general-owner@postgresql.org] *On Behalf Of *Melvin Davidson
*Sent:* Wednesday, September 09, 2015 12:31 PM
*To:* pgsql-general@postgresql.org
*Subject:* [GENERAL] clone_schema function

I noted there was an inquiry as to how to copy or clone_schema
an entire schema. The standard method for doing that is to
1. pg_dump the schema in plain format
2. edit the dump file and change all occurrences of the schema name
3. reload the dump into the new schema.

The attached function is an alternate method for doing that.
It is a revision of the clone_schema by by Emanuel '3manuek'
from https://wiki.postgresql.org/wiki/Clone_schema

Originally, it did not copy views, functions or data from
the source schema despite the claim that it "copies everything".

I've added error checking and verified that it now copies the
current sequnce values, table data, views and functions.

As always, use with caution.
--

*Melvin Davidson*

I assume you are aware that this script does not produce complete copy of
the source schema.

Foregn Key constraints are not recreated along with the tables.

Regards,

Igor Neyman

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

From: Melvin Davidson [mailto:melvin6925@gmail.com]
Sent: Wednesday, September 09, 2015 4:48 PM
To: Igor Neyman <ineyman@perceptron.com>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] clone_schema function

Thanks Igor,
hmm, apparently the "INCLUDING CONSTRAINTS' option of "CREATE TABLE' has a glitch and only includes the primary key.
I also noticed that INCLUDING ALL generates an error, so I'll have to report that also.
I'll go eat some crow and work on a fix to add all constraints in the meantime.

It’s not a bug.
According to docs:
“Not-null constraints are always copied to the new table. CHECK constraints will only be copied if INCLUDING CONSTRAINTS is specified; other types of constraints will never be copied.”
So, FK constraints are not supposed to be copied.

Regards,
Igor Neyman

Yes, however, the documentation would be a lot clearer if it said "copies
all constraints except foreign keys". I've made this known.

At any rate, I've attached a new version of the function that now does copy
the foreign keys. Let me know if I missed anything else.

On Thu, Sep 10, 2015 at 9:09 AM, Igor Neyman <ineyman@perceptron.com> wrote:

*From:* Melvin Davidson [mailto:melvin6925@gmail.com]
*Sent:* Wednesday, September 09, 2015 4:48 PM
*To:* Igor Neyman <ineyman@perceptron.com>
*Cc:* pgsql-general@postgresql.org
*Subject:* Re: [GENERAL] clone_schema function

Thanks Igor,

hmm, apparently the "INCLUDING CONSTRAINTS' option of "CREATE TABLE' has a
glitch and only includes the primary key.

I also noticed that INCLUDING ALL generates an error, so I'll have to
report that also.

I'll go eat some crow and work on a fix to add all constraints in the
meantime.

It’s not a bug.

According to docs:

“Not-null constraints are always copied to the new table. CHECK
constraints will only be copied if INCLUDING CONSTRAINTS is specified;
other types of constraints will never be copied.”

So, FK constraints are not supposed to be copied.

Regards,

Igor Neyman

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Here is one more tweak of clone_schema.

I've added an include_recs flag.
If FALSE, then no records are copied into the tables from the old_schema
and all sequences start with the minimum value.
If TRUE, then all records are copied and sequences are set tot the last
value.

On Thu, Sep 10, 2015 at 11:52 AM, Melvin Davidson <melvin6925@gmail.com>
wrote:

Yes, however, the documentation would be a lot clearer if it said "copies
all constraints except foreign keys". I've made this known.

At any rate, I've attached a new version of the function that now does
copy the foreign keys. Let me know if I missed anything else.

On Thu, Sep 10, 2015 at 9:09 AM, Igor Neyman <ineyman@perceptron.com>
wrote:

*From:* Melvin Davidson [mailto:melvin6925@gmail.com]
*Sent:* Wednesday, September 09, 2015 4:48 PM
*To:* Igor Neyman <ineyman@perceptron.com>
*Cc:* pgsql-general@postgresql.org
*Subject:* Re: [GENERAL] clone_schema function

Thanks Igor,

hmm, apparently the "INCLUDING CONSTRAINTS' option of "CREATE TABLE' has
a glitch and only includes the primary key.

I also noticed that INCLUDING ALL generates an error, so I'll have to
report that also.

I'll go eat some crow and work on a fix to add all constraints in the
meantime.

It’s not a bug.

According to docs:

“Not-null constraints are always copied to the new table. CHECK
constraints will only be copied if INCLUDING CONSTRAINTS is specified;
other types of constraints will never be copied.”

So, FK constraints are not supposed to be copied.

Regards,

Igor Neyman

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Melvin Davidson wrote:

Here is one more tweak of clone_schema.

Are you updating the wiki to match? If not (why?), I think at the very
least you should add a link in the wiki page to this thread.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Alvaro,

no I haven't updated the wiki (or git). To be honest, I'm retired and I
just don't want to bother learning something new,
but I do enjoy helping othersfrom time to time. I would consider it a favor
if you would do the update for me.

TIA,
Melvin

On Fri, Sep 11, 2015 at 12:30 PM, Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

Melvin Davidson wrote:

Here is one more tweak of clone_schema.

Are you updating the wiki to match? If not (why?), I think at the very
least you should add a link in the wiki page to this thread.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Melvin Davidson wrote:

Alvaro,

no I haven't updated the wiki (or git). To be honest, I'm retired and I
just don't want to bother learning something new,
but I do enjoy helping othersfrom time to time. I would consider it a favor
if you would do the update for me.

I wouldn't want to prevent your enjoyment of learning something new such
as editing the wiki -- in spite of which I added a link to the wiki.

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Thank you very much Alvaro. Now I can go back to being Chief Engineer of
Sleeping Late @ retired. :)

On Fri, Sep 11, 2015 at 2:21 PM, Alvaro Herrera <alvherre@2ndquadrant.com>
wrote:

Melvin Davidson wrote:

Alvaro,

no I haven't updated the wiki (or git). To be honest, I'm retired and I
just don't want to bother learning something new,
but I do enjoy helping othersfrom time to time. I would consider it a

favor

if you would do the update for me.

I wouldn't want to prevent your enjoyment of learning something new such
as editing the wiki -- in spite of which I added a link to the wiki.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Melvin Davidson wrote:

Thank you very much Alvaro. Now I can go back to being Chief Engineer of
Sleeping Late @ retired. :)

What? No! You still have a lot of other Snippet pages to go through to
improve ;-)

--
�lvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Melvin Davidson wrote:

I've added error checking and verified that it now copies the
current sequnce values, table data, views and functions.

The code dealing with functions is seriously flawed.

Consider that part:
SELECT pg_get_functiondef(func_oid) INTO qry;
SELECT replace(qry, source_schema, dest_schema) INTO dest_qry;
EXECUTE dest_qry;

It suggests that to duplicate a function in schema A to B,
every letter A in the entire function definition should be replaced
by B, garbling everything along the way.
For example CREATE FUNCTION would become CREBTE FUNCTION,
DECLARE would become DECLBRE and so on.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

"seriously flawed" is a bit of a stretch. Most sane developers would not
have schema names of one letter.
They usually name a schema something practical, which totally avoids your
nit picky exception.
However, if you are that concerned about the "serious flaw", you have the
option of using the method
of dumping the schema, editing the dump and reloading. Or, I invite you to
use your great skills and
write a better method.

On Fri, Sep 11, 2015 at 4:06 PM, Daniel Verite <daniel@manitou-mail.org>
wrote:

Melvin Davidson wrote:

I've added error checking and verified that it now copies the
current sequnce values, table data, views and functions.

The code dealing with functions is seriously flawed.

Consider that part:
SELECT pg_get_functiondef(func_oid) INTO qry;
SELECT replace(qry, source_schema, dest_schema) INTO dest_qry;
EXECUTE dest_qry;

It suggests that to duplicate a function in schema A to B,
every letter A in the entire function definition should be replaced
by B, garbling everything along the way.
For example CREATE FUNCTION would become CREBTE FUNCTION,
DECLARE would become DECLBRE and so on.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Melvin Davidson
Sent: Friday, September 11, 2015 4:24 PM
To: Daniel Verite <daniel@manitou-mail.org>
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] clone_schema function

"seriously flawed" is a bit of a stretch. Most sane developers would not have schema names of one letter.
They usually name a schema something practical, which totally avoids your nit picky exception.
However, if you are that concerned about the "serious flaw", you have the option of using the method
of dumping the schema, editing the dump and reloading. Or, I invite you to use your great skills and
write a better method.

On Fri, Sep 11, 2015 at 4:06 PM, Daniel Verite <daniel@manitou-mail.org<mailto:daniel@manitou-mail.org>> wrote:
Melvin Davidson wrote:

I've added error checking and verified that it now copies the
current sequnce values, table data, views and functions.

The code dealing with functions is seriously flawed.

Consider that part:
SELECT pg_get_functiondef(func_oid) INTO qry;
SELECT replace(qry, source_schema, dest_schema) INTO dest_qry;
EXECUTE dest_qry;

It suggests that to duplicate a function in schema A to B,
every letter A in the entire function definition should be replaced
by B, garbling everything along the way.
For example CREATE FUNCTION would become CREBTE FUNCTION,
DECLARE would become DECLBRE and so on.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

____________________________________________________

It does not have to be one-letter schema name.
Consider the following:

Schema called “vector” has a table called “vector_config” referenced in some function.
Now, what happens if schema “vector” is copied into some destination schema using your script?

Melvin, you needn’t consider every critique of your script to be a personal attack on you.

Regards,
Igor Neyman

On Fri, Sep 11, 2015 at 4:23 PM, Melvin Davidson <melvin6925@gmail.com>
wrote:

"seriously flawed" is a bit of a stretch. Most sane developers would not
have schema names of one letter.
They usually name a schema something practical, which totally avoids your
nit picky exception.
However, if you are that concerned about the "serious flaw", you have the
option of using the method
of dumping the schema, editing the dump and reloading. Or, I invite you to
use your great skills and
write a better method.

SELECT replace(qry, source_schema, dest_schema) INTO dest_qry;

Or maybe you can at least mitigate the potential problem a bit by changing
this to read:

replace(qry, source_schema || '.', dest_schema || '.') INTO dest_qry; ...

Posting code for public consumption involves accepting constructive
criticism and even if the example is contrived I'm doubting the possibility
of collision is as close to zero as you think it may be or as close as it
could be with a simple re-evaluation of what constraints as imposed on a
particular sequence of characters being interpreted as a schema. You do
still end up with a possible false-positive when you have a
(column.composite).composite_field expression.

Regular expressions could maybe help here since the leading character is
limited too...but less so then the trailing character.

David J.

Igor & David,

You are correct, I am subject to criticism, However, I have spent a few
days getting this to work as it should and it now does.
Even though the chance of a collision is not zero, it is still low and the
function does work.
I don't mind criticism, but when someone finds a problem, the least they
can do is suggest a fix, as you have David.

I'll try that and test over the weekend.. Or I also invite you to submit a
fixed version.

On Fri, Sep 11, 2015 at 4:39 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

On Fri, Sep 11, 2015 at 4:23 PM, Melvin Davidson <melvin6925@gmail.com>
wrote:

"seriously flawed" is a bit of a stretch. Most sane developers would not
have schema names of one letter.
They usually name a schema something practical, which totally avoids your
nit picky exception.
However, if you are that concerned about the "serious flaw", you have the
option of using the method
of dumping the schema, editing the dump and reloading. Or, I invite you
to use your great skills and
write a better method.

SELECT replace(qry, source_schema, dest_schema) INTO dest_qry;

Or maybe you can at least mitigate the potential problem a bit by changing
this to read:

replace(qry, source_schema || '.', dest_schema || '.') INTO dest_qry; ...

Posting code for public consumption involves accepting constructive
criticism and even if the example is contrived I'm doubting the possibility
of collision is as close to zero as you think it may be or as close as it
could be with a simple re-evaluation of what constraints as imposed on a
particular sequence of characters being interpreted as a schema. You do
still end up with a possible false-positive when you have a
(column.composite).composite_field expression.

Regular expressions could maybe help here since the leading character is
limited too...but less so then the trailing character.

David J.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

Melvin Davidson wrote:

"seriously flawed" is a bit of a stretch. Most sane developers would not
have schema names of one letter.
They usually name a schema something practical, which totally avoids your
nit picky exception.

That's confusing the example with the problem it shows.

Another example could be:
if the source schema is "public" and the function body contains
GRANT SELECT on sometable to public;
then this statement would be wrongly altered by replace().

My objection is not about some corner case: it's the general
idea of patching the entire body of a function without a fully-fledged
parser that is dead on arrival.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

With thanks to a suggestion by David G. Johnston, I've attached another
revised version
of the function that hopefully eliminates the problem reported by Daniel
Verite.
This version also handles CamelCase schemas and tables better.

If anyone else finds a problem, kindly attach a dump of the schema to
duplicate the problem.

On Sat, Sep 12, 2015 at 10:38 AM, Daniel Verite <daniel@manitou-mail.org>
wrote:

Melvin Davidson wrote:

"seriously flawed" is a bit of a stretch. Most sane developers would not
have schema names of one letter.
They usually name a schema something practical, which totally avoids your
nit picky exception.

That's confusing the example with the problem it shows.

Another example could be:
if the source schema is "public" and the function body contains
GRANT SELECT on sometable to public;
then this statement would be wrongly altered by replace().

My objection is not about some corner case: it's the general
idea of patching the entire body of a function without a fully-fledged
parser that is dead on arrival.

Best regards,
--
Daniel Vérité
PostgreSQL-powered mailer: http://www.manitou-mail.org
Twitter: @DanielVerite

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

On 9/12/15 9:38 AM, Daniel Verite wrote:

"seriously flawed" is a bit of a stretch. Most sane developers would not

have schema names of one letter.
They usually name a schema something practical, which totally avoids your
nit picky exception.

That's confusing the example with the problem it shows.

Another example could be:
if the source schema is "public" and the function body contains
GRANT SELECT on sometable to public;
then this statement would be wrongly altered by replace().

Well, the new version actually fixes that. But you could still trip this
up, certainly in the functions. IE:

CREATE FUNCTION ...
SELECT old.field FROM old.old;

That will end up as

SELECT new.field FROM new.old

which won't work.

My objection is not about some corner case: it's the general
idea of patching the entire body of a function without a fully-fledged
parser that is dead on arrival.

ISTM that's also the biggest blocker for allowing extensions that refer
to other schemas to be relocatable. It would be interesting if we had
some way to handle this inside function bodies, perhaps via something
equivalent to @extschema@.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Jim,

Have you actually tried this, or is it just a theory? AFAIK, the function
will work because only the schema name is changed.. So please provide
a full working example of a function that fails and I will attempt a
solution.

On Mon, Sep 14, 2015 at 6:36 PM, Jim Nasby <Jim.Nasby@bluetreble.com> wrote:

On 9/12/15 9:38 AM, Daniel Verite wrote:

"seriously flawed" is a bit of a stretch. Most sane developers would not

have schema names of one letter.
They usually name a schema something practical, which totally avoids

your

nit picky exception.

That's confusing the example with the problem it shows.

Another example could be:
if the source schema is "public" and the function body contains
GRANT SELECT on sometable to public;
then this statement would be wrongly altered by replace().

Well, the new version actually fixes that. But you could still trip this
up, certainly in the functions. IE:

CREATE FUNCTION ...
SELECT old.field FROM old.old;

That will end up as

SELECT new.field FROM new.old

which won't work.

My objection is not about some corner case: it's the general

idea of patching the entire body of a function without a fully-fledged
parser that is dead on arrival.

ISTM that's also the biggest blocker for allowing extensions that refer to
other schemas to be relocatable. It would be interesting if we had some way
to handle this inside function bodies, perhaps via something equivalent to
@extschema@.
--
Jim Nasby, Data Architect, Blue Treble Consulting, Austin TX
Experts in Analytics, Data Architecture and PostgreSQL
Data in Trouble? Get it in Treble! http://BlueTreble.com

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.