Re: dbi-link questions + patch - Mailing list pgsql-general

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!