Thread: Cross-schema view issue/question

Hi folks,

We have a current DB on PG 8.4.7 on Ubuntu Server 10.04 LTS.
This DB used to only have one schema and that schema was replaced
on a regular schedule using a pg_dump/pg_restore process.  The old
schema was renamed to another name and the incoming schema and data
replaced it in the DB.  If an old renamed schema was present at the
time it was dropped prior to the renaming above. This schema is only
writable by the owning user.  There are other users that read this
schema/data for reports and other tasks.

Let's call that schema 'abc'.

This was all well and good until a user (quite rightly) decided to
create their own views of the 'abc' schema in their own schema which
we'll call 'xyz'.

The issue that has arisen is that we can no longer simply rename/drop
the 'abc' schema as the other user's schema objects in 'xyz' now refer
to objects in 'abc'.  At least, not without dropping their objects as
well.  Not a good thing.

My quesion is:  Is there any way to reduce/eliminate the tight coupling
of the views, et al. in schema 'xyz' to those in 'abc'?  My thoughts have
brought me to:
    1) a function that removes/creates the views, etc in the 'xyz' schema
       that gets called as part of the replacement process for schema 'abc'

    2) replacing the views, etc. with functions that return tables or
       the results of dynamic queries.

    3) have the user create the views, etc. as 'temp' items in their
       session each time.  Though this may still conflict with the
       replacement since there will still be a tight coupling between
       the temp objects and the 'abc' schema and the replacement occurs
       regardless of the current state of user connections.

None of these is very appealing.

Anyone have any thoughts or suggestions?  Or even an RTFM reference. :-)

TIA,
Bosco.

On Thu, Apr 14, 2011 at 07:33:17PM -0700, Bosco Rama wrote:
>     1) a function that removes/creates the views, etc in the 'xyz' schema
>        that gets called as part of the replacement process for schema 'abc'
>
>     2) replacing the views, etc. with functions that return tables or
>        the results of dynamic queries.
>
>     3) have the user create the views, etc. as 'temp' items in their
>        session each time.  Though this may still conflict with the
>        replacement since there will still be a tight coupling between
>        the temp objects and the 'abc' schema and the replacement occurs
>        regardless of the current state of user connections.

#2 will screw up query planning substantially; I'd steer clear of it. The
other two options are essentially deciding whether you or your user will
recreate the xyz objects each time you replace abc. That's kinda a religious
issue, and depends on things like how stable your user expects the objects in
xyz to be.

--
Joshua Tolley / eggyknap
End Point Corporation
http://www.endpoint.com

Joshua Tolley wrote:
> On Thu, Apr 14, 2011 at 07:33:17PM -0700, Bosco Rama wrote:
>>     1) a function that removes/creates the views, etc in the 'xyz' schema
>>        that gets called as part of the replacement process for schema 'abc'
>>
>>     2) replacing the views, etc. with functions that return tables or
>>        the results of dynamic queries.
>>
>>     3) have the user create the views, etc. as 'temp' items in their
>>        session each time.  Though this may still conflict with the
>>        replacement since there will still be a tight coupling between
>>        the temp objects and the 'abc' schema and the replacement occurs
>>        regardless of the current state of user connections.
>
> #2 will screw up query planning substantially; I'd steer clear of it. The
> other two options are essentially deciding whether you or your user will
> recreate the xyz objects each time you replace abc. That's kinda a religious
> issue, and depends on things like how stable your user expects the objects in
> xyz to be.

Thanks Joshua.  While not for the planner performance reason, we also ended up
rejecting #2.  We tried a quick sample of both of the other two options and
decided to go with #1.  It seemed to be the least intrusive upon the user.  Even
though they have to maintain the function(s) to drop/create the views (we gave
them templates to work from) all other aspects of their application and schema
remained untouched.  This weekend was the first true test of it and it seemed to
work just fine.

Thanks again,
Bosco.