Обсуждение: dblink vs dbi-link (and errors compiling)
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. dbi-link seems like it uses perl's dbi to connect to a remote db. 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. 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
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