Re: Where to set search_path

Jeff Janes wrote
> 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

I don't really have any support for my thinking but I'd much rather put this
kind of setup on the database user and not the application. At worse you
MAY want to validate what the current search_path is at the client level.
My thinking would be along the lines of most users should not care what
schemas they are using and so should be also to simply logon and have things
work. The users who do care about bridging between the two worlds should
have the search_path default to something invalid and in doing so force
those users to be explicit regarding which schemas they intend to work with.

My $0.02

David J

--
View this message in context: http://postgresql.1045698.n5.nabble.com/Where-to-set-search-path-tp5753452p5753462.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.