Lists: | Postg메이저 토토 사이트SQL |
---|
From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | clone_schema function |
Date: | 2015-09-09 16:31:00 |
Message-ID: | CANu8FixK9P8UD43nv2s+c-9jHkyy8wPUc_f5K3HcrmJYVPr-wQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | Postg토토 꽁 머니SQL |
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 reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Attachment | Content-Type | Size |
---|---|---|
clone_schema.sql | text/plain | 4.9 KB |
From: | Igor Neyman <ineyman(at)perceptron(dot)com> |
---|---|
To: | Melvin Davidson <melvin6925(at)gmail(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-09 19:43:08 |
Message-ID: | A76B25F2823E954C9E45E32FA49D70ECCD515210@mail.corp.perceptron.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Melvin Davidson
Sent: Wednesday, September 09, 2015 12:31 PM
To: pgsql-general(at)postgresql(dot)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
From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Igor Neyman <ineyman(at)perceptron(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-09 20:48:15 |
Message-ID: | CANu8FizQ0+UA3skWiF2w-yiNa0r1+fJS+RFi969yQSAFrXHtJg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)perceptron(dot)com> wrote:
>
>
>
>
> *From:* pgsql-general-owner(at)postgresql(dot)org [mailto:
> pgsql-general-owner(at)postgresql(dot)org] *On Behalf Of *Melvin Davidson
> *Sent:* Wednesday, September 09, 2015 12:31 PM
> *To:* pgsql-general(at)postgresql(dot)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: | Igor Neyman <ineyman(at)perceptron(dot)com> |
---|---|
To: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-10 13:09:42 |
Message-ID: | A76B25F2823E954C9E45E32FA49D70ECCD515332@mail.corp.perceptron.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
From: Melvin Davidson [mailto:melvin6925(at)gmail(dot)com]
Sent: Wednesday, September 09, 2015 4:48 PM
To: Igor Neyman <ineyman(at)perceptron(dot)com>
Cc: pgsql-general(at)postgresql(dot)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
From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Igor Neyman <ineyman(at)perceptron(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-10 15:52:10 |
Message-ID: | CANu8FiwiBiAjYgdehYBkJcSRFd6ZFFnN5kDJE7TG4rad5BNXZQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)perceptron(dot)com> wrote:
>
>
>
>
> *From:* Melvin Davidson [mailto:melvin6925(at)gmail(dot)com]
> *Sent:* Wednesday, September 09, 2015 4:48 PM
> *To:* Igor Neyman <ineyman(at)perceptron(dot)com>
> *Cc:* pgsql-general(at)postgresql(dot)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.
Attachment | Content-Type | Size |
---|---|---|
clone_schema.sql | text/plain | 5.3 KB |
From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Igor Neyman <ineyman(at)perceptron(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-11 14:22:12 |
Message-ID: | CANu8FiyJtt-0q=bkUxyra66tHi6FFzgU8TqVR2aahseCBDDntA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)gmail(dot)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(at)perceptron(dot)com>
> wrote:
>
>>
>>
>>
>>
>> *From:* Melvin Davidson [mailto:melvin6925(at)gmail(dot)com]
>> *Sent:* Wednesday, September 09, 2015 4:48 PM
>> *To:* Igor Neyman <ineyman(at)perceptron(dot)com>
>> *Cc:* pgsql-general(at)postgresql(dot)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.
Attachment | Content-Type | Size |
---|---|---|
clone_schema.sql | application/octet-stream | 5.6 KB |
From: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
---|---|
To: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
Cc: | Igor Neyman <ineyman(at)perceptron(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-11 16:30:03 |
Message-ID: | 20150911163003.GX2912@alvherre.pgsql |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
Cc: | Igor Neyman <ineyman(at)perceptron(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-11 17:51:49 |
Message-ID: | CANu8Fiy+56wsDwFsCDkXuG1mA9qesrmeHCwEeaF60WKGB-EpTw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | 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(at)2ndquadrant(dot)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.
From: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
---|---|
To: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
Cc: | Igor Neyman <ineyman(at)perceptron(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-11 18:21:00 |
Message-ID: | 20150911182100.GZ2912@alvherre.pgsql |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
Cc: | Igor Neyman <ineyman(at)perceptron(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-11 18:35:01 |
Message-ID: | CANu8Fixs+_TsAu_UB_Dna+9Fw+5_-RZtCPbhstg1g78H_evnjg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | 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(at)2ndquadrant(dot)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.
From: | Alvaro Herrera <alvherre(at)2ndquadrant(dot)com> |
---|---|
To: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
Cc: | Igor Neyman <ineyman(at)perceptron(dot)com>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-11 19:11:16 |
Message-ID: | 20150911191115.GB2912@alvherre.pgsql |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | "Daniel Verite" <daniel(at)manitou-mail(dot)org> |
---|---|
To: | "Melvin Davidson" <melvin6925(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-11 20:06:45 |
Message-ID: | 03928174-78b8-409e-9ab1-04775b34b673@mm |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | 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
From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Daniel Verite <daniel(at)manitou-mail(dot)org> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-11 20:23:47 |
Message-ID: | CANu8FizoJ0B7GHTwxH8gAcjXfhgkshUmT=GMX5rPO=h94526zQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | Postg무지개 토토SQL |
"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(at)manitou-mail(dot)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: | Igor Neyman <ineyman(at)perceptron(dot)com> |
---|---|
To: | Melvin Davidson <melvin6925(at)gmail(dot)com>, Daniel Verite <daniel(at)manitou-mail(dot)org> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-11 20:34:12 |
Message-ID: | A76B25F2823E954C9E45E32FA49D70ECCD5158B2@mail.corp.perceptron.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | Postg범퍼카 토토SQL |
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Melvin Davidson
Sent: Friday, September 11, 2015 4:24 PM
To: Daniel Verite <daniel(at)manitou-mail(dot)org>
Cc: pgsql-general(at)postgresql(dot)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(at)manitou-mail(dot)org<mailto:daniel(at)manitou-mail(dot)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
From: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
Cc: | Daniel Verite <daniel(at)manitou-mail(dot)org>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-11 20:39:24 |
Message-ID: | CAKFQuwbRhjPpHR7QrdjNL6d2iNg58zEVKUkSRFNH1um8GAyimQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On Fri, Sep 11, 2015 at 4:23 PM, Melvin Davidson <melvin6925(at)gmail(dot)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.
From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Daniel Verite <daniel(at)manitou-mail(dot)org>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-11 20:47:18 |
Message-ID: | CANu8FiyNoQSJ385Lj8+c748beSadz200x+GNqbVPB1hxCPmRMw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> On Fri, Sep 11, 2015 at 4:23 PM, Melvin Davidson <melvin6925(at)gmail(dot)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.
From: | "Daniel Verite" <daniel(at)manitou-mail(dot)org> |
---|---|
To: | "Melvin Davidson" <melvin6925(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-12 14:38:35 |
Message-ID: | efcf569b-05a2-4b78-a160-ed5c8ff7321f@mm |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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
From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Daniel Verite <daniel(at)manitou-mail(dot)org> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-12 14:49:21 |
Message-ID: | CANu8Fix_YNH9_+9rhBPchnmKSjCBiL+aGxiF=9G2gP9xc9obZw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | 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(at)manitou-mail(dot)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.
Attachment | Content-Type | Size |
---|---|---|
clone_schema.sql | text/plain | 5.7 KB |
From: | Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> |
---|---|
To: | Daniel Verite <daniel(at)manitou-mail(dot)org>, Melvin Davidson <melvin6925(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-14 22:36:15 |
Message-ID: | 55F74BDF.6080401@BlueTreble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
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(at)(dot)
--
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
From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> |
Cc: | Daniel Verite <daniel(at)manitou-mail(dot)org>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-15 00:42:49 |
Message-ID: | CANu8FiySLs33eWuJj72rO49bPqaeO_i6K=9_4ouOx5PxNdpbeA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | 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(dot)Nasby(at)bluetreble(dot)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(at)(dot)
> --
> 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.
From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> |
Cc: | Daniel Verite <daniel(at)manitou-mail(dot)org>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-15 01:02:53 |
Message-ID: | CANu8FiwQ9s8yMJu2oZcFeH8Qji6Ky_8b2UuFZu+cpFJGn9F9-g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | Postg메이저 토토 사이트SQL |
Actually, on further thought, you example shows that it works correctly
because we do want all references to the old schema to be changed to the
new schema, since all copies of functions will now reside in the new
schema. Otherwise, there is no point of duplicating those functions.
On Mon, Sep 14, 2015 at 8:42 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:
> 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(dot)Nasby(at)bluetreble(dot)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(at)(dot)
>> --
>> 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.
>
--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
From: | Jim Nasby <Jim(dot)Nasby(at)BlueTreble(dot)com> |
---|---|
To: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
Cc: | Daniel Verite <daniel(at)manitou-mail(dot)org>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-15 07:22:58 |
Message-ID: | 55F7C752.3000608@BlueTreble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
On 9/14/15 8:02 PM, Melvin Davidson wrote:
> Actually, on further thought, you example shows that it works correctly
> because we do want all references to the old schema to be changed to the
> new schema, since all copies of functions will now reside in the new
> schema. Otherwise, there is no point of duplicating those functions.
Read my example again:
SELECT old.field FROM old.old;
That will end up as
SELECT new.field FROM new.old
Which will give you this error:
ERROR: missing FROM-clause entry for table "new"
LINE 1: SELECT new.field FROM new.old;
Even if you could fix that, there's yet more problems you'll run into,
like if someone has a plpgsql block with the same name as the old schema.
I'm not trying to denigrate the work you and others have put into this
script, but everyone should be aware that it's impossible to create a
robust solution without a parser. Unfortunately, you could end up with a
function that still compiles but does something rather different after
the move. That makes the script potentially dangerous (granted, the odds
of this are pretty low).
One thing I think would be very interesting is a parser that preserves
whitespace and comments. That would allow us to store a parsed version
of (at least plpgsql and sql) functions. The same technique would also
be handy for views. This would allow a lot (all?) other renames to
propagate to functions instead of breaking them (as currently happens).
Another option is supporting some kind of official way to specially
designate database objects in any procedure language (ie, the @schema@
syntax that extensions use). That would make it possible to rename
properly written functions without adverse side effects.
--
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
From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> |
Cc: | Daniel Verite <daniel(at)manitou-mail(dot)org>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-15 13:28:48 |
Message-ID: | CANu8FizQkrrR0_+hc-7HDMTtgegrfEYSrbPY_nGvEK6m6vprQQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | 503 토토 꽁 머니 페치 |
I still do not see any problem. The whole purpose of the function is to
copy ALL sequences , tables and functions to "new" schema, so new.old WILL
exist.
I don't see how you can possibly write a function that references a schema
that does not yet exist!
Again, please provide a _working_ example of what you think the problem is.
On Tue, Sep 15, 2015 at 3:22 AM, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> wrote:
> On 9/14/15 8:02 PM, Melvin Davidson wrote:
>
>> Actually, on further thought, you example shows that it works correctly
>> because we do want all references to the old schema to be changed to the
>> new schema, since all copies of functions will now reside in the new
>> schema. Otherwise, there is no point of duplicating those functions.
>>
>
> Read my example again:
>
> SELECT old.field FROM old.old;
>
> That will end up as
>
> SELECT new.field FROM new.old
>
> Which will give you this error:
>
> ERROR: missing FROM-clause entry for table "new"
> LINE 1: SELECT new.field FROM new.old;
>
> Even if you could fix that, there's yet more problems you'll run into,
> like if someone has a plpgsql block with the same name as the old schema.
>
> I'm not trying to denigrate the work you and others have put into this
> script, but everyone should be aware that it's impossible to create a
> robust solution without a parser. Unfortunately, you could end up with a
> function that still compiles but does something rather different after the
> move. That makes the script potentially dangerous (granted, the odds of
> this are pretty low).
>
> One thing I think would be very interesting is a parser that preserves
> whitespace and comments. That would allow us to store a parsed version of
> (at least plpgsql and sql) functions. The same technique would also be
> handy for views. This would allow a lot (all?) other renames to propagate
> to functions instead of breaking them (as currently happens).
>
> Another option is supporting some kind of official way to specially
> designate database objects in any procedure language (ie, the @schema@
> syntax that extensions use). That would make it possible to rename properly
> written functions without adverse side effects.
>
> --
> 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.
From: | Igor Neyman <ineyman(at)perceptron(dot)com> |
---|---|
To: | Melvin Davidson <melvin6925(at)gmail(dot)com>, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com> |
Cc: | Daniel Verite <daniel(at)manitou-mail(dot)org>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-15 13:39:12 |
Message-ID: | A76B25F2823E954C9E45E32FA49D70ECCD516009@mail.corp.perceptron.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | Postg사설 토토 사이트SQL |
I still do not see any problem. The whole purpose of the function is to copy ALL sequences , tables and functions to "new" schema, so new.old WILL exist.
I don't see how you can possibly write a function that references a schema that does not yet exist!
Again, please provide a _working_ example of what you think the problem is.
Melvin,
This statement:
SELECT old.field FROM old.old;
selects column “field” from table “old” which is in schema “old”.
Your script converts it into:
SELECT new.field FROM new.old
which will try to select column “field” from table “new” in schema “new”.
The obvious problem is that there is no table “new” in schema “new”, the table will still be called “old”.
Jim’s example is very similar to what I provided a few days ago.
Regards,
Igor Neyman
From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Igor Neyman <ineyman(at)perceptron(dot)com> |
Cc: | Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>, Daniel Verite <daniel(at)manitou-mail(dot)org>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-15 13:44:30 |
Message-ID: | CANu8FiyTLoRd-TUCNazXvygBYBYMgtvrEvC6_rvC0oyknCBvsw@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
That is correct. But table old will NOT be converted to new because
only the schema name is converted. And table "old" WILL exist because it
will also be copied.
I have tested and it works properly.
Please do not provide hypothetical examples. Give me an actual working
example that causes the problem.
On Tue, Sep 15, 2015 at 9:39 AM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:
> I still do not see any problem. The whole purpose of the function is to
> copy ALL sequences , tables and functions to "new" schema, so new.old WILL
> exist.
>
>
> I don't see how you can possibly write a function that references a schema
> that does not yet exist!
>
> Again, please provide a _working_ example of what you think the problem is.
>
>
>
> Melvin,
>
>
>
> This statement:
>
>
>
> SELECT old.field FROM old.old;
>
>
>
> selects column “field” from table “old” which is in schema “old”.
>
>
>
> Your script converts it into:
>
>
>
> SELECT new.field FROM new.old
>
>
>
> which will try to select column “field” from table “new” in schema “new”.
>
> The obvious problem is that there is no table “new” in schema “new”, the
> table will still be called “old”.
>
>
>
> Jim’s example is very similar to what I provided a few days ago.
>
>
>
> 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: | Igor Neyman <ineyman(at)perceptron(dot)com> |
---|---|
To: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
Cc: | Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>, Daniel Verite <daniel(at)manitou-mail(dot)org>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-15 13:55:58 |
Message-ID: | A76B25F2823E954C9E45E32FA49D70ECCD516041@mail.corp.perceptron.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
That is correct. But table old will NOT be converted to new because
only the schema name is converted. And table "old" WILL exist because it will also be copied.
I have tested and it works properly.
Please do not provide hypothetical examples. Give me an actual working example that causes the problem.
This statement:
SELECT old.field FROM old.old;
selects column “field” from table “old” which is in schema “old”.
Your script converts it into:
SELECT new.field FROM new.old
which will try to select column “field” from table “old” in schema “new”.
Again:
SELECT new.field
means select column “field” from table “new”, which does not exists.
Not sure, what other example you need.
Regards,
Igor Neyman
From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Igor Neyman <ineyman(at)perceptron(dot)com> |
Cc: | Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>, Daniel Verite <daniel(at)manitou-mail(dot)org>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-15 16:20:57 |
Message-ID: | CANu8FixeXzzVS4Df6pYirPpJhxAJ=EaVYTsS9sRLc3wi0fxNAg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Igor,
I understand your point, however, I have spent over a week making a
function
that previously did very little do a lot.
Naming a table the same as a schema is a very silly idea.
Unless you care to take the time to provide a full
schema, and function that fails for reasonable , practical design
I will ignore all further comments.
On Tue, Sep 15, 2015 at 9:55 AM, Igor Neyman <ineyman(at)perceptron(dot)com> wrote:
>
>
> That is correct. But table old will NOT be converted to new because
>
> only the schema name is converted. And table "old" WILL exist because it
> will also be copied.
>
> I have tested and it works properly.
>
> Please do not provide hypothetical examples. Give me an actual working
> example that causes the problem.
>
> This statement:
>
> SELECT old.field FROM old.old;
>
> selects column “field” from table “old” which is in schema “old”.
>
> Your script converts it into:
>
> SELECT new.field FROM new.old
>
> which will try to select column “field” from table “old” in schema “new”.
>
>
>
> Again:
>
> SELECT new.field
>
> means select column “field” from table “new”, which does not exists.
>
> Not sure, what other example you need.
>
> 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: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
Cc: | Igor Neyman <ineyman(at)perceptron(dot)com>, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>, Daniel Verite <daniel(at)manitou-mail(dot)org>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-15 16:27:18 |
Message-ID: | CAKFQuwaec+2vTt5YxOAirOZaMq4XMKBUpAC07Ws36xh=Dn2ofA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
To make the casual user's life easier, in the face of this reality, it
would nice if the routine would generate a reasonably attempted "diff"
between the two so that all changes can be reviewed in a structured manner
aided by correctly configured tools and advice.
On Tue, Sep 15, 2015 at 12:20 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:
> Igor,
> I understand your point, however, I have spent over a week making a
> function
> that previously did very little do a lot.
> Naming a table the same as a schema is a very silly idea.
>
> Unless you care to take the time to provide a full
> schema, and function that fails for reasonable , practical design
> I will ignore all further comments.
>
> On Tue, Sep 15, 2015 at 9:55 AM, Igor Neyman <ineyman(at)perceptron(dot)com>
> wrote:
>
>>
>>
>> That is correct. But table old will NOT be converted to new because
>>
>> only the schema name is converted. And table "old" WILL exist because it
>> will also be copied.
>>
>> I have tested and it works properly.
>>
>> Please do not provide hypothetical examples. Give me an actual working
>> example that causes the problem.
>>
>> This statement:
>>
>> SELECT old.field FROM old.old;
>>
>> selects column “field” from table “old” which is in schema “old”.
>>
>> Your script converts it into:
>>
>> SELECT new.field FROM new.old
>>
>> which will try to select column “field” from table “old” in schema “new”.
>>
>>
>>
>> Again:
>>
>> SELECT new.field
>>
>> means select column “field” from table “new”, which does not exists.
>>
>> Not sure, what other example you need.
>>
>> 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 <melvin6925(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Igor Neyman <ineyman(at)perceptron(dot)com>, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>, Daniel Verite <daniel(at)manitou-mail(dot)org>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-15 16:37:14 |
Message-ID: | CANu8FizuTn74i7b==E=OvTOegL2NCs5wMT_2GW400k0hsaihVA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
David,
Yes, it would be nice, but
1. I am still working also on bringing over the comments for various objects
2. What you request is currently beyond my capability. Not to mention that
there already
are existing tools that do that, albeit they are not free.
On Tue, Sep 15, 2015 at 12:27 PM, David G. Johnston <
david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
> To make the casual user's life easier, in the face of this reality, it
> would nice if the routine would generate a reasonably attempted "diff"
> between the two so that all changes can be reviewed in a structured manner
> aided by correctly configured tools and advice.
>
> On Tue, Sep 15, 2015 at 12:20 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
> wrote:
>
>> Igor,
>> I understand your point, however, I have spent over a week making a
>> function
>> that previously did very little do a lot.
>> Naming a table the same as a schema is a very silly idea.
>>
>> Unless you care to take the time to provide a full
>> schema, and function that fails for reasonable , practical design
>> I will ignore all further comments.
>>
>> On Tue, Sep 15, 2015 at 9:55 AM, Igor Neyman <ineyman(at)perceptron(dot)com>
>> wrote:
>>
>>>
>>>
>>> That is correct. But table old will NOT be converted to new because
>>>
>>> only the schema name is converted. And table "old" WILL exist because it
>>> will also be copied.
>>>
>>> I have tested and it works properly.
>>>
>>> Please do not provide hypothetical examples. Give me an actual working
>>> example that causes the problem.
>>>
>>> This statement:
>>>
>>> SELECT old.field FROM old.old;
>>>
>>> selects column “field” from table “old” which is in schema “old”.
>>>
>>> Your script converts it into:
>>>
>>> SELECT new.field FROM new.old
>>>
>>> which will try to select column “field” from table “old” in schema
>>> “new”.
>>>
>>>
>>>
>>> Again:
>>>
>>> SELECT new.field
>>>
>>> means select column “field” from table “new”, which does not exists.
>>>
>>> Not sure, what other example you need.
>>>
>>> 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.
From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | "David G(dot) Johnston" <david(dot)g(dot)johnston(at)gmail(dot)com> |
Cc: | Igor Neyman <ineyman(at)perceptron(dot)com>, Jim Nasby <Jim(dot)Nasby(at)bluetreble(dot)com>, Daniel Verite <daniel(at)manitou-mail(dot)org>, "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-17 13:47:37 |
Message-ID: | CANu8FiwrogONnvkO6SPWbFsp1eg8Qya_Saa1d8WKRwY2NMJp2g@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | Postg토토 캔SQL : |
Attached is hopefully the final version of
FUNCTION clone_schema(text, text, boolean)
This function now does the following:
1. Checks that the source schema exists and the destination does not.
2. Creates the destination schema
3. Copies all sequences, tables, indexes, rules, triggers, data(optional),
views & functions from the source schema to the destination schema
4. Optionally copies records from source schema tables to destination
tabled. (boolean)
5. Copies comments for source schema and all sequences, tables, functions,
rules and triggers;
If you discover a problem with this function, then kindly advise me what
it is
and attach a script (SQL dump) to duplicate it. If you also have a fix,
that is
even better.
However, if you "think" there is a problem that occurs when
A. The moon is full
B. You have blood type A/B negative
C. You have a table the same name as your database and schema
D. All you tables have column "id" in them
E. You've had 16 beers and 4 oxycodones
F. Your meth supplier raised the price
then do not contact me. Instead, run, do not walk, immediately to your
psychologist, as you have serious issues in addition to database design
problems
and you should not use this function under any circumstance.
CAVEAT EMPTOR!
The only known problem with this script is if functions in the source
schema
have a SELECT using the form of tablename.columm, and tablename is the
same
as source schema, then tablename will be changed to destination schema
name.
However, since good developers and DBA's use the form of alias.column,
this
should rarely be a problem.
On Tue, Sep 15, 2015 at 12:37 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:
> David,
>
> Yes, it would be nice, but
> 1. I am still working also on bringing over the comments for various
> objects
> 2. What you request is currently beyond my capability. Not to mention that
> there already
> are existing tools that do that, albeit they are not free.
>
> On Tue, Sep 15, 2015 at 12:27 PM, David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
>> To make the casual user's life easier, in the face of this reality, it
>> would nice if the routine would generate a reasonably attempted "diff"
>> between the two so that all changes can be reviewed in a structured manner
>> aided by correctly configured tools and advice.
>>
>> On Tue, Sep 15, 2015 at 12:20 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
>> wrote:
>>
>>> Igor,
>>> I understand your point, however, I have spent over a week making a
>>> function
>>> that previously did very little do a lot.
>>> Naming a table the same as a schema is a very silly idea.
>>>
>>> Unless you care to take the time to provide a full
>>> schema, and function that fails for reasonable , practical design
>>> I will ignore all further comments.
>>>
>>> On Tue, Sep 15, 2015 at 9:55 AM, Igor Neyman <ineyman(at)perceptron(dot)com>
>>> wrote:
>>>
>>>>
>>>>
>>>> That is correct. But table old will NOT be converted to new because
>>>>
>>>> only the schema name is converted. And table "old" WILL exist because
>>>> it will also be copied.
>>>>
>>>> I have tested and it works properly.
>>>>
>>>> Please do not provide hypothetical examples. Give me an actual working
>>>> example that causes the problem.
>>>>
>>>> This statement:
>>>>
>>>> SELECT old.field FROM old.old;
>>>>
>>>> selects column “field” from table “old” which is in schema “old”.
>>>>
>>>> Your script converts it into:
>>>>
>>>> SELECT new.field FROM new.old
>>>>
>>>> which will try to select column “field” from table “old” in schema
>>>> “new”.
>>>>
>>>>
>>>>
>>>> Again:
>>>>
>>>> SELECT new.field
>>>>
>>>> means select column “field” from table “new”, which does not exists.
>>>>
>>>> Not sure, what other example you need.
>>>>
>>>> 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*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Attachment | Content-Type | Size |
---|---|---|
clone_schema.sql | application/octet-stream | 12.0 KB |
From: | Marc Mamin <M(dot)Mamin(at)intershop(dot)de> |
---|---|
To: | "'Melvin Davidson'" <melvin6925(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-17 15:05:28 |
Message-ID: | B6F6FD62F2624C4C9916AC0175D56D884208801F@jenmbs01.ad.intershop.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Hello,
I had to make 2 changes to get it running:
line 193:
- REPLACE(column_default::text, quote_ident(source_schema) || '.', quote_ident(dest_schema || '.') )
+ REPLACE(column_default::text, quote_ident(source_schema) || '.', quote_ident(dest_schema) || '.' )
line 319
- SELECT replace(v_def, ' ON ', ' ON ' || quote_ident(dest_schema) || '.') INTO dest_qry;
+ SELECT replace(v_def, ' ON ' || quote_ident(source_schema), ' ON ' || quote_ident(dest_schema) ) INTO dest_qry;
moreover, you don't take care of the column statistic targets
(i.e. ALTER TABLE t ALTER COLUMN c SET STATISTICS n;)
regards,
Marc Mamin
From: pgsql-general-owner(at)postgresql(dot)org [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Melvin Davidson
Sent: Donnerstag, 17. September 2015 15:48
To: David G. Johnston
Cc: Igor Neyman; Jim Nasby; Daniel Verite; pgsql-general(at)postgresql(dot)org
Subject: Re: [GENERAL] clone_schema function
Attached is hopefully the final version of
FUNCTION clone_schema(text, text, boolean)
This function now does the following:
1. Checks that the source schema exists and the destination does not.
2. Creates the destination schema
3. Copies all sequences, tables, indexes, rules, triggers, data(optional),
views & functions from the source schema to the destination schema
4. Optionally copies records from source schema tables to destination tabled. (boolean)
5. Copies comments for source schema and all sequences, tables, functions, rules and triggers;
If you discover a problem with this function, then kindly advise me what it is
and attach a script (SQL dump) to duplicate it. If you also have a fix, that is
even better.
However, if you "think" there is a problem that occurs when
A. The moon is full
B. You have blood type A/B negative
C. You have a table the same name as your database and schema
D. All you tables have column "id" in them
E. You've had 16 beers and 4 oxycodones
F. Your meth supplier raised the price
then do not contact me. Instead, run, do not walk, immediately to your
psychologist, as you have serious issues in addition to database design problems
and you should not use this function under any circumstance.
CAVEAT EMPTOR!
The only known problem with this script is if functions in the source schema
have a SELECT using the form of tablename.columm, and tablename is the same
as source schema, then tablename will be changed to destination schema name.
However, since good developers and DBA's use the form of alias.column, this
should rarely be a problem.
On Tue, Sep 15, 2015 at 12:37 PM, Melvin Davidson <melvin6925(at)gmail(dot)com<mailto:melvin6925(at)gmail(dot)com>> wrote:
David,
Yes, it would be nice, but
1. I am still working also on bringing over the comments for various objects
2. What you request is currently beyond my capability. Not to mention that there already
are existing tools that do that, albeit they are not free.
On Tue, Sep 15, 2015 at 12:27 PM, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com<mailto:david(dot)g(dot)johnston(at)gmail(dot)com>> wrote:
To make the casual user's life easier, in the face of this reality, it would nice if the routine would generate a reasonably attempted "diff" between the two so that all changes can be reviewed in a structured manner aided by correctly configured tools and advice.
On Tue, Sep 15, 2015 at 12:20 PM, Melvin Davidson <melvin6925(at)gmail(dot)com<mailto:melvin6925(at)gmail(dot)com>> wrote:
Igor,
I understand your point, however, I have spent over a week making a function
that previously did very little do a lot.
Naming a table the same as a schema is a very silly idea.
Unless you care to take the time to provide a full
schema, and function that fails for reasonable , practical design
I will ignore all further comments.
On Tue, Sep 15, 2015 at 9:55 AM, Igor Neyman <ineyman(at)perceptron(dot)com<mailto:ineyman(at)perceptron(dot)com>> wrote:
That is correct. But table old will NOT be converted to new because
only the schema name is converted. And table "old" WILL exist because it will also be copied.
I have tested and it works properly.
Please do not provide hypothetical examples. Give me an actual working example that causes the problem.
This statement:
SELECT old.field FROM old.old;
selects column “field” from table “old” which is in schema “old”.
Your script converts it into:
SELECT new.field FROM new.old
which will try to select column “field” from table “old” in schema “new”.
Again:
SELECT new.field
means select column “field” from table “new”, which does not exists.
Not sure, what other example you need.
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. [http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif]
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you. [http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif]
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you. [http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif]
From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Marc Mamin <M(dot)Mamin(at)intershop(dot)de> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-17 15:11:14 |
Message-ID: | CANu8FiynrY2sAsNB8abGV=Zk=BLFo9UdiyLAdBSovsd9E=nOCA@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Thanks,
I'm not sure why you had trouble with the REPLACE(), as I did extensive
testing and it was working as coded.
As for SET STATISTICS, I'm guessing that is a glitch in the CREATE TABLE
.... LIKE option.
On Thu, Sep 17, 2015 at 11:05 AM, Marc Mamin <M(dot)Mamin(at)intershop(dot)de> wrote:
> Hello,
>
> I had to make 2 changes to get it running:
>
>
>
> line 193:
>
> - REPLACE(column_default::text, quote_ident(source_schema) || '.',
> quote_ident(dest_schema || '.') )
>
> + REPLACE(column_default::text, quote_ident(source_schema) || '.',
> quote_ident(dest_schema) || '.' )
>
>
>
> line 319
>
> - SELECT replace(v_def, ' ON ', ' ON ' || quote_ident(dest_schema) ||
> '.') INTO dest_qry;
>
> + SELECT replace(v_def, ' ON ' || quote_ident(source_schema), ' ON ' ||
> quote_ident(dest_schema) ) INTO dest_qry;
>
>
>
>
>
> moreover, you don't take care of the column statistic targets
>
> (i.e. ALTER TABLE t ALTER COLUMN c SET STATISTICS n;)
>
>
>
>
>
> regards,
>
>
>
> Marc Mamin
>
>
>
>
>
> *From:* pgsql-general-owner(at)postgresql(dot)org [mailto:
> pgsql-general-owner(at)postgresql(dot)org] *On Behalf Of *Melvin Davidson
> *Sent:* Donnerstag, 17. September 2015 15:48
> *To:* David G. Johnston
> *Cc:* Igor Neyman; Jim Nasby; Daniel Verite; pgsql-general(at)postgresql(dot)org
> *Subject:* Re: [GENERAL] clone_schema function
>
>
>
> Attached is hopefully the final version of
>
> FUNCTION clone_schema(text, text, boolean)
>
> This function now does the following:
> 1. Checks that the source schema exists and the destination does not.
> 2. Creates the destination schema
> 3. Copies all sequences, tables, indexes, rules, triggers,
> data(optional),
> views & functions from the source schema to the destination schema
> 4. Optionally copies records from source schema tables to destination
> tabled. (boolean)
> 5. Copies comments for source schema and all sequences, tables,
> functions, rules and triggers;
>
> If you discover a problem with this function, then kindly advise me what
> it is
> and attach a script (SQL dump) to duplicate it. If you also have a fix,
> that is
> even better.
>
> However, if you "think" there is a problem that occurs when
> A. The moon is full
> B. You have blood type A/B negative
> C. You have a table the same name as your database and schema
> D. All you tables have column "id" in them
> E. You've had 16 beers and 4 oxycodones
> F. Your meth supplier raised the price
>
> then do not contact me. Instead, run, do not walk, immediately to your
> psychologist, as you have serious issues in addition to database design
> problems
> and you should not use this function under any circumstance.
>
> CAVEAT EMPTOR!
> The only known problem with this script is if functions in the source
> schema
> have a SELECT using the form of tablename.columm, and tablename is the
> same
> as source schema, then tablename will be changed to destination schema
> name.
> However, since good developers and DBA's use the form of alias.column,
> this
> should rarely be a problem.
>
>
>
> On Tue, Sep 15, 2015 at 12:37 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
> wrote:
>
> David,
>
> Yes, it would be nice, but
>
> 1. I am still working also on bringing over the comments for various
> objects
>
> 2. What you request is currently beyond my capability. Not to mention that
> there already
>
> are existing tools that do that, albeit they are not free.
>
>
>
> On Tue, Sep 15, 2015 at 12:27 PM, David G. Johnston <
> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>
> To make the casual user's life easier, in the face of this reality, it
> would nice if the routine would generate a reasonably attempted "diff"
> between the two so that all changes can be reviewed in a structured manner
> aided by correctly configured tools and advice.
>
>
>
> On Tue, Sep 15, 2015 at 12:20 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
> wrote:
>
> Igor,
> I understand your point, however, I have spent over a week making a
> function
> that previously did very little do a lot.
>
> Naming a table the same as a schema is a very silly idea.
>
> Unless you care to take the time to provide a full
>
> schema, and function that fails for reasonable , practical design
>
> I will ignore all further comments.
>
>
>
> On Tue, Sep 15, 2015 at 9:55 AM, Igor Neyman <ineyman(at)perceptron(dot)com>
> wrote:
>
>
>
> That is correct. But table old will NOT be converted to new because
>
> only the schema name is converted. And table "old" WILL exist because it
> will also be copied.
>
> I have tested and it works properly.
>
> Please do not provide hypothetical examples. Give me an actual working
> example that causes the problem.
>
> This statement:
>
> SELECT old.field FROM old.old;
>
> selects column “field” from table “old” which is in schema “old”.
>
> Your script converts it into:
>
> SELECT new.field FROM new.old
>
> which will try to select column “field” from table “old” in schema “new”.
>
>
>
> Again:
>
> SELECT new.field
>
> means select column “field” from table “new”, which does not exists.
>
> Not sure, what other example you need.
>
> 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*
>
> *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.
From: | Marc Mamin <M(dot)Mamin(at)intershop(dot)de> |
---|---|
To: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-17 16:06:56 |
Message-ID: | B6F6FD62F2624C4C9916AC0175D56D884208B056@jenmbs01.ad.intershop.net |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | Postg롤 토토SQL : |
________________________________
Von: Melvin Davidson [melvin6925(at)gmail(dot)com]
Gesendet: Donnerstag, 17. September 2015 17:11
An: Marc Mamin
Cc: pgsql-general(at)postgresql(dot)org
Betreff: Re: [GENERAL] clone_schema function
Thanks,
>I'm not sure why you had trouble with the REPLACE(), as I did extensive testing and it was working as coded.
might be that my modification is required when ( and only when ?) the source_schema is not part of the current search_path.
This is just a guess, I only gave your code a quick try ...
>As for SET STATISTICS, I'm guessing that is a glitch in the CREATE TABLE .... LIKE option.
Yes, we can see it as an incomplete feature.
regards,
Marc Mamin
On Thu, Sep 17, 2015 at 11:05 AM, Marc Mamin <M(dot)Mamin(at)intershop(dot)de<mailto:M(dot)Mamin(at)intershop(dot)de>> wrote:
Hello,
I had to make 2 changes to get it running:
line 193:
- REPLACE(column_default::text, quote_ident(source_schema) || '.', quote_ident(dest_schema || '.') )
+ REPLACE(column_default::text, quote_ident(source_schema) || '.', quote_ident(dest_schema) || '.' )
line 319
- SELECT replace(v_def, ' ON ', ' ON ' || quote_ident(dest_schema) || '.') INTO dest_qry;
+ SELECT replace(v_def, ' ON ' || quote_ident(source_schema), ' ON ' || quote_ident(dest_schema) ) INTO dest_qry;
moreover, you don't take care of the column statistic targets
(i.e. ALTER TABLE t ALTER COLUMN c SET STATISTICS n;)
regards,
Marc Mamin
From: pgsql-general-owner(at)postgresql(dot)org<mailto:pgsql-general-owner(at)postgresql(dot)org> [mailto:pgsql-general-owner(at)postgresql(dot)org<mailto:pgsql-general-owner(at)postgresql(dot)org>] On Behalf Of Melvin Davidson
Sent: Donnerstag, 17. September 2015 15:48
To: David G. Johnston
Cc: Igor Neyman; Jim Nasby; Daniel Verite; pgsql-general(at)postgresql(dot)org<mailto:pgsql-general(at)postgresql(dot)org>
Subject: Re: [GENERAL] clone_schema function
Attached is hopefully the final version of
FUNCTION clone_schema(text, text, boolean)
This function now does the following:
1. Checks that the source schema exists and the destination does not.
2. Creates the destination schema
3. Copies all sequences, tables, indexes, rules, triggers, data(optional),
views & functions from the source schema to the destination schema
4. Optionally copies records from source schema tables to destination tabled. (boolean)
5. Copies comments for source schema and all sequences, tables, functions, rules and triggers;
If you discover a problem with this function, then kindly advise me what it is
and attach a script (SQL dump) to duplicate it. If you also have a fix, that is
even better.
However, if you "think" there is a problem that occurs when
A. The moon is full
B. You have blood type A/B negative
C. You have a table the same name as your database and schema
D. All you tables have column "id" in them
E. You've had 16 beers and 4 oxycodones
F. Your meth supplier raised the price
then do not contact me. Instead, run, do not walk, immediately to your
psychologist, as you have serious issues in addition to database design problems
and you should not use this function under any circumstance.
CAVEAT EMPTOR!
The only known problem with this script is if functions in the source schema
have a SELECT using the form of tablename.columm, and tablename is the same
as source schema, then tablename will be changed to destination schema name.
However, since good developers and DBA's use the form of alias.column, this
should rarely be a problem.
On Tue, Sep 15, 2015 at 12:37 PM, Melvin Davidson <melvin6925(at)gmail(dot)com<mailto:melvin6925(at)gmail(dot)com>> wrote:
David,
Yes, it would be nice, but
1. I am still working also on bringing over the comments for various objects
2. What you request is currently beyond my capability. Not to mention that there already
are existing tools that do that, albeit they are not free.
On Tue, Sep 15, 2015 at 12:27 PM, David G. Johnston <david(dot)g(dot)johnston(at)gmail(dot)com<mailto:david(dot)g(dot)johnston(at)gmail(dot)com>> wrote:
To make the casual user's life easier, in the face of this reality, it would nice if the routine would generate a reasonably attempted "diff" between the two so that all changes can be reviewed in a structured manner aided by correctly configured tools and advice.
On Tue, Sep 15, 2015 at 12:20 PM, Melvin Davidson <melvin6925(at)gmail(dot)com<mailto:melvin6925(at)gmail(dot)com>> wrote:
Igor,
I understand your point, however, I have spent over a week making a function
that previously did very little do a lot.
Naming a table the same as a schema is a very silly idea.
Unless you care to take the time to provide a full
schema, and function that fails for reasonable , practical design
I will ignore all further comments.
On Tue, Sep 15, 2015 at 9:55 AM, Igor Neyman <ineyman(at)perceptron(dot)com<mailto:ineyman(at)perceptron(dot)com>> wrote:
That is correct. But table old will NOT be converted to new because
only the schema name is converted. And table "old" WILL exist because it will also be copied.
I have tested and it works properly.
Please do not provide hypothetical examples. Give me an actual working example that causes the problem.
This statement:
SELECT old.field FROM old.old;
selects column “field” from table “old” which is in schema “old”.
Your script converts it into:
SELECT new.field FROM new.old
which will try to select column “field” from table “old” in schema “new”.
Again:
SELECT new.field
means select column “field” from table “new”, which does not exists.
Not sure, what other example you need.
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. [http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif]
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you. [http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif]
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you. [http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif]
--
Melvin Davidson
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you. [http://us.i1.yimg.com/us.yimg.com/i/mesg/tsmileys2/01.gif]
From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Marc Mamin <M(dot)Mamin(at)intershop(dot)de> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-18 20:09:27 |
Message-ID: | CANu8FiycOjY4W9BT0AF3d36i=KqS-JHeHxCjDKfCvNXrxhPWeg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Revised to add rules after all tables are create to avoid error where table
referenced in rule was not created yet.
Added copying of column statistics with thanks to Marc Mamin for pointing
that out.
On Thu, Sep 17, 2015 at 12:06 PM, Marc Mamin <M(dot)Mamin(at)intershop(dot)de> wrote:
>
> ------------------------------
> *Von:* Melvin Davidson [melvin6925(at)gmail(dot)com]
> *Gesendet:* Donnerstag, 17. September 2015 17:11
> *An:* Marc Mamin
> *Cc:* pgsql-general(at)postgresql(dot)org
> *Betreff:* Re: [GENERAL] clone_schema function
>
> Thanks,
>
> >I'm not sure why you had trouble with the REPLACE(), as I did extensive
> testing and it was working as coded.
>
> might be that my modification is required when ( and only when ?) the
> source_schema is not part of the current search_path.
> This is just a guess, I only gave your code a quick try ...
>
> >As for SET STATISTICS, I'm guessing that is a glitch in the CREATE TABLE
> .... LIKE option.
> Yes, we can see it as an incomplete feature.
>
> regards,
>
> Marc Mamin
>
> On Thu, Sep 17, 2015 at 11:05 AM, Marc Mamin <M(dot)Mamin(at)intershop(dot)de> wrote:
>
>> Hello,
>>
>> I had to make 2 changes to get it running:
>>
>>
>>
>> line 193:
>>
>> - REPLACE(column_default::text, quote_ident(source_schema) || '.',
>> quote_ident(dest_schema || '.') )
>>
>> + REPLACE(column_default::text, quote_ident(source_schema) || '.',
>> quote_ident(dest_schema) || '.' )
>>
>>
>>
>> line 319
>>
>> - SELECT replace(v_def, ' ON ', ' ON ' || quote_ident(dest_schema) ||
>> '.') INTO dest_qry;
>>
>> + SELECT replace(v_def, ' ON ' || quote_ident(source_schema), ' ON ' ||
>> quote_ident(dest_schema) ) INTO dest_qry;
>>
>>
>>
>>
>>
>> moreover, you don't take care of the column statistic targets
>>
>> (i.e. ALTER TABLE t ALTER COLUMN c SET STATISTICS n;)
>>
>>
>>
>>
>>
>> regards,
>>
>>
>>
>> Marc Mamin
>>
>>
>>
>>
>>
>> *From:* pgsql-general-owner(at)postgresql(dot)org [mailto:
>> pgsql-general-owner(at)postgresql(dot)org] *On Behalf Of *Melvin Davidson
>> *Sent:* Donnerstag, 17. September 2015 15:48
>> *To:* David G. Johnston
>> *Cc:* Igor Neyman; Jim Nasby; Daniel Verite; pgsql-general(at)postgresql(dot)org
>> *Subject:* Re: [GENERAL] clone_schema function
>>
>>
>>
>> Attached is hopefully the final version of
>>
>> FUNCTION clone_schema(text, text, boolean)
>>
>> This function now does the following:
>> 1. Checks that the source schema exists and the destination does not.
>> 2. Creates the destination schema
>> 3. Copies all sequences, tables, indexes, rules, triggers,
>> data(optional),
>> views & functions from the source schema to the destination schema
>> 4. Optionally copies records from source schema tables to destination
>> tabled. (boolean)
>> 5. Copies comments for source schema and all sequences, tables,
>> functions, rules and triggers;
>>
>> If you discover a problem with this function, then kindly advise me what
>> it is
>> and attach a script (SQL dump) to duplicate it. If you also have a fix,
>> that is
>> even better.
>>
>> However, if you "think" there is a problem that occurs when
>> A. The moon is full
>> B. You have blood type A/B negative
>> C. You have a table the same name as your database and schema
>> D. All you tables have column "id" in them
>> E. You've had 16 beers and 4 oxycodones
>> F. Your meth supplier raised the price
>>
>> then do not contact me. Instead, run, do not walk, immediately to your
>> psychologist, as you have serious issues in addition to database design
>> problems
>> and you should not use this function under any circumstance.
>>
>> CAVEAT EMPTOR!
>> The only known problem with this script is if functions in the source
>> schema
>> have a SELECT using the form of tablename.columm, and tablename is the
>> same
>> as source schema, then tablename will be changed to destination schema
>> name.
>> However, since good developers and DBA's use the form of alias.column,
>> this
>> should rarely be a problem.
>>
>>
>>
>> On Tue, Sep 15, 2015 at 12:37 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
>> wrote:
>>
>> David,
>>
>> Yes, it would be nice, but
>>
>> 1. I am still working also on bringing over the comments for various
>> objects
>>
>> 2. What you request is currently beyond my capability. Not to mention
>> that there already
>>
>> are existing tools that do that, albeit they are not free.
>>
>>
>>
>> On Tue, Sep 15, 2015 at 12:27 PM, David G. Johnston <
>> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>
>> To make the casual user's life easier, in the face of this reality, it
>> would nice if the routine would generate a reasonably attempted "diff"
>> between the two so that all changes can be reviewed in a structured manner
>> aided by correctly configured tools and advice.
>>
>>
>>
>> On Tue, Sep 15, 2015 at 12:20 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
>> wrote:
>>
>> Igor,
>> I understand your point, however, I have spent over a week making a
>> function
>> that previously did very little do a lot.
>>
>> Naming a table the same as a schema is a very silly idea.
>>
>> Unless you care to take the time to provide a full
>>
>> schema, and function that fails for reasonable , practical design
>>
>> I will ignore all further comments.
>>
>>
>>
>> On Tue, Sep 15, 2015 at 9:55 AM, Igor Neyman <ineyman(at)perceptron(dot)com>
>> wrote:
>>
>>
>>
>> That is correct. But table old will NOT be converted to new because
>>
>> only the schema name is converted. And table "old" WILL exist because it
>> will also be copied.
>>
>> I have tested and it works properly.
>>
>> Please do not provide hypothetical examples. Give me an actual working
>> example that causes the problem.
>>
>> This statement:
>>
>> SELECT old.field FROM old.old;
>>
>> selects column “field” from table “old” which is in schema “old”.
>>
>> Your script converts it into:
>>
>> SELECT new.field FROM new.old
>>
>> which will try to select column “field” from table “old” in schema “new”.
>>
>>
>>
>> Again:
>>
>> SELECT new.field
>>
>> means select column “field” from table “new”, which does not exists.
>>
>> Not sure, what other example you need.
>>
>> 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*
>>
>> *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*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.
Attachment | Content-Type | Size |
---|---|---|
clone_schema.sql | application/octet-stream | 12.8 KB |
From: | Melvin Davidson <melvin6925(at)gmail(dot)com> |
---|---|
To: | Marc Mamin <M(dot)Mamin(at)intershop(dot)de> |
Cc: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: clone_schema function |
Date: | 2015-09-20 15:05:03 |
Message-ID: | CANu8FizFEVbO9TVTC8dF+==PEywQZLT=XXeCEanpGr+s36iP5Q@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Lists: | pgsql-general |
Additional revision to avoid duplicating RI_Constraint triggers.
On Fri, Sep 18, 2015 at 4:09 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
wrote:
> Revised to add rules after all tables are create to avoid error where
> table referenced in rule was not created yet.
>
> Added copying of column statistics with thanks to Marc Mamin for pointing
> that out.
>
>
> On Thu, Sep 17, 2015 at 12:06 PM, Marc Mamin <M(dot)Mamin(at)intershop(dot)de> wrote:
>
>>
>> ------------------------------
>> *Von:* Melvin Davidson [melvin6925(at)gmail(dot)com]
>> *Gesendet:* Donnerstag, 17. September 2015 17:11
>> *An:* Marc Mamin
>> *Cc:* pgsql-general(at)postgresql(dot)org
>> *Betreff:* Re: [GENERAL] clone_schema function
>>
>> Thanks,
>>
>> >I'm not sure why you had trouble with the REPLACE(), as I did extensive
>> testing and it was working as coded.
>>
>> might be that my modification is required when ( and only when ?) the
>> source_schema is not part of the current search_path.
>> This is just a guess, I only gave your code a quick try ...
>>
>> >As for SET STATISTICS, I'm guessing that is a glitch in the CREATE TABLE
>> .... LIKE option.
>> Yes, we can see it as an incomplete feature.
>>
>> regards,
>>
>> Marc Mamin
>>
>> On Thu, Sep 17, 2015 at 11:05 AM, Marc Mamin <M(dot)Mamin(at)intershop(dot)de>
>> wrote:
>>
>>> Hello,
>>>
>>> I had to make 2 changes to get it running:
>>>
>>>
>>>
>>> line 193:
>>>
>>> - REPLACE(column_default::text, quote_ident(source_schema) || '.',
>>> quote_ident(dest_schema || '.') )
>>>
>>> + REPLACE(column_default::text, quote_ident(source_schema) || '.',
>>> quote_ident(dest_schema) || '.' )
>>>
>>>
>>>
>>> line 319
>>>
>>> - SELECT replace(v_def, ' ON ', ' ON ' || quote_ident(dest_schema) ||
>>> '.') INTO dest_qry;
>>>
>>> + SELECT replace(v_def, ' ON ' || quote_ident(source_schema), ' ON ' ||
>>> quote_ident(dest_schema) ) INTO dest_qry;
>>>
>>>
>>>
>>>
>>>
>>> moreover, you don't take care of the column statistic targets
>>>
>>> (i.e. ALTER TABLE t ALTER COLUMN c SET STATISTICS n;)
>>>
>>>
>>>
>>>
>>>
>>> regards,
>>>
>>>
>>>
>>> Marc Mamin
>>>
>>>
>>>
>>>
>>>
>>> *From:* pgsql-general-owner(at)postgresql(dot)org [mailto:
>>> pgsql-general-owner(at)postgresql(dot)org] *On Behalf Of *Melvin Davidson
>>> *Sent:* Donnerstag, 17. September 2015 15:48
>>> *To:* David G. Johnston
>>> *Cc:* Igor Neyman; Jim Nasby; Daniel Verite;
>>> pgsql-general(at)postgresql(dot)org
>>> *Subject:* Re: [GENERAL] clone_schema function
>>>
>>>
>>>
>>> Attached is hopefully the final version of
>>>
>>> FUNCTION clone_schema(text, text, boolean)
>>>
>>> This function now does the following:
>>> 1. Checks that the source schema exists and the destination does not.
>>> 2. Creates the destination schema
>>> 3. Copies all sequences, tables, indexes, rules, triggers,
>>> data(optional),
>>> views & functions from the source schema to the destination schema
>>> 4. Optionally copies records from source schema tables to destination
>>> tabled. (boolean)
>>> 5. Copies comments for source schema and all sequences, tables,
>>> functions, rules and triggers;
>>>
>>> If you discover a problem with this function, then kindly advise me
>>> what it is
>>> and attach a script (SQL dump) to duplicate it. If you also have a fix,
>>> that is
>>> even better.
>>>
>>> However, if you "think" there is a problem that occurs when
>>> A. The moon is full
>>> B. You have blood type A/B negative
>>> C. You have a table the same name as your database and schema
>>> D. All you tables have column "id" in them
>>> E. You've had 16 beers and 4 oxycodones
>>> F. Your meth supplier raised the price
>>>
>>> then do not contact me. Instead, run, do not walk, immediately to your
>>> psychologist, as you have serious issues in addition to database design
>>> problems
>>> and you should not use this function under any circumstance.
>>>
>>> CAVEAT EMPTOR!
>>> The only known problem with this script is if functions in the source
>>> schema
>>> have a SELECT using the form of tablename.columm, and tablename is the
>>> same
>>> as source schema, then tablename will be changed to destination schema
>>> name.
>>> However, since good developers and DBA's use the form of alias.column,
>>> this
>>> should rarely be a problem.
>>>
>>>
>>>
>>> On Tue, Sep 15, 2015 at 12:37 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
>>> wrote:
>>>
>>> David,
>>>
>>> Yes, it would be nice, but
>>>
>>> 1. I am still working also on bringing over the comments for various
>>> objects
>>>
>>> 2. What you request is currently beyond my capability. Not to mention
>>> that there already
>>>
>>> are existing tools that do that, albeit they are not free.
>>>
>>>
>>>
>>> On Tue, Sep 15, 2015 at 12:27 PM, David G. Johnston <
>>> david(dot)g(dot)johnston(at)gmail(dot)com> wrote:
>>>
>>> To make the casual user's life easier, in the face of this reality, it
>>> would nice if the routine would generate a reasonably attempted "diff"
>>> between the two so that all changes can be reviewed in a structured manner
>>> aided by correctly configured tools and advice.
>>>
>>>
>>>
>>> On Tue, Sep 15, 2015 at 12:20 PM, Melvin Davidson <melvin6925(at)gmail(dot)com>
>>> wrote:
>>>
>>> Igor,
>>> I understand your point, however, I have spent over a week making a
>>> function
>>> that previously did very little do a lot.
>>>
>>> Naming a table the same as a schema is a very silly idea.
>>>
>>> Unless you care to take the time to provide a full
>>>
>>> schema, and function that fails for reasonable , practical design
>>>
>>> I will ignore all further comments.
>>>
>>>
>>>
>>> On Tue, Sep 15, 2015 at 9:55 AM, Igor Neyman <ineyman(at)perceptron(dot)com>
>>> wrote:
>>>
>>>
>>>
>>> That is correct. But table old will NOT be converted to new because
>>>
>>> only the schema name is converted. And table "old" WILL exist because it
>>> will also be copied.
>>>
>>> I have tested and it works properly.
>>>
>>> Please do not provide hypothetical examples. Give me an actual working
>>> example that causes the problem.
>>>
>>> This statement:
>>>
>>> SELECT old.field FROM old.old;
>>>
>>> selects column “field” from table “old” which is in schema “old”.
>>>
>>> Your script converts it into:
>>>
>>> SELECT new.field FROM new.old
>>>
>>> which will try to select column “field” from table “old” in schema
>>> “new”.
>>>
>>>
>>>
>>> Again:
>>>
>>> SELECT new.field
>>>
>>> means select column “field” from table “new”, which does not exists.
>>>
>>> Not sure, what other example you need.
>>>
>>> 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*
>>>
>>> *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*
> 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.
Attachment | Content-Type | Size |
---|---|---|
clone_schema.sql | text/plain | 12.9 KB |