Any Plans for cross database queries on the same server?

[Tony Caduto]

Dblink is nice, but should it really be needed for databases on the same
physical server?

What would be cool is to allow a double dot notation i.e.
database1..schema1.table1

Just a idea. Comments?

[Peter Eisentraut]

This has been discussed about ten thousand times, and the answer is
still no.

[Tony Caduto]

Post by Peter Eisentraut
This has been discussed about ten thousand times, and the answer is
still no.

Why? Seems to me if it was discussed that much it must be a very sought
after feature.
How come it's not on the TO Do list for the future at least?
Is it because of some limitation of the core engine or something?

[Guido Neitzer]

Post by Tony Caduto
Why? Seems to me if it was discussed that much it must be a very
sought after feature.
How come it's not on the TO Do list for the future at least?
Is it because of some limitation of the core engine or something?

http://www.postgresql.org/docs/faqs.FAQ.html#item4.17

I guess, nobody has a real interest on that because, if you really
need that, there are work arounds ... E.g. I do a lot of cross
database queries all the day with my apps. It's just handled by the
app server ...

cug

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

[Brandon Aiken]

I always assumed the general argument is if you need to query different
databases on the same server with the same application, they ought not
to be separate databases because they're clearly related data.

It's kinda like "why isn't there a way to do an exactly one to exactly
one relationship between tables?". Well, because if one A always means
one B and one B always means one A, shouldn't they ought to be in the
same table already?

--
Brandon Aiken
CS/IT Systems Engineer

-----Original Message-----
From: pgsql-general-***@postgresql.org
[mailto:pgsql-general-***@postgresql.org] On Behalf Of Guido Neitzer
Sent: Tuesday, January 30, 2007 2:22 PM
To: Postgresql General
Subject: Re: [GENERAL] Any Plans for cross database queries on the same
server?

Post by Tony Caduto
Why? Seems to me if it was discussed that much it must be a very
sought after feature.
How come it's not on the TO Do list for the future at least?
Is it because of some limitation of the core engine or something?

http://www.postgresql.org/docs/faqs.FAQ.html#item4.17

I guess, nobody has a real interest on that because, if you really
need that, there are work arounds ... E.g. I do a lot of cross
database queries all the day with my apps. It's just handled by the
app server ...

cug

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq


--------------------------------------------------------------------
** LEGAL DISCLAIMER **
Statements made in this email may or may not reflect the views and opinions of Wineman Technology, Inc.
This E-mail message and any attachments may contain legally privileged, confidential or proprietary information. If you are not the intended recipient(s), or the employee or agent responsible for delivery of this message to the intended recipient(s), you are hereby notified that any dissemination, distribution or copying of this E-mail message is strictly prohibited. If you have received this message in error, please immediately notify the sender and delete this E-mail message from your computer.

QS Disclaimer Demo. Copyright (C) Pa-software.
Visit www.pa-software.com for more information.


---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Ron Johnson]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Post by Brandon Aiken
I always assumed the general argument is if you need to query different
databases on the same server with the same application, they ought not
to be separate databases because they're clearly related data.

Just because they are related, doesn't mean that it's always wise to
lump it all in the same database. Mainly for scalability and
performance reasons.

Our system looks like this set of databases:
REF database has all customer "low-volatility" data.
TRANS1 - Transaction data for accounts 0 - 999999.
TRANS2 - Transaction data for accounts 1000000 - 1999999.
TRANS3 - Transaction data for accounts 2000000 - 2999999.
etc.

This allows us to backup all the databases at the same time, and
horizontally scale as systems reach capacity

Currently, "joins" between REF & the TRANSx databases are handled by
the app server, but being able to attach to both databases and being
able to do distributed joins would really simplify our apps.

Of course, reducing the complexity of our apps means increasing the
complexity down to the RDBMS... And thus the balance of the
Universe is maintained.

Post by Brandon Aiken
It's kinda like "why isn't there a way to do an exactly one to exactly
one relationship between tables?". Well, because if one A always means
one B and one B always means one A, shouldn't they ought to be in the
same table already?

Vertical partitioning. If it's a large table, and certain columns
are "hot", while others not accessed so much, then partitioning the
table would speed up access to the hot column.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFv6G1S9HxQb37XmcRAumFAKCghFl/ryKtLQ+nlyP+jMRF3NJj1ACgruEU
wok9v3BkB6EFlJ01i/nYDLI=
=gzzP
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Tony Caduto]

Post by Ron Johnson

Post by Brandon Aiken
be separate databases because they're clearly related data.

Just because they are related, doesn't mean that it's always wise to
lump it all in the same database. Mainly for scalability and
performance reasons.

I would tend to agree, there are numerous times being able to do a cross
database query without the hassle of DBlink
would be extremely handy. I could also see it being valuable in a data
warehouse type situation.

I know it can be done in M$ SQL server using .. notation and I bet you
can do it in DB2 and Oracle.
you can even do it in MySQL, in MySQL it's their way of implementing
schemas.

Considering all these other DBs can do it, doesn't it make sense to at
least put it on the radar for Postgresql?

Just my 2 cents....

[Tom Lane]

Post by Tony Caduto
Considering all these other DBs can do it, doesn't it make sense to at
least put it on the radar for Postgresql?

It's already in the TODO list.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

[Mark Walker]

It's interesting that this is yet another issue of where exactly you
want to place your business logic. Do you do it as much as you can on
your sql server or do you bias it towards your client application. It's
obvious that you can do cross database linking in your application
layer, but if you want to keep your logic on your server as much as
possible, you need some form of cross database linking at the server
level. It's sort of a matter of taste, but there are lots of people who
like to keep there logic on the server or at least within sql
statements, so there's probably a good sized market that your not
reaching if you ignore it.

Post by Tony Caduto

Post by Ron Johnson

Post by Brandon Aiken
be separate databases because they're clearly related data.

Just because they are related, doesn't mean that it's always wise to
lump it all in the same database. Mainly for scalability and
performance reasons.

