Thread: Any Plans for cross database queries on the same server?

Any Plans for cross database queries on the same server?

From
Tony Caduto
Date:
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?

--
Tony


Re: Any Plans for cross database queries on the same server?

From
Peter Eisentraut
Date:
This has been discussed about ten thousand times, and the answer is
still no.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Re: Any Plans for cross database queries on the same server?

From
Tony Caduto
Date:
Peter Eisentraut wrote:
> 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?

--
Tony


Re: Any Plans for cross database queries on the same server?

From
Guido Neitzer
Date:
Am 30.01.2007 um 12:11 schrieb 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

Re: Any Plans for cross database queries on the same server?

From
"Brandon Aiken"
Date:
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-owner@postgresql.org
[mailto:pgsql-general-owner@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?

Am 30.01.2007 um 12:11 schrieb 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
arenot 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
messagefrom your computer. 

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


Re: Any Plans for cross database queries on the same server?

From
Ron Johnson
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/30/07 13:33, Brandon Aiken wrote:
> 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.

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

Re: Any Plans for cross database queries on the same server?

From
Tony Caduto
Date:
Ron Johnson wrote:
>>  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....

--
Tony


Re: Any Plans for cross database queries on the same server?

From
Tom Lane
Date:
Tony Caduto <tony_caduto@amsoftwaredesign.com> writes:
> 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

Re: Any Plans for cross database queries on the same server?

From
Mark Walker
Date:
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.



Tony Caduto wrote:
> Ron Johnson wrote:
>>>  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....
>


Re: Any Plans for cross database queries on the same server?

From
Tony Caduto
Date:
Mark Walker wrote:
>  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,

--
Tony


Re: Any Plans for cross database queries on the same server?

From
Ron Johnson
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/30/07 14:41, Tony Caduto wrote:
> Mark Walker wrote:
>>  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-----

Re: Any Plans for cross database queries on the same server?

From
Richard Troy
Date:
> On 01/30/07 14:41, Tony Caduto wrote:
> > Mark Walker wrote:
> >>  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

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


Re: Any Plans for cross database queries on the same server?

From
Ron Johnson
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/30/07 15:55, Richard Troy wrote:
>> On 01/30/07 14:41, Tony Caduto wrote:
>>> Mark Walker wrote:
[snip]
> 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-----

Re: Any Plans for cross database queries on the same server?

From
Mark Walker
Date:
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?

Ron Johnson wrote:
> -----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
>
>


Re: Any Plans for cross database queries on the same server?

From
Ron Johnson
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/30/07 16:35, Mark Walker wrote:
> 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-----

Re: Any Plans for cross database queries on the same server?

From
Richard Troy
Date:
On Tue, 30 Jan 2007, Mark Walker wrote:
>
> 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
rtroy@ScienceTools.com, http://ScienceTools.com/


Re: Any Plans for cross database queries on the same server?

From
"Jeffrey Webster"
Date:
On 1/30/07, Peter Eisentraut <peter_e@gmx.net> wrote:
This has been discussed about ten thousand times, and the answer is
still no.

How did we go from this?

To this:

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

Re: Any Plans for cross database queries on the same server?

From
Mark Walker
Date:
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.


Richard Troy wrote:
> On Tue, 30 Jan 2007, Mark Walker wrote:
>
>> 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
>
>


Re: Any Plans for cross database queries on the same server?

From
Richard Troy
Date:
On Tue, 30 Jan 2007, Mark Walker wrote:
>
> 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

>
> 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.
>
>
> Richard Troy wrote:
> > On Tue, 30 Jan 2007, Mark Walker wrote:
> >
> >> 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
rtroy@ScienceTools.com, http://ScienceTools.com/


Re: Any Plans for cross database queries on the same server?

From
Tom Lane
Date:
Mark Walker <furface@omnicode.com> writes:
> 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

Re: Any Plans for cross database queries on the same server?

From
"Joshua D. Drake"
Date:
Peter Eisentraut wrote:
> 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




Re: Any Plans for cross database queries on the same server?

From
"Gregory S. Williamson"
Date:
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
awayand 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
similarrepresentation (with some serious handwaving for speed) of the spatial data. And there's some links between
contentmanagement 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'tpossible [remote index structures aren't visible IIRC and a few data manipulations]. I could use a synonym to do
joinsand updates on the remote tables in native SQL; with postgres I need to do a lot more handwaving -- actually
pullinglogic 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-owner@postgresql.org on behalf of Joshua D. Drake
Sent:    Tue 1/30/2007 6:15 PM
To:    Peter Eisentraut
Cc:    pgsql-general@postgresql.org; Tony Caduto
Subject:    Re: [GENERAL] Any Plans for cross database queries on the same server?

