Thread: Join between databases or (???)

Join between databases or (???)

From
"Net Virtual Mailing Lists"
Date:
Hello,

I have situation where multiple databases need to use data from a common
source and it would consume way too much disk space to reproduce this
data into the many databases which require it.  Is there some way to do a
join between databases or some other way of making the data in one
database available in another in a space-efficient manner?   Of course it
is necessary that if this is possible any queries need to take advantage
of indexes/etc.

Thanks!

- Greg


Re: Join between databases or (???)

From
Michael Fuhr
Date:
On Sat, Nov 20, 2004 at 06:09:49PM -0700, Net Virtual Mailing Lists wrote:

> I have situation where multiple databases need to use data from a common
> source and it would consume way too much disk space to reproduce this
> data into the many databases which require it.  Is there some way to do a
> join between databases or some other way of making the data in one
> database available in another in a space-efficient manner?   Of course it
> is necessary that if this is possible any queries need to take advantage
> of indexes/etc.

You could use dblink but it might not provide all the functionality
you're looking for.  Could the multiple databases possibly be converted
into multiple schemas in the same database?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: Join between databases or (???)

From
"Net Virtual Mailing Lists"
Date:
I suspect that schemas are the best way to go, but gosh is it going to be
a bear to re-engineer all this stuff... Unfortunately it was all done in
the "pre-schema" days of Postgres and just evolved from there.....

If I want to take an existing table and add it into a schema is it
basically "alter database [database] rename [table] to schema.[table]"?....

.. I guess that I was just looking for confirmation there was not
something in postgres which would allow this "full featured cross-
database join" before pulling an a few all nighters...

Thanks!

- Greg

>On Sat, Nov 20, 2004 at 06:09:49PM -0700, Net Virtual Mailing Lists wrote:
>
>> I have situation where multiple databases need to use data from a common
>> source and it would consume way too much disk space to reproduce this
>> data into the many databases which require it.  Is there some way to do a
>> join between databases or some other way of making the data in one
>> database available in another in a space-efficient manner?   Of course it
>> is necessary that if this is possible any queries need to take advantage
>> of indexes/etc.
>
>You could use dblink but it might not provide all the functionality
>you're looking for.  Could the multiple databases possibly be converted
>into multiple schemas in the same database?
>
>--
>Michael Fuhr
>http://www.fuhr.org/~mfuhr/
>



Re: Join between databases or (???)

From
Michael Fuhr
Date:
On Sat, Nov 20, 2004 at 07:37:12PM -0700, Net Virtual Mailing Lists wrote:

> If I want to take an existing table and add it into a schema is it
> basically "alter database [database] rename [table] to schema.[table]"?....

Unfortunately not.  See a recent thread in pgsql-sql that discussed
this:

http://archives.postgresql.org/pgsql-sql/2004-11/msg00139.php

> .. I guess that I was just looking for confirmation there was not
> something in postgres which would allow this "full featured cross-
> database join" before pulling an a few all nighters...

PostgreSQL doesn't have any inherent cross-database capabilities.
You can use dblink to query another database and join the results
against the current database, but its capabilities probably aren't
what you'd consider "full-featured."  Still, you might want to
check it out if you're not familiar with it.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: Join between databases or (???)

From
"Net Virtual Mailing Lists"
Date:
Thanks for all your help Michael!

I am having one problem I just can't figure out.... In my dump file I
have something like:
CREATE SEQUENCE testschema.industries_industry_id_seq
    INCREMENT BY 1
    NO MAXVALUE
    NO MINVALUE
    CACHE 1;
);

CREATE TABLE testschema.industries (
    industry_id integer DEFAULT nextv
al('"testschema.industries_industry_id_seq"'::text) NOT NULL,
    industry character varying(80) NOT NULL,
    entered_dt timestamp with time zone,
    updated_dt timestamp with time zone
);


When I try to insert a value into schema.industries it complains about
testschema.industries_industry_id_seq not existing, yet I can execute
"nextval" against that very schema....  Any idea what might be going
wrong here?...

Thanks!

- Greg


>On Sat, Nov 20, 2004 at 07:37:12PM -0700, Net Virtual Mailing Lists wrote:
>
>> If I want to take an existing table and add it into a schema is it
>> basically "alter database [database] rename [table] to schema.[table]"?....
>
>Unfortunately not.  See a recent thread in pgsql-sql that discussed
>this:
>
>http://archives.postgresql.org/pgsql-sql/2004-11/msg00139.php
>
>> .. I guess that I was just looking for confirmation there was not
>> something in postgres which would allow this "full featured cross-
>> database join" before pulling an a few all nighters...
>
>PostgreSQL doesn't have any inherent cross-database capabilities.
>You can use dblink to query another database and join the results
>against the current database, but its capabilities probably aren't
>what you'd consider "full-featured."  Still, you might want to
>check it out if you're not familiar with it.
>
>--
>Michael Fuhr
>http://www.fuhr.org/~mfuhr/
>