I would tend to agree, there are numerous times being able to do a
cross database query without the hassle of DBlink
would be extremely handy. I could also see it being valuable in a
data warehouse type situation.
I know it can be done in M$ SQL server using .. notation and I bet you
can do it in DB2 and Oracle.
you can even do it in MySQL, in MySQL it's their way of implementing
schemas.
Considering all these other DBs can do it, doesn't it make sense to at
least put it on the radar for Postgresql?
Just my 2 cents....

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

[Tony Caduto]

It's sort of a matter of taste, but there are lots of people who like
to keep there logic on the server or at least within sql statements,
so there's probably a good sized market that your not reaching if you
ignore it.

That is a good point, I and many developers I know like to keep all the
business logic on the server in stored procedure and functions and
having this ability as a native part of Postgresql would be a huge
advantage.
DBlink is a decent workaround,but it becomes a pain having to wrap
everything with the dblink syntax, plus there is a little bit of
overhead involved creating a connection over TCP/IP to run a query on
the same server. DBlink is great when you need to connect to a
different server though.

Later,

[Ron Johnson]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Post by Tony Caduto

It's sort of a matter of taste, but there are lots of people who like
to keep there logic on the server or at least within sql statements,
so there's probably a good sized market that your not reaching if you
ignore it.

That is a good point, I and many developers I know like to keep all the
business logic on the server in stored procedure and functions and
having this ability as a native part of Postgresql would be a huge
advantage.
DBlink is a decent workaround,but it becomes a pain having to wrap
everything with the dblink syntax, plus there is a little bit of
overhead involved creating a connection over TCP/IP to run a query on
the same server. DBlink is great when you need to connect to a
different server though.

True distributed transactions would be handy, though.

Many of our design decisions might have been different had they been
available.


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

iD8DBQFFv7FZS9HxQb37XmcRAjtPAJ9/eXPPp7xcOWYPhfkNVYudAeXB+wCfU96x
9ENICnZnVmYj59GB7niatyY=
=aQsm
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Richard Troy]

Post by Tony Caduto

It's sort of a matter of taste, but there are lots of people who like
to keep there logic on the server or at least within sql statements,
so there's probably a good sized market that your not reaching if you
ignore it.

That is a good point, I and many developers I know like to keep all the
business logic on the server in stored procedure and functions and
having this ability as a native part of Postgresql would be a huge
advantage.
DBlink is a decent workaround,but it becomes a pain having to wrap
everything with the dblink syntax, plus there is a little bit of
overhead involved creating a connection over TCP/IP to run a query on
the same server. DBlink is great when you need to connect to a
different server though.

"Business logic on the server" - heh. If you want to know where all such
ideas came from, think cynically.

...Originally, back in the day, the goal of creating "stored procedures",
in particular, and aiding and abeting features like "triggers", had
nothing to do with performance or clean architectures, rather the DBMS
vendors figured this was a good way to trap customers into only being able
to use their database engine. The more DBMS-engine-unique features a
customer used the more expensive it would be for them to switch to another
DBMS engine. The lack of standards regarding such "business logic" is
precisely because the vendors didn't want portability.

'Course, in a mature market (like the RDBMS) this mostly benefits the
largest vendors as gaining market share requires customers to convert. And
so today we have companies like ANTs Software absorbing the costs of
porting such features to their ANTs Data Server - last I heard they'd do
the conversion for free.

These days with good open source choices, things are a bit different, but
that doesn't mean it's always good to go hog wild with any particular tool
set just because you can. Sometimes people over-use engine-side features,
forgetting that there are nearly always more cycles available on clients
than servers...

Richard

[Ron Johnson]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
[snip]

Post by Richard Troy
These days with good open source choices, things are a bit
different, but that doesn't mean it's always good to go hog wild
with any particular tool set just because you can. Sometimes
people over-use engine-side features, forgetting that there are
nearly always more cycles available on clients than servers...

Call me Dinosaur Ron, but I think that CICS and the 3270 terminal
were a pair of the best IT inventions ever. With them, a modern
system can support 10s of thousands of concurrent online users.

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

iD8DBQFFv7x4S9HxQb37XmcRAk6dAJ0UoeQSOIajiw/BXdbTeJ53VHWuIwCghpc+
X4RYf7W8kWrmZ1V9vlkvFgE=
=uKXe
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Mark Walker]

LOL, I remember those days. "Uh, can you hold on? My computer just
went down." or "you need to fill out form 1203-B, send us $25 and we'll
get you the information you need in six weeks." Just kidding, but
certainly reliability standards and information demands are much higher
these days, aren't they?

Post by Ron Johnson
-----BEGIN PGP SIGNED MESSAGE-----
Call me Dinosaur Ron, but I think that CICS and the 3270 terminal
were a pair of the best IT inventions ever. With them, a modern
system can support 10s of thousands of concurrent online users.
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)
iD8DBQFFv7x4S9HxQb37XmcRAk6dAJ0UoeQSOIajiw/BXdbTeJ53VHWuIwCghpc+
X4RYf7W8kWrmZ1V9vlkvFgE=
=uKXe
-----END PGP SIGNATURE-----
---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

[Ron Johnson]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Post by Mark Walker
LOL, I remember those days. "Uh, can you hold on? My computer just
went down." or "you need to fill out form 1203-B, send us $25 and we'll
get you the information you need in six weeks." Just kidding, but
certainly reliability standards and information demands are much higher
these days, aren't they?

Which is more reliable, a mainframe where the app and database runs
on the host, or an N-tier client-server system where the RDBMS and
each tier are created by a different company, and the client is
thick and MS Windows?
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.6 (GNU/Linux)

iD8DBQFFv8xNS9HxQb37XmcRAmEFAJ91viT3qgEJNOT2A0nc9kL+xHSRSwCfYwYU
fuHBvnxFbV9tptbADVfwPmA=
=K7nY
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Richard Troy]

