I've have an application which uses the public schema for all of its
relations.
I want to change the name of the schema (in preparation to merging the
database into that of another application, which also uses the public
schema for its tables).
I could qualify all of the tables and sequence with the schema names in the
source code SQL, but that is a lot of tedious work and I don't think it
even counts as an improvement. I think I'd rather use search_path in the
application being migrated, and only use fully qualified names for the
(small) part of the code that will explicitly need to bridge both systems.
Once I rename the schema ("alter schema public rename to new_schema;") I
can set the search path either on the db side, or on the app side. That
is, by "alter role web_user set search_path TO new_schema", or by changing
the centralized subroutine used by the application to get a database
handle, something like this:
sub getdbh {
require DBI;
my $dbh = DBI->connect(..., {AutoCommit=>1, RaiseError=>1,
PrintError=>0});
$dbh->do("set search_path to new_schema");
return $dbh;
};
Is there a reason to choose one of these options over the other? Or is it
purely a matter of taste?
I'm leaning towards the latter method, because it seems the future
application maintainer is more likely to benefit from the clue about the
search_path than the future DBA (assuming those roles get split).
Thanks,
Jeff