Re: Join between databases or (???)

From
Michael Fuhr
Date:
On Sun, Nov 21, 2004 at 12:27:11AM -0700, Net Virtual Mailing Lists wrote:

> I am having one problem I just can't figure out.... In my dump file I
> have something like:
> CREATE SEQUENCE testschema.industries_industry_id_seq
>     INCREMENT BY 1
>     NO MAXVALUE
>     NO MINVALUE
>     CACHE 1;
> );

The closing parenthesis is a syntax error.  Did you cut and paste
this or type it by hand?

> CREATE TABLE testschema.industries (
>     industry_id integer DEFAULT nextv
> al('"testschema.industries_industry_id_seq"'::text) NOT NULL,
>     industry character varying(80) NOT NULL,
>     entered_dt timestamp with time zone,
>     updated_dt timestamp with time zone
> );
>
> When I try to insert a value into schema.industries it complains about
> testschema.industries_industry_id_seq not existing, yet I can execute
> "nextval" against that very schema....  Any idea what might be going
> wrong here?...

I think you mean that you can execute nextval() against the sequence,
not the schema.  Anyway, it looks like whatever created the dump
file incorrectly added double quotes around the sequence name in
the nextval() expression.  What created that dump?

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/

Re: Join between databases or (???)

From
"Net Virtual Mailing Lists"
Date:
See comments below..

>On Sun, Nov 21, 2004 at 12:27:11AM -0700, Net Virtual Mailing Lists wrote:
>
>> I am having one problem I just can't figure out.... In my dump file I
>> have something like:
>> CREATE SEQUENCE testschema.industries_industry_id_seq
>>     INCREMENT BY 1
>>     NO MAXVALUE
>>     NO MINVALUE
>>     CACHE 1;
>> );
>
>The closing parenthesis is a syntax error.  Did you cut and paste
>this or type it by hand?
>

Yeha, sorry, I cut and pasted but fumbled.. The ); is not there - the
sequence did get created correctly...

>> CREATE TABLE testschema.industries (
>>     industry_id integer DEFAULT nextv
>> al('"testschema.industries_industry_id_seq"'::text) NOT NULL,
>>     industry character varying(80) NOT NULL,
>>     entered_dt timestamp with time zone,
>>     updated_dt timestamp with time zone
>> );
>>
>> When I try to insert a value into schema.industries it complains about
>> testschema.industries_industry_id_seq not existing, yet I can execute
>> "nextval" against that very schema....  Any idea what might be going
>> wrong here?...
>
>I think you mean that you can execute nextval() against the sequence,
>not the schema.  Anyway, it looks like whatever created the dump
>file incorrectly added double quotes around the sequence name in
>the nextval() expression.  What created that dump?
>

pg_dump created it with the double quotes, I have been modifying the dump
to make it so appropriate things get created inside the schema, so I
added in the testschema. part of it in this example.

- Greg

>--
>Michael Fuhr
>http://www.fuhr.org/~mfuhr/
>



Re: Join between databases or (???)

From
"gnari"
Date:
From: "Net Virtual Mailing Lists" <mailinglists@net-virtual.com>


> See comments below..
>
>
> >> CREATE TABLE testschema.industries (
> >>     industry_id integer DEFAULT nextv
> >> al('"testschema.industries_industry_id_seq"'::text) NOT NULL,
> >> [...]
> >>
> >> When I try to insert a value into schema.industries it complains about
> >> testschema.industries_industry_id_seq not existing, yet I can execute
> >> "nextval" against that very schema....  Any idea what might be going
> >> wrong here?...
> >
>
> pg_dump created it with the double quotes, I have been modifying the dump
> to make it so appropriate things get created inside the schema, so I
> added in the testschema. part of it in this example.

if you put the schema name inside the double quotes, it gets interpreted
as part of the relation name.
try DEFAULT nextval('testschema."industries_industry_id_seq"'::text)
or  DEFAULT nextval('"testschema"."industries_industry_id_seq"'::text)
or  DEFAULT nextval('testschema.industries_industry_id_seq'::text)

gnari



Re: Join between databases or (???)

From
Tom Lane
Date:
"Net Virtual Mailing Lists" <mailinglists@net-virtual.com> writes:
> CREATE TABLE testschema.industries (
> industry_id integer DEFAULT nextval('"testschema.industries_industry_id_seq"'::text) NOT NULL,

> pg_dump created it with the double quotes, I have been modifying the dump
> to make it so appropriate things get created inside the schema, so I
> added in the testschema. part of it in this example.

Ah-hah.  You put the testschema. part in the wrong place then.  Correct
is
    nextval('"testschema"."industries_industry_id_seq"'::text)
Or you could leave out the double quotes.

            regards, tom lane