pg.ddx.io  pgsql-sql@postgresql.org mailing list archive  
help / color / mirror / Atom feed
Calling Java from psql (was Re: requesting help)
12+ messages / 7 participants
[nested] [flat]

* Calling Java from psql (was Re: requesting help)
@ 2001-03-26 08:33 Mathijs Brands <mathijs@ilse.nl>
  2001-03-26 17:00 ` Re: Calling Java from psql (was Re: requesting help) Peter Eisentraut <peter_e@gmx.net>
  2001-03-29 09:09 ` Re: Calling Java from psql (was Re: requesting help) Peter Mount <peter@retep.org.uk>
  0 siblings, 2 replies; 12+ messages in thread

From: Mathijs Brands @ 2001-03-26 08:33 UTC (permalink / raw)
  To: pgsql-sql

On Mon, Mar 26, 2001 at 09:53:52AM +0200, Poul L. Christiansen allegedly wrote:
> Log into PostgreSQL using psql and run the help command:
> "\h create trigger".
> 
> See also:
> http://www.postgresql.org/users-lounge/docs/6.5/postgres/sql-createtrigger.htm
> 
> HTH,
> Poul L. Christiansen
> 
> On Sun, 25 Mar 2001, [iso-8859-1] Mohamed ebrahim wrote:
> 
> > Hi
> >     I am an user of postgresql. I want to do some
> > manipulation on table and i want to do some java stuff
> > after inserting a row into table(i.e like sending
> > email). I want to fire the trigger to do this after
> > the insertion took place. I know how to insert into
> > the table but i want to know that it is possible to
> > java stuff after inserting rows each time. Please help
> > me in this stuff. I will be thankful to you.
> > 
> > Thank you
> > Ebrahim

Has anybody ever tried calling Java code from a pgsql trigger written
in C? Shouldn't this be possible using JNI?

I'm not exactly a Java expert myself, but this is the way PHP allows
you to call Java code from your PHP scripts.

Mathijs
-- 
It's not that perl programmers are idiots, it's that the language
rewards idiotic behavior in a way that no other language or tool has
ever done.
                                                    Erik Naggum



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Calling Java from psql (was Re: requesting help)
  2001-03-26 08:33 Calling Java from psql (was Re: requesting help) Mathijs Brands <mathijs@ilse.nl>
@ 2001-03-26 17:00 ` Peter Eisentraut <peter_e@gmx.net>
  2001-03-26 19:37   ` Re: Calling Java from psql (was Re: requesting help) Mathijs Brands <mathijs@ilse.nl>
  2001-03-29 09:10   ` Re: Calling Java from psql (was Re: requesting help) Peter Mount <peter@retep.org.uk>
  1 sibling, 2 replies; 12+ messages in thread

From: Peter Eisentraut @ 2001-03-26 17:00 UTC (permalink / raw)
  To: Mathijs Brands <mathijs@ilse.nl>; +Cc: pgsql-sql

Mathijs Brands writes:

> Has anybody ever tried calling Java code from a pgsql trigger written
> in C? Shouldn't this be possible using JNI?

I have, and given the current Java implementations it's a desaster.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Calling Java from psql (was Re: requesting help)
  2001-03-26 08:33 Calling Java from psql (was Re: requesting help) Mathijs Brands <mathijs@ilse.nl>
  2001-03-26 17:00 ` Re: Calling Java from psql (was Re: requesting help) Peter Eisentraut <peter_e@gmx.net>
@ 2001-03-26 19:37   ` Mathijs Brands <mathijs@ilse.nl>
  1 sibling, 0 replies; 12+ messages in thread

From: Mathijs Brands @ 2001-03-26 19:37 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql

On Mon, Mar 26, 2001 at 07:00:43PM +0200, Peter Eisentraut allegedly wrote:
> Mathijs Brands writes:
> 
> > Has anybody ever tried calling Java code from a pgsql trigger written
> > in C? Shouldn't this be possible using JNI?
> 
> I have, and given the current Java implementations it's a desaster.

That bad eh? Well, I must admit I couldn't get the PHP-Java coupling to
work stable either :(

Mathijs
-- 
It's not that perl programmers are idiots, it's that the language
rewards idiotic behavior in a way that no other language or tool has
ever done.
                                                    Erik Naggum



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Calling Java from psql (was Re: requesting help)
  2001-03-26 08:33 Calling Java from psql (was Re: requesting help) Mathijs Brands <mathijs@ilse.nl>
  2001-03-26 17:00 ` Re: Calling Java from psql (was Re: requesting help) Peter Eisentraut <peter_e@gmx.net>