Post by Mark Walker
LOL, I remember those days. "Uh, can you hold on? My computer just
went down." or "you need to fill out form 1203-B, send us $25 and we'll
get you the information you need in six weeks." Just kidding, but
certainly reliability standards and information demands are much higher
these days, aren't they?

"Reliability standards ... higher these days?"

-har-har-har!- That's a good one!

Sure, in terms of bits moved/processed between hardware failures, things
have much improved, but I can't help but think if what a joke it is that
favored operating systems think it's OK to run out of memory for their own
activity and randomly kill processes so they don't hang! HAH! Some
Reilability. And people think this is a Good Thing (tm) because 1% of
overhead was saved!

<rant>
Sure wish the Open Source OS people would get a clue; paying a percent or
so for reliability pays for itself thousands of times over and most
people, if knowledgeable, would choose to spend the overhead to have a
system that really is reliable.
</rant>


Richard
--
Richard Troy, Chief Scientist
Science Tools Corporation
510-924-1363 or 202-747-1263
***@ScienceTools.com, http://ScienceTools.com/


---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Mark Walker]

I don't know. My customers expect 24/7 reliability. They expect to be
able to access their info anywhere in the world over a variety of
different devices. I can remember times when people would just go home
because computer networks were down. I haven't seen that happen in a
long time.

Maybe that's just my experience with my customers. I have seen signs of
dysfunctional computer systems lately. I was in a fast food restaurant
in San Francisco a few months back and they were manually taking
orders. I think the only reason they stayed open was because the owner
was there. Last summer a McDonald's in Paris next to the hotel my
family was staying at shut down because their computer system was down.
It ticked me off because we ended up eating at some pricey cafe next
door. I guess I'm a typical dumb American, traveling all the way to
Paris to eat at McDonald's.

Post by Richard Troy

Post by Mark Walker
LOL, I remember those days. "Uh, can you hold on? My computer just
went down." or "you need to fill out form 1203-B, send us $25 and we'll
get you the information you need in six weeks." Just kidding, but
certainly reliability standards and information demands are much higher
these days, aren't they?

"Reliability standards ... higher these days?"
-har-har-har!- That's a good one!
Sure, in terms of bits moved/processed between hardware failures, things
have much improved, but I can't help but think if what a joke it is that
favored operating systems think it's OK to run out of memory for their own
activity and randomly kill processes so they don't hang! HAH! Some
Reilability. And people think this is a Good Thing (tm) because 1% of
overhead was saved!
<rant>
Sure wish the Open Source OS people would get a clue; paying a percent or
so for reliability pays for itself thousands of times over and most
people, if knowledgeable, would choose to spend the overhead to have a
system that really is reliable.
</rant>
Richard

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Richard Troy]

Post by Mark Walker
I don't know. My customers expect 24/7 reliability. They expect to be
able to access their info anywhere in the world over a variety of
different devices. I can remember times when people would just go home
because computer networks were down. I haven't seen that happen in a
long time.

...Back in 1986, Cheryl Healy and I took on running Polaroid's corporate
systems "24 X 7" - and we worked hard to make it "24 X 7 X 365.24".
Shortly thereafter - while still working with Cheryl, Angel Vila, Chris
Boerner and I took on running Bellcore's 800 telephone network full time
also - our success was measured how few minutes/seconds there was any lost
business at all on an annual basis. (Bellcore was previously known as AT&T
Bell Laboratories.) If you made an 800 number based call from '86 to '89,
the systems I managed for Bellcore helped place that call. ... I could go
on. I've worked in the "always up" community a long time now and have
worked with/for more corporations in this capacity than nearly anyone you
might find - mostly very large, well known companies.

My observation is that we have a real shortage of quality operating
systems today, and what few exist/remain don't enjoy much market share
because they're not based on Unix, so they're largely missing out on the
Open Source activity. What may be worse, young people who don't know any
better are sometimes told/taught not to bother with anything over five
years old as it's antiquated so they don't ever find out that things could
be better - and once were. (Example, anyone who thinks "man pages" are
great has obviously got a very limited experience from which to base their
opinion!) ... As a practical matter today we mostly have a choice of
Windows or some flavor of unix, neither of which are great. That would be
very different in my opinion if only Unix didn't have this asenine view
that the choice between a memory management strategy that kills random
processes and turning that off and accepting that your system hangs is a
reasonable choice and that spending a measily % of performance in overhead
to eliminate the problem is out of the question. Asenine, I tell you.

Meanwhile, what Operating Systems ARE _today_ reliable choices upon which
to run your Postgres datababse engine?

...BTW, McDonalds in Paris?! -smile- Just make sure you order Freedom
Fries!

Richard

Post by Mark Walker
Maybe that's just my experience with my customers. I have seen signs of
dysfunctional computer systems lately. I was in a fast food restaurant
in San Francisco a few months back and they were manually taking
orders. I think the only reason they stayed open was because the owner
was there. Last summer a McDonald's in Paris next to the hotel my
family was staying at shut down because their computer system was down.
It ticked me off because we ended up eating at some pricey cafe next
door. I guess I'm a typical dumb American, traveling all the way to
Paris to eat at McDonald's.

Post by Richard Troy

Post by Mark Walker
LOL, I remember those days. "Uh, can you hold on? My computer just
went down." or "you need to fill out form 1203-B, send us $25 and we'll
get you the information you need in six weeks." Just kidding, but
certainly reliability standards and information demands are much higher
these days, aren't they?

"Reliability standards ... higher these days?"
-har-har-har!- That's a good one!
Sure, in terms of bits moved/processed between hardware failures, things
have much improved, but I can't help but think if what a joke it is that
favored operating systems think it's OK to run out of memory for their own
activity and randomly kill processes so they don't hang! HAH! Some
Reilability. And people think this is a Good Thing (tm) because 1% of
overhead was saved!
<rant>
Sure wish the Open Source OS people would get a clue; paying a percent or
so for reliability pays for itself thousands of times over and most
people, if knowledgeable, would choose to spend the overhead to have a
system that really is reliable.
</rant>
Richard

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

