Thread: Using tables in other PostGreSQL database

I work at a fairly large company 2000 people just at my site alone.
I've been given access to a new database which will be used for source
data for some software that the company just purchased.

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?

The other user all ready updates and adds important information so I
don't want to have to replicate his/her work.  I just want to purloin
his finished product... Though I think that that's the whole point.



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

Regards,
Barry Pettis





























http://www.ozgrid.com/forum/misc.php?do=getsmilies&editorid=vB_Editor_00
1

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

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

> 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

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

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 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/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

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
irrespectiveof 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 &
havingto shift from this paradigm is an issue for them. They are perfectly entitled to require such an approach, if we
wantto increase the user base of Postgres, we need to meet more users needs..... (simplistic I know, but I think a
validconcern).  

For example, any user may need to be able to create databases, add data & use referentials in a corporate database of
lookuprecords. Using schemas, everyone needs to have create table privileges to the database by default, then this
needsto be denied by schema (is this possible???- never tried yet). Cross db queries allow read only access to
corporatemetadata 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
irrespectiveof 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
wewant to increase the user base of Postgres, we need to meet more users needs..... (simplistic I know, but I think a
validconcern). 

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
oflookup records. Using schemas, everyone needs to have create table privileges to the database by default, then this
needsto be denied by schema (is this possible???- never tried yet). Cross db queries allow read only access to
corporatemetadata 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

> 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

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

On Mon, Mar 31, 2008 at 05:20:56AM -0600, Pettis, Barry wrote:
> 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.

Schemas are a subdivision of a database. Every table/function/etc is
within a schema. You can reference them as schema.tablename or just
tablename if it's in your path. You can set a default schema for each
user so that when they login they only see their own tables, but they
can access other people's tables if they use the schema name (and have
permissions).

http://sql-info.de/postgresql/schemas.html

I find it odd you dismissed them as an option without knowing what they
are. Access between databases is not an often requested feature, since
schemas are what most people use...

> But I thank the forum here for all the input.

No problem.

Have a nice day,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Please line up in a tree and maintain the heap invariant while
> boarding. Thank you for flying nlogn airlines.

On Monday 31 March 2008 4:20 am, Pettis, Barry wrote:
> 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.

Now I am confused. If the data has no relationship to other data why do you
need to link to it? Second in your original post you said you where trying to
pull data from multiple Postgres databases now you say you are using multiple
Access databases.

>
> 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
>

> http://www.postgresql.org/mailpref/pgsql-general

--
Adrian Klaver
aklaver@comcast.net

My bad.

1.  I work at a Manufacturing plant.
2.  We identify different process steps using numbers along with a short
and long description
3.  For a part we manufacture the system identifies SOME properties of
said part.  Things like nuper of process steps, part name, part family,
and other items.
4.  Using various other properties, the total of all process steps of
said part being one, I can identify what kind of metals said part uses
by referencing the "SHORT DESC" of the process steps.

Now on it's own the "PROCESS STEP" is what I consider an atomic data
meaning that the if I look at it there is not a sub piece that it could
be broken down to.  So when I need to store that data I place it into
it's own MSAccess Database.

However, I have a database for all of the parts my site manufactures.  I
have a need to be able to extract a parts full process flow so using
some other tables which will identify and order the process steps I can
make a process flow which lists the short or long description depending
on my need.

I have another database which I need to look at the inventory ( not
caring what particular part just qty ) at a particular process step.
Now I may want to see one desc or another.  However, the fact that a
part is placed in a family value of say "Microcontroller or ASIC or
EEPROM" it doesn't have any relationship to say a particular "PROCESS
STEP" hence my comment of no relation ( I guess I should have said
tenuous relation ) instead of no relation.

Now as I see it logically I have 3 different databases ( 1 for holding
atomic information about a process step, 1 for being able to list a
products manufacturing flow, and 1 that is an inventory ).  So in my
logic I either have to have the "PROCESS STEP" data in 2 databases or I
have it in one database and other databases just "LINK" to it.

Now with the discussion in this thread I'm beginning to see that the
term "LINK" is kind of MSAccess unique.