Peter Eisentraut wrote:
> 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 majordomo@postgresql.org so that your
       message can get through to the mailing list cleanly


-------------------------------------------------------
Click link below if it is SPAM gsw@globexplorer.com

"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=45bff9ca316118362916074&user=gsw@globexplorer.com&retrain=spam&template=history&history_page=1"
!DSPAM:45bff9ca316118362916074!
-------------------------------------------------------






Re: Any Plans for cross database queries on the same server?

From
"Joshua D. Drake"
Date:
Gregory S. Williamson wrote:
> 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

> 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
awayand 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
similarrepresentation (with some serious handwaving for speed) of the spatial data. And there's some links between
contentmanagement 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
operationsaren't possible [remote index structures aren't visible IIRC and a few data manipulations]. I could use a
synonymto do joins and updates on the remote tables in native SQL; with postgres I need to do a lot more handwaving --
actuallypulling 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-owner@postgresql.org on behalf of Joshua D. Drake
> Sent:    Tue 1/30/2007 6:15 PM
> To:    Peter Eisentraut
> Cc:    pgsql-general@postgresql.org; Tony Caduto
> Subject:    Re: [GENERAL] Any Plans for cross database queries on the same server?
>
> Peter Eisentraut wrote:
>
>> 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 majordomo@postgresql.org so that your
>        message can get through to the mailing list cleanly
>
>
> -------------------------------------------------------
> Click link below if it is SPAM gsw@globexplorer.com
>
"https://mailscanner.globexplorer.com/dspam/dspam.cgi?signatureID=45bff9ca316118362916074&user=gsw@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
>
>


Re: Any Plans for cross database queries on the same server?

From
Tom Lane
Date:
"Joshua D. Drake" <jd@commandprompt.com> writes:
> 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

Re: Any Plans for cross database queries on the same server?

From
David Fetter
Date:
On Tue, Jan 30, 2007 at 06:15:01PM -0800, Joshua D. Drake wrote:
> Peter Eisentraut wrote:
> >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 <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!

Re: Any Plans for cross database queries on the same server?

From
David Fetter
Date:
On Tue, Jan 30, 2007 at 04:43:14PM -0800, Richard Troy wrote:
> On Tue, 30 Jan 2007, Mark Walker wrote:
> >
> > 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.

> 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 <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!

Re: Any Plans for cross database queries on the same server?

From
Bruno Wolff III
Date:
On Tue, Jan 30, 2007 at 16:43:14 -0800,
  Richard Troy <rtroy@ScienceTools.com> wrote:
>
> 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.)

Re: Any Plans for cross database queries on the same server?

From
Tom Lane
Date:
David Fetter <david@fetter.org> writes:
> On Tue, Jan 30, 2007 at 04:43:14PM -0800, Richard Troy wrote:
>> ... 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

Re: Any Plans for cross database queries on the same server?

From
Tony Caduto
Date:
David Fetter wrote:
>> 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.



Re: Any Plans for cross database queries on the same server?

From
Paul Lambert
Date:
Richard Troy wrote:
>
> [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. ;)

--
Paul Lambert
Database Administrator
AutoLedgers


Re: Any Plans for cross database queries on the same server?

From
Scott Marlowe
Date:
On Tue, 2007-01-30 at 23:45 -0600, Tony Caduto wrote:
> David Fetter wrote:
> >> 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.