--
Richard Troy, Chief Scientist
Science Tools Corporation
510-924-1363 or 202-747-1263
***@ScienceTools.com, http://ScienceTools.com/


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

[David Fetter]

Post by Richard Troy

Post by Mark Walker
I don't know. My customers expect 24/7 reliability. They expect
to be able to access their info anywhere in the world over a
variety of different devices. I can remember times when people
would just go home because computer networks were down. I haven't
seen that happen in a long time.

...Back in 1986, Cheryl Healy and I took on running Polaroid's
corporate systems "24 X 7" - and we worked hard to make it "24 X 7 X
365.24". Shortly thereafter - while still working with Cheryl,
Angel Vila, Chris Boerner and I took on running Bellcore's 800
telephone network full time also - our success was measured how few
minutes/seconds there was any lost business at all on an annual
basis. (Bellcore was previously known as AT&T Bell Laboratories.) If
you made an 800 number based call from '86 to '89, the systems I
managed for Bellcore helped place that call. ... I could go on. I've
worked in the "always up" community a long time now and have worked
with/for more corporations in this capacity than nearly anyone you
might find - mostly very large, well known companies.
My observation is that we have a real shortage of quality operating
systems today, and what few exist/remain don't enjoy much market
share because they're not based on Unix, so they're largely missing
out on the Open Source activity. What may be worse, young people who
don't know any better are sometimes told/taught not to bother with
anything over five years old as it's antiquated so they don't ever
find out that things could be better - and once were. (Example,
anyone who thinks "man pages" are great has obviously got a very
limited experience from which to base their opinion!) ... As a
practical matter today we mostly have a choice of Windows or some
flavor of unix, neither of which are great. That would be very
different in my opinion if only Unix didn't have this asenine view
that the choice between a memory management strategy that kills
random processes and turning that off and accepting that your system
hangs is a reasonable choice and that spending a measily % of
performance in overhead to eliminate the problem is out of the
question. Asenine, I tell you.

The OOM killer in Linux is, indeed, asinine. You can shut it off,
though, and systems administrators worth their salt know this and do
it as a matter of routine. If you have some strategy that doesn't
involve those hangs as a consequence, I'm sure you can get an audience
from the Linux kernel people and/or the FreeBSD ones.

Post by Richard Troy
Meanwhile, what Operating Systems ARE _today_ reliable choices upon
which to run your Postgres datababse engine?
...BTW, McDonalds in Paris?! -smile- Just make sure you order
Freedom Fries!

McDonalds in Paris is good for one thing and one thing only: their
free public toilets. :)

Cheers,
D (and even the French understand that fries are actually from Belgium ;)
--
David Fetter <***@fetter.org> http://fetter.org/
phone: [phone] AIM: dfetter666
Skype: davidfetter

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

[Tom Lane]

Post by David Fetter

... different in my opinion if only Unix didn't have this asenine view
that the choice between a memory management strategy that kills
random processes and turning that off and accepting that your system
hangs is a reasonable choice and that spending a measily % of
performance in overhead to eliminate the problem is out of the
question. Asenine, I tell you.

The OOM killer in Linux is, indeed, asinine.

Well, it probably has some use for desktop systems, or would if it could
distinguish essential from inessential processes. But please Richard:
Linux is not Unix, it's merely one implementation of a Unix-ish system.
You are tarring *BSD, Solaris, HPUX, and a bunch of others with a
failing that is not theirs.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

[Richard Troy]

Post by Tom Lane

Post by David Fetter

... different in my opinion if only Unix didn't have this asenine view
that the choice between a memory management strategy that kills
random processes and turning that off and accepting that your system
hangs is a reasonable choice and that spending a measily % of
performance in overhead to eliminate the problem is out of the
question. Asenine, I tell you.

The OOM killer in Linux is, indeed, asinine.

Well, it probably has some use for desktop systems, or would if it could
Linux is not Unix, it's merely one implementation of a Unix-ish system.
You are tarring *BSD, Solaris, HPUX, and a bunch of others with a
failing that is not theirs.

...Hmmm, You're Right, Tom, no tarring intended beyond that deserved.
Skipping the "is Linux a varriant of Unix" debate, I was apparently under
the mistaken impression that at the very least HPUX and Solaris share this
OOM Killer -ahem- feature as folks made comments to that effect on one of
the PG lists a few months ago - or, perhaps I simply
misread/misunderstood.

Meanwhile, it's a very useful question to ask what the most reliable
platforms are to run your production Postgres installations on, though it
deserves its own thread, rather than treading on a cross-db-same-server
dialogue.

Regards,
Richard

[Jeff Davis]

Post by David Fetter

Post by Richard Troy

Post by Mark Walker
I don't know. My customers expect 24/7 reliability. They expect
to be able to access their info anywhere in the world over a
variety of different devices. I can remember times when people
would just go home because computer networks were down. I haven't
seen that happen in a long time.

...Back in 1986, Cheryl Healy and I took on running Polaroid's
corporate systems "24 X 7" - and we worked hard to make it "24 X 7 X
365.24". Shortly thereafter - while still working with Cheryl,
Angel Vila, Chris Boerner and I took on running Bellcore's 800
telephone network full time also - our success was measured how few
minutes/seconds there was any lost business at all on an annual
basis. (Bellcore was previously known as AT&T Bell Laboratories.) If
you made an 800 number based call from '86 to '89, the systems I
managed for Bellcore helped place that call. ... I could go on. I've
worked in the "always up" community a long time now and have worked
with/for more corporations in this capacity than nearly anyone you
might find - mostly very large, well known companies.
My observation is that we have a real shortage of quality operating
systems today, and what few exist/remain don't enjoy much market
share because they're not based on Unix, so they're largely missing
out on the Open Source activity. What may be worse, young people who
don't know any better are sometimes told/taught not to bother with
anything over five years old as it's antiquated so they don't ever
find out that things could be better - and once were. (Example,
anyone who thinks "man pages" are great has obviously got a very
limited experience from which to base their opinion!) ... As a
practical matter today we mostly have a choice of Windows or some
flavor of unix, neither of which are great. That would be very
different in my opinion if only Unix didn't have this asenine view
that the choice between a memory management strategy that kills
random processes and turning that off and accepting that your system
hangs is a reasonable choice and that spending a measily % of
performance in overhead to eliminate the problem is out of the
question. Asenine, I tell you.