@ 2001-03-29 09:10   ` Peter Mount <peter@retep.org.uk>
  2001-03-29 13:46     ` Re: Calling Java from psql (was Re: requesting help) A James Lewis <james@fsck.co.uk>
  2001-03-29 14:05     ` Re: Calling Java from psql (was Re: requesting help) Mathijs Brands <mathijs@ilse.nl>
  1 sibling, 2 replies; 12+ messages in thread

From: Peter Mount @ 2001-03-29 09:10 UTC (permalink / raw)
  To: Mathijs Brands <mathijs@ilse.nl>; Peter Eisentraut <peter_e@gmx.net>; +Cc: pgsql-sql

At 21:37 26/03/01 +0200, Mathijs Brands wrote:
>On Mon, Mar 26, 2001 at 07:00:43PM +0200, Peter Eisentraut allegedly wrote:
> > Mathijs Brands writes:
> >
> > > Has anybody ever tried calling Java code from a pgsql trigger written
> > > in C? Shouldn't this be possible using JNI?
> >
> > I have, and given the current Java implementations it's a desaster.
>
>That bad eh? Well, I must admit I couldn't get the PHP-Java coupling to
>work stable either :(

Not having looked at the PHP-Java link, are they working as one process or 
is it some IPC type link?

PeterM




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Calling Java from psql (was Re: requesting help)
  2001-03-26 08:33 Calling Java from psql (was Re: requesting help) Mathijs Brands <mathijs@ilse.nl>
  2001-03-26 17:00 ` Re: Calling Java from psql (was Re: requesting help) Peter Eisentraut <peter_e@gmx.net>
  2001-03-29 09:10   ` Re: Calling Java from psql (was Re: requesting help) Peter Mount <peter@retep.org.uk>
@ 2001-03-29 13:46     ` A James Lewis <james@fsck.co.uk>
  2001-03-29 16:59       ` Re: Calling Java from psql (was Re: requesting help) Peter Eisentraut <peter_e@gmx.net>
  2001-04-02 13:17       ` Re: Calling Java from psql (was Re: requesting help) Peter T Mount <peter@retep.org.uk>
  1 sibling, 2 replies; 12+ messages in thread

From: A James Lewis @ 2001-03-29 13:46 UTC (permalink / raw)
  To: Peter Mount <peter@retep.org.uk>; +Cc: Mathijs Brands <mathijs@ilse.nl>; Peter Eisentraut <peter_e@gmx.net>; pgsql-sql


Before I go investigating this, is it possible to trigger an arbitrary
program from the SQL, say a shell script?

Also, why am I getting "not subscribed messages", I am subscribed since
I'm replying to a message that was sent to me!!

On Thu, 29 Mar 2001, Peter Mount wrote:

> At 21:37 26/03/01 +0200, Mathijs Brands wrote:
> >On Mon, Mar 26, 2001 at 07:00:43PM +0200, Peter Eisentraut allegedly wrote:
> > > Mathijs Brands writes:
> > >
> > > > Has anybody ever tried calling Java code from a pgsql trigger written
> > > > in C? Shouldn't this be possible using JNI?
> > >
> > > I have, and given the current Java implementations it's a desaster.
> >
> >That bad eh? Well, I must admit I couldn't get the PHP-Java coupling to
> >work stable either :(
>
> Not having looked at the PHP-Java link, are they working as one process or
> is it some IPC type link?
>
> PeterM
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly
>

A. James Lewis (james@fsck.co.uk)
If your OS needs a virus detector... RUN!!!
...Out and get Linux!




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Calling Java from psql (was Re: requesting help)
  2001-03-26 08:33 Calling Java from psql (was Re: requesting help) Mathijs Brands <mathijs@ilse.nl>
  2001-03-26 17:00 ` Re: Calling Java from psql (was Re: requesting help) Peter Eisentraut <peter_e@gmx.net>
  2001-03-29 09:10   ` Re: Calling Java from psql (was Re: requesting help) Peter Mount <peter@retep.org.uk>
  2001-03-29 13:46     ` Re: Calling Java from psql (was Re: requesting help) A James Lewis <james@fsck.co.uk>
@ 2001-03-29 16:59       ` Peter Eisentraut <peter_e@gmx.net>
  2001-03-29 17:13         ` Re: Calling Java from psql (was Re: requesting help) Tom Lane <tgl@sss.pgh.pa.us>
  1 sibling, 1 reply; 12+ messages in thread

From: Peter Eisentraut @ 2001-03-29 16:59 UTC (permalink / raw)
  To: A James Lewis <james@fsck.co.uk>; +Cc: Peter Mount <peter@retep.org.uk>; Mathijs Brands <mathijs@ilse.nl>; pgsql-sql

A James Lewis writes:

> Before I go investigating this, is it possible to trigger an arbitrary
> program from the SQL, say a shell script?

At the lowest level, you can generally do anything a C program can do.
Writing the equivalent of system() in SQL should be rather trivial.

You can also take a look at my little toy project PL/sh:

http://www.postgresql.org/~petere/plsh.html

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Calling Java from psql (was Re: requesting help)
  2001-03-26 08:33 Calling Java from psql (was Re: requesting help) Mathijs Brands <mathijs@ilse.nl>
  2001-03-26 17:00 ` Re: Calling Java from psql (was Re: requesting help) Peter Eisentraut <peter_e@gmx.net>
  2001-03-29 09:10   ` Re: Calling Java from psql (was Re: requesting help) Peter Mount <peter@retep.org.uk>
  2001-03-29 13:46     ` Re: Calling Java from psql (was Re: requesting help) A James Lewis <james@fsck.co.uk>
  2001-03-29 16:59       ` Re: Calling Java from psql (was Re: requesting help) Peter Eisentraut <peter_e@gmx.net>
@ 2001-03-29 17:13         ` Tom Lane <tgl@sss.pgh.pa.us>
  2001-03-29 19:03           ` Re: Calling Java from psql (was Re: requesting help) Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 1 reply; 12+ messages in thread

From: Tom Lane @ 2001-03-29 17:13 UTC (permalink / raw)
  To: Peter Eisentraut <peter_e@gmx.net>; +Cc: A James Lewis <james@fsck.co.uk>; Peter Mount <peter@retep.org.uk>; Mathijs Brands <mathijs@ilse.nl>; pgsql-sql

Peter Eisentraut <peter_e@gmx.net> writes:
> A James Lewis writes:
>> Before I go investigating this, is it possible to trigger an arbitrary
>> program from the SQL, say a shell script?

> At the lowest level, you can generally do anything a C program can do.
> Writing the equivalent of system() in SQL should be rather trivial.

I'm generally pretty suspicious of any system design that requires
calling outside programs from an SQL function.  The problem is that
this fundamentally breaks transactional semantics: if the transaction
is rolled back after the function call, its effects inside the database
disappear ... but there's no way to roll back whatever the outside
program did.  Now you have a consistency problem.

I'd suggest thinking hard about how to restructure your system design
so that you do not need this capability.  Yes, it'd be easy to implement,
but that doesn't make it a good idea.

			regards, tom lane



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Calling Java from psql (was Re: requesting help)
  2001-03-26 08:33 Calling Java from psql (was Re: requesting help) Mathijs Brands <mathijs@ilse.nl>
  2001-03-26 17:00 ` Re: Calling Java from psql (was Re: requesting help) Peter Eisentraut <peter_e@gmx.net>
  2001-03-29 09:10   ` Re: Calling Java from psql (was Re: requesting help) Peter Mount <peter@retep.org.uk>
  2001-03-29 13:46     ` Re: Calling Java from psql (was Re: requesting help) A James Lewis <james@fsck.co.uk>
  2001-03-29 16:59       ` Re: Calling Java from psql (was Re: requesting help) Peter Eisentraut <peter_e@gmx.net>
  2001-03-29 17:13         ` Re: Calling Java from psql (was Re: requesting help) Tom Lane <tgl@sss.pgh.pa.us>
@ 2001-03-29 19:03           ` Peter Eisentraut <peter_e@gmx.net>
  0 siblings, 0 replies; 12+ messages in thread

From: Peter Eisentraut @ 2001-03-29 19:03 UTC (permalink / raw)
  To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: A James Lewis <james@fsck.co.uk>; Peter Mount <peter@retep.org.uk>; Mathijs Brands <mathijs@ilse.nl>; pgsql-sql

Tom Lane writes:

> I'm generally pretty suspicious of any system design that requires
> calling outside programs from an SQL function.  The problem is that
> this fundamentally breaks transactional semantics: if the transaction
> is rolled back after the function call, its effects inside the database
> disappear ... but there's no way to roll back whatever the outside
> program did.  Now you have a consistency problem.

The trick here is to organize your outside programs into fairly atomic
chunks and do conservative error logging.  The need to synchronize the
non-database world with the database is definitely real.  But usually a
regular function call near the end of the transaction block is much more
appropriate than a trigger function.

-- 
Peter Eisentraut      peter_e@gmx.net       http://yi.org/peter-e/




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Calling Java from psql (was Re: requesting help)
  2001-03-26 08:33 Calling Java from psql (was Re: requesting help) Mathijs Brands <mathijs@ilse.nl>
  2001-03-26 17:00 ` Re: Calling Java from psql (was Re: requesting help) Peter Eisentraut <peter_e@gmx.net>
  2001-03-29 09:10   ` Re: Calling Java from psql (was Re: requesting help) Peter Mount <peter@retep.org.uk>
  2001-03-29 13:46     ` Re: Calling Java from psql (was Re: requesting help) A James Lewis <james@fsck.co.uk>
@ 2001-04-02 13:17       ` Peter T Mount <peter@retep.org.uk>
  1 sibling, 0 replies; 12+ messages in thread

From: Peter T Mount @ 2001-04-02 13:17 UTC (permalink / raw)
  To: A James Lewis <james@fsck.co.uk>; +Cc: Peter Mount <peter@retep.org.uk>; Mathijs Brands <mathijs@ilse.nl>; Peter Eisentraut <peter_e@gmx.net>; pgsql-sql

Quoting A James Lewis <james@fsck.co.uk>:

> 
> Before I go investigating this, is it possible to trigger an arbitrary
> program from the SQL, say a shell script?

In theory yes, but I'd suspect not a good idea.

Even more so with java, as the JVM's startup time is pretty big. Having it talk 
to an already running JVM is the best option.

> Also, why am I getting "not subscribed messages", I am subscribed since
> I'm replying to a message that was sent to me!!

I'm getting that with the patches list as well.

Peter

> On Thu, 29 Mar 2001, Peter Mount wrote:
> 
> > At 21:37 26/03/01 +0200, Mathijs Brands wrote:
> > >On Mon, Mar 26, 2001 at 07:00:43PM +0200, Peter Eisentraut allegedly
> wrote:
> > > > Mathijs Brands writes:
> > > >
> > > > > Has anybody ever tried calling Java code from a pgsql trigger
> written
> > > > > in C? Shouldn't this be possible using JNI?
> > > >
> > > > I have, and given the current Java implementations it's a
> desaster.
> > >
> > >That bad eh? Well, I must admit I couldn't get the PHP-Java coupling
> to
> > >work stable either :(
> >
> > Not having looked at the PHP-Java link, are they working as one
> process or
> > is it some IPC type link?
> >
> > PeterM
> >
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 3: if posting/reading through Usenet, please send an appropriate
> > subscribe-nomail command to majordomo@postgresql.org so that your
> > message can get through to the mailing list cleanly
> >
> 
> A. James Lewis (james@fsck.co.uk)
> If your OS needs a virus detector... RUN!!!
> ...Out and get Linux!
> 
> 



-- 
Peter Mount peter@retep.org.uk
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Calling Java from psql (was Re: requesting help)
  2001-03-26 08:33 Calling Java from psql (was Re: requesting help) Mathijs Brands <mathijs@ilse.nl>
  2001-03-26 17:00 ` Re: Calling Java from psql (was Re: requesting help) Peter Eisentraut <peter_e@gmx.net>
  2001-03-29 09:10   ` Re: Calling Java from psql (was Re: requesting help) Peter Mount <peter@retep.org.uk>
@ 2001-03-29 14:05     ` Mathijs Brands <mathijs@ilse.nl>
  1 sibling, 0 replies; 12+ messages in thread

From: Mathijs Brands @ 2001-03-29 14:05 UTC (permalink / raw)
  To: Peter Mount <peter@retep.org.uk>; +Cc: Peter Eisentraut <peter_e@gmx.net>; pgsql-sql

On Thu, Mar 29, 2001 at 10:10:44AM +0100, Peter Mount allegedly wrote:
> At 21:37 26/03/01 +0200, Mathijs Brands wrote:
> >On Mon, Mar 26, 2001 at 07:00:43PM +0200, Peter Eisentraut allegedly wrote:
> > > Mathijs Brands writes:
> > >
> > > > Has anybody ever tried calling Java code from a pgsql trigger written
> > > > in C? Shouldn't this be possible using JNI?
> > >
> > > I have, and given the current Java implementations it's a desaster.
> >
> >That bad eh? Well, I must admit I couldn't get the PHP-Java coupling to
> >work stable either :(
> 
> Not having looked at the PHP-Java link, are they working as one process or 
> is it some IPC type link?

They run a seperate JVM and connect to it. At least that what I gathered.

Regards,

Mathijs
-- 
It's not that perl programmers are idiots, it's that the language
rewards idiotic behavior in a way that no other language or tool has
ever done.
                                                    Erik Naggum



^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Calling Java from psql (was Re: requesting help)
  2001-03-26 08:33 Calling Java from psql (was Re: requesting help) Mathijs Brands <mathijs@ilse.nl>
@ 2001-03-29 09:09 ` Peter Mount <peter@retep.org.uk>
  2001-03-29 15:54   ` Re: Calling Java from psql (was Re: requesting help) clayton cottingham <drfrog@smartt.com>
  1 sibling, 1 reply; 12+ messages in thread

From: Peter Mount @ 2001-03-29 09:09 UTC (permalink / raw)
  To: Mathijs Brands <mathijs@ilse.nl>; pgsql-sql

At 10:33 26/03/01 +0200, Mathijs Brands wrote:

>Has anybody ever tried calling Java code from a pgsql trigger written
>in C? Shouldn't this be possible using JNI?

This was discussed recently.


>I'm not exactly a Java expert myself, but this is the way PHP allows
>you to call Java code from your PHP scripts.

It may be something to look at when things quieten down a bit. I'm rushed 
of my feet at the moment (busy at work, long hours etc) but once 7.1 is 
finally out of the door, I may get the chance to start looking at JNI (& my 
employers don't mind me spending some time working on postgres when I'm not 
on other projects either which is nice ;-) )

PS: the JDBC list is the best place for java based emails - I rarely read 
sql ;-)

