Using tables in other PostGreSQL database

Pettis, Barry wrote:

Now this PostGreSQL "server" has many databases in it. Some of which I
have access to. I don't want to reinvent the wheel ( so to speak ) by
having to replicate the table in my database. Then having to create
routines that will extract from A to replicate in B. How do I reference
a table in another database?

There is an add-on in contrib/ called "dblink" that lets you connect to
a remote database. There's also a dbilink project that uses Perl.

Also to respond in the thread... Do I just reply to the message?

Reply-All is the default on the pg lists.

--
Richard Huxton
Archonet Ltd

An addon???? Being self schooled in databases to me this seems to be a
kludge. If you work in a large company environment the odds that
someone somewhere is all ready storing or collecting data that you need
( by this I mean base data ) could probably be pretty high. So why, if
PostGre is so old/established, is the ability to share information
between databases have to be done through an add on.

So let me give an example to help clarify.
1. I work in a manufacturing environment
2. Our product can have 150 to 450 different / unique process steps
3. We have a description of each process step
4. So with a product we can look at it's flow and see the descriptions
of each step

Now say person A pulls this information on a daily basis and then
summarizes the product manufacturing information and creates a table
that has say the total number of process modules ( aka group of steps ),
the total number of steps, the total number of a particular type of
step.

Now let's say that another person NEEDS that very information in a query
or table in their own database. Are you saying that each person needs
to generate this. To me the sharing of information seems to be so basic
that within a said postgre server, that as along as you have access to a
said database you should be able to say use the data stored here. And
that that ability should be a rudimentary ability not an addon.

Reason why I don't' have ability to install addon's onto the database.

Regards,
Barry Pettis
-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Thursday, March 27, 2008 3:45 AM
To: Pettis, Barry
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Using tables in other PostGreSQL database

Pettis, Barry wrote:

Now this PostGreSQL "server" has many databases in it. Some of which

I

have access to. I don't want to reinvent the wheel ( so to speak ) by
having to replicate the table in my database. Then having to create
routines that will extract from A to replicate in B. How do I

reference

a table in another database?

There is an add-on in contrib/ called "dblink" that lets you connect to
a remote database. There's also a dbilink project that uses Perl.

Also to respond in the thread... Do I just reply to the message?

Reply-All is the default on the pg lists.

--
Richard Huxton
Archonet Ltd

Pettis, Barry wrote:

An addon???? Being self schooled in databases to me this seems to be a
kludge.

Ah, well, self-schooling is always a good position from which to make
sweeping generalisations.

If you work in a large company environment the odds that
someone somewhere is all ready storing or collecting data that you need
( by this I mean base data ) could probably be pretty high. So why, if
PostGre is so old/established, is the ability to share information
between databases have to be done through an add on.

Because the whole point of a database is to be a coherent set of
connected facts. What is your opinion on handling:
1. Differing character-sets in different databases
2. Differing locales+sorting in different databases
3. Cross-database foreign-keys and other constraints
4. Differing objects with the same names in different databases
5. Handling #1-4 when the databases are on different physical servers.

Now let's say that another person NEEDS that very information in a query
or table in their own database. Are you saying that each person needs
to generate this. To me the sharing of information seems to be so basic
that within a said postgre server, that as along as you have access to a
said database you should be able to say use the data stored here. And
that that ability should be a rudimentary ability not an addon.

It sounds to me like you want to share a single database between users,
possibly using a suitable mix of schemas and roles to apply suitable
permissions. If you don't want them to have shared access to the data
then you can have separate databases and grant them access only to their
own DB.

Reason why I don't' have ability to install addon's onto the database.

Nobody is forcing you to. You'll have problems with procedural
languages, custom types, dictionaries, replication, GIS though.

--
Richard Huxton
Archonet Ltd

It sounds to me like you want to share a single database between

users,

possibly using a suitable mix of schemas and roles to apply suitable
permissions. If you don't want them to have shared access to the data
then you can have separate databases and grant them access only to

their

own DB.