The OOM killer in Linux is, indeed, asinine. You can shut it off,
though, and systems administrators worth their salt know this and do
it as a matter of routine. If you have some strategy that doesn't
involve those hangs as a consequence, I'm sure you can get an audience
from the Linux kernel people and/or the FreeBSD ones.

I know this is off-topic for this list, but is there a place I can get
some details about linux OOM killer, and the conditions that cause this
OS hang when you turn off the OOM killer? I'd like to really know what's
happening, and also know more about the OS hanging condition that you're
talking about. I'd also like to know how safe the "safe" settings really
are ( vm.overcommmit_memory=2 and vm.oom-kill=0? ).

Right now I'm using FreeBSD (in a large part due to the Linux OOM
killer), but I have a different set of problems on FreeBSD.

Regards,
Jeff Davis


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Ron Johnson]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Post by Jeff Davis

Post by David Fetter

Post by Mark Walker
I don't know. My customers expect 24/7 reliability. They expect
to be able to access their info anywhere in the world over a

[snip]

Post by Jeff Davis

Post by David Fetter
The OOM killer in Linux is, indeed, asinine. You can shut it off,
though, and systems administrators worth their salt know this and do
it as a matter of routine. If you have some strategy that doesn't
involve those hangs as a consequence, I'm sure you can get an audience
from the Linux kernel people and/or the FreeBSD ones.

I know this is off-topic for this list, but is there a place I can get
some details about linux OOM killer, and the conditions that cause this
OS hang when you turn off the OOM killer? I'd like to really know what's
happening, and also know more about the OS hanging condition that you're
talking about. I'd also like to know how safe the "safe" settings really
are ( vm.overcommmit_memory=2 and vm.oom-kill=0? ).

No, but I *can* tell you that it's easy to create swap *files* and
enable them at a moment's notice. "man mkswap" tells you how to
make them. Supposedly, the 2.6 kernels fixed any performance
deficits to using swapfiles.

Post by Jeff Davis
Right now I'm using FreeBSD (in a large part due to the Linux OOM
killer), but I have a different set of problems on FreeBSD.

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

iD8DBQFFwObOS9HxQb37XmcRAmyvAJ9tpJN6EhWRoPEI62f5gF8q1URe7wCguq+X
uw3xex5Jd+IbvhElAAofH2E=
=pS5o
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Richard Troy]

Post by Jeff Davis
I know this is off-topic for this list, but is there a place I can get
some details about linux OOM killer, and the conditions that cause this
OS hang when you turn off the OOM killer? I'd like to really know what's
happening, and also know more about the OS hanging condition that you're
talking about. I'd also like to know how safe the "safe" settings really
are ( vm.overcommmit_memory=2 and vm.oom-kill=0? ).
Right now I'm using FreeBSD (in a large part due to the Linux OOM
killer), but I have a different set of problems on FreeBSD.
Regards,
Jeff Davis

...Back in, oh, October perhaps it was, I did some research on this subjet
and posted my findings on one of the PG lists, so you can look into the PG
archives for OOM and my name and likely find a summary and some URLs to
more complete information.

Richard
--
Richard Troy, Chief Scientist
Science Tools Corporation
510-924-1363 or 202-747-1263
***@ScienceTools.com, http://ScienceTools.com/


---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

[Mark Walker]

I used to have OOM killer problems with Tomcat, Apache's JSP server, but
not any more. A new variable appeared in the config settings which had
to do with the maximum memory that Tomcat would use for itself, and I
think that may have been what fixed the problem. Does Postgresql need
something like that? It may be comparing apples and oranges because
Java is very funny about the way it uses memory. It pretty much never
lets go of memory until it decides to invoke its trash collector which
if you decide to do just when the system tells you it's low on memory
can cause big problems.

Post by Richard Troy

Post by Jeff Davis
I know this is off-topic for this list, but is there a place I can get
some details about linux OOM killer, and the conditions that cause this
OS hang when you turn off the OOM killer? I'd like to really know what's
happening, and also know more about the OS hanging condition that you're
talking about. I'd also like to know how safe the "safe" settings really
are ( vm.overcommmit_memory=2 and vm.oom-kill=0? ).
Right now I'm using FreeBSD (in a large part due to the Linux OOM
killer), but I have a different set of problems on FreeBSD.
Regards,
Jeff Davis

...Back in, oh, October perhaps it was, I did some research on this subjet
and posted my findings on one of the PG lists, so you can look into the PG
archives for OOM and my name and likely find a summary and some URLs to
more complete information.
Richard

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

[Richard Troy]

Hello All,

it was recently brought to my attention that last year the U.S. altered
the dates when Daylight Savings Time starts and ends. Many if not most
computers presume the old change dates and therefore, if left to change
automatically, will change at the wrong times. This will be vital for
people in the database community who manage applications that need
accurate timestamps.

You can read up on this issue here, among other places:

http://www.washingtonpost.com/wp-dyn/content/article/2007/01/31/AR2007013102318.html?referrer=emailarticle

I've never investigated how NTP servers handle DST changes - that is,
whether they switch with the fabrication that we have more daylight hours
or leave it to clients. Hmmm... Anybody know? It'd be nice to know that
we can trust our NTP servers to tell our systems what time it is and
therefore ignore this issue for those systems that are NTP clients.

Regards,
Richard

[Scott Marlowe]