Re: Any Plans for cross database queries on the same server?

From
David Fetter
Date:
On Tue, Jan 30, 2007 at 11:45:51PM -0600, Tony Caduto wrote:
> David Fetter wrote:
> >>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
--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778        AIM: dfetter666
                              Skype: davidfetter

Remember to vote!

Re: Any Plans for cross database queries on the same server?

From
Ron Johnson
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/30/07 23:46, Paul Lambert wrote:
> Richard Troy wrote:
>>
>> [snip]  My observation is that we have a real shortage of
>> quality
[snip]
>> 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.

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

Re: Any Plans for cross database queries on the same server?

From
"Merlin Moncure"
Date:
On 1/30/07, Tony Caduto <tony_caduto@amsoftwaredesign.com> wrote:
> 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

Re: Any Plans for cross database queries on the same server?

From
Jorge Godoy
Date:
"Merlin Moncure" <mmoncure@gmail.com> writes:

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

--
Jorge Godoy      <jgodoy@gmail.com>

Re: Any Plans for cross database queries on the same server?

From
Jeff Davis
Date:
On Tue, 2007-01-30 at 20:44 -0800, David Fetter wrote:
> On Tue, Jan 30, 2007 at 04:43:14PM -0800, Richard Troy wrote:
> > On Tue, 30 Jan 2007, Mark Walker wrote:
> > >
> > > 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


Re: Any Plans for cross database queries on the same server?

From
Richard Troy
Date:
On Wed, 31 Jan 2007, Tom Lane wrote:
> David Fetter <david@fetter.org> writes:
> > On Tue, Jan 30, 2007 at 04:43:14PM -0800, Richard Troy wrote:
> >> ... 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.

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

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


Re: Any Plans for cross database queries on the same server?

From
Ron Johnson
Date:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 01/31/07 12:37, Jeff Davis wrote:
> On Tue, 2007-01-30 at 20:44 -0800, David Fetter wrote:
>> On Tue, Jan 30, 2007 at 04:43:14PM -0800, Richard Troy wrote:
>>> On Tue, 30 Jan 2007, Mark Walker wrote:
>>>> 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]
>> 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.

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

Re: Any Plans for cross database queries on the same server?

From
Richard Troy
Date:
On Wed, 31 Jan 2007, Jeff Davis wrote:
>
> 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
rtroy@ScienceTools.com, http://ScienceTools.com/


Re: Any Plans for cross database queries on the same server?

From
Mark Walker
Date:
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.


Richard Troy wrote:
> On Wed, 31 Jan 2007, Jeff Davis wrote:
>
>> 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
>
>


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

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


On Thu, 2007-02-01 at 15:15, Richard Troy wrote:
> 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.

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

Richard Troy <rtroy@ScienceTools.com> writes:
> 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

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

On 02/01/07 15:15, Richard Troy wrote:
> 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-----

On Thu, 2007-02-01 at 16:40, Ron Johnson wrote:
> -----BEGIN PGP SIGNED MESSAGE-----
> Hash: SHA1
>
> On 02/01/07 15:15, Richard Troy wrote:
> > 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.

Re: Any Plans for cross database queries on the same server?

From
Martijn van Oosterhout
Date:
On Wed, Jan 31, 2007 at 10:37:34AM -0800, Jeff Davis wrote:
> 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,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> From each according to his ability. To each according to his ability to litigate.

Attachment

Re: Production systems beware: U.S. Daylight Savings Time comes at a new time this year

From
"John D. Burger"
Date:
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



John D. Burger wrote:
> 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.

Re: Any Plans for cross database queries on the same server?

From
Michelle Konzack
Date:
Am 2007-01-30 15:40:23, schrieb 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


--
Linux-User #280138 with the Linux Counter, http://counter.li.org/
##################### Debian GNU/Linux Consultant #####################
Michelle Konzack   Apt. 917                  ICQ #328449886
                   50, rue de Soultz         MSM LinuxMichi
0033/6/61925193    67100 Strasbourg/France   IRC #Debian (irc.icq.com)

Attachment