Обсуждение: Procedural language functions across servers
I know that the FAQ says that the only way to implement a query across databases is to use dblink, is this the only way available if additional procedural languages are installed? For example, assume I have a production server A that does not have PL/Perl installed, and a hacker's server B (let's assume this is a separate machine) complete with every possible bell and whistle. If I want to apply Perl (in a read-only fashion) to extract data from the production tables is the "best" way to have a PL/Perl function including references to dblink functions? Similarly, if I have PostGIS or PL/R on the hacker's server, or- heaven forfend- both, is the best way to get at the production server still to use dblink? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
On 7/9/06, Mark Morgan Lloyd <markMLl.pgsql-general@telemetry.co.uk> wrote: > I know that the FAQ says that the only way to implement a query across databases > is to use dblink, is this the only way available if additional procedural > languages are installed? > > For example, assume I have a production server A that does not have PL/Perl > installed, and a hacker's server B (let's assume this is a separate machine) > complete with every possible bell and whistle. If I want to apply Perl (in a > read-only fashion) to extract data from the production tables is the "best" way > to have a PL/Perl function including references to dblink functions? > > Similarly, if I have PostGIS or PL/R on the hacker's server, or- heaven forfend- > both, is the best way to get at the production server still to use dblink? dblink allows you to send queries from one server to another in a couple of different ways. What the 'client' server has installed is irrelevant...the sql is processed by the 'server' server (in your example, the production server i think). now, you could send the data across via a dblink query/view and pl/perl process it in your developer box. if you have pl/pgsql installed on the production server, I would suggest using that though and just invoking a function call across the dblink ;) merlin
Merlin Moncure wrote: > > > Similarly, if I have PostGIS or PL/R on the hacker's server, or- heaven > > forfend- both, is the best way to get at the production server still to use > > dblink? > > dblink allows you to send queries from one server to another in a > couple of different ways. What the 'client' server has installed is > irrelevant...the sql is processed by the 'server' server (in your > example, the production server i think). > > now, you could send the data across via a dblink query/view and > pl/perl process it in your developer box. if you have pl/pgsql > installed on the production server, I would suggest using that though > and just invoking a function call across the dblink ;) Thanks for that. One of the reasons that I am contemplating this is that when I built the server it wouldn't build PL/Perl since the underlying distro didn't provide a libperl.so file. Now I could obviously recompile the distro's Perl sources but that would mean I'd no longer have a common Perl architecture site wide; I'm considering building a .so on a scratch machine and copying it to the production server but I'm not confident that I understand every possible implication. The other thing that I'm thinking is that it's quite possible that (as hypothetical examples) PL/Perl, PostGIS and PL/R wouldn't be happy on the same machine, at which point the only way to merge their functionality in complex work would be to use a "farm". I hasten to say that I don't anticipate trying that, at least /this/ year, I'm just trying to think ahead :-) -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
On Sun, Jul 09, 2006 at 12:40:56PM +0000, Mark Morgan Lloyd wrote: > I know that the FAQ says that the only way to implement a query > across databases is to use dblink, The FAQ doesn't say dblink is the only way, it says "contrib/dblink allows cross-database queries using function calls." However, the paragraph that says "There is no way to query a database other than the current one" could be misinterpreted to mean dblink is the only way if you read "current one" as "method mentioned in the current FAQ item" rather than as "database to which you are currently connected." http://www.postgresql.org/docs/faqs.FAQ.html#item4.17 dbi-link is an alternative to dblink that uses Perl/DBI: http://pgfoundry.org/projects/dbi-link/ > is this the only way available if additional procedural languages > are installed? With the untrusted version of a language you can do essentially anything that language supports. For example, with plperlu, you could use DBI to open a connection to another database (even another DBMS like Oracle, MySQL, etc.), issue a query, fetch the results, and do whatever you want with those results. Example: CREATE FUNCTION remote_version(text, text, text) RETURNS text AS $$ use DBI; my ($dsn, $user, $pass) = @_; my $dbh = DBI->connect($dsn, $user, $pass); my @row = $dbh->selectrow_array("SELECT version()"); $dbh->disconnect; return $row[0]; $$ LANGUAGE plperlu VOLATILE; SELECT remote_version('dbi:mysql:wopr;host=norad', 'falken', 'joshua'); remote_version ---------------- 5.0.22-log (1 row) -- Michael Fuhr
On Sun, Jul 09, 2006 at 03:00:08PM +0000, Mark Morgan Lloyd wrote: > The other thing that I'm thinking is that it's quite possible that (as > hypothetical examples) PL/Perl, PostGIS and PL/R wouldn't be happy on the same > machine, at which point the only way to merge their functionality in complex > work would be to use a "farm". What sort of "unhapiness" are you thinking is "quite possible"? -- Michael Fuhr
Mark Morgan Lloyd wrote: > Thanks for that. One of the reasons that I am contemplating this is that when I > built the server it wouldn't build PL/Perl since the underlying distro didn't > provide a libperl.so file. Now I could obviously recompile the distro's Perl > sources but that would mean I'd no longer have a common Perl architecture site > wide; I'm considering building a .so on a scratch machine and copying it to the > production server but I'm not confident that I understand every possible > implication. Or maybe you could install the development Perl package, which at least on some distros I know include the libperl.so file you need. If you weren't so misterious about what distro you are using maybe you could even find someone to tell you what is that package called. -- Alvaro Herrera http://www.CommandPrompt.com/ PostgreSQL Replication, Consulting, Custom Development, 24x7 support
> With the untrusted version of a language you can do essentially > anything that language supports. For example, with plperlu, you > could use DBI to open a connection to another database (even another > DBMS like Oracle, MySQL, etc.), issue a query, fetch the results, > and do whatever you want with those results. Thanks Michael, interesting thoughts. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
Michael Fuhr wrote: > > > The other thing that I'm thinking is that it's quite possible that (as > > hypothetical examples) PL/Perl, PostGIS and PL/R wouldn't be happy on the > > same machine, at which point the only way to merge their functionality in > > complex work would be to use a "farm". > > What sort of "unhapiness" are you thinking is "quite possible"? Well I must confess that I was rather looking at worst cases here- constructive pessimism :-) However I think one scenario would be if I were relying on binary packages and found that while most of the ones I wanted were in (say) Debian/stable but one was only in Debian/testing. I'd be reluctant in this case to "upgrade" a development machine from stable to testing, and would either go for a scratch machine or for an image running under User Mode Linux. Historically my preference has usually been to build from source, but even then there are cases where installing some prerequisite implies unwelcome mutilation of a stable machine- not strictly database-related but having to install gd and then finding that requires an upgrade of TrueType springs to mind. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
Alvaro Herrera wrote: > > I'm considering building a .so on a scratch machine and copying it to the > > production server but I'm not confident that I understand every possible > > implication. > > Or maybe you could install the development Perl package, which at least > on some distros I know include the libperl.so file you need. If you > weren't so misterious about what distro you are using maybe you could > even find someone to tell you what is that package called. Slackware 8.1, extensively hacked, kernel and anything that talks to the outside World patched up to date, firewalled to oblivion. In other words if I want something other than what's there already I have to build it myself- there's only the one Perl package, and I checked the build procedure before I raised my head above the parapet. What's more looking at the Perl source (in other words we're getting into Perl territory here, not Slackware) it appears that you can build with or without a .so, but if you have one it's always used and that slows normal Perl operations which is something I can't afford. Granted that this isn't the most recent distro by far, but I've got reasons for sticking with it. Otherwise most desktops here have Debian on them and I agree that having competent binary package management helps, although it's not a universal panacea. -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]
Michael Fuhr wrote: > dbi-link is an alternative to dblink that uses Perl/DBI: > > http://pgfoundry.org/projects/dbi-link/ > > > is this the only way available if additional procedural languages > > are installed? > > With the untrusted version of a language you can do essentially > anything that language supports. For example, with plperlu, you > could use DBI to open a connection to another database (even another > DBMS like Oracle, MySQL, etc.), issue a query, fetch the results, > and do whatever you want with those results. Example: > > CREATE FUNCTION remote_version(text, text, text) RETURNS text AS $$ > use DBI; As a related question, assume I have PostgreSQL on an application server X with functions a() and b() defined to use some method (dblink or whatever) to return a result set from a remote backend server. If X runs a complex query including references to a() and b() which themselves initiate complex queries on backend servers A and B, can I tell X's planner to run a(A) and b(B) simultaneously, or is the only way to have these in distinct sessions storing their results in tables on X? -- Mark Morgan Lloyd markMLl .AT. telemetry.co .DOT. uk [Opinions above are the author's, not those of his employers or colleagues]