Plpsql connecting to more than one database?

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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?

It's plpgsql, potentially with a slash, pl/pgsql.

You would have to use foreign data wrappers or dblink if you want a
function to speak to a database other than the one it's calling session is
attached to.

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

David J.

On Wed, Mar 30, 2016 at 5:18 PM, David G. Johnston <
david.g.johnston@gmail.com> wrote:

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?

It's plpgsql, potentially with a slash, pl/pgsql.

You would have to use foreign data wrappers or dblink if you want a
function to speak to a database other than the one it's calling session is
attached to.

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

David J.

Have you looked at the dblink extension? That certainly give you the
possibility to connect to more than one db.

--
*Melvin Davidson*
I reserve the right to fantasize. Whether or not you
wish to share my fantasy is entirely up to you.

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

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

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general