Re: How ugly would this be? (ALTER DATABASE)

On 10/24/14, 1:28 PM, Robert Haas wrote:
> On Fri, Oct 24, 2014 at 2:06 PM, Joshua D. Drake <jd(at)commandprompt(dot)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