Ok this kind of hits my issue right on the head. One of my tasks is to
generate a summary report of metrics from the local site here. Now each
week I need to summarize information on the product that was shipped
that week. My IS dept exports a text report each week which contains (
some of the information on the shipped product ). I know that another
individual uses this information for reports that he has to do. So
right now he is parsing this data back out from the report and storing
the info in a PostGre table. You said the point of a database was
coherent set of connected facts. Now his use of the report is to report
to his supervisor what and how product for his business group the
factory has made and shipped each week. Now the raw report is "ALL"
devices. Because he is extracting it he stores the entire thing not
just his small section of it.

Now like I said I'm being tasked to report for the mfg division on how
much, how fast, and other metrics which begins with data in his
database. Now the "coherent facts" thing would state that I shouldn't
put a mfg report info into the business group ( marketing ) groups
database. However, he has this table in his database. I want to use
it. Now for me I'd store this information in it's own database and give
everybody restricted access. This way if they need it they can get it
and they can use it. But as of right now I can't tell one database to
look in another database and use a table that it finds there. I think
that kind of restricts the use of data, or it promotes the duplication
of data being stored.

I can't answer to "foreign-keys" or databases on different servers
etc... due to my lack of ( sufficient { will that work } ) knowledge.
So maybe as my experience grows things will become clearer.

It's just right now I had to make my own data loader and store this data
that I know is being done by another... From a business productivity
point of view I see this as a waste of money.

Regards,
Barry Pettis

-----Original Message-----
From: Richard Huxton [mailto:dev@archonet.com]
Sent: Thursday, March 27, 2008 5:55 AM
To: Pettis, Barry
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Using tables in other PostGreSQL database

Pettis, Barry wrote:

An addon???? Being self schooled in databases to me this seems to be

a

kludge.

Ah, well, self-schooling is always a good position from which to make
sweeping generalisations.

If you work in a large company environment the odds that
someone somewhere is all ready storing or collecting data that you

need

( by this I mean base data ) could probably be pretty high. So why,

if

PostGre is so old/established, is the ability to share information
between databases have to be done through an add on.

Because the whole point of a database is to be a coherent set of
connected facts. What is your opinion on handling:
1. Differing character-sets in different databases
2. Differing locales+sorting in different databases
3. Cross-database foreign-keys and other constraints
4. Differing objects with the same names in different databases
5. Handling #1-4 when the databases are on different physical servers.

Now let's say that another person NEEDS that very information in a

query

or table in their own database. Are you saying that each person needs
to generate this. To me the sharing of information seems to be so

basic

that within a said postgre server, that as along as you have access to

a

said database you should be able to say use the data stored here. And
that that ability should be a rudimentary ability not an addon.

It sounds to me like you want to share a single database between users,
possibly using a suitable mix of schemas and roles to apply suitable
permissions. If you don't want them to have shared access to the data
then you can have separate databases and grant them access only to their

own DB.

Reason why I don't' have ability to install addon's onto the database.

Nobody is forcing you to. You'll have problems with procedural
languages, custom types, dictionaries, replication, GIS though.

--
Richard Huxton
Archonet Ltd

Pettis, Barry wrote:

It sounds to me like you want to share a single database between

users,

possibly using a suitable mix of schemas and roles to apply suitable
permissions. If you don't want them to have shared access to the data
then you can have separate databases and grant them access only to

their

own DB.

Ok this kind of hits my issue right on the head. One of my tasks is to
generate a summary report of metrics from the local site here. Now each
week I need to summarize information on the product that was shipped
that week. My IS dept exports a text report each week which contains (
some of the information on the shipped product ). I know that another
individual uses this information for reports that he has to do. So
right now he is parsing this data back out from the report and storing
the info in a PostGre table.

aside: PostgreSQL or Postgres, rather than postgre. Or "pg" if you
really need to keep things short.

The fact that you are taking a report (produced from a database, one
suspects) and re-inserting it to another database already means you're
doing copying here, so it' probably not worth worrying over excessively.

You said the point of a database was
coherent set of connected facts. Now his use of the report is to report
to his supervisor what and how product for his business group the
factory has made and shipped each week. Now the raw report is "ALL"
devices. Because he is extracting it he stores the entire thing not
just his small section of it.

OK, we have user1 needs all_products_summary. This isn't actually live
data, it's a copy of a regular snapshot.