So yes I use multiple databases.  ( 4 of which are )
1.  Lists all pats I manufacture ( includes general summary information
like cycle time, number of process', number of critical process', number
of metals used, etc... )
2.  Lists all process steps at my site, and indicates if it is a metal
and which type, if it's a critical process or not, which functional area
said process resides )
3.  Lists the substeps for a particular PROCESS STEP to determine how
many internal steps there are to any given process ( this can be
dependent on the part )
4.  Lists IF a part has an "ENGINEERING" restriction to a particular
tool and which tool part has to run on at a given Process Step.



I guess the biggest problem is trying to describe something in terms
that all can understand.  On a forum like this most "CONTRIBUTORS" those
who answer questions speak a language that the typical novice poster
doesn't use or understand.  So when the question is posed there is a
disconnect between poster and responder.  So I'm hoping that I've been
able to adequately clear the air.  If not it's always fun to try.


-----Original Message-----
From: Adrian Klaver [mailto:aklaver@comcast.net]
Sent: Tuesday, April 01, 2008 8:30 AM
To: pgsql-general@postgresql.org
Cc: Pettis, Barry
Subject: Re: [GENERAL] Using tables in other PostGreSQL database

On Monday 31 March 2008 4:20 am, Pettis, Barry wrote:
> 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.

Now I am confused. If the data has no relationship to other data why do
you
need to link to it? Second in your original post you said you where
trying to
pull data from multiple Postgres databases now you say you are using
multiple
Access databases.

>
> 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
>

> http://www.postgresql.org/mailpref/pgsql-general

--
Adrian Klaver
aklaver@comcast.net

On Tue, Apr 01, 2008 at 09:12:54AM -0600, Pettis, Barry wrote:
> I guess the biggest problem is trying to describe something in terms
> that all can understand.  On a forum like this most "CONTRIBUTORS" those
> who answer questions speak a language that the typical novice poster
> doesn't use or understand.  So when the question is posed there is a
> disconnect between poster and responder.  So I'm hoping that I've been
> able to adequately clear the air.  If not it's always fun to try.

I think a large part of the problem comes from mixing technical terms
(that have *highly* specific meanings) with normal prose.  I have a
feeling that when you say "database" you're in fact referring to what
every database admin/developer would know as a "table" (or "relation"),
but I'm not sure.  A "relationship" also has a specific technical
meaning and it may be worth having a look in a technical reference to
ensure you're using the term in the way that we will interpret it.  A
"link" in Access is generally referred to as a "join", but again I'd
recommend finding out what this word means before using it too much as,
again, it has a very specific technical meaning.  However in Access
you can perform a "Link Tables..." command which is what you may be
describing, based upon the remainder of your description I doubt it
though.


  Sam

 -------------- Original message ----------------------
From: "Pettis, Barry" <Barry.Pettis@atmel.com>
> My bad.
>
> 1.  I work at a Manufacturing plant.
> 2.  We identify different process steps using numbers along with a short
> and long description
> 3.  For a part we manufacture the system identifies SOME properties of
> said part.  Things like nuper of process steps, part name, part family,
> and other items.
> 4.  Using various other properties, the total of all process steps of
> said part being one, I can identify what kind of metals said part uses
> by referencing the "SHORT DESC" of the process steps.
>
> Now on it's own the "PROCESS STEP" is what I consider an atomic data
> meaning that the if I look at it there is not a sub piece that it could
> be broken down to.  So when I need to store that data I place it into
> it's own MSAccess Database.

The part about "PROCESS STEP" is not  consistent, see below.  Is the data in an actual Access database or a Postgres
tablelinked in to Access?  

>
> However, I have a database for all of the parts my site manufactures.  I
> have a need to be able to extract a parts full process flow so using
> some other tables which will identify and order the process steps I can
> make a process flow which lists the short or long description depending
> on my need.
>
> I have another database which I need to look at the inventory ( not
> caring what particular part just qty ) at a particular process step.
> Now I may want to see one desc or another.  However, the fact that a
> part is placed in a family value of say "Microcontroller or ASIC or
> EEPROM" it doesn't have any relationship to say a particular "PROCESS
> STEP" hence my comment of no relation ( I guess I should have said
> tenuous relation ) instead of no relation.
>
> Now as I see it logically I have 3 different databases ( 1 for holding
> atomic information about a process step, 1 for being able to list a
> products manufacturing flow, and 1 that is an inventory ).  So in my
> logic I either have to have the "PROCESS STEP" data in 2 databases or I
> have it in one database and other databases just "LINK" to it.
>
> Now with the discussion in this thread I'm beginning to see that the
> term "LINK" is kind of MSAccess unique.
>
> So yes I use multiple databases.  ( 4 of which are )
> 1.  Lists all pats I manufacture ( includes general summary information
> like cycle time, number of process', number of critical process', number
> of metals used, etc... )
> 2.  Lists all process steps at my site, and indicates if it is a metal
> and which type, if it's a critical process or not, which functional area
> said process resides )
> 3.  Lists the substeps for a particular PROCESS STEP to determine how
> many internal steps there are to any given process ( this can be
> dependent on the part )

