Re: clone_schema function

[Marc Mamin <M.Mamin@xxxxxxxxxxxx>]

---

Von: Melvin Davidson [melvin6925@xxxxxxxxx]
Gesendet: Donnerstag, 17. September 2015 17:11
An: Marc Mamin
Cc: pgsql-general@xxxxxxxxxxxxxx
Betreff: Re: 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.Mamin@xxxxxxxxxxxx> 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@xxxxxxxxxxxxxx [mailto:pgsql-general-owner@xxxxxxxxxxxxxx] 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@xxxxxxxxxxxxxx
Subject: Re: 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@xxxxxxxxx> 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.g.johnston@xxxxxxxxx> 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@xxxxxxxxx> 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@xxxxxxxxxxxxxx> 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.