[Dbix-class] Moving table out of database

[Darren Duncan]

Actually, PostgreSQL can do the traversal as well, recent versions at least.

PostgreSQL versions 9.3+ support federated databases for both reading and 
writing, and versions 9.1 thru 9.2.x support federations for reading.

So you can, at the database level, make it look like your moved table still 
lives in the old database, and you don't have to do anything special on the 
database client.

Granted, PostgreSQL federation support is a work in progress and certain things 
may not be as efficient as they could be, though 9.3 makes large strides in that 
area over previous versions, I think.

-- Darren Duncan

On 2013.12.10 10:38 AM, neil.lunn wrote:
> On 11/12/2013 5:00 AM, Bill Moseley wrote:
>> One of our tables has become very large in our database and we are looking at
>> moving it into a separate, partitioned database.
>>
>> Is there anything more "clever" we can do with DBIC to abstract this out other
>> than pull this result class out into a new class and have two $schema objects
>> for the two connections?
>
> Hey Bill. If you grok numerous responses I've given over the years this depends
> on your database backend and your concept of schema connection. For MySQL, MSSQL
> and Oracle : as long as you have your DBIC schema namespace set up right you can
> transverse 'database/schema' all on a single connection. For PostgreSQL this is
> a no go. <And again disclaimer if I have not gone through the docs correctly :) >
>
> In the former case, the best option is via SQL::Translator aka Fairy, to genere
>>
>> I'm assuming any place that table is used in a join will need to be rewritten.
...
> Crap. mail fail.
>
> Suffice to say, DDL diffs via SQL translator will work in the former case. But YMMV on performance hit, downtime etc etc etc. <Exit Stage Right Snaggles>
>
> If you need a double DSN case < aka PosgreSQL > then you are working through the client. So plan for efficiency.