Not consistent with assertion that "PROCESS STEP" is atomic and can not be broken down further as stated above.

> 4.  Lists IF a part has an "ENGINEERING" restriction to a particular
> tool and which tool part has to run on at a given Process Step.
>
>
>
> I guess the biggest problem is trying to describe something in terms
> that all can understand.  On a forum like this most "CONTRIBUTORS" those
> who answer questions speak a language that the typical novice poster
> doesn't use or understand.  So when the question is posed there is a
> disconnect between poster and responder.  So I'm hoping that I've been
> able to adequately clear the air.  If not it's always fun to try.

I am a great believer in "graphite engineering" i.e putting pencil to paper and sketching out  my ideas before tweaking
software.You might want to spend some time doodling to work out you want before getting too much into the details. As
Samsaid in another post a review of terms might be worthwhile, will help in making sure you are comparing apples to
apples,oranges to oranges. Access is not the best way to learn about databases as it has its own terminology that does
nottranslate well. 

>
>
> -----Original Message-----
> From: Adrian Klaver [mailto:aklaver@comcast.net]
> Sent: Tuesday, April 01, 2008 8:30 AM
> To: pgsql-general@postgresql.org
> Cc: Pettis, Barry
> Subject: Re: [GENERAL] Using tables in other PostGreSQL database
>
> On Monday 31 March 2008 4:20 am, Pettis, Barry wrote:
> > 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.
>
> Now I am confused. If the data has no relationship to other data why do
> you
> need to link to it? Second in your original post you said you where
> trying to
> pull data from multiple Postgres databases now you say you are using
> multiple
> Access databases.
>
> >
> > 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
> >
>
> > http://www.postgresql.org/mailpref/pgsql-general
>
> --
> Adrian Klaver
> aklaver@comcast.net
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general

--
Adrian Klaver
aklaver@comcast.net

    Pettis, Barry wrote:

> So yes I use multiple databases.  ( 4 of which are )
> 1.  Lists all pats I manufacture ( includes general summary
information
> like cycle time, number of process', number of critical process',
number
> of metals used, etc... )
> 2.  Lists all process steps at my site, and indicates if it is a
metal
> and which type, if it's a critical process or not, which functional
area
> said process resides )
> 3.  Lists the substeps for a particular PROCESS STEP to determine how
> many internal steps there are to any given process ( this can be
> dependent on the part )
> 4.  Lists IF a part has an "ENGINEERING" restriction to a particular
> tool and which tool part has to run on at a given Process Step.

So what you call a database is (I guess) an Access MDB file, either on
your local disk or accessible through a network share. MS-Access
provides an embedded database, but it can also access remote tables.
For those who don't know, an .MDB file is a big container that can have
tables, links to remote tables, forms, stored queries, vba code... The
links to remote tables can be :
1) client-server: by way of an ODBC connection to a database server.
The user needs to provides the connection information and the table
name.
2) by file. The user needs to provide the path of the remote .MDB file
(possibly on a network share) and the table name. I guess again that
this is the method you're using at the moment.
In both cases once the "link" to the remote tables are established, the
user can refer to them almost as if they were local (actually they have
a local name, as a soft link on a file system).
It's typical with MS-Access to separate data from the rest in order to
have an "application" part and a "data" part, each in its own MDB file.
It's crucial when the "data part" has to be shared or when you want the
ability to upgrade the "application" without touching the data.

Anyway, assuming I understood your setup, why is your data scattered
into 4 different databases? Not knowning anything about manufacturing I
don't understand your data, but still why not having different tables
into one .MDB file, as opposed to different .MDB files? And how does PG
enters the equation? You want to migrate all these data into it, or
just a part of them?


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