Peter




^ permalink  raw  reply  [nested|flat] 12+ messages in thread

* Re: Calling Java from psql (was Re: requesting help)
  2001-03-26 08:33 Calling Java from psql (was Re: requesting help) Mathijs Brands <mathijs@ilse.nl>
  2001-03-29 09:09 ` Re: Calling Java from psql (was Re: requesting help) Peter Mount <peter@retep.org.uk>
@ 2001-03-29 15:54   ` clayton cottingham <drfrog@smartt.com>
  0 siblings, 0 replies; 12+ messages in thread

From: clayton cottingham @ 2001-03-29 15:54 UTC (permalink / raw)
  To: Peter Mount <peter@retep.org.uk>; +Cc: Mathijs Brands <mathijs@ilse.nl>; pgsql-sql

Peter Mount wrote:
> 
> At 10:33 26/03/01 +0200, Mathijs Brands wrote:
> 
> >Has anybody ever tried calling Java code from a pgsql trigger written
> >in C? Shouldn't this be possible using JNI?
> 
> This was discussed recently.
> 
> >I'm not exactly a Java expert myself, but this is the way PHP allows
> >you to call Java code from your PHP scripts.
> 
> It may be something to look at when things quieten down a bit. I'm rushed
> of my feet at the moment (busy at work, long hours etc) but once 7.1 is
> finally out of the door, I may get the chance to start looking at JNI (& my
> employers don't mind me spending some time working on postgres when I'm not
> on other projects either which is nice ;-) )
> 
> PS: the JDBC list is the best place for java based emails - I rarely read
> sql ;-)
> 
> Peter
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Don't 'kill -9' the postmaster



