Обсуждение: a: queries across databases b: plperl

Sirs and Madams:

I have been researching PostgreSQL for some time in anticipation of
migrating my most important application from MSAccess to something
*NIX-based.

I've settled on FreeBSD (love it as a desktop/workstation OS)  and am
using PostgreSQL 7.3.3 (compiled from ports collection).

2 things

A.  I read in one of my PostgreSQL books that you cannot execute queries
across databases.  What I have are several lookup tables that I need to
reference from other databases.  Each project requires its own database,
and I don't want to copy those huge tables to each database I am using.
I also have up to 5 project databases going at any one time.  Did I
understand wrong, or if I understood right, is there a workaround, or am
I stuck using MySQL for this?

B.  I cannot get PostgreSQL to accept plperl as a language.  Here's what
I posted to another forum (for which I did not get a satisfactory
answer).

Compiled PostgreSQL 7.3.3. from ports. I didn't know that shared
libraries were required from Perl to use plperl.

So I recompiled Perl 5.8.0 from ports - amended Makefile to allow
threads and shared libraries
(-Dusethreads=y -Duseithreads=y -Duseshrplib=y) (I enabled threads
because I needed them to try something in MySQL)

Then I compiled plperl from ports.

I logged in as root and su to pgsql (postgres user) and type:

CREATE FUNCTION plperl_call_handler() RETURNS opaque AS
'/usr/local/lib/postgresql/plperl.so' LANGUAGE 'C';

This didn't work. I get back <deletia> Undefined symbol
"Perl_sv_2pv_flags".

I ran make again and sent the output to a text file. I checked the
output and it was using the aforementioned version of perl.

Any help is greatly appreciated

--
         Wayne M. Syvinski - matthew@techcelsior.com
     FreeBSD:  A Declaration of Independence from Windows
           There ain't a horse that can't be rode,
        and there ain't a rider that can't be throwed.

Am Son, 2003-08-17 um 22.10 schrieb Wayne M. Syvinski:

> A.  I read in one of my PostgreSQL books that you cannot execute queries
> across databases.  What I have are several lookup tables that I need to

If you really _have_ to query across databases, you could use dblink
(it's in the contribs).

If the databases are on the same machine, you should merge them together
in one big databse.
With SCHEMA you can have multiple 'virtual' databases in one db.
All have their own namespace, so every SCHEMA can have tables with the
same tablenames as other SCHEMA.
With SCHEMA you can have REFERENTIAL INTEGRITY across the 'virtual
databases' and other goodies :)

> Any help is greatly appreciated

done

--
 e-Trolley Sayegh & John, Nabil Sayegh
 Tel.: 0700 etrolley /// 0700 38765539
 Fax.: +49 69 8299381-8
 PGP : http://www.e-trolley.de

"Wayne M. Syvinski" <matthew@techcelsior.com> writes:
> Compiled PostgreSQL 7.3.3. from ports. I didn't know that shared
> libraries were required from Perl to use plperl.

> So I recompiled Perl 5.8.0 from ports - amended Makefile to allow
> threads and shared libraries
> (-Dusethreads=y -Duseithreads=y -Duseshrplib=y) (I enabled threads
> because I needed them to try something in MySQL)

> Then I compiled plperl from ports.

> I logged in as root and su to pgsql (postgres user) and type:

> CREATE FUNCTION plperl_call_handler() RETURNS opaque AS
> '/usr/local/lib/postgresql/plperl.so' LANGUAGE 'C';

> This didn't work. I get back <deletia> Undefined symbol
> "Perl_sv_2pv_flags".

I'm wondering whether you compiled plperl against the Perl include files
that match the Perl library you were using.  This is an easy mistake to
make if you build/install Perl in a nonstandard place (and "the standard
place" isn't very well-defined, either).  Check the -I arguments that
were used at compile time for plperl, and check the dynamic link search
path used at runtime.

            regards, tom lane

I did make deinstall, make clean, then make and captured output to
file.  createlang still bombed.  Here's the relevant lines.

Thanks again,
Wayne

===>  Building for p5-postgresql-plperl-7.3.3
gmake: Entering directory
`/usr/ports/databases/p5-postgresql-plperl/work/postgresql-7.3.3/src/pl/plperl'
cc -O -pipe -fpic -DPIC -I. -I/usr/local/lib/perl5/5.8.0/mach/CORE
-I../../../src/include   -c -o plperl.o plperl.c
cc -O -pipe -fpic -DPIC -I. -I/usr/local/lib/perl5/5.8.0/mach/CORE
-I../../../src/include   -c -o eloglvl.o eloglvl.c
/usr/bin/perl /usr/local/lib/perl5/5.8.0/ExtUtils/xsubpp -typemap
/usr/local/lib/perl5/5.8.0/ExtUtils/typemap SPI.xs >SPI.c
cc -O -pipe -fpic -DPIC -I. -I/usr/local/lib/perl5/5.8.0/mach/CORE
-I../../../src/include   -c -o SPI.o SPI.c
ar cr libplperl.a `lorder plperl.o eloglvl.o SPI.o | tsort`
ranlib libplperl.a
cc -O -pipe -fpic -DPIC -shared -Wl,-x,-soname,libplperl.so.0 plperl.o
eloglvl.o SPI.o  -pthread -Wl,-E  -L/usr/local/lib
/usr/local/lib/perl5/5.8.0/mach/auto/DynaLoader/DynaLoader.a
-L/usr/local/lib/perl5/5.8.0/mach/CORE -lperl -lm -lc_r -lcrypt -lutil
-R/usr/local/lib -o libplperl.so.0
rm -f libplperl.so
ln -s libplperl.so.0 libplperl.so
gmake: Leaving directory
`/usr/ports/databases/p5-postgresql-plperl/work/postgresql-7.3.3/src/pl/plperl'

On Sun, 2003-08-17 at 18:59, Tom Lane wrote:
> "Wayne M. Syvinski" <matthew@techcelsior.com> writes:
> > Compiled PostgreSQL 7.3.3. from ports. I didn't know that shared
> > libraries were required from Perl to use plperl.
>
> > So I recompiled Perl 5.8.0 from ports - amended Makefile to allow
> > threads and shared libraries
> > (-Dusethreads=y -Duseithreads=y -Duseshrplib=y) (I enabled threads
> > because I needed them to try something in MySQL)
>
> > Then I compiled plperl from ports.
>
> > I logged in as root and su to pgsql (postgres user) and type:
>
> > CREATE FUNCTION plperl_call_handler() RETURNS opaque AS
> > '/usr/local/lib/postgresql/plperl.so' LANGUAGE 'C';
>
> > This didn't work. I get back <deletia> Undefined symbol
> > "Perl_sv_2pv_flags".
>
> I'm wondering whether you compiled plperl against the Perl include files
> that match the Perl library you were using.  This is an easy mistake to
> make if you build/install Perl in a nonstandard place (and "the standard
> place" isn't very well-defined, either).  Check the -I arguments that
> were used at compile time for plperl, and check the dynamic link search
> path used at runtime.
>
>             regards, tom lane
>
>

"Wayne M. Syvinski" <matthew@techcelsior.com> writes:
> I did make deinstall, make clean, then make and captured output to
> file.  createlang still bombed.  Here's the relevant lines.

The make output looks reasonable, but is libperl.so being fetched from
/usr/local/lib/perl5/5.8.0/mach/CORE at runtime?  Or somewhere else?
If you didn't teach ldcondig about that directory, "somewhere else"
is quite a likely answer.

If the error message shown at runtime doesn't tell you, it might help to
look in the postmaster's log (stderr output).  Dynamic link code often
spits messages to stderr ...

            regards, tom lane

Well, I need something like this too, however I had just been planning to
make a 2nd connection to the other database and shuffle a little amount
(<1kB) of data between the two that way.

Anyone know if there is a way to assign an element to a schema AFTER
creation?
I was thinking "INSERT INTO SCHEMA schema {TABLE | FUNCTION | etc.} element"
would be most logical...

> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Wayne M. Syvinski
> Sent: Sunday, August 17, 2003 4:11 PM
> To: pgsql-novice@postgresql.org
> Subject: [NOVICE] a: queries across databases b: plperl
>
>
> Sirs and Madams:
>
> I have been researching PostgreSQL for some time in anticipation of
> migrating my most important application from MSAccess to something
> *NIX-based.
>
> I've settled on FreeBSD (love it as a desktop/workstation OS)  and am
> using PostgreSQL 7.3.3 (compiled from ports collection).
>
> 2 things
>
> A.  I read in one of my PostgreSQL books that you cannot execute queries
> across databases.  What I have are several lookup tables that I need to
> reference from other databases.  Each project requires its own database,
> and I don't want to copy those huge tables to each database I am using.
> I also have up to 5 project databases going at any one time.  Did I
> understand wrong, or if I understood right, is there a workaround, or am
> I stuck using MySQL for this?
>
> B.  I cannot get PostgreSQL to accept plperl as a language.  Here's what
> I posted to another forum (for which I did not get a satisfactory
> answer).
>
> Compiled PostgreSQL 7.3.3. from ports. I didn't know that shared
> libraries were required from Perl to use plperl.
>
> So I recompiled Perl 5.8.0 from ports - amended Makefile to allow
> threads and shared libraries
> (-Dusethreads=y -Duseithreads=y -Duseshrplib=y) (I enabled threads
> because I needed them to try something in MySQL)
>
> Then I compiled plperl from ports.
>
> I logged in as root and su to pgsql (postgres user) and type:
>
> CREATE FUNCTION plperl_call_handler() RETURNS opaque AS
> '/usr/local/lib/postgresql/plperl.so' LANGUAGE 'C';
>
> This didn't work. I get back <deletia> Undefined symbol
> "Perl_sv_2pv_flags".
>
> I ran make again and sent the output to a text file. I checked the
> output and it was using the aforementioned version of perl.
>
> Any help is greatly appreciated
>
> --
>          Wayne M. Syvinski - matthew@techcelsior.com
>      FreeBSD:  A Declaration of Independence from Windows
>            There ain't a horse that can't be rode,
>         and there ain't a rider that can't be throwed.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 7: don't forget to increase your free space map settings
>

Jason Hihn <jhihn@paytimepayroll.com> writes:
> Anyone know if there is a way to assign an element to a schema AFTER
> creation?

You mean as in "CREATE TABLE schema.table (..." ?

            regards, tom lane

No, that's during creation. I mean after it's been created. Say I create my
tables, create a schema, then want to MOVE the tables to that schema.

There should also be a way to remove elements from a schema too, to outside
any schemas.

The three schema moves for existing tables that I am thinking about:
table1 -> schema1.table1
schema1.table1 -> table1
schema1.table1 -> schema2.table1

> -----Original Message-----
> From: pgsql-novice-owner@postgresql.org
> [mailto:pgsql-novice-owner@postgresql.org]On Behalf Of Tom Lane
> Sent: Monday, August 18, 2003 9:48 AM
> To: Jason Hihn
> Cc: Wayne M. Syvinski; pgsql-novice@postgresql.org
> Subject: Re: [NOVICE] a: queries across databases b: plperl
>
>
> Jason Hihn <jhihn@paytimepayroll.com> writes:
> > Anyone know if there is a way to assign an element to a schema AFTER
> > creation?
>
> You mean as in "CREATE TABLE schema.table (..." ?
>
>             regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster
>

"Wayne M. Syvinski" <matthew@techcelsior.com> writes:
> I read the ldconfig manpage, but I'm not sure what to do next (I'm
> really new to FreeBSD administration).

On Linux you'd add the directory /usr/local/lib/perl5/5.8.0/mach/CORE to
the list in ldconfig's configuration file (/etc/somethingorother) and
then re-run ldconfig.  Not sure if this applies to FreeBSD, though if
you have ldconfig at all it's a good bet ...

            regards, tom lane

I ran ldconfig -r | grep perl and got:

50:-lperl.3 => /usr/lib/libperl.so.3

So, as I understand it, I'm NOT getting the correct perl library.

And as far as the logs, I can't find the log you're talking about (I
checked Google and I have 4 books on PostgreSQL, and I still can't buy a
clue).

I read the ldconfig manpage, but I'm not sure what to do next (I'm
really new to FreeBSD administration).

Thanks for your help (as I inch closer to a solution),

Wayne Syvinski

On Mon, 2003-08-18 at 07:43, Tom Lane wrote:
> "Wayne M. Syvinski" <matthew@techcelsior.com> writes:
> > I did make deinstall, make clean, then make and captured output to
> > file.  createlang still bombed.  Here's the relevant lines.
>
> The make output looks reasonable, but is libperl.so being fetched from
> /usr/local/lib/perl5/5.8.0/mach/CORE at runtime?  Or somewhere else?
> If you didn't teach ldcondig about that directory, "somewhere else"
> is quite a likely answer.
>
> If the error message shown at runtime doesn't tell you, it might help to
> look in the postmaster's log (stderr output).  Dynamic link code often
> spits messages to stderr ...
>
>             regards, tom lane
>
>