dblink vs dbi-link (and errors compiling)

On Sep 6, 2007, at 10:54 PM, Ow Mun Heng wrote:

I'm confused as to the difference between dblink and dbi-link.

dblink is included in the contrib directory and dbi-link is available
from pgfoundry.

dblink seems like it creates a view of a remote DB and is static,
which
means that it needs to be refreshed each time.

Yes. dblink allows you to run queries from one postgres database
against another postgres database.

dbi-link seems like it uses perl's dbi to connect to a remote db.

dbi-link allows you to run queries from one postgres database against
any database that can be accessed via perl's dbi library.

In either of the above, I would like to know which one is able to help
me to like connect to a remote DB, use the table there and join to a
local table in PG so that I don't have to use DBI to pull and insert
those data into the local PG database.

Neither. To the best of my knowledge, there isn't anything that will
allow you to to do that. Select the data you need from the remote
source into a temp table and join against that if you don't want to
keep the data locally after you've used it.

BTW, dblink doesn't compile. (8.2.4)

gcc -O2 -Wall -Wmissing-prototypes -Wpointer-arith -Winline
-Wdeclaration-after-statement -Wendif-labels -fno-strict-aliasing -
fpic
-I../../src/interfaces/libpq -I. -I../../src/include -
D_GNU_SOURCE -c
-o dblink.o dblink.c
dblink.c:56:28: error: utils/fmgroids.h: No such file or directory
dblink.c: In function 'get_pkey_attnames':
dblink.c:1684: error: 'F_OIDEQ' undeclared (first use in this
function)
dblink.c:1684: error: (Each undeclared identifier is reported only
once
dblink.c:1684: error: for each function it appears in.)
make: *** [dblink.o] Error 1

In the src/contrib/dblink/ directory of the source tree you built
postgres from just do

make
make install

Erik Jones

Software Developer | Emma®
erik@myemma.com
800.595.4401 or 615.292.5888
615.292.0777 (fax)

Emma helps organizations everywhere communicate & market in style.
Visit us online at http://www.myemma.com

On Fri, 2007-09-07 at 00:17 -0500, Erik Jones wrote:

On Sep 6, 2007, at 10:54 PM, Ow Mun Heng wrote:

In either of the above, I would like to know which one is able to help
me to like connect to a remote DB, use the table there and join to a
local table in PG so that I don't have to use DBI to pull and insert
those data into the local PG database.

Neither. To the best of my knowledge, there isn't anything that will
allow you to to do that. Select the data you need from the remote
source into a temp table and join against that if you don't want to
keep the data locally after you've used it.

Ah.. Too bad.. There goes my "easy peasy life" out the window. But in
any case, good to know.. dbi-link would be what I would want to try out.

BTW, dblink doesn't compile. (8.2.4)

dblink.c:56:28: error: utils/fmgroids.h: No such file or directory
dblink.c: In function 'get_pkey_attnames':

In the src/contrib/dblink/ directory of the source tree you built
postgres from just do

make
make install

Did that.. ended up with above error. the tablefunc compile went through
though. BTW, this is gentoo and though I compiled it from source, the
compiled tarballs are deleted.

I did a configure in the PG source main dir and then proceeded to do a
make in the contrib/dblink directory with above errors.

Anyhow.. dbi-link is what I'll play with.

Thanks..

Ow Mun Heng wrote:

In either of the above, I would like to know which one
is able to help me to like connect to a remote DB, use the
table there and join to a local table in PG so that I
don't have to use DBI to pull and insert those data into
the local PG database.

Neither. To the best of my knowledge, there isn't anything
that will allow you to to do that.

Ah.. Too bad.. There goes my "easy peasy life" out the window. But in
any case, good to know.. dbi-link would be what I would want
to try out.

True, you'll have to write some stuff that does something like:
- pull the data over from the other database
- replace the data in the local table

But I think that it does not matter if you use dblink or dbi-link
for that.

Since it is between PostgreSQL databases, I would probably prefer
dblink, because dbi-link is designed to work with ANY other database
that supports Perl::DBI and so preserves less of the PostgreSQL-ness
of the data - e.g. data type is lost, all data arrive as text.
Also, dbi-link has a more complicated architecture since it depends
on more things outside of PostgreSQL (naturally).

dbi-link is cool software though :^)

BTW, dblink doesn't compile. (8.2.4)

dblink.c:56:28: error: utils/fmgroids.h: No such file or directory

BTW, this is gentoo and though I compiled it from source, the
compiled tarballs are deleted.

I did a configure in the PG source main dir and then proceeded to do a
make in the contrib/dblink directory with above errors.

The correct way is like this:
cd into the PostgreSQL source directory
./configure with the correct arguments
make -C src/backend ../../src/include/utils/fmgroids.h
make -C contrib/dblink

Yours,
Laurenz Albe

On Fri, 2007-09-07 at 10:16 +0200, Albe Laurenz wrote:

Ow Mun Heng wrote:
True, you'll have to write some stuff that does something like:
- pull the data over from the other database
- replace the data in the local table

Already doing that currently for some data tables/

Since it is between PostgreSQL databases,

actually, it'll be against SQL Servers

preserves less of the PostgreSQL-ness
of the data - e.g. data type is lost, all data arrive as text.

Another thing I didn't know.. thanks again for this heads up..

The correct way is like this:
cd into the PostgreSQL source directory
./configure with the correct arguments
make -C src/backend ../../src/include/utils/fmgroids.h
make -C contrib/dblink

Cool. I'll give it a shot//

Ow Mun Heng wrote:

Since it is between PostgreSQL databases,

actually, it'll be against SQL Servers

Oh, ok, then you will have to use DBI-Link.
dblink is not an option since it only connects to PostgreSQL databases.

Yours,
Laurenz Albe