dbi-link questions + patch

[Filip Rembiałkowski <filip.rembialkowski@xxxxx>]

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.

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?

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


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



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




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.