Thread: stored function (possible to access file system or call java program)?
Is it possible within a stored function to:
1) Access the file system (to rename a file for example)
2) Call a java program
thanks
J.V.
On Mon, Oct 3, 2011 at 2:05 PM, J.V. <jvsrvcs@gmail.com> wrote: > Is it possible within a stored function to: > 1) Access the file system (to rename a file for example) > 2) Call a java program > For both of these: 1) yes, if you use an untrusted program and 2) These are dangerous because they are operations that exist outside the transactional control of the RDBMS, so it's usually better to look to other ways of doing this, like NOTIFY and LISTEN..... Best Wishes, Chris Travers
Re: stored function (possible to access file system or call java program)?
From
Merlin Moncure
Date:
On Mon, Oct 3, 2011 at 4:05 PM, J.V. <jvsrvcs@gmail.com> wrote: > Is it possible within a stored function to: > 1) Access the file system (to rename a file for example) > 2) Call a java program sounds like you should be using pl/sh (http://plsh.projects.postgresql.org/) or pl/java (http://pgfoundry.org/projects/pljava/). just remember, try not to get into situation where your function blocks indefinitely waiting for called program to exist. keep it short and sweet, or spawn the program off and collect the response in separate transaction. merlin
On 10/03/11 2:12 PM, Merlin Moncure wrote: > sounds like you should be using pl/sh > (http://plsh.projects.postgresql.org/) or pl/java > (http://pgfoundry.org/projects/pljava/). pljava is a integration nightmare I'd suggest plperlu -- john r pierce N 37, W 122 santa cruz ca mid-left coast
Re: stored function (possible to access file system or call java program)?
From
Merlin Moncure
Date:
On Mon, Oct 3, 2011 at 4:16 PM, John R Pierce <pierce@hogranch.com> wrote: > On 10/03/11 2:12 PM, Merlin Moncure wrote: >> >> sounds like you should be using pl/sh >> (http://plsh.projects.postgresql.org/) or pl/java >> (http://pgfoundry.org/projects/pljava/). > > pljava is a integration nightmare hm -- care to comment in more detail? merlin
On 10/03/11 2:18 PM, Merlin Moncure wrote: > hm -- care to comment in more detail? I've had to custom build it for each platform I've deployed it on, and it then has dependencies on the exact JDK version and location. It currently doesn't work with JDK 6, only 1.5. It also requires custom lines be added to postgresql.conf and environment variables defined by the postgres service startup script. My first attempt to get it working with 64bit CentOS 6, Sun JDK 1.5.0_22, and PG 9.0.5 failed, although I need to start over and make sure I didn't mess up a step. The idea of dragging a whole JVM into the process context of the postgres server also bothers me, but I must say, that once you DO get it working, the developers in another division of $job were able to move some code over from Oracle pljava to Postgres pljava without any modifications at all. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
Re: stored function (possible to access file system or call java program)?
From
Merlin Moncure
Date:
On Mon, Oct 3, 2011 at 4:53 PM, John R Pierce <pierce@hogranch.com> wrote:
> On 10/03/11 2:18 PM, Merlin Moncure wrote:
>>
>> hm -- care to comment in more detail?
>
>
> I've had to custom build it for each platform I've deployed it on, and it
> then has dependencies on the exact JDK version and location. It currently
> doesn't work with JDK 6, only 1.5. It also requires custom lines be added
> to postgresql.conf and environment variables defined by the postgres service
> startup script.
I don't remember if I did anything special to get this running (I
downloaded the stock .so from pgfoundry), but this is working fine for
me:
merlin@mmoncure-ubuntu:~$ ldd /home/merlin/pljava/pljava.so
linux-gate.so.1 => (0x002b2000)
libjvm.so => /usr/lib/libjvm.so (0x00cfc000)
libc.so.6 => /lib/libc.so.6 (0x007db000)
libm.so.6 => /lib/libm.so.6 (0x0075f000)
libdl.so.2 => /lib/libdl.so.2 (0x001b2000)
libpthread.so.0 => /lib/libpthread.so.0 (0x0049a000)
merlin@mmoncure-ubuntu:~$ ldd /usr/lib/libjvm.so
linux-gate.so.1 => (0x0029c000)
libm.so.6 => /lib/libm.so.6 (0x0033b000)
libdl.so.2 => /lib/libdl.so.2 (0x00110000)
libpthread.so.0 => /lib/libpthread.so.0 (0x00114000)
libc.so.6 => /lib/libc.so.6 (0x0012e000)
merlin@mmoncure-ubuntu:~$ ls -l /usr/lib/libjvm.so
lrwxrwxrwx 1 root root 57 2011-08-12 17:13 /usr/lib/libjvm.so ->
/usr/lib/jvm/java-6-openjdk/jre/lib/i386/server/libjvm.so
also postgres startup script etc is normal -- just the custom .conf
entries, etc. basically, everything was smooth as silk. it looks
like a real bear to compile though if anything goes wrong, I'll admit.
> My first attempt to get it working with 64bit CentOS 6, Sun JDK 1.5.0_22,
> and PG 9.0.5 failed, although I need to start over and make sure I didn't
> mess up a step.
that's (sun) probably the issue. everything's easier on linux i guess.
> The idea of dragging a whole JVM into the process context of the postgres
> server also bothers me, but I must say, that once you DO get it working, the
> developers in another division of $job were able to move some code over from
> Oracle pljava to Postgres pljava without any modifications at all.
yeah -- understood, but especially with the new stuff coming down the
pike for java 7 there are definitely advantages for java shops in
going in this direction...I wouldn't go so far as to say 'integration
nightmare' -- maybe 'situationally dependent difficulties' :-).
merlin
On 10/03/11 3:09 PM, Merlin Moncure wrote:
> libjvm.so => /usr/lib/libjvm.so (0x00cfc000)
my libjvm.so is ...
$ ldd /usr/pgsql-9.0/lib/pljava.so
linux-vdso.so.1 => (0x00007fff7bfff000)
libjvm.so =>
/usr/java/jdk1.5.0_22/jre/lib/amd64/server/libjvm.so (0x00007f79d9324000)
libc.so.6 => /lib64/libc.so.6 (0x00007f79d8f98000)
libm.so.6 => /lib64/libm.so.6 (0x00007f79d8d14000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f79d8b10000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f79d88f2000)
/lib64/ld-linux-x86-64.so.2 (0x0000003c5f800000)
$ ldd /usr/java/jdk1.5.0_22/jre/lib/amd64/server/libjvm.so
linux-vdso.so.1 => (0x00007fffcbfff000)
libm.so.6 => /lib64/libm.so.6 (0x00007f73bc596000)
libdl.so.2 => /lib64/libdl.so.2 (0x00007f73bc392000)
libpthread.so.0 => /lib64/libpthread.so.0 (0x00007f73bc174000)
libc.so.6 => /lib64/libc.so.6 (0x00007f73bbdf5000)
/lib64/ld-linux-x86-64.so.2 (0x0000003c5f800000)
I've never heard of a Sun JRE in /usr/lib
are you using (eeew) GCJ ?
--
john r pierce N 37, W 122
santa cruz ca mid-left coast
Re: stored function (possible to access file system or call java program)?
From
Rodrigo Gonzalez
Date:
On 10/03/2011 09:50 PM, John R Pierce wrote: > On 10/03/11 3:09 PM, Merlin Moncure wrote: >> libjvm.so => /usr/lib/libjvm.so (0x00cfc000) > > > I've never heard of a Sun JRE in /usr/lib > > are you using (eeew) GCJ ? > > He is using... merlin@mmoncure-ubuntu:~$ ls -l /usr/lib/libjvm.so lrwxrwxrwx 1 root root 57 2011-08-12 17:13 /usr/lib/libjvm.so -> /usr/lib/jvm/java-6-openjdk/jre/lib/i386/server/libjvm.so So, it is a symlink to openjdk Rodrigo
Re: stored function (possible to access file system or call java program)?
From
Merlin Moncure
Date:
On Mon, Oct 3, 2011 at 8:00 PM, Rodrigo Gonzalez <rjgonzale@estrads.com.ar> wrote: > On 10/03/2011 09:50 PM, John R Pierce wrote: >> >> On 10/03/11 3:09 PM, Merlin Moncure wrote: >>> >>> libjvm.so => /usr/lib/libjvm.so (0x00cfc000) >> >> >> I've never heard of a Sun JRE in /usr/lib >> >> are you using (eeew) GCJ ? >> >> > He is using... > > merlin@mmoncure-ubuntu:~$ ls -l /usr/lib/libjvm.so > lrwxrwxrwx 1 root root 57 2011-08-12 17:13 /usr/lib/libjvm.so -> > /usr/lib/jvm/java-6-openjdk/jre/lib/i386/server/libjvm.so > > So, it is a symlink to openjdk yup merlin@mmoncure-ubuntu:~$ java -version java version "1.6.0_20" OpenJDK Runtime Environment (IcedTea6 1.9.9) (6b20-1.9.9-0ubuntu1~10.10.2) OpenJDK Client VM (build 19.0-b09, mixed mode, sharing) I recall making a symlink now. note I threw this together in a hurry and it's not heavily tested. java isn't really my thing be we do a lot of it at the job, so I'm 'going with the flow'. merlin
On 10/04/11 9:14 AM, Merlin Moncure wrote: > merlin@mmoncure-ubuntu:~$ java -version > java version "1.6.0_20" > OpenJDK Runtime Environment (IcedTea6 1.9.9) (6b20-1.9.9-0ubuntu1~10.10.2) > OpenJDK Client VM (build 19.0-b09, mixed mode, sharing) > I was told pljava flat doesn't work with jdk 1.6 yet. huh. -- john r pierce N 37, W 122 santa cruz ca mid-left coast