Now like I said I'm being tasked to report for the mfg division on how
much, how fast, and other metrics which begins with data in his
database. Now the "coherent facts" thing would state that I shouldn't
put a mfg report info into the business group ( marketing ) groups
database. However, he has this table in his database. I want to use
it.

You want to use mfg_products_summary which is a subset of the above.
It's going to link to some manufacturing-specific data that marketing
probably don't want.

Now for me I'd store this information in it's own database and give
everybody restricted access. This way if they need it they can get it
and they can use it. But as of right now I can't tell one database to
look in another database and use a table that it finds there. I think
that kind of restricts the use of data, or it promotes the duplication
of data being stored.

You'd like shared access to a centralised body of data, which sounds to
me like a single database.

I can't answer to "foreign-keys" or databases on different servers
etc... due to my lack of ( sufficient { will that work } ) knowledge.
So maybe as my experience grows things will become clearer.

Well, not sure about clearer. More complicated certainly :-)

It's just right now I had to make my own data loader and store this data
that I know is being done by another... From a business productivity
point of view I see this as a waste of money.

Sounds like you want a single database (let's call it "reporting") with
separate schemas such as:
- manufacturing
- marketing
- is_dept_weekly
- shared
You place the relevant tables, views, functions etc in the relevant
schemas and then you can control who has access to what parts of the
database. That also lets you share useful views etc.

You'll have at least three db user accounts: you, marketing, db owner.

Restrictions:
- all the data will be in the same character set and locale

--
Richard Huxton
Archonet Ltd

Em Thursday 27 March 2008 08:29:04 Pettis, Barry escreveu:

An addon???? Being self schooled in databases to me this seems to be a
kludge. If you work in a large company environment the odds that
someone somewhere is all ready storing or collecting data that you need
( by this I mean base data ) could probably be pretty high. So why, if
PostGre is so old/established, is the ability to share information
between databases have to be done through an add on.

So let me give an example to help clarify.
1. I work in a manufacturing environment
2. Our product can have 150 to 450 different / unique process steps
3. We have a description of each process step
4. So with a product we can look at it's flow and see the descriptions
of each step

Now say person A pulls this information on a daily basis and then
summarizes the product manufacturing information and creates a table
that has say the total number of process modules ( aka group of steps ),
the total number of steps, the total number of a particular type of
step.

Now let's say that another person NEEDS that very information in a query
or table in their own database. Are you saying that each person needs
to generate this. To me the sharing of information seems to be so basic
that within a said postgre server, that as along as you have access to a
said database you should be able to say use the data stored here. And
that that ability should be a rudimentary ability not an addon.

Reason why I don't' have ability to install addon's onto the database.

It sounds to me like your company could make a good use of a DBA to organize
all that.

Users should just use the data, not plan the database and keep multiple copies
of information around.

One person designing all this would be able to organize the information, keep
its integrity, safety / secrecy and while doing all that also provide the
people using the information a better way to get it.

If everyone is creating their own database, then getting access to the
information isn't the biggest problem. Guaranteeing that all reports are
generated from the same information -- imagine sales reporting something from
last month while marketing is doing the same for this month and manufacture
is insterested on the history for the same month but comparing it to the last
three years history? A big mess...

--
Jorge Godoy <jgodoy@gmail.com>

I am fairly new to Postgres. However, I have to say that I agree with
Barry's comments.
The community's response is technically valid; they do talk about a better
way of 'designing' things, and what the company 'should' be doing.
However, coming from a MS-Sql world, people want multiple databases for
different reasons. Sometimes, they are in different departments, and they
keep their own databases, as in Barry's example. Sometimes, a billing
database is behind a firewall for security.
There are multiple ways to do the consolidation, by copying over data to a
common database with multiple schemas. However, the core question of Barry's
has not been answered.

1. There is a feature for cross-linking databases
2. That feature is available as an add-on
3. That feature is very useful for a lot of users, who are not as
knowledgeable as the PgSql community, and who are used to doing that for
other databases
4. Why not provide that feature as a core feature, rather than an add-on? If
the community really feels strongly about this, discourage this practice
with a best-practices section, citing problems with examples, and
workarounds. But why don't you provide this feature out of the box? After
all, isn't widespread adoption of a high quality database like Postgres our
overall goal?

On Thu, Mar 27, 2008 at 6:08 PM, Jorge Godoy <jgodoy@gmail.com> wrote:

On Thu, Mar 27, 2008 at 10:29:37PM -0700, Swaminathan Saikumar wrote:

4. Why not provide that feature as a core feature, rather than an add-on? If
the community really feels strongly about this, discourage this practice
with a best-practices section, citing problems with examples, and
workarounds. But why don't you provide this feature out of the box? After
all, isn't widespread adoption of a high quality database like Postgres our
overall goal?

Why do people read the word "add-on" in a negative way? All it means is
"not installed by default", which is probably a good thing since the
security implications are not trivial. Installation is just a
question of:

psql -f <dblink install script>

(assuming your admin didn't do a minimal install).

I'm unsure what "widespread adoption of postgres" has to do with any of
this though.

Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

On Thu, Mar 27, 2008 at 11:29 PM, Swaminathan Saikumar
<swami@giveexam.com> wrote:

I am fairly new to Postgres. However, I have to say that I agree with
Barry's comments.

The real problem here is that you are not using the db properly. You
should have one db with all these data in it in different schemas.
PostgreSQL provides you with the ability to segregate these data via
schemas and fine grained (by the table) ACLs.

Your refusal to use multiple schemas in one database due to some
perceived problem with them all being in the same database is what's
causing your issues.

Put your data into various schemas in one database and you can then
use access control to decide who sees what.

I have mixed feelings,

I agree that pretty much equivalent functionality CAN be delivered using schemas, but some RDBMS's do not have this restriction. Business cases & preferences do not necessarily follow database design preferences or capabilities, so irrespective of whether a schema approach CAN work, any user is entitled to ask whether an alternative approach is possible. Enough such users & the capability may well be implemented.

I am looking to convince a business which does not use schemas, but does use separate databases to move to Postgres & having to shift from this paradigm is an issue for them. They are perfectly entitled to require such an approach, if we want to increase the user base of Postgres, we need to meet more users needs..... (simplistic I know, but I think a valid concern).

For example, any user may need to be able to create databases, add data & use referentials in a corporate database of lookup records. Using schemas, everyone needs to have create table privileges to the database by default, then this needs to be denied by schema (is this possible???- never tried yet). Cross db queries allow read only access to corporate metadata for data integrity rules to be applied to any database any user creates. Agreed, not a common requirement, but one where schemas are less flexible & less secure.

Cheers,

Brent Wood

"Scott Marlowe" <scott.marlowe@gmail.com> 29/03/08 4:37 AM >>>

On Thu, Mar 27, 2008 at 11:29 PM, Swaminathan Saikumar
<swami@giveexam.com> wrote:

I am fairly new to Postgres. However, I have to say that I agree with
Barry's comments.

The real problem here is that you are not using the db properly. You
should have one db with all these data in it in different schemas.
PostgreSQL provides you with the ability to segregate these data via
schemas and fine grained (by the table) ACLs.

Your refusal to use multiple schemas in one database due to some
perceived problem with them all being in the same database is what's
causing your issues.

Put your data into various schemas in one database and you can then
use access control to decide who sees what.

Brent Wood wrote:

I am looking to convince a business which does not use schemas, but
does use separate databases to move to Postgres & having to shift
from this paradigm is an issue for them. They are perfectly entitled
to require such an approach, if we want to increase the user base of
Postgres, we need to meet more users needs..... (simplistic I know,
but I think a valid concern).

Did you ever hear of this thing called the Y2K bug a few years back?

The whole (or main) cause of this was in the early days management made
decisions on how programs would do certain tasks and in this case how it
would store data. Programmers wanted things done the way they are now
(or similar), but management knew better.

The developers and technical staff should decide on how features are
implemented not managers that know nothing of the technical reasoning
behind such decisions.

They are within their rights to say we want the program to perform these
tasks and this data only available to x staff and this data available to
y staff. How you implement these restrictions is best decided by someone
who knows how this decision will impact with future development as well
as complications of implementing and maintaining the tasks required.

I agree that we need to meet users needs and in some cases there is a
need for the feature you require which is why there is an add-on
available. The developers involved have left this as an add-on feature
for several reasons, one is a limited need for this feature (in properly
designed systems?), another is the security considerations which need to
be taken into account when one does implement such a feature.

Adding a plugin you need is of little consequence and is common place
with something like a web browser ,media player or graphic design
software, so why should we need everything added to a default install of
postgresql and not use plugin style feature additions?

Even simple things like procedural languages need to be manually
installed by those who want to make use of them and they get used more
than cross db data sharing.

--

Shane Ambler
pgSQL (at) Sheeky (dot) Biz

Get Sheeky @ http://Sheeky.Biz

On Fri, Mar 28, 2008 at 8:44 PM, Brent Wood <b.wood@niwa.co.nz> wrote:

I have mixed feelings,

I agree that pretty much equivalent functionality CAN be delivered using schemas, but some RDBMS's do not have this restriction. Business cases & preferences do not necessarily follow database design preferences or capabilities, so irrespective of whether a schema approach CAN work, any user is entitled to ask whether an alternative approach is possible. Enough such users & the capability may well be implemented.

Sadly, the way postgresql is built, this is not a simple addition of a
few lines of code. How do you do cross db access within a
transaction? You can't, and it's not likely that any code will be put
in place to do this.

I am looking to convince a business which does not use schemas, but does use separate databases to move to Postgres & having to shift from this paradigm is an issue for them. They are perfectly entitled to require such an approach, if we want to increase the user base of Postgres, we need to meet more users needs..... (simplistic I know, but I think a valid concern).

You're energy would be better spent showing them why they're wrong.
Every major db I've worked with supported schemas in one form or
another, and it's THE answer to this type of problem. If they want
their data in separate databases, then they need to know the
consequences. Even in Oracle you don't have cross db queries. You
use schemas there.

For example, any user may need to be able to create databases, add data & use referentials in a corporate database of lookup records. Using schemas, everyone needs to have create table privileges to the database by default, then this needs to be denied by schema (is this possible???- never tried yet). Cross db queries allow read only access to corporate metadata for data integrity rules to be applied to any database any user creates. Agreed, not a common requirement, but one where schemas are less flexible & less secure.

There IS a solution. The correct one is to use schemas. The less
correct one is to use dblink across separate databases.

I challenge you to show me how schemas are less secure than cross db
work with dblink, because I do not believe that to be true.

Let me further explain things. From the perspective of PostgreSQL,
all transactions occur within a single instance of a connection to a
single database.

When that connection is lost, any transactions roll back.
Transactional integrity is therefore only guaranteed during the
duration of a connection to a database. PostgreSQL has no semantics
to enforce any kind of transaction that spans databases or connections
at this time.

So, the ultimate division of your data is to split it up into separate
databases. By doing so you are saying that the data contained in the
new database in no way relates, at least in real time, with the data
in any other database (unless you've got some kind of synchronous
replication going I guess.)

It's why in PostgreSQL, nearly every statement can be issued inside a
transaction as long as it occurs within a database. Hence create
database and create tablespace don't exist inside a transaction, like
most statements.

Now, if by magic, a method to do cross db queries showed up, what
would it have to do to "do the right thing"? Would it need to ensure
transactional integrity? That would make it non-trivial. And if it
didn't ensure such a thing, then it would be a very real gotcha for a
user.

Using schemas ensures transactional integrity now. No need for any
addons or upgrades. No gotchas, no messed up data because half a
transaction got committed and half didn't.

So, the problem here, to me, is that the bosses making the decisions
don't get the fact that by meddling in the DBAs job to tell him how to
do something, they are hamstringing him and their databases for life,
and eventually the load on the DB will break something and they'll
want someone to blame. At that point, tell them to look in the
mirror. Or explain to them now why it's a bad idea and make it stick.

Oh, I also know about 2pc, but that's not quite done yet, and it's not
perfect either. When it's working then maybe someone could work on
cross-db queries through it.

But I'm not looking for that, 2pc has so many more interesting
applications than cross db queries.

Scott Marlowe wrote:

How do you do cross db access within a transaction?
You can't, and it's not likely that any code will be put in place to

do this.

But wasn't such code announced in 8.1? From
http://www.postgresql.org/about/news.422 :
"Two-Phase Commit (2PC): Long in demand for WAN applications and
heterogeneous data centers using PostgreSQL, this feature allows
ACID-compliant transactions across widely separated servers."

However I admit I don't know what end-user-level functionality 2PC has
enabled in PG if any. From the announcement one might expect some
equivalent of Oracle's database links, yet obviously we don't have
this.

Even in Oracle you don't have cross db queries.

On the contrary you do. You can refer to objects in another database by
OBJECT_NAME@DBLINK_NAME, very useful to mix local and remote data in no
time. DBLINK_NAME represents a connection to another database.
What you don't have is OTHERDB.OBJECT_NAME to refer to a different
database within the same instance, because there is only one database
in an Oracle instance.

You use schemas there.

Also there are no real schemas in Oracle, or they're strictly tied from
db users, and that's again quite different from how it's done in PG.

Regards,
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

On Sat, Mar 29, 2008 at 4:53 PM, Daniel Verite <daniel@manitou-mail.org> wrote:

Scott Marlowe wrote:

How do you do cross db access within a transaction?
You can't, and it's not likely that any code will be put in place to

do this.

But wasn't such code announced in 8.1? From
http://www.postgresql.org/about/news.422 :
"Two-Phase Commit (2PC): Long in demand for WAN applications and
heterogeneous data centers using PostgreSQL, this feature allows
ACID-compliant transactions across widely separated servers."

Yeah, I mentioned 2pc in a later post. No, there are no semantics in
postgresql that let it be represented by a simple cross db reference.

Also, 2PC is subject to unresolved transactions (or something like that).

Even in Oracle you don't have cross db queries.

On the contrary you do. You can refer to objects in another database by
OBJECT_NAME@DBLINK_NAME, very useful to mix local and remote data in no
time. DBLINK_NAME represents a connection to another database.
What you don't have is OTHERDB.OBJECT_NAME to refer to a different
database within the same instance, because there is only one database
in an Oracle instance.

What you are talking about are cross schema references, not cross db.
Oracle instances can have > 1 database, it's just not that common. I
know this because we had an internal instance at the last company I
worked at that had 2 databases in it, each with their own schemas. Or
maybe they somehow had two instances of oracle running on the same
box. I'm no oracle expert, I'm just reporting what I saw with my own
eye.

You use schemas there.

Also there are no real schemas in Oracle, or they're strictly tied from
db users, and that's again quite different from how it's done in PG.

Just because schemas in oracle are tied to user accounts doesn't make
them less schema-ish than postgresql's schemas. They're still
schemas. Create a user get a schema. Issue "alter session set
currnet_schema=abc and you change schemas, just like set search_path
does for postgresql, but with only one schema instead of multiples to
search through.

But my point remains. You don't use multiple dbs in oracle to do
this, and you shouldn't in PostgreSQL either.

Scott Marlowe wrote:

Even in Oracle you don't have cross db queries.

On the contrary you do. You can refer to objects in another

database by

OBJECT_NAME@DBLINK_NAME, very useful to mix local and remote data

in no

time. DBLINK_NAME represents a connection to another database.
What you don't have is OTHERDB.OBJECT_NAME to refer to a different
database within the same instance, because there is only one

database

in an Oracle instance.

What you are talking about are cross schema references, not cross db.

No I'm definitively referring to cross db, not cross schema.
See this piece from Oracle documentation:
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/sql_elem
ents009.htm#i27761

Oracle instances can have > 1 database, it's just not that common. I
know this because we had an internal instance at the last company I
worked at that had 2 databases in it, each with their own schemas.

Or

maybe they somehow had two instances of oracle running on the same
box.

CREATE DATABASE is to Oracle what initdb is to PG, it's something you
do once per instance. So no, an Oracle instance doesn't have >1
databases, just like a PG instance doesn't have >1 data directories.

Regards,

--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

Wow seems like this post took on a life of it's own. All I wanted to do
was to be able to use a table that someone else has all ready created.
Seems like somewhere someone mentioned a DBA ( which I'm assuming to be
"Database Administrator" ) well as far as I know we don't have one
though I wish we did.

The basis of my question comes from the fact that I currently use
"Multiple" access databases. Each database contains 1 piece of
information ( information that on it's own has no relationship to other
data ), but information in other databases will use items from it in it.
Hence in MSAccess I "LINK" the tables in. Which I know is nothing more
than a connection.

I wish I could say that I knew with certainty what schemas are or 2PC
is. Would be nice if I had exposure to other databases as well. I'm
sure that I'd have the same questions about MSft's SQL server.

But I thank the forum here for all the input.

Regards,
Barry Pettis

CSO Atmel Corp
Project Tech

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailto:pgsql-general-owner@postgresql.org] On Behalf Of Daniel Verite
Sent: Monday, March 31, 2008 3:36 AM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Using tables in other PostGreSQL database

Scott Marlowe wrote:

Even in Oracle you don't have cross db queries.

On the contrary you do. You can refer to objects in another

database by

OBJECT_NAME@DBLINK_NAME, very useful to mix local and remote data

in no

time. DBLINK_NAME represents a connection to another database.
What you don't have is OTHERDB.OBJECT_NAME to refer to a different
database within the same instance, because there is only one

database

in an Oracle instance.

What you are talking about are cross schema references, not cross db.

No I'm definitively referring to cross db, not cross schema.
See this piece from Oracle documentation:
http://download.oracle.com/docs/cd/B14117_01/server.101/b10759/sql_elem
ents009.htm#i27761

Oracle instances can have > 1 database, it's just not that common. I
know this because we had an internal instance at the last company I
worked at that had 2 databases in it, each with their own schemas.

Or

maybe they somehow had two instances of oracle running on the same
box.

CREATE DATABASE is to Oracle what initdb is to PG, it's something you
do once per instance. So no, an Oracle instance doesn't have >1
databases, just like a PG instance doesn't have >1 data directories.

Regards,

--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org

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

Also, 2PC is subject to unresolved transactions (or something like

that).

Even in Oracle you don't have cross db queries.

On the contrary you do. You can refer to objects in another

database by

OBJECT_NAME@DBLINK_NAME, very useful to mix local and remote data

in no

time. DBLINK_NAME represents a connection to another database.
What you don't have is OTHERDB.OBJECT_NAME to refer to a different
database within the same instance, because there is only one

database

in an Oracle instance.

What you are talking about are cross schema references, not cross db.
Oracle instances can have > 1 database, it's just not that common. I
know this because we had an internal instance at the last company I
worked at that had 2 databases in it, each with their own schemas. Or
maybe they somehow had two instances of oracle running on the same
box. I'm no oracle expert, I'm just reporting what I saw with my own
eye.

An Oracle instance is equal to a single database except for Oracle RAC.
With Oracle RAC, there is an instance per node so a two node RAC would
have two instances running. But to a developer, these two instances
look like one physical database.

You can have multiple instances running on the same box. You just need
to configure the Oracle listener to listen on two different ports; one
for each instance. The two instances are not tied together at all. You
could also run two separate Oracle homes and basically have two sets of
binaries installed and run two listeners.

Using an Oracle DB link, you can link to another database with the
@db_name syntax and get two phase commits.

HOWEVER, you can achieve a two phase commit in PostgreSQL with db_link
and using basic exception handling.

Example:
create table log (update_datetime timestamp);

create or replace function fn_test (p_fail boolean) returns void as
$$
declare
v_sql varchar;
v_int int;

begin
perform dblink_connect('pg', 'dbname=postgres user=scott
password=tiger host=localhost');

v_sql := 'begin;';
perform dblink_exec('pg', v_sql, false);

v_sql := 'insert into log values (now())';
perform dblink_exec('pg', v_sql, false);

if p_fail then
v_int := 1/0;
end if;

v_sql := 'commit;';
perform dblink_exec('pg', v_sql, false);

perform dblink_disconnect('pg');
exception
when others then
v_sql := 'rollback;';
perform dblink_exec('pg', v_sql, false);
perform dblink_disconnect('pg');
raise exception '%', sqlerrm;
end;
$$
language 'plpgsql';

Now that is a basic function that will insert data into the postgres
database via a dblink. If you pass in fail, it will hit "division by
zero" and rollback both the linked transaction and the primary
transaction of the function.

select fn_test(false);
select * from log;
--you see a new row

select fn_test(true)
--ERROR: division by zero
select * from log;
--you see that a new row wasn't inserted.

Jon