another possible angle is to use perl and its new inline module
{not sure if java is fully supported yet...}
or its other perl->java binding

it should be avail a t cpan



^ permalink  raw  reply  [nested|flat] 12+ messages in thread


end of thread, other threads:[~2001-04-02 13:17 UTC | newest]

Thread overview: 12+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2001-03-26 08:33 Calling Java from psql (was Re: requesting help) Mathijs Brands <mathijs@ilse.nl>
2001-03-26 17:00 ` Peter Eisentraut <peter_e@gmx.net>
2001-03-26 19:37   ` Mathijs Brands <mathijs@ilse.nl>
2001-03-29 09:10   ` Peter Mount <peter@retep.org.uk>
2001-03-29 13:46     ` A James Lewis <james@fsck.co.uk>
2001-03-29 16:59       ` Peter Eisentraut <peter_e@gmx.net>
2001-03-29 17:13         ` Tom Lane <tgl@sss.pgh.pa.us>
2001-03-29 19:03           ` Peter Eisentraut <peter_e@gmx.net>
2001-04-02 13:17       ` Peter T Mount <peter@retep.org.uk>
2001-03-29 14:05     ` Mathijs Brands <mathijs@ilse.nl>
2001-03-29 09:09 ` Peter Mount <peter@retep.org.uk>
2001-03-29 15:54   ` clayton cottingham <drfrog@smartt.com>

This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox