From: | Jeff Janes <jeff(dot)janes(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | Where to set search_path |
Date: | 2013-04-26 18:07:54 |
Message-ID: | CAMkU=1zGKZqQxpGRZucD9JypqzVO5oPMsFkRJH5EhARBec6NOg@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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
From | Date | Subject | |
---|---|---|---|
Next Message | David Johnston | 2013-04-26 19:40:02 | Re: Where to set search_path |
Previous Message | Tom Lane | 2013-04-26 17:25:09 | Re: How to find current row number relative to window frame |