Post by Richard Troy
Hello All,
it was recently brought to my attention that last year the U.S. altered
the dates when Daylight Savings Time starts and ends. Many if not most
computers presume the old change dates and therefore, if left to change
automatically, will change at the wrong times. This will be vital for
people in the database community who manage applications that need
accurate timestamps.
http://www.washingtonpost.com/wp-dyn/content/article/2007/01/31/AR2007013102318.html?referrer=emailarticle
I've never investigated how NTP servers handle DST changes - that is,
whether they switch with the fabrication that we have more daylight hours
or leave it to clients. Hmmm... Anybody know? It'd be nice to know that
we can trust our NTP servers to tell our systems what time it is and
therefore ignore this issue for those systems that are NTP clients.

As far as I know, NTP servers run on UTC, so time zone changes are
outside the realm of things they worry about.

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Tom Lane]

Post by Richard Troy
I've never investigated how NTP servers handle DST changes - that is,

I'm pretty sure that NTP runs strictly in UTC. They're more interested
in leap seconds than DST changes ;-). Your NTP clients will still know
what time it is UTC; whether they can convert that to local time is not
NTP's problem.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Ron Johnson]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Post by Richard Troy
Hello All,
it was recently brought to my attention that last year the U.S. altered
the dates when Daylight Savings Time starts and ends. Many if not most
computers presume the old change dates and therefore, if left to change
automatically, will change at the wrong times. This will be vital for
people in the database community who manage applications that need
accurate timestamps.

Your distro (or *BSD) should supply updated tz data, no?

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

iD8DBQFFwmxeS9HxQb37XmcRAoxmAKDTXeVVGV4C+yGAgyg7pjf/wuQYZwCg1bLD
ZV1hDCztZCg5D+XjByTY0qA=
=OyUg
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

[Scott Marlowe]

Post by Ron Johnson
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Post by Richard Troy
Hello All,
it was recently brought to my attention that last year the U.S. altered
the dates when Daylight Savings Time starts and ends. Many if not most
computers presume the old change dates and therefore, if left to change
automatically, will change at the wrong times. This will be vital for
people in the database community who manage applications that need
accurate timestamps.

Your distro (or *BSD) should supply updated tz data, no?

Yes, but as of pgsql 8.0 the database does it's own timezone shifting.

However, I wonder. If it's on a server with the hardware clock tracking
UTC, and a timezone database that might be out of wack, would pgsql
still get the timezones right internally?

Another point. The timezone databases need to be updated before you
start storing things referencing days during that period. I.e. if
you've got a scheduling app that's scheduling people today for meetings
on March 12th and the database has an out of date timezone db, then it
will be scheduling them for the wrong times, and when you put the right
tz db on it, it will be an hour off come March 12th. I think.

---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[John D. Burger]

Sorry if I'm the only one to find this amusing, but I see that the
original message was sent twenty minutes =after= I received it. :)

- John D. Burger
MITRE



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

[Jim C.]

Post by John D. Burger
Sorry if I'm the only one to find this amusing, but I see that the
original message was sent twenty minutes =after= I received it. :)

Probably sent from a different time zone.

Jim C.


---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Martijn van Oosterhout]

Post by Jeff Davis
I know this is off-topic for this list, but is there a place I can get
some details about linux OOM killer, and the conditions that cause this
OS hang when you turn off the OOM killer? I'd like to really know what's
happening, and also know more about the OS hanging condition that you're
talking about. I'd also like to know how safe the "safe" settings really
are ( vm.overcommmit_memory=2 and vm.oom-kill=0? ).

The most important fact about overcommit is that if off, the system
won't let you allocate more memory than swap plus a percentage of real
memory. If you use it, make sure you allocate plenty of swap, or you'll
find you get out of memory errors long before ou're actually out of
memory.

Have a nice day,

[Bruno Wolff III]

On Tue, Jan 30, 2007 at 16:43:14 -0800,

Post by Richard Troy
be better - and once were. (Example, anyone who thinks "man pages" are
great has obviously got a very limited experience from which to base their
opinion!) ... As a practical matter today we mostly have a choice of

I remember when I first was exposed to man pages. My thoughts were that it
was great to have documentation on line, but too bad the total extent of
the documentation was the man pages. Actually there were a few more things
in the unix books, but I didn't easy access to them, unlike our VMS manuals
which I really liked. (I've also read a lot of exec 8 documentation and I
didn't like that system.)

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Paul Lambert]

Post by Ron Johnson
[snip]
My observation is that we have a real shortage of quality operating
systems today, and what few exist/remain don't enjoy much market share
because they're not based on Unix, so they're largely missing out on the
Open Source activity. What may be worse, young people who don't know any
better are sometimes told/taught not to bother with anything over five
years old as it's antiquated so they don't ever find out that things could
be better - and once were. (Example, anyone who thinks "man pages" are
great has obviously got a very limited experience from which to base their
opinion!) ... As a practical matter today we mostly have a choice of
Windows or some flavor of unix, neither of which are great. That would be
very different in my opinion if only Unix didn't have this asenine view
that the choice between a memory management strategy that kills random
processes and turning that off and accepting that your system hangs is a
reasonable choice and that spending a measily % of performance in overhead
to eliminate the problem is out of the question. Asenine, I tell you.
Meanwhile, what Operating Systems ARE _today_ reliable choices upon which
to run your Postgres datababse engine?
[snip]

<Insert another plug for an OpenVMS port here>

Aside from the fact that HP's upper management don't appear to be aware
of the existance of OpenVMS, it's a system that it hard to find fault
with. On the alpha chip anyway... the Itanium is another story. It would
be a very reliable choice on which to run a high-availability database

As for your young people don't know any better comment... I'm a young 25
years of age, and I know much greener pastures than Weenblows or Unix. ;)

[Ron Johnson]

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Post by Paul Lambert

[snip] My observation is that we have a real shortage of
quality

[snip]

Post by Paul Lambert

Meanwhile, what Operating Systems ARE _today_ reliable choices
upon which to run your Postgres datababse engine? [snip]

<Insert another plug for an OpenVMS port here>
Aside from the fact that HP's upper management don't appear to be
aware of the existance of OpenVMS, it's a system that it hard to
find fault with. On the alpha chip anyway... the Itanium is
another story. It would be a very reliable choice on which to run
a high-availability database

If PostgreSQL is a typical Unix app and relies on forking lots of
children, then I don't think it would run well on VMS. The two (Unix
vs VMS) process models are very different, and LIB$SPAWN is very
expensive.

Post by Paul Lambert
As for your young people don't know any better comment... I'm a
young 25 years of age, and I know much greener pastures than
Weenblows or Unix. ;)

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

iD8DBQFFwI47S9HxQb37XmcRAoUSAKCeEAGGvlM9Y6uNTDondV8+vHn/qgCfWKy0
6XRVjSi6vCQcBMo4rmzQyoQ=
=ElzL
-----END PGP SIGNATURE-----

---------------------------(end of broadcast)---------------------------
TIP 4: Have you searched our list archives?

http://archives.postgresql.org/

[Tom Lane]

Post by Mark Walker
Maybe that's just my experience with my customers. I have seen signs of
dysfunctional computer systems lately. I was in a fast food restaurant
in San Francisco a few months back and they were manually taking
orders. I think the only reason they stayed open was because the owner
was there.

The local McD's seemed completely unable to cope the other day when
their computer was down. If they had any manual procedures in place,
the cashier my wife was dealing with was unaware of them ... she ended
up going somewhere else.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: Don't 'kill -9' the postmaster

[Michelle Konzack]

Post by Mark Walker
Maybe that's just my experience with my customers. I have seen signs of
dysfunctional computer systems lately. I was in a fast food restaurant
in San Francisco a few months back and they were manually taking
orders. I think the only reason they stayed open was because the owner
was there. Last summer a McDonald's in Paris next to the hotel my
family was staying at shut down because their computer system was down.
It ticked me off because we ended up eating at some pricey cafe next
door. I guess I'm a typical dumb American, traveling all the way to
Paris to eat at McDonald's.

:-)

Do you know "Döner Kebab"?

Realy good beek or chicken with fresh salad!
Not this "american" fast-feed. :-)

And its cheaper!

Greetings
Michelle Konzack
Systemadministrator
Tamay Dogan Network
Debian GNU/Linux Consultant

[Merlin Moncure]

Post by Tony Caduto
I know it can be done in M$ SQL server using .. notation and I bet you
can do it in DB2 and Oracle.
you can even do it in MySQL, in MySQL it's their way of implementing
schemas.

exactly. mysql does not have schemas, and imho schemas > mysql
databases. Some servers (ms sql server) allow you to do 4 part
qualification, but the architecture is different over there and not
necessarily bettter (IMO). The postgresql database is neatly
associated with a connection which I think is rather elegant...in my
opinion it would be better to allow schemas to nest than to allow
cross database querying.

merlin

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

[Jorge Godoy]

Post by Merlin Moncure
opinion it would be better to allow schemas to nest than to allow
cross database querying.

Nested schemas would be great, indeed. But, on the other hand, being able to
do queries in other databases would also help with partitioning and legacy
systems integration...

[Jeffrey Webster]

Post by Peter Eisentraut
This has been discussed about ten thousand times, and the answer is
still no.

How did we go from this?

Post by Peter Eisentraut
It's already in the TODO list.
regards, tom lane

Perhaps we should be more diplomatic in our approach to responding?

I'm going to assume that, at some point, I can look forward to this feature
in PostgreSQL.

-- Jeff W.

[Joshua D. Drake]

Post by Peter Eisentraut
This has been discussed about ten thousand times, and the answer is
still no.

Actually the answer is: Check the TODO list. It is listed under Exotic
features, so the answer is, no we can't yes we would like to.

That being said, I think it is a dumb feature. If you have data in one
database, that requires access to another database within the same
cluster. You designed your database incorrectly and should be using schemas.

If you have data in one database that requires access to another
database that is not in the same cluster (which happens alot) use dbi-link.

Joshua D. Drake




---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly

[Gregory S. Williamson]

I actually disagree, mildly.

Our system uses two variants of two types of data.

Client data has a presence in the billing database, but has an incarnation in our runtime servers to allow for authentication. Not the same databases, since we can't afford the extra time for the hop, which might be scores of miles away and not necessarily available. Not exactly the same data, and not all of the billing stuff goes to runtime.

Spatial data has a representation in our backroom servers which support processing incoming imagery. Runtime has a similar representation (with some serious handwaving for speed) of the spatial data. And there's some links between content management and billing to allow for royalties. Again, similar but not identical data/purposes.

Informix has a capability (a "synonym") to make a table in another instance appear as a local table; certain operations aren't possible [remote index structures aren't visible IIRC and a few data manipulations]. I could use a synonym to do joins and updates on the remote tables in native SQL; with postgres I need to do a lot more handwaving -- actually pulling logic out of the databases and putting it into applications. (Yes, db-link would work but it seemed

Sorry for top-posting but this interface doesn't do graceful quoting, etc.

Greg Williamson
DBA
GlobeXplorer LLC, a DigitalGlobe company

Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.

(My corporate masters made me say this.)

-----Original Message-----
From: pgsql-general-***@postgresql.org on behalf of Joshua D. Drake
Sent: Tue 1/30/2007 6:15 PM
To: Peter Eisentraut
Cc: pgsql-***@postgresql.org; Tony Caduto
Subject: Re: [GENERAL] Any Plans for cross database queries on the same server?

Post by Peter Eisentraut
This has been discussed about ten thousand times, and the answer is
still no.

Actually the answer is: Check the TODO list. It is listed under Exotic
features, so the answer is, no we can't yes we would like to.

That being said, I think it is a dumb feature. If you have data in one
database, that requires access to another database within the same
cluster. You designed your database incorrectly and should be using schemas.

If you have data in one database that requires access to another
database that is not in the same cluster (which happens alot) use dbi-link.

Joshua D. Drake




---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to ***@postgresql.org so that your
message can get through to the mailing list cleanly


-------------------------------------------------------
Click link below if it is SPAM ***@globexplorer.com
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=45bff9ca316118362916074&user=***@globexplorer.com&retrain=spam&template=history&history_page=1"
!DSPAM:45bff9ca316118362916074!
-------------------------------------------------------






---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

[Joshua D. Drake]

Post by Gregory S. Williamson
I actually disagree, mildly.

Keep in mind that I was speaking generally and to that note, I generally
agree with what you suggest below. The point I was trying to make
and wasn't be clear enough about is most people that want the feature,
want it for the wrong reasons. :)

Joshua D. Drake

Post by Gregory S. Williamson
Our system uses two variants of two types of data.
Client data has a presence in the billing database, but has an incarnation in our runtime servers to allow for authentication. Not the same databases, since we can't afford the extra time for the hop, which might be scores of miles away and not necessarily available. Not exactly the same data, and not all of the billing stuff goes to runtime.
Spatial data has a representation in our backroom servers which support processing incoming imagery. Runtime has a similar representation (with some serious handwaving for speed) of the spatial data. And there's some links between content management and billing to allow for royalties. Again, similar but not identical data/purposes.
Informix has a capability (a "synonym") to make a table in another instance appear as a local table; certain operations aren't possible [remote index structures aren't visible IIRC and a few data manipulations]. I could use a synonym to do joins and updates on the remote tables in native SQL; with postgres I need to do a lot more handwaving -- actually pulling logic out of the databases and putting it into applications. (Yes, db-link would work but it seemed
Sorry for top-posting but this interface doesn't do graceful quoting, etc.
Greg Williamson
DBA
GlobeXplorer LLC, a DigitalGlobe company
Confidentiality Notice: This e-mail message, including any attachments, is for the sole use of the intended recipient(s) and may contain confidential and privileged information and must be protected in accordance with those provisions. Any unauthorized review, use, disclosure or distribution is prohibited. If you are not the intended recipient, please contact the sender by reply e-mail and destroy all copies of the original message.
(My corporate masters made me say this.)
-----Original Message-----
Sent: Tue 1/30/2007 6:15 PM
To: Peter Eisentraut
Subject: Re: [GENERAL] Any Plans for cross database queries on the same server?

Post by Peter Eisentraut
This has been discussed about ten thousand times, and the answer is
still no.

Actually the answer is: Check the TODO list. It is listed under Exotic
features, so the answer is, no we can't yes we would like to.
That being said, I think it is a dumb feature. If you have data in one
database, that requires access to another database within the same
cluster. You designed your database incorrectly and should be using schemas.
If you have data in one database that requires access to another
database that is not in the same cluster (which happens alot) use dbi-link.
Joshua D. Drake
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
message can get through to the mailing list cleanly
-------------------------------------------------------
!DSPAM:45bff9ca316118362916074!
-------------------------------------------------------
---------------------------(end of broadcast)---------------------------
TIP 5: don't forget to increase your free space map settings

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Tom Lane]

Post by Joshua D. Drake
Actually the answer is: Check the TODO list. It is listed under Exotic
features, so the answer is, no we can't yes we would like to.
That being said, I think it is a dumb feature.

FWIW, the SQL committee thinks it's a fine idea --- the SQL-MED section
of the standard is all about this. (Note MED = "Management of External
Data", or something close to that; not MEDical as one might guess.)

But don't hold your breath waiting for us to implement SQL-MED; it's
not something that seems to be high on the priority list of any active
hackers. As far as I've heard it's not had that much uptake among
commercial databases either. (Hm, you don't suppose that Oracle might
be less than excited about exchanging data with non-Oracle DBs? Naw,
couldn't be...)

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[David Fetter]

Post by Joshua D. Drake

Post by Peter Eisentraut
This has been discussed about ten thousand times, and the answer is
still no.

Actually the answer is: Check the TODO list. It is listed under
Exotic features, so the answer is, no we can't yes we would like to.
That being said, I think it is a dumb feature. If you have data in
one database, that requires access to another database within the
same cluster. You designed your database incorrectly and should be
using schemas.
If you have data in one database that requires access to another
database that is not in the same cluster (which happens alot) use dbi-link.

Thanks for the pointer, but you only need DBI-Link
<http://pgfoundry.org/projects/dbi-link/>, and I only recommend it, if
you need data from a non-PostgreSQL data source.

For a PostgreSQL data source, use Joe Conway's excellent contrib/dblink.

Cheers,
D
--
David Fetter <***@fetter.org> http://fetter.org/
phone: [phone] AIM: dfetter666
Skype: davidfetter

Remember to vote!

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match

[Tony Caduto]

Post by Joshua D. Drake
That being said, I think it is a dumb feature. If you have data in
one database, that requires access to another database within the
same cluster. You designed your database incorrectly and should be
using schemas.

I would have to disagree, it's a feature that has been available on M$
SQL server and the other commercial
databases for years. It's hardly a dumb feature.



---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

[Scott Marlowe]

Post by Tony Caduto

Post by Joshua D. Drake
That being said, I think it is a dumb feature. If you have data in
one database, that requires access to another database within the
same cluster. You designed your database incorrectly and should be
using schemas.

I would have to disagree, it's a feature that has been available on M$
SQL server and the other commercial
databases for years. It's hardly a dumb feature.

I think it's a more complex problem than most people think. For
instance, if I do:

/connect db3
begin;
set transaction isolation level serializable;
insert into mytable (id1, id2) (select a.id, b.name from
db1..schema.table a join db2..schema.table b);

what exactly DOES that transaction isolation level mean? We don't have
distributed transactions yet... I can see this creating as many
problems as it solves, at least in the short run. Especially if people
start doing updates based on cross db joins.



---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

[David Fetter]

Post by Tony Caduto

Post by Joshua D. Drake
That being said, I think it is a dumb feature. If you have data in
one database, that requires access to another database within the
same cluster. You designed your database incorrectly and should be
using schemas.

I would have to disagree, it's a feature that has been available on
M$ SQL server and the other commercial databases for years. It's
hardly a dumb feature.

That was Josh Drake, not me. Please to watch the quote count and
preserve attribution.

Cheers,
D