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...d=vB_Editor_00
1


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

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

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

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

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

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

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

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

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

> 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

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

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>


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

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:

> 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>
>
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>

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.

-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.1 (GNU/Linux)

iD8DBQFH7J54IB7bNG8LQkwRAu7NAJ9WrpnKtPijlzRxWVRH9g/mo6I9zACaA7zk
KZDXN7/6ccwgzxBjQ+lo+Qg=
=aj/7
-----END PGP SIGNATURE-----

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.

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