I answered my own question--if one upgrades the server, one must also
consider the consequences for the client (DBD::Pg). It had been compiled
using an older library that got removed when I upgraded.
Sean
On 8/24/05 4:31 PM, "Davis, Sean (NIH/NHGRI)" <sdavis2@xxxxxxxxxxxx> wrote:
> I ran across this problem while running a plperlu procedure. I do not have
> a libpq.3.dylib, but do have a libpq.4.dylib. I am running perl 5.8.1,
> macos 10.3.8 on an Xserve G5, and Postgres 8.0.2. Any suggestions? I
> should mention that other db functions seem to work just fine.
>
> From psql:
>
> select
> utilities.make_mysql_idmap('xxxx','xxxx','localhost','xxxx','xxxx','xxxx','i
> dmap','idmap','idmap');
> psql: NOTICE: Connecting....
> psql: server closed the connection unexpectedly
> This probably means the server terminated abnormally
> before or while processing the request.
> psql: connection to server was lost
>
> From my server log:
>
> NOTICE: Connecting....
> dyld: /usr/local/pgsql/bin/postmaster can't open library:
> /usr/local/pgsql/lib/libpq.3.dylib (No such file or directory, errno = 2)
> LOG: server process (PID 826) was terminated by signal 5
> LOG: terminating any other active server processes
> LOG: all server processes terminated; reinitializing
> LOG: database system was interrupted at 2005-08-24 16:24:07 EDT
> LOG: checkpoint record is at 18/A35570C0
> LOG: redo record is at 18/A35570C0; undo record is at 0/0; shutdown TRUE
> LOG: next transaction ID: 44610957; next OID: 473189363
> LOG: database system was not properly shut down; automatic recovery in
> progress
> LOG: record with zero length at 18/A3557100
> LOG: redo is not required
> LOG: database system is ready
>
> And the offending procedure:
>
> create or replace function utilities.make_mysql_idmap(pg_user text,pg_pass
> text,pg_host text,mysql_user text,mysql_pass text,mysql_host text,mysql_db
> text,idmaptable text,query text) returns boolean as $$
> use strict;
> use warnings;
> use DBI;
>
> my
> ($pg_user,$pg_pass,$pg_host,$mysql_user,$mysql_pass,$mysql_host,$mysql_db,$i
> dmaptable,$query) = @_;
> elog NOTICE, "Connecting....";
> my $dbh_pg =
> DBI->connect("dbi:Pg:dbname=annodb4;host=$pg_host",$pg_user,$pg_pass) or
> elog ERROR, 'cannot connect to postgres';
> elog NOTICE, "Connected....";
> my $dbh_mysql =
> DBI->connect("dbi:mysql:database=$mysql_db;host=$mysql_host",$mysql_user,$my
> sql_pass) or elog ERROR, 'cannot connect to mysql';
> elog NOTICE, "Connected....";
>
> $dbh_mysql->do("DROP TABLE $idmaptable");
> elog NOTICE, "Dopped table $idmaptable";
> $dbh_mysql->do(qq/CREATE TABLE $idmaptable (
> id_no autoincrement primary key,
> from_id varchar(15),
> from_value varchar(15),
> to_id varchar(200),
> to_value varchar(15))/);
> elog NOTICE, "Created table $idmaptable";
> my $sth_pg = $dbh_pg->prepare(qq/
> select ug_id,'Hs.data',symbol,'gene' from ug_main where species='Hs'
> union
> select ug_id,'Mm.data',symbol,'gene' from ug_main where species='Mm'
> union
> select ug_id,'Mm.data',description,'title' from ug_main where species='Mm'
> union
> select ug_id,'Hs.data',description,'title' from ug_main where species='Hs'/
> );
> $sth_pg->execute();
> my $sth_mysql = $dbh_mysql->prepare(qq/INSERT into $idmaptable
> (from_id,from_value,to_id,to_value) values (?,?,?,?)/);
> while (my $row = $sth_pg->selectrow_arrayref) {
> $sth_mysql->execute(@{$row});
> }
> $sth_mysql->finish;
> $sth_pg->finish;
> $dbh_mysql->disconnect;
> $dbh_pg->disconnect;
> return 1;
> $$ language plperlu;
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: don't forget to increase your free space map settings
>
---------------------------(end of broadcast)---------------------------
TIP 1: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@xxxxxxxxxxxxxx so that your
message can get through to the mailing list cleanly
