dbi-link questions + patch

On Wed, Sep 27, 2006 at 03:50:46PM +0200, Filip Rembia��kowski wrote:

Hi all :)

first, sorry for crossposting but dbilink mailinglist is extremely low traffic
so I decided to mail this also to pgsql-general

I'm looking for a solution that gives PostgreSQL "remote server" aka "proxy
tables" functionality.

DBI-Link should do that.

We're trying here to evaluate dbi-link, and have some problems.
I got version 1.0.0 from PgFoundry.

First I had to do some fixes to make it install on PostgreSQL 8.1 with Perl
5.8.8 (the patch is below).

but then I also had some problems.

question 1)

-- PREPARATION (stripped output/diagnostic messages):
pgdba=# CREATE DATABASE local;
pgdba=# CREATE DATABASE remote;
pgdba=# \c remote
remote=# CREATE TABLE tab1(id bigserial, data text);
remote=# insert into tab1(data) values('AAA');
remote=# \c local
local=# CREATE LANGUAGE 'plperlu';
local=# \i dbi_link.sql
local=# SELECT dbi_link.make_accessor_functions(
local-# 'dbi:Pg:dbname=remote;host=localhost;port=5810', 'pgdba',
local-# NULL, '{AutoCommit => 1, RaiseError => 1}', 'public', NULL, remote');

-- TEST 1
local=# select * from remote.tab1;
NOTICE: Connected to database
NOTICE: sql is
COMMENT ON COLUMN tab1_rowtype.id IS 23361
bigint
23361

id | data
----+------
1 | AAA
(1 row)
------------- it is OK but... why these NOTICEs? is it normal behaviour?

They're debugging info. You can remove them.

-- TEST 2
local=# insert into remote.tab1(data) values('BBB');
ERROR: error from Perl trigger function: column "ad" does not exist at line 28.
------------- it is definitely not OK. what could be the problem here?

question 2)
why is DBI-Link marked as "stable" on PgFoundry?
if it does not even work out-of-the-box, it is not mature yet.
maybe it should be "beta"?

I think it's bitrotted :P

question 3)
did anyone try to establish DBI-Link between UTF8 encoded database and non-UTF8
encoded database? i'm asking because i got:
utf8db=# select count(*) from remotelatin2db.dict01;
(...)
ERROR: invalid UTF-8 byte sequence detected near byte 0xf3

Nope, but if you have something you can put together as a regression
test, I'd be grateful.

question 4) could anyone please suggest some software that gives similar
functionality (and works)?

That's about what there is. I'm working on 2.0, which has a lot of
improvements including more tests.

Cheers,
D

regards && thanks for your time
Filip

dbi-link-1.0.0 patch begin.

diff -Naur dbi-link-1.0.0/make_connection.sql dbi-link-1.0.0.1/make_connection.sql
--- dbi-link-1.0.0/make_connection.sql  2005-01-26 09:47:11.000000000 +0100
+++ dbi-link-1.0.0.1/make_connection.sql        2006-09-27 13:12:14.000000000 +0200
@@ -36,7 +36,7 @@
, db_password => $db_password
);

-return TRUE;
+return 'TRUE';

sub check_connection {
my %parms = (
diff -Naur dbi-link-1.0.0/remote_query.sql dbi-link-1.0.0.1/remote_query.sql
--- dbi-link-1.0.0/remote_query.sql     2005-01-26 09:47:11.000000000 +0100
+++ dbi-link-1.0.0.1/remote_query.sql   2006-09-27 13:15:12.000000000 +0200
@@ -164,7 +164,7 @@
FROM dbi_link.dbi_connection
WHERE ad = $data_source_id
SQL
-my ($data_source, $user, $auth, $dbh_attr);
+my ($data_source, $user_name, $auth, $dbh_attr);
my $driver_there = spi_exec_query($dtsql);
my $nrows = $driver_there->{processed};
if ($nrows == 0) {
@@ -260,6 +260,8 @@
RETURNS TRIGGER
LANGUAGE plperlu
AS $$
+our %_TD;
+my $user_name;
#####################################################
#                                                   #
# Immediately reject anything that's not an INSERT. #
@@ -371,7 +373,7 @@
INSERT INTO $table (
@{[join("\n, ", sort keys %$new) ]}
) VALUES (
-  @{[join("\n, ", { $new->{$_} } sort keys %$new) ]}
+  @{[join("\n, ", map { $new->{$_} } sort keys %$new) ]}
)
SQL
my $sth = $dbh->prepare($sql);
@@ -379,7 +381,7 @@
}

sub update {
- my $table = $_TD{relname}
+ my $table = $_TD{relname};
my $sql = <<SQL;
UPDATE $table
SET
@@ -400,7 +402,7 @@
}

sub delete {
- my $table = $_TD{relname}
+ my $table = $_TD{relname};
my $sql = <<SQL;
DELETE FROM $table
WHERE

dbi-link-1.0.0 patch end.

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

--
David Fetter <david@fetter.org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!