Thread: transfering tables into other schema
I've around 150-200 tables in the same schema. Some of them have pk/fk relationships and are referenced into functions (~20). One of them is surely referenced by most of those 20 and it is the largest. I'd like to move all the 200 tables to a new schema and leave that one in the public schema. The DB size accordingly to select pg_size_pretty(pg_database_size is 1.5Gb I can afford 1 day down (WE). I was wondering what would be the "cheapest" and safest way to do the above without making a mess of the relationships, constraints, functions considering... I can afford one day down. Of course running a well crafted set of SQL statement may do... But sed and plain text backup may do as well. Any other options? Any advices on how to procede once an option is the clear winner? thanks -- Ivan Sergio Borgonovo http://www.webthatworks.it
On Tue, Feb 17, 2009 at 12:34 AM, Ivan Sergio Borgonovo <mail@webthatworks.it> wrote: > I've around 150-200 tables in the same schema. > Some of them have pk/fk relationships and are referenced into > functions (~20). > > One of them is surely referenced by most of those 20 and it is the > largest. > > I'd like to move all the 200 tables to a new schema and leave that > one in the public schema. > > The DB size accordingly to select pg_size_pretty(pg_database_size is > 1.5Gb > > I can afford 1 day down (WE). Have you tried ALTER TABLE name SET SCHEMA new_schema; ??? Should just work with no real downtime.
On 17/02/2009 08:09, Scott Marlowe wrote: > ALTER TABLE name SET SCHEMA new_schema; And you can do this for all your tables inside a transaction, making it an all-or-nothing operation. :-) Ray. ------------------------------------------------------------------ Raymond O'Donnell, Director of Music, Galway Cathedral, Ireland rod@iol.ie Galway Cathedral Recitals: http://www.galwaycathedral.org/recitals ------------------------------------------------------------------
On Tue, Feb 17, 2009 at 01:09:10AM -0700, Scott Marlowe wrote: > On Tue, Feb 17, 2009 at 12:34 AM, Ivan Sergio Borgonovo > <mail@webthatworks.it> wrote: > > I've around 150-200 tables in the same schema. > > Some of them have pk/fk relationships and are referenced into > > functions (~20). > > > > I'd like to move all the 200 tables to a new schema and leave that > > one in the public schema. > > ALTER TABLE name SET SCHEMA new_schema; Make sure your functions don't contain any hard coded references to the old schema name though! As Raymond says, you can do this all in a transaction and roll back if any of the functions don't do the correct thing. Be aware that you're probably going to lock other users out when doing this so it may be worth having a script (so it's locked for as small a time as possible and doesn't wait for human amounts of time) that renames the schema and runs a few of the functions with representative arguments and a rollback at the end. Once you've run this a few times with different functions and arguments and generally convinced yourself that all is OK, change the rollback to commit and all will be done. -- Sam http://samason.me.uk/
On Tue, 17 Feb 2009 12:19:14 +0000
Sam Mason <sam@samason.me.uk> wrote:
> > > I'd like to move all the 200 tables to a new schema and leave
> > > that one in the public schema.
> >
> > ALTER TABLE name SET SCHEMA new_schema;
>
> Make sure your functions don't contain any hard coded references to
> the old schema name though!
>
> As Raymond says, you can do this all in a transaction and roll
> back if any of the functions don't do the correct thing. Be aware
> that you're probably going to lock other users out when doing this
> so it may be worth having a script (so it's locked for as small a
> time as possible and doesn't wait for human amounts of time) that
> renames the schema and runs a few of the functions with
> representative arguments and a rollback at the end. Once you've
> run this a few times with different functions and arguments and
> generally convinced yourself that all is OK, change the rollback
> to commit and all will be done.
I can't get how this really work.
You're saying that constraint, fk/pk relationships will be preserved
automatically... what else?
OK BEFORE:
create table x (
xid primary key,
...
);
create table y (
xid int referencex x (xid),
...
);
-- following in application
select x.a, y.b from x join y on x.xid=y.xid;
-- following in the DB
create or replace function xy() as
$$
begin
select x.a, y.b from x join y on x.xid=y.xid;
...
end;
$$ ...
ALTER TABLE y SET SCHEMA new_schema;
What should I change by hand?
thanks
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
On Tue, Feb 17, 2009 at 06:20:54PM +0100, Ivan Sergio Borgonovo wrote:
> I can't get how this really work.
> You're saying that constraint, fk/pk relationships will be preserved
> automatically... what else?
>
> OK BEFORE:
>
> create table x (
> xid primary key,
> ...
> );
> create table y (
> xid int referencex x (xid),
> ...
> );
>
> -- following in application
> select x.a, y.b from x join y on x.xid=y.xid;
>
> -- following in the DB
> create or replace function xy() as
> $$
> begin
> select x.a, y.b from x join y on x.xid=y.xid;
> ...
> end;
> $$ ...
>
> ALTER TABLE y SET SCHEMA new_schema;
>
> What should I change by hand?
Sorry, I could have been clearer... Nothing in function xy() needs to
change because you don't explicitly refer to any schema anywhere. If
your tables had been created in the "public" schema, as per default, and
your code was:
CREATE FUNCTION foo() AS $$
SELECT x.a, y.b FROM public.x, public.y WHERE x.xid = y.xid $$;
Then you'd have to change the function to be:
CREATE FUNCTION foo() AS $$
SELECT x.a, y.b FROM newschema.x, newschema.y WHERE x.xid = y.xid $$;
does that make any more sense?
I actually mis-read the original suggestion from Scott as being:
ALTER SCHEMA foo RENAME TO bar;
I.e. just rename the whole schema across with everything inside it.
The same caveats would apply.
--
Sam http://samason.me.uk/
On Tue, 17 Feb 2009 17:36:32 +0000
Sam Mason <sam@samason.me.uk> wrote:
> On Tue, Feb 17, 2009 at 06:20:54PM +0100, Ivan Sergio Borgonovo
> wrote:
> > I can't get how this really work.
> > You're saying that constraint, fk/pk relationships will be
> > preserved automatically... what else?
> >
> > OK BEFORE:
> >
> > create table x (
> > xid primary key,
> > ...
> > );
> > create table y (
> > xid int referencex x (xid),
> > ...
> > );
> >
> > -- following in application
> > select x.a, y.b from x join y on x.xid=y.xid;
> >
> > -- following in the DB
> > create or replace function xy() as
> > $$
> > begin
> > select x.a, y.b from x join y on x.xid=y.xid;
> > ...
> > end;
> > $$ ...
> >
> > ALTER TABLE y SET SCHEMA new_schema;
> >
> > What should I change by hand?
>
> Sorry, I could have been clearer... Nothing in function xy()
> needs to change because you don't explicitly refer to any schema
> anywhere. If your tables had been created in the "public" schema,
> as per default, and your code was:
So... somehow everything is going to happen by magic if everything
was in public schema... mmm
create language 'plpgsql';
create schema new_schema;
create table x(
xid int primary key,
a varchar(2)
);
create table y(
xid int references x(xid),
b varchar(2)
);
create or replace function x(out varchar(2), out varchar(2)) returns
setof record
as
$$
begin
return query select a,b from x join y on x.xid=y.xid;
end;
$$ language plpgsql;
test=# select * from x();
column1 | column2
---------+---------
(0 rows)
alter table y set schema new_schema;
test=# SELECT * from x();
ERROR: relation "y" does not exist
CONTEXT: SQL statement " select a,b from x join y on x.xid=y.xid"
PL/pgSQL function "x" line 2 at RETURN QUERY
test=# SELECT a,b from x join new_schema.y on y.xid=x.xid;
a | b
---+---
(0 rows)
I did a similar test changing x schema with similar results.
So, pk/fk relationships survive. Function don't survive.
Do constraints survive?
I'm running 8.3.6
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
On Tue, Feb 17, 2009 at 3:31 PM, Ivan Sergio Borgonovo <mail@webthatworks.it> wrote: > On Tue, 17 Feb 2009 17:36:32 +0000 > Sam Mason <sam@samason.me.uk> wrote: > >> On Tue, Feb 17, 2009 at 06:20:54PM +0100, Ivan Sergio Borgonovo >> wrote: >> > I can't get how this really work. >> > You're saying that constraint, fk/pk relationships will be >> > preserved automatically... what else? >> > >> > OK BEFORE: >> > >> > create table x ( >> > xid primary key, >> > ... >> > ); >> > create table y ( >> > xid int referencex x (xid), >> > ... >> > ); >> > >> > -- following in application >> > select x.a, y.b from x join y on x.xid=y.xid; >> > >> > -- following in the DB >> > create or replace function xy() as >> > $$ >> > begin >> > select x.a, y.b from x join y on x.xid=y.xid; >> > ... >> > end; >> > $$ ... >> > >> > ALTER TABLE y SET SCHEMA new_schema; >> > >> > What should I change by hand? >> >> Sorry, I could have been clearer... Nothing in function xy() >> needs to change because you don't explicitly refer to any schema >> anywhere. If your tables had been created in the "public" schema, >> as per default, and your code was: > > So... somehow everything is going to happen by magic if everything > was in public schema... mmm > > create language 'plpgsql'; > > create schema new_schema; > > create table x( > xid int primary key, > a varchar(2) > ); > create table y( > xid int references x(xid), > b varchar(2) > ); > > create or replace function x(out varchar(2), out varchar(2)) returns > setof record > as > $$ > begin > return query select a,b from x join y on x.xid=y.xid; > end; > $$ language plpgsql; > > test=# select * from x(); > column1 | column2 > ---------+--------- > (0 rows) > > alter table y set schema new_schema; > > test=# SELECT * from x(); > ERROR: relation "y" does not exist > CONTEXT: SQL statement " select a,b from x join y on x.xid=y.xid" > PL/pgSQL function "x" line 2 at RETURN QUERY > test=# SELECT a,b from x join new_schema.y on y.xid=x.xid; > a | b > ---+--- > (0 rows) > > I did a similar test changing x schema with similar results. > So, pk/fk relationships survive. Function don't survive. You are wrong. The problem is that the function plan is cached in your current session. Try this. alter your table to another schema. then \q. then psql back in. Then SET YOUR SEARCH PATH to match the schemas set search_path='public','new_schema'; and then run the function and it should work. > Do constraints survive? Yes
On Wed, Feb 18, 2009 at 07:31, Ivan Sergio Borgonovo <mail@webthatworks.it> wrote: > alter table y set schema new_schema; > > test=# SELECT * from x(); > ERROR: relation "y" does not exist > CONTEXT: SQL statement " select a,b from x join y on x.xid=y.xid" > PL/pgSQL function "x" line 2 at RETURN QUERY > test=# SELECT a,b from x join new_schema.y on y.xid=x.xid; > a | b > ---+--- > (0 rows) > > I did a similar test changing x schema with similar results. > So, pk/fk relationships survive. Function don't survive. you just moved one table to the new schema. 'x' is still in the old, where 'y' is in the new schema. If you want to keep one table in "public" and the other in "new_schema" then you have to rewrite all functions. I am not sure about constraints, they might do that automatically. -- [ Clemens Schwaighofer -----=====:::::~ ] [ IT Engineer/Manager ] [ E-Graphics Communications, TEQUILA\ Japan IT Group ] [ 6-17-2 Ginza Chuo-ku, Tokyo 104-8167, JAPAN ] [ Tel: +81-(0)3-3545-7703 Fax: +81-(0)3-3545-7343 ] [ http://www.tequila.jp ] Advertising Age Global Agency of the Year 2008 Adweek Global Agency of the Year 2008 This e-mail is intended only for the named person or entity to which it is addressed and contains valuable business information that is privileged, confidential and/or otherwise protected from disclosure. Dissemination, distribution or copying of this e-mail or the information herein by anyone other than the intended recipient, or an employee or agent responsible for delivering the message to the intended recipient, is strictly prohibited. All contents are the copyright property of TBWA Worldwide, its agencies or a client of such agencies. If you are not the intended recipient, you are nevertheless bound to respect the worldwide legal rights of TBWA Worldwide, its agencies and its clients. We require that unintended recipients delete the e-mail and destroy all electronic copies in their system, retaining no copies in any media.If you have received this e-mail in error, please immediately notify us via e-mail to disclaimer@tbwaworld.com. We appreciate your cooperation. We make no warranties as to the accuracy or completeness of this e-mail and accept no liability for its content or use. Any opinions expressed in this e-mail are those of the author and do not necessarily reflect the opinions of TBWA Worldwide or any of its agencies or affiliates.
On Tue, Feb 17, 2009 at 8:42 PM, Schwaighofer Clemens <clemens.schwaighofer@tequila.jp> wrote: > On Wed, Feb 18, 2009 at 07:31, Ivan Sergio Borgonovo > <mail@webthatworks.it> wrote: >> alter table y set schema new_schema; >> >> test=# SELECT * from x(); >> ERROR: relation "y" does not exist >> CONTEXT: SQL statement " select a,b from x join y on x.xid=y.xid" >> PL/pgSQL function "x" line 2 at RETURN QUERY >> test=# SELECT a,b from x join new_schema.y on y.xid=x.xid; >> a | b >> ---+--- >> (0 rows) >> >> I did a similar test changing x schema with similar results. >> So, pk/fk relationships survive. Function don't survive. > > you just moved one table to the new schema. 'x' is still in the old, > where 'y' is in the new schema. > > If you want to keep one table in "public" and the other in > "new_schema" then you have to rewrite all functions. I am not sure > about constraints, they might do that automatically. This is incorrect. As long as both tables are in your search path it will work just fine. But you do have to re-connect to flush your cached plans for the functions. > Advertising Age Global Agency of the Year 2008 > Adweek Global Agency of the Year 2008 > > This e-mail is intended only for the named person or entity to which 19 lines snipped. > agencies or affiliates. Wow, could you have a longer mandatory but legally worthless signature? :) I know, lots of companies have them. Interestingly, including them in ALL emails is what makes them basically worthless for the emails you would really need them for.
On Tue, 17 Feb 2009 22:11:03 -0700 Scott Marlowe <scott.marlowe@gmail.com> wrote: > This is incorrect. As long as both tables are in your search path > it will work just fine. But you do have to re-connect to flush > your cached plans for the functions. That was the missing part. It seems the trick is going to be the search path. I'm not sure this is what I'm looking for or at least I don't feel that comfort with the scenario. It looks like I may put myself in a situation where I may get trapped by insidious bugs related to the order in which the schema path is read. I think I prefer to spend some time qualifying the schema in the functions so if I'm going to run in any bug I'll find it earlier than later. Before I try to see what happens to every object, relation, etc... etc... is there anything else that is going to change automatically/should be done manually once I move a table in a new schema? The manual says: Associated indexes, constraints, and sequences owned by table columns are moved as well. But at my eyes I still can grasp the extent of the change. So I wouldn't like to be bitten by something I didn't take into account. -- Ivan Sergio Borgonovo http://www.webthatworks.it
On Wed, Feb 18, 2009 at 10:44:27AM +0100, Ivan Sergio Borgonovo wrote:
> It looks like I may put myself in a situation where I may get
> trapped by insidious bugs related to the order in which the schema
> path is read.
>
> I think I prefer to spend some time qualifying the schema in the
> functions so if I'm going to run in any bug I'll find it earlier
> than later.
Because of PG's somewhat arbitrary use of lexical/dynamic scoping this is
recommended practice. For example:
CREATE TABLE foo ( i INT );
CREATE VIEW testview AS SELECT i FROM foo;
CREATE FUNCTION testfn() RETURNS SETOF INT LANGUAGE SQL AS $$
SELECT i FROM foo $$;
The view is lexically bound and the function dynamically bound. This
means that running the function later will cause it to look for what
"foo" means at-the-moment, rather than when it was defined, but the view
always refers back to the same relation as when it was defined. For
example:
ALTER TABLE foo RENAME TO bar;
SELECT * from testview;
SELECT * FROM testfn();
The view will continue to work, but the function now fails.
> Before I try to see what happens to every object, relation, etc...
> etc... is there anything else that is going to change
> automatically/should be done manually once I move a table in a new
> schema?
The answer to that gets complicated pretty quickly; is it possible to
back the database up and restore it to a development database? Once
there you can make the change and see what happens inside the database,
and more importantly how your code deals with the change.
Sam