Thread: Calling Java from psql (was Re: requesting help)
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
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/
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
Tom, Jan, I've already contacted PGSQL Inc. support about this, but I thought that you might have some immediate insight. I'm using 7.1 Beta 3, which has been pretty stable up until now. This morning, I went to export a function I spent 5 hours debugging on on Friday to text so that I could have a copy of the final version. To my horror, the function was GONE from the system catalog (pg_proc). I have not run VACUUM on the database anytime recently ... is there any possibility that my function is still present as a "deleted" row? -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
"Josh Berkus" <josh@agliodbs.com> writes:
> I'm using 7.1 Beta 3, which has been pretty stable up until now. This
> morning, I went to export a function I spent 5 hours debugging on on
> Friday to text so that I could have a copy of the final version. To my
> horror, the function was GONE from the system catalog (pg_proc).
Ick. Were you maybe working on it inside a transaction that you forgot
to commit?
> I have
> not run VACUUM on the database anytime recently ... is there any
> possibility that my function is still present as a "deleted" row?
Should still be there in the table, if you haven't vacuumed. Getting
it out again is another story though. If it was a small enough function,
good ol' "strings" would do to extract the function body, which is
probably all that you really need. But if it's more than a couple K
then the text will be compressed and difficult to spot or extract.
regards, tom lane
Tom, > Ick. Were you maybe working on it inside a transaction that you > forgot > to commit? Nope. Friday was debugging work; the function had already been saved as a buggy version. I can even find the last buggy call to the function, on Friday, in the logs. I do have a copy of the buggy version, but if I can get the debugged version back ... > Should still be there in the table, if you haven't vacuumed. Getting > it out again is another story though. If it was a small enough > function, > good ol' "strings" would do to extract the function body, which is > probably all that you really need. But if it's more than a couple K > then the text will be compressed and difficult to spot or extract. Can you explain that? I think that the text may be short entough to be uncompressed. -Josh Berkus ______AGLIO DATABASE SOLUTIONS___________________________ Josh Berkus Complete informationtechnology josh@agliodbs.com and data management solutions (415) 565-7293 for law firms, small businesses fax 621-2533 and non-profit organizations. San Francisco
"Josh Berkus" <josh@agliodbs.com> writes:
>> Should still be there in the table, if you haven't vacuumed. Getting
>> it out again is another story though. If it was a small enough
>> function,
>> good ol' "strings" would do to extract the function body, which is
>> probably all that you really need. But if it's more than a couple K
>> then the text will be compressed and difficult to spot or extract.
> Can you explain that? I think that the text may be short entough to be
> uncompressed.
Try running strings(1) on the pg_proc table, which will be ... hmm ...
$PGDATA/base/YOURDBOID/1255. Look in pg_database if you're not sure of
the OID of the database you are using.
If you have not vacuumed then the latest version of the row will be the
one closest to the front of the last 8K page of the file (got that? ;-))
regards, tom lane
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > I'm using 7.1 Beta 3, which has been pretty
> stable up until now. This
> > morning, I went to export a function I spent 5
> hours debugging on on
> > Friday to text so that I could have a copy of the
> final version. To my
> > horror, the function was GONE from the system
> catalog (pg_proc).
>
> Ick. Were you maybe working on it inside a
> transaction that you forgot
> to commit?
���� Ick �O�����N��, �O�����r���Y�g?
S.F.Lee 2001-03-27
__________________________________________________
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/
Hi would it work to do a pg_dump -d or a pgdumpall, then look through the dumped sql file? I do that to retrieve PL/pgSQL functions from the database when I've accidentally wiped or modified the source (not often, but it happens). Regards and best wishes, Justin Clift Tom Lane wrote: > > "Josh Berkus" <josh@agliodbs.com> writes: > > I'm using 7.1 Beta 3, which has been pretty stable up until now. This > > morning, I went to export a function I spent 5 hours debugging on on > > Friday to text so that I could have a copy of the final version. To my > > horror, the function was GONE from the system catalog (pg_proc). > > Ick. Were you maybe working on it inside a transaction that you forgot > to commit? > > > I have > > not run VACUUM on the database anytime recently ... is there any > > possibility that my function is still present as a "deleted" row? > > Should still be there in the table, if you haven't vacuumed. Getting > it out again is another story though. If it was a small enough function, > good ol' "strings" would do to extract the function body, which is > probably all that you really need. But if it's more than a couple K > then the text will be compressed and difficult to spot or extract. > > regards, tom lane > > ---------------------------(end of broadcast)--------------------------- > TIP 6: Have you searched our list archives? > > http://www.postgresql.org/search.mpl
What does "=P0] Ick ,O$0;r7N+d, ,O$0;r&r*:AY<g?" mean?
-----Original Message-----
From: pgsql-sql-owner@postgresql.org
[mailto:pgsql-sql-owner@postgresql.org]On Behalf Of S.F. Lee
Sent: Monday, March 26, 2001 5:40 PM
To: shuyeelee@yahoo.com
Cc: pgsql-sql@postgresql.org
Subject: [SQL] AY<g*:'t8q
--- Tom Lane <tgl@sss.pgh.pa.us> wrote:
> > I'm using 7.1 Beta 3, which has been pretty
> stable up until now. This
> > morning, I went to export a function I spent 5
> hours debugging on on
> > Friday to text so that I could have a copy of the
> final version. To my
> > horror, the function was GONE from the system
> catalog (pg_proc).
>
> Ick. Were you maybe working on it inside a
> transaction that you forgot
> to commit?
=P0] Ick ,O$0;r7N+d, ,O$0;r&r*:AY<g?
S.F.Lee 2001-03-27
__________________________________________________
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/
---------------------------(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
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
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
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
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
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/
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
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/
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!
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/