Re: 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(at)fetter(dot)org> http://fetter.org/
phone: +1 415 235 3778 AIM: dfetter666
Skype: davidfetter

Remember to vote!