Where to set search_path

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

Responses

Browse pgsql-general by date

  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