On 10/24/14, 1:28 PM, Robert Haas wrote:
On Fri, Oct 24, 2014 at 2:06 PM, Joshua D. Drake <j...@commandprompt.com> wrote:
One of the things we run into quite a bit is customers who are using
multiple databases when they should be using multiple schemas. I am sure
other consultants run into this as well. This gets even more difficult as
uptime requirements have become all but 100%. So my question is, what would
this take?
ALTER DATABASE foo LOCATION DATABASE bar SCHEMA baz?
Where if we execute that command, database foo would move to schema baz
within database bar?
I am fully aware of what it takes on the client side but structurally within
postgres what would it take? Is it even reasonable?
What if the database contains more than one schema?
You could perhaps try to create a command that would move a schema
between two databases in the same cluster. It's fraught with
practical difficulties because a single backend can't be connected to
both databases at the same time, so how exactly do you make the
required catalog changes all in a single transaction? But if you
imagine that you have an infinite pool of top-notch PostgreSQL talent
with unbounded time to work on this problem and no other, I bet
somebody could engineer a solution.
ISTM that the multiple-databases-per-backend issue is the huge hang-up here.
Maybe there's some way that could be hacked around if you're just re-jiggering
a bunch of catalog stuff (assuming you lock users out of both databases while
you're doing that), but if you were going to go to that extent perhaps it'd be
better to just support cross-database access in a single backend...
--
Jim Nasby, Data Architect, Blue Treble Consulting
Data in Trouble? Get it in Treble! http://BlueTreble.com
--
Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-hackers
