I want to create a test environment where the basic underlying postgres database is overlain with an instance-localized private view, such that all queries from a specific set of processes go through the private view while other (potentially concurrent or merely subsequent) processes would remain unaffected.
I think I can do something like this using the search_path mechanism, but it's not clear if I can do that transparently (e.g., without having each application execute some set of SQL setup for each connection). For example, is there something I could set as an environment variable saying "use this search_path" and have every process that I start thereafter see that and use the same private table instances?
If it matters, the processes are all going through the C++ adapter, libpqxx, to access the database.
Thanks, Jeff
If every instance has a separate database user role, you can simply create a schema with the same name as the user and it'll use it -- without any change to configuration:
myuser=> show search_path;
search_path
--------------
"$user",public
(1 row)
myuser=> create schema myuser;
CREATE SCHEMA
myuser=> create table foo(i int);
CREATE TABLE
myuser=> \d foo
Table "myuser.foo"
Column Type Modifiers
------ ------- ---------
i integer
If you want to have different names for users and schemas, you can configure it for each user manually:
ALTER USER foo SET search_path=foo_schema;