Thread: Experiences with pl/Java
Hi, was wondering if there is anyone wanted to share some experiences gained and some knowledge on pl/Java. Have looked into it for a couple of days now and am getting the impression it is not something ready to use in production environment. Also have trouble sending to the developer mailing list (the last email on the mail archive of that list is almost three weeks old) which raises questions on how active this project is. Have worked with PostgreSQL and pg/SQL before and also used Apache Derby in the past. On Apache Derby I have implemented some stored procedures using Java code and my interst now was on seeing how much of this could be re-used this pl/Java so PostgreSQL could potentially become a second data base backend my application would run on. But trying to port my java procedures from Derby to PostgreSQL had a bad start and for me pl/Java and the support around it so far suggest not to pursue this any further. Why?. Porting the simlest procedure was possible, but trying procedures with out parameters was not. Has anyone made the same experience with pl/Java or is it just my lack of understanding (in conjunction with a lack of documentation on pl/Java). Thanks a lot in advance Thomas
i used it for a project about 6 months ago. it took a little bit of effort to get things going, and the documentation coulduse some work, but it was reliable once i got oriented. the developer does read the mailing list, and responds to requests for help. i think you could use it in production so long as you have good processes in place and can dedicate some developer time tolearning & supporting it. but it's definitely not plug and play right now. richard ________________________________________ From: pgsql-general-owner@postgresql.org [pgsql-general-owner@postgresql.org] on behalf of Thomas Hill [Thomas.K.Hill@t-online.de] Sent: Monday, November 19, 2012 3:02 AM To: pgsql-general@postgresql.org Subject: [GENERAL] Experiences with pl/Java Hi, was wondering if there is anyone wanted to share some experiences gained and some knowledge on pl/Java. Have looked into it for a couple of days now and am getting the impression it is not something ready to use in production environment. Also have trouble sending to the developer mailing list (the last email on the mail archive of that list is almost three weeks old) which raises questions on how active this project is. Have worked with PostgreSQL and pg/SQL before and also used Apache Derby in the past. On Apache Derby I have implemented some stored procedures using Java code and my interst now was on seeing how much of this could be re-used this pl/Java so PostgreSQL could potentially become a second data base backend my application would run on. But trying to port my java procedures from Derby to PostgreSQL had a bad start and for me pl/Java and the support around it so far suggest not to pursue this any further. Why?. Porting the simlest procedure was possible, but trying procedures with out parameters was not. Has anyone made the same experience with pl/Java or is it just my lack of understanding (in conjunction with a lack of documentation on pl/Java). Thanks a lot in advance Thomas -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
On 19 November 2012 08:02, Thomas Hill <Thomas.K.Hill@t-online.de> wrote: > was wondering if there is anyone wanted to share some experiences gained and > some knowledge on pl/Java. Have looked into it for a couple of days now and > am getting the impression it is not something ready to use in production > environment. Also have trouble sending to the developer mailing list (the > last email on the mail archive of that list is almost three weeks old) which > raises questions on how active this project is. I'd caution you against using pl/java in production. I came up against a problem with it that I could not find a satisfactory solution for. Essentially, each Postgres backend (connection process) must start its own JVM, and does so using the JNI. If native memory runs out (I realise that this may not actually be due to malloc() returning NULL, but the effect is about the same), this failure is handled rather poorly. It results in an OutOfMemoryError due to "native memory exhaustion". This results in a segfault of the Postgres backend, originating from within libjvm.so. There is a workaround - which is to set the maximum JVM heap size to a sufficiently low value - but in general the need to do so left me with a very low opinion of pl/java as a project. -- Peter Geoghegan http://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Training and Services
Hello Peter, glad to meet you again after http://2012.pgconf.eu ! On ÎÎµÏ 19 Îοε 2012 16:26:56 you wrote: > On 19 November 2012 08:02, Thomas Hill <Thomas.K.Hill@t-online.de> wrote: > > was wondering if there is anyone wanted to share some experiences gained and > > some knowledge on pl/Java. Have looked into it for a couple of days now and > > am getting the impression it is not something ready to use in production > > environment. Also have trouble sending to the developer mailing list (the > > last email on the mail archive of that list is almost three weeks old) which > > raises questions on how active this project is. > > I'd caution you against using pl/java in production. I came up against > a problem with it that I could not find a satisfactory solution for. > > Essentially, each Postgres backend (connection process) must start its > own JVM, and does so using the JNI. If native memory runs out (I > realise that this may not actually be due to malloc() returning NULL, > but the effect is about the same), this failure is handled rather > poorly. It results in an OutOfMemoryError due to "native memory > exhaustion". This is the situation in J2EE app servers as well, once OutOfMemoryError is thrown there is not much the admin can do besides killing/restarting the app server. > This results in a segfault of the Postgres backend, > originating from within libjvm.so. There is a workaround - which is to > set the maximum JVM heap size to a sufficiently low value - Wouldn't that just make the problem manifest itself earlier? > but in > general the need to do so left me with a very low opinion of pl/java > as a project. > > However, in most cases this error denotes a system (jvm itself) memory leak, an insufficient garbage collector operation or a poorly designed application. In the java 7, i heard good stories about the new G1 garbage collector. We have never tried pl/java, despite being a postgresql/java house, and this is definitely something we are looking forward to explore, but i agree with Thomas that the traffic on the list is low which points to what Peter is suggesting about being scared to use this in production environments. That would be very cool if it was much more active and stable. - Achilleas Mantzios IT DEV IT DEPT Dynacom Tankers Mgmt
I don't know much about PostgreSQL with plJava, but I can give a few tips about memory stability when using Java (see inline): Em 19/11/2012 14:55, Achilleas Mantzios escreveu: > Hello Peter, glad to meet you again after http://2012.pgconf.eu ! > > On Δευ 19 �οε 2012 16:26:56 you wrote: >> On 19 November 2012 08:02, Thomas Hill <Thomas.K.Hill@t-online.de> wrote: >>> was wondering if there is anyone wanted to share some experiences gained and >>> some knowledge on pl/Java. Have looked into it for a couple of days now and >>> am getting the impression it is not something ready to use in production >>> environment. Also have trouble sending to the developer mailing list (the >>> last email on the mail archive of that list is almost three weeks old) which >>> raises questions on how active this project is. >> I'd caution you against using pl/java in production. I came up against >> a problem with it that I could not find a satisfactory solution for. >> >> Essentially, each Postgres backend (connection process) must start its >> own JVM, and does so using the JNI. If native memory runs out (I >> realise that this may not actually be due to malloc() returning NULL, >> but the effect is about the same), this failure is handled rather >> poorly. It results in an OutOfMemoryError due to "native memory >> exhaustion". > This is the situation in J2EE app servers as well, once OutOfMemoryError is thrown > there is not much the admin can do besides killing/restarting the app server. If you uses JDK 6, would worth make a test using JRockit - it's memory management is far more stable than default Oracle/Sun JVM. I've servers that run for months without restart, even reloading classes to accomodate code changes. The biggest problem with JVM is class reloading, since nursery is not garbage collected (or, at least, is not effective as it should be). JRockit uses a different approach, so it's not affected by this problem. But eventually even JRockit will run out of memory if you don't use the right approach. In the past, one choice was using static classes whenever possible. This is not true (neither recommended) anymore, and in most of the cases having thousands of cicles creation-use-release makes GC more effective (and your VM more stable). > >> This results in a segfault of the Postgres backend, >> originating from within libjvm.so. There is a workaround - which is to >> set the maximum JVM heap size to a sufficiently low value - > Wouldn't that just make the problem manifest itself earlier? > >> but in >> general the need to do so left me with a very low opinion of pl/java >> as a project. >> >> > However, in most cases this error denotes a system (jvm itself) memory leak, > an insufficient garbage collector operation or a poorly designed application. > In the java 7, i heard good stories about the new G1 garbage collector. I've been using JDK 7 with G1 GC for hosting NetBeans and Tomcat, and once you don't have lots of class reload, you get a stable environment. JDK 7 still suffer of nursery (or whatever the new name they put in) issues, and classes are not properly disposed. I don't even think this is a Java issue - most Object Oriented Virtual Machines that relay on garbage collection suffer of the same issue (references are kept and then objects are never released, so classes cannot be disposed). Regards, Edson Richter > > We have never tried pl/java, despite being a postgresql/java house, and this is definitely > something we are looking forward to explore, but i agree with Thomas that the traffic on the list > is low which points to what Peter is suggesting about being scared to use this in production environments. > That would be very cool if it was much more active and stable. > > - > Achilleas Mantzios > IT DEV > IT DEPT > Dynacom Tankers Mgmt > >
Edson Richter [edsonrichter@hotmail.com] writes: >I don't know much about PostgreSQL with plJava, but I can give a few >tips about memory stability when using Java (see inline): ... >In the past, one choice was using static classes whenever possible. This >is not true (neither recommended) anymore, and in most of the cases >having thousands of cicles creation-use-release makes GC more effective >(and your VM more stable). PL/Java requires that the methods being directly called from PostgreSQL are static. while i don't disagree with the advice, PL/Java is limited in this respect. richard
Em 19/11/2012 15:26, Welty, Richard escreveu: > Edson Richter [edsonrichter@hotmail.com] writes: > >> I don't know much about PostgreSQL with plJava, but I can give a few >> tips about memory stability when using Java (see inline): > ... >> In the past, one choice was using static classes whenever possible. This >> is not true (neither recommended) anymore, and in most of the cases >> having thousands of cicles creation-use-release makes GC more effective >> (and your VM more stable). > PL/Java requires that the methods being directly called from PostgreSQL are static. > while i don't disagree with the advice, PL/Java is limited in this respect. :-) as I said, I know little about pl/Java... thanks for pointing this out. So, after calling the static method, probably inside the static methods, the programmer will create his/her logic. Writing this logic considering GC behavior would make your apps more stable. I would recommend to not use any Java library that keeps "caches" (ones like EclipseLink or Hibernate), unless you know exactly how to configure caches and soft/weak references - and their limitations. Regards, Edson > > richard > >
Edson Richter [edsonrichter@hotmail.com] writes: >Em 19/11/2012 15:26, Welty, Richard escreveu: >> PL/Java requires that the methods being directly called from PostgreSQL are static. >> while i don't disagree with the advice, PL/Java is limited in this respect. >:-) as I said, I know little about pl/Java... thanks for pointing this out. >So, after calling the static method, probably inside the static methods, >the programmer will create his/her logic. Writing this logic considering >GC behavior would make your apps more stable. >I would recommend to not use any Java library that keeps "caches" (ones >like EclipseLink or Hibernate), unless you know exactly how to configure >caches and soft/weak references - and their limitations. i would probably recommend severely limiting what you try to do in PL/Java. in my particular project, the overall goal was incrementally doing push updates to an Apache Solr search engine. this entailed building xml descriptions of the update that was required, sending it with an http request (which required untrusted pl/java), and providing a recovery & retry mechanism in case the http request failed, which limited itself to using the provided jdbc. i got it working and working well, but i put a lot of care into insuring that the garbage collecter never got stressed very hard and i didn't try to do more than was strictly necessary. i'd argue that if you find yourself wanting to use hibernate or eclipselink down in PL/Java, you should rethink your application design. richard
Thanks to all for providing feedback and sharing opinions. Looks like
you have gone much further on it than I thought someone would have. So I
think I might spend some more time with it, but not plan to use it for
my application in a production environment.
My initial attempts were to try to re-use/port some simple procedures
which are running fine on Apache Derby, but then I got stuck quite early
in the process and could not find documentation showing how things needs
to be done and helping me to understand what I am doing wrong.
My first use case was calling a procedure which does not have any
parameter and this I actually got to run, i.e.
public static String CURRENT_CLIENTID() throws SQLException {
String vcFKClientID = "000";
return vcFKClientID;
}
CREATE OR REPLACE FUNCTION rte."CURRENT_CLIENTID"()
RETURNS character varying AS
'onlyPostgreSQLPk.Functions.CURRENT_CLIENTID'
LANGUAGE java VOLATILE SECURITY DEFINER
COST 100;
ALTER FUNCTION rte."CURRENT_CLIENTID"()
OWNER TO postgres;
=> select rte."CURRENT_CLIENTID"() returns '000'
My second use case was to create a procedure with an out parameter, i.e.
public static void SP_getNextID(int iNextVal[], String vcIDName)
throws SQLException {
Connection conn = getDefaultConnection();
Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
ResultSet.CONCUR_UPDATABLE);
String cSQL = "SELECT \"LastValue\" \n" + "FROM rte.\"TBL_IDs\"
\n"
+ "WHERE \"IDName\" = '" + vcIDName + "'\n";
ResultSet rs = stmt.executeQuery(cSQL);
while (rs.next()) {
iNextVal[0] = rs.getInt(1) + 1;
rs.updateInt("LastValue", iNextVal[0]);
rs.updateRow();
}
rs.close();
stmt.close();
return;
}
CREATE OR REPLACE FUNCTION rte."SP_getNextID"(OUT "iNextID" integer, IN
"vcIDName" character varying)
RETURNS integer AS
'allDatabasesPk.Procedures.SP_getNextID(int[], java.lang.String)'
The static method seems to be found. But when calling the function using
pgadmin and issuing a 'Select rte."SP_getNextID"('xy');' I am getting error
Too many parameters - expected 1
which I find confusing as I am only passing one parameter!?
I tried some variations I could think of, but without success.
Unfortunately I have not found an exmaple anywhere on the web showing
how this needs to be done.
Would be great if someone could have a look at above and spot what I am
doing wrong.
Thanks a lot in advance.
Kind regards
Thomas
Am 19.11.2012 20:19, schrieb Welty, Richard:
> Edson Richter [edsonrichter@hotmail.com] writes:
>> Em 19/11/2012 15:26, Welty, Richard escreveu:
>>> PL/Java requires that the methods being directly called from PostgreSQL are static.
>>> while i don't disagree with the advice, PL/Java is limited in this respect.
>> :-) as I said, I know little about pl/Java... thanks for pointing this out.
>> So, after calling the static method, probably inside the static methods,
>> the programmer will create his/her logic. Writing this logic considering
>> GC behavior would make your apps more stable.
>> I would recommend to not use any Java library that keeps "caches" (ones
>> like EclipseLink or Hibernate), unless you know exactly how to configure
>> caches and soft/weak references - and their limitations.
> i would probably recommend severely limiting what you try to do in PL/Java.
>
> in my particular project, the overall goal was incrementally doing push updates to an Apache
> Solr search engine. this entailed building xml descriptions of the update that was required,
> sending it with an http request (which required untrusted pl/java), and providing a recovery & retry
> mechanism in case the http request failed, which limited itself to using the provided jdbc.
>
> i got it working and working well, but i put a lot of care into insuring that the garbage collecter
> never got stressed very hard and i didn't try to do more than was strictly necessary. i'd argue
> that if you find yourself wanting to use hibernate or eclipselink down in PL/Java, you should
> rethink your application design.
>
> richard
>
>
thanks - not sure how plJava works here and if the implementation is
identical to Apache Derby - what I can tell however is that defining the
types the way I did (integer on one side vs an array of integers on the
other side) is exactely how Apache Derby needs this as there out parms
always need to be defined as arrays in the method declaration and are
then automatically returned as integers - I will try to use integers on
both sides for plJava tomorrow, but if this would solve the problem this
would also mean that method declaration is different and depending on
data base backend implementation - which would make could re-use impossible
Am 19.11.2012 22:58, schrieb Welty, Richard:
> i looked your stuff over. i never needed to make out params work, so i'm kind of guessing here, but the first thing
i'dlook at is the type mismatch between the args given in the pl/pgsql 'create or replace function' vs the args in the
javadeclaration, that is, the int on one side vs the array of ints on the other. due to the lack of examples available
thatyou previously pointed out, i have no real idea how that should look in a working example.
>
> richard
>
> ________________________________________
> From: Thomas Hill [Thomas.K.Hill@t-online.de]
> Sent: Monday, November 19, 2012 2:55 PM
> To: Welty, Richard; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Experiences with pl/Java
>
> Thanks to all for providing feedback and sharing opinions. Looks like
> you have gone much further on it than I thought someone would have. So I
> think I might spend some more time with it, but not plan to use it for
> my application in a production environment.
>
> My initial attempts were to try to re-use/port some simple procedures
> which are running fine on Apache Derby, but then I got stuck quite early
> in the process and could not find documentation showing how things needs
> to be done and helping me to understand what I am doing wrong.
>
> My first use case was calling a procedure which does not have any
> parameter and this I actually got to run, i.e.
>
> public static String CURRENT_CLIENTID() throws SQLException {
>
> String vcFKClientID = "000";
>
> return vcFKClientID;
> }
>
> CREATE OR REPLACE FUNCTION rte."CURRENT_CLIENTID"()
> RETURNS character varying AS
> 'onlyPostgreSQLPk.Functions.CURRENT_CLIENTID'
> LANGUAGE java VOLATILE SECURITY DEFINER
> COST 100;
> ALTER FUNCTION rte."CURRENT_CLIENTID"()
> OWNER TO postgres;
>
> => select rte."CURRENT_CLIENTID"() returns '000'
>
> My second use case was to create a procedure with an out parameter, i.e.
>
> public static void SP_getNextID(int iNextVal[], String vcIDName)
> throws SQLException {
> Connection conn = getDefaultConnection();
>
> Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
> ResultSet.CONCUR_UPDATABLE);
>
> String cSQL = "SELECT \"LastValue\" \n" + "FROM rte.\"TBL_IDs\"
> \n"
> + "WHERE \"IDName\" = '" + vcIDName + "'\n";
>
> ResultSet rs = stmt.executeQuery(cSQL);
>
> while (rs.next()) {
> iNextVal[0] = rs.getInt(1) + 1;
> rs.updateInt("LastValue", iNextVal[0]);
> rs.updateRow();
> }
>
> rs.close();
> stmt.close();
>
> return;
>
> }
>
> CREATE OR REPLACE FUNCTION rte."SP_getNextID"(OUT "iNextID" integer, IN
> "vcIDName" character varying)
> RETURNS integer AS
> 'allDatabasesPk.Procedures.SP_getNextID(int[], java.lang.String)'
>
> The static method seems to be found. But when calling the function using
> pgadmin and issuing a 'Select rte."SP_getNextID"('xy');' I am getting error
> Too many parameters - expected 1
> which I find confusing as I am only passing one parameter!?
>
> I tried some variations I could think of, but without success.
> Unfortunately I have not found an exmaple anywhere on the web showing
> how this needs to be done.
>
> Would be great if someone could have a look at above and spot what I am
> doing wrong.
>
> Thanks a lot in advance.
>
> Kind regards
> Thomas
>
> Am 19.11.2012 20:19, schrieb Welty, Richard:
>> Edson Richter [edsonrichter@hotmail.com] writes:
>>> Em 19/11/2012 15:26, Welty, Richard escreveu:
>>>> PL/Java requires that the methods being directly called from PostgreSQL are static.
>>>> while i don't disagree with the advice, PL/Java is limited in this respect.
>>> :-) as I said, I know little about pl/Java... thanks for pointing this out.
>>> So, after calling the static method, probably inside the static methods,
>>> the programmer will create his/her logic. Writing this logic considering
>>> GC behavior would make your apps more stable.
>>> I would recommend to not use any Java library that keeps "caches" (ones
>>> like EclipseLink or Hibernate), unless you know exactly how to configure
>>> caches and soft/weak references - and their limitations.
>> i would probably recommend severely limiting what you try to do in PL/Java.
>>
>> in my particular project, the overall goal was incrementally doing push updates to an Apache
>> Solr search engine. this entailed building xml descriptions of the update that was required,
>> sending it with an http request (which required untrusted pl/java), and providing a recovery & retry
>> mechanism in case the http request failed, which limited itself to using the provided jdbc.
>>
>> i got it working and working well, but i put a lot of care into insuring that the garbage collecter
>> never got stressed very hard and i didn't try to do more than was strictly necessary. i'd argue
>> that if you find yourself wanting to use hibernate or eclipselink down in PL/Java, you should
>> rethink your application design.
>>
>> richard
>>
>>
>
On 11/19/12 2:56 PM, Thomas Hill wrote: > thanks - not sure how plJava works here and if the implementation is > identical to Apache Derby - what I can tell however is that defining > the types the way I did (integer on one side vs an array of integers > on the other side) is exactely how Apache Derby needs this as there > out parms always need to be defined as arrays in the method > declaration and are then automatically returned as integers - I will > try to use integers on both sides for plJava tomorrow, but if this > would solve the problem this would also mean that method declaration > is different and depending on data base backend implementation - which > would make could re-use impossible can't answer with respect to Derby, but we've got one internal application that requires pljava which was ported over from Oracle, and uses JDBC to implement trigger based replication of static data between oracle 10g and postgres servers, apparently the pljava code moved without any hassle. getting pljava working on a given platform is a pain. I had to build it for AIX which was definitely not fun, but once I got it working, the apps work just fine. -- john r pierce N 37, W 122 santa cruz ca mid-left coast
i think pl/java may expect the method signatures to match up precisely. not entirely sure, as there are no
examples published as to how pl/java expects out parameters to work.
richard
________________________________________
From: Thomas Hill [Thomas.K.Hill@t-online.de]
Sent: Monday, November 19, 2012 5:56 PM
To: Welty, Richard; pgsql-general@postgresql.org
Subject: Re: [GENERAL] Experiences with pl/Java
thanks - not sure how plJava works here and if the implementation is
identical to Apache Derby - what I can tell however is that defining the
types the way I did (integer on one side vs an array of integers on the
other side) is exactely how Apache Derby needs this as there out parms
always need to be defined as arrays in the method declaration and are
then automatically returned as integers - I will try to use integers on
both sides for plJava tomorrow, but if this would solve the problem this
would also mean that method declaration is different and depending on
data base backend implementation - which would make could re-use impossible
Am 19.11.2012 22:58, schrieb Welty, Richard:
> i looked your stuff over. i never needed to make out params work, so i'm kind of guessing here, but the first thing
i'dlook at is the type mismatch between the args given in the pl/pgsql 'create or replace function' vs the args in the
javadeclaration, that is, the int on one side vs the array of ints on the other. due to the lack of examples available
thatyou previously pointed out, i have no real idea how that should look in a working example.
>
> richard
>
> ________________________________________
> From: Thomas Hill [Thomas.K.Hill@t-online.de]
> Sent: Monday, November 19, 2012 2:55 PM
> To: Welty, Richard; pgsql-general@postgresql.org
> Subject: Re: [GENERAL] Experiences with pl/Java
>
> Thanks to all for providing feedback and sharing opinions. Looks like
> you have gone much further on it than I thought someone would have. So I
> think I might spend some more time with it, but not plan to use it for
> my application in a production environment.
>
> My initial attempts were to try to re-use/port some simple procedures
> which are running fine on Apache Derby, but then I got stuck quite early
> in the process and could not find documentation showing how things needs
> to be done and helping me to understand what I am doing wrong.
>
> My first use case was calling a procedure which does not have any
> parameter and this I actually got to run, i.e.
>
> public static String CURRENT_CLIENTID() throws SQLException {
>
> String vcFKClientID = "000";
>
> return vcFKClientID;
> }
>
> CREATE OR REPLACE FUNCTION rte."CURRENT_CLIENTID"()
> RETURNS character varying AS
> 'onlyPostgreSQLPk.Functions.CURRENT_CLIENTID'
> LANGUAGE java VOLATILE SECURITY DEFINER
> COST 100;
> ALTER FUNCTION rte."CURRENT_CLIENTID"()
> OWNER TO postgres;
>
> => select rte."CURRENT_CLIENTID"() returns '000'
>
> My second use case was to create a procedure with an out parameter, i.e.
>
> public static void SP_getNextID(int iNextVal[], String vcIDName)
> throws SQLException {
> Connection conn = getDefaultConnection();
>
> Statement stmt = conn.createStatement(ResultSet.TYPE_FORWARD_ONLY,
> ResultSet.CONCUR_UPDATABLE);
>
> String cSQL = "SELECT \"LastValue\" \n" + "FROM rte.\"TBL_IDs\"
> \n"
> + "WHERE \"IDName\" = '" + vcIDName + "'\n";
>
> ResultSet rs = stmt.executeQuery(cSQL);
>
> while (rs.next()) {
> iNextVal[0] = rs.getInt(1) + 1;
> rs.updateInt("LastValue", iNextVal[0]);
> rs.updateRow();
> }
>
> rs.close();
> stmt.close();
>
> return;
>
> }
>
> CREATE OR REPLACE FUNCTION rte."SP_getNextID"(OUT "iNextID" integer, IN
> "vcIDName" character varying)
> RETURNS integer AS
> 'allDatabasesPk.Procedures.SP_getNextID(int[], java.lang.String)'
>
> The static method seems to be found. But when calling the function using
> pgadmin and issuing a 'Select rte."SP_getNextID"('xy');' I am getting error
> Too many parameters - expected 1
> which I find confusing as I am only passing one parameter!?
>
> I tried some variations I could think of, but without success.
> Unfortunately I have not found an exmaple anywhere on the web showing
> how this needs to be done.
>
> Would be great if someone could have a look at above and spot what I am
> doing wrong.
>
> Thanks a lot in advance.
>
> Kind regards
> Thomas
>
> Am 19.11.2012 20:19, schrieb Welty, Richard:
>> Edson Richter [edsonrichter@hotmail.com] writes:
>>> Em 19/11/2012 15:26, Welty, Richard escreveu:
>>>> PL/Java requires that the methods being directly called from PostgreSQL are static.
>>>> while i don't disagree with the advice, PL/Java is limited in this respect.
>>> :-) as I said, I know little about pl/Java... thanks for pointing this out.
>>> So, after calling the static method, probably inside the static methods,
>>> the programmer will create his/her logic. Writing this logic considering
>>> GC behavior would make your apps more stable.
>>> I would recommend to not use any Java library that keeps "caches" (ones
>>> like EclipseLink or Hibernate), unless you know exactly how to configure
>>> caches and soft/weak references - and their limitations.
>> i would probably recommend severely limiting what you try to do in PL/Java.
>>
>> in my particular project, the overall goal was incrementally doing push updates to an Apache
>> Solr search engine. this entailed building xml descriptions of the update that was required,
>> sending it with an http request (which required untrusted pl/java), and providing a recovery & retry
>> mechanism in case the http request failed, which limited itself to using the provided jdbc.
>>
>> i got it working and working well, but i put a lot of care into insuring that the garbage collecter
>> never got stressed very hard and i didn't try to do more than was strictly necessary. i'd argue
>> that if you find yourself wanting to use hibernate or eclipselink down in PL/Java, you should
>> rethink your application design.
>>
>> richard
>>
>>
>
have tested further combinations - without success - any other idea?
1st attempt (note: this implementation works on Apache Derby!)
===========
CREATE OR REPLACE FUNCTION rte."SP_getNextID"(OUT "iNextID" integer, IN
"vcIDName" character varying)
RETURNS integer LANGUAGE JAVA
EXTERNAL SECURITY DEFINER AS
'onlyPostgreSQLPk.Functions.SP_getNextID(int[], String)'
public static void SP_getNextID(int[] iNextVal, String vcIDName)
throws SQLException {
Connection conn = getDefaultConnection();
// some JDBC code here
return;
}
Select rte."SP_getNextID"('xx');
==> result: FEHLER: To many parameters - expected 1
********** Fehler **********
FEHLER: To many parameters - expected 1
SQL Status:42601
2nd attempt (omitting the signature in the function declaration)
===========
CREATE OR REPLACE FUNCTION rte."SP_getNextID"(OUT "iNextID" int, IN "vcIDName"
character varying)
RETURNS integer LANGUAGE JAVA
EXTERNAL SECURITY DEFINER AS
'onlyPostgreSQLPk.Functions.SP_getNextID'
public static void SP_getNextID(int[] iNextVal, String vcIDName)
throws SQLException {
Connection conn = getDefaultConnection();
// some JDBC code here
return;
}
Select rte."SP_getNextID"('Transaction');
==> result: FEHLER: Unable to find static method
onlyPostgreSQLPk.Functions.SP_getNextID with signature (Ljava/lang/String;)I
********** Fehler **********
FEHLER: Unable to find static method onlyPostgreSQLPk.Functions.SP_getNextID
with signature (Ljava/lang/String;)I
SQL Status:XX000
3rd attempt (using int in the java method declarartion and in function signature)
===========
CREATE OR REPLACE FUNCTION rte."SP_getNextID"(OUT "iNextID" int, IN "vcIDName"
character varying)
RETURNS integer LANGUAGE JAVA
EXTERNAL SECURITY DEFINER AS
'onlyPostgreSQLPk.Functions.SP_getNextID(int, String)'
public static void SP_getNextID(int iNextVal, String vcIDName)
throws SQLException {
Connection conn = getDefaultConnection();
// some JDBC code here
return;
}
Select rte."SP_getNextID"('xx');
==> result: FEHLER: To many parameters - expected 1
********** Fehler **********
FEHLER: To many parameters - expected 1
SQL Status:42601
5th attempt (using java.lang.Integer in the java method declarartion, integer in
function declaration and java.lang.Integer in function signature)
===========
CREATE OR REPLACE FUNCTION rte."SP_getNextID"(OUT "iNextID" integer, IN
"vcIDName" character varying)
RETURNS integer LANGUAGE JAVA
EXTERNAL SECURITY DEFINER AS
'onlyPostgreSQLPk.Functions.SP_getNextID(java.lang.Integer, String)'
public static void SP_getNextID(java.lang.Integer iNextVal, String vcIDName)
throws SQLException {
Connection conn = getDefaultConnection();
// some JDBC code here
return;
}
Select rte."SP_getNextID"('xx');
==> result: FEHLER: To many parameters - expected 1
********** Fehler **********
FEHLER: To many parameters - expected 1
SQL Status:42601
Select rte."SP_getNextID"('xx');
==> result: FEHLER: To many parameters - expected 1
********** Fehler **********
FEHLER: To many parameters - expected 1
SQL Status:42601
Thomas wrote: > have tested further combinations - without success - any other > idea? > [attempts to use Java parameter list for PostgreSQL OUT parameters] My guess is that for a single PostgreSQL OUT parameter you should make your Java function return a value of that type and omit the parameter from the Java function signature. PostgreSQL implements OUT parameters in a different way from what I've seen in other products: a single OUT parrameter is the same as specifying the type in the RETURNS for the function. Specifying multiple OUT parameters returns a record with matching fields. That always seems odd to me, but at this point it is very unlikely to change. -Kevin