Re: Plpsql connecting to more than one database?

On 3/30/2016 2:52 PM, Francisco Reyes wrote:
> On 03/30/2016 05:44 PM, John R Pierce wrote:
>> and what if commit db2 fails for any number of reasons?  you've 
>> already committed db1, so you can't roll it back.      this sort of 
>> work requires '2pc' (2-phase commit), which is rather gnarly to 
>> implement. 
>
> You mean when executing the actual commit? Wouldn't that be a rare 
> ocurrence? Right now they are doing each DB independently and then 
> doing a cleanup if something goes wrong so even if there are times 
> where the "commit db2" fails, it would not be any worse than what they 
> have now.


fyi, you might look into...
http://www.postgresql.org/docs/current/static/sql-prepare-transaction.html

you'd begin the two transactions on the two seperate connections, do 
whatever you need in both sessions, then prepare transaction on each 
one, and if they both succeed, commit both, if either errors, rollback 
the other.

re: your original question about connecting to two databases... ideally, 
you'd merge both databases into seperate schemas on the same database, 
then you can freely mix and match elements of both in your queries.   
otherwise, foreign data wrappers are required (or FDW's predecessor, 
dblink...).





-- 
john r pierce, recycling bits in santa cruz