Thread: Plpsql connecting to more than one database?

Is it possible to have a plpsql program connect to 2 databases?

This seems to imply it,
http://stackoverflow.com/questions/18257003/pl-sql-querying-a-table-on-multiple-databases

However looking at the postgresql plpsql reference,
http://www.postgresql.org/docs/current/static/plpgsql-cursors.html#PLPGSQL-CURSOR-OPENING,
doesn't seem like there is a way to specify database. Is the
stackoverflow for plpsql in some other database (not postgresql)?

Any pointers greatly appreciated.

Have users that are familiar with plpsql and have a process which needs
to be done withing  transactions in 2 databases.
begin in db1
begin in db2

work on 1
work on 2

if work on both dbs worked

commit db1
commit db2

Do they need to use some other language (Java/Python)? How about a
foreign data wrapper?

On 03/30/2016 02:02 PM, Francisco Reyes wrote:
> Is it possible to have a plpsql program connect to 2 databases?
>
> This seems to imply it,
> http://stackoverflow.com/questions/18257003/pl-sql-querying-a-table-on-multiple-databases

Well the above is referring to Oracle, so is not applicable to Postgres.

>
>
> However looking at the postgresql plpsql reference,
> http://www.postgresql.org/docs/current/static/plpgsql-cursors.html#PLPGSQL-CURSOR-OPENING,
> doesn't seem like there is a way to specify database. Is the
> stackoverflow for plpsql in some other database (not postgresql)?
>
> Any pointers greatly appreciated.
>
> Have users that are familiar with plpsql and have a process which needs
> to be done withing  transactions in 2 databases.
> begin in db1
> begin in db2
>
> work on 1
> work on 2
>
> if work on both dbs worked
>
> commit db1
> commit db2
>
> Do they need to use some other language (Java/Python)? How about a
> foreign data wrapper?
>
>


--
Adrian Klaver
adrian.klaver@aklaver.com

On Wednesday, March 30, 2016, Francisco Reyes <lists@natserv.net> wrote:

Is it possible to have a plpsql program connect to 2 databases?

This seems to imply it, http://stackoverflow.com/questions/18257003/pl-sql-querying-a-table-on-multiple-databases

However looking at the postgresql plpsql reference, http://www.postgresql.org/docs/current/static/plpgsql-cursors.html#PLPGSQL-CURSOR-OPENING, doesn't seem like there is a way to specify database. Is the stackoverflow for plpsql in some other database (not postgresql)?

Any pointers greatly appreciated.

Have users that are familiar with plpsql and have a process which needs to be done withing  transactions in 2 databases.
begin in db1
begin in db2

work on 1
work on 2

if work on both dbs worked

commit db1
commit db2

Do they need to use some other language (Java/Python)? How about a foreign data wrapper?

Your SO link references Oracle's language.  It is called pl/SQL there.  Neither one has "plpsql".

On 3/30/2016 2:02 PM, Francisco Reyes wrote:
> Have users that are familiar with plpsql and have a process which
> needs to be done withing  transactions in 2 databases.
> begin in db1
> begin in db2
>
> work on 1
> work on 2
>
> if work on both dbs worked
>
> commit db1
> commit db2


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.



--
john r pierce, recycling bits in santa cruz

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.

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