Subject: [Pljava-dev] odd error with 1.5.0-snapshot

From: John R Pierce <[email-masked]>
Date: Wed, 3 Feb 2016 11:24:10 -0800

I built 1.5.0-snapshot about 2 weeks ago, it passed the tests, so I 
handed it off to my production folks.  Now, I do NOT currently have 
source to the pljava module they are trying to deploy, but they just 
reported to me late last night that they got this error.   All I know is 
their app uses jdbc to connect to an oracle database on another system 
and updates local 'static tables' from the master copy on said oracle.   
I've asked to see the source, if I get it, I'll try and drilll in to see 
whats going on in the code referenced in the traceback below, but I'm 
curious if you have any hints about what that SQLException means.

database=> select jdblink_version();
ERROR: 03 Feb 16 16:27:28 jdblink.core.Jdblink null
java.sql.SQLException: An attempt was made to call a PostgreSQL backend 
function after an elog(ERROR) had been issued
         at org.postgresql.pljava.
internal.Oid._forSqlType(Native Method)
         at org.postgresql.pljava.internal.Oid.forSqlType(Oid.java:74)
         at 
org.postgresql.pljava.jdbc.SPIPreparedStatement.setObject(SPIPreparedStatement.java:206)
         at 
org.postgresql.pljava.jdbc.SPIPreparedStatement.setString(SPIPreparedStatement.java:137)
         at jdblink.dao.JdblinkMapDAOImpl.find(JdblinkMapDAOImpl.java:84)
         at jdblink.core.Jdblink.getJdblinkMap(Jdblink.java:78)
         at jdblink.core.Jdblink.init(Jdblink.java:72)
         at jdblink.core.Jdblink.<init>(Jdblink.java:64)
         at jdblink.core.PlJdblink.<init>(PlJdblink.java:37)
         at jdblink.core.PlJdblink.getInstance(PlJdblink.java:24)
         at 
jdblink.core.JdblinkFactoryImpl.getPlJdblink(JdblinkFactoryImpl.java:16)
         at jdblink.postgres.Session.jdblink_version(Session.java:53)

if it matters, this is CentOS 6.7, PostgreSQL 9.4.5 from the 
yum.postgresql.org repo, and OpenJDK 1.8.0 from the CentOS repository, 
all 64 bit.

-- 
john r pierce, recycling bits in santa cruz

_______________________________________________
Pljava-dev mailing list
[email-masked]
http://lists.pgfoundry.org/mailman/listinfo/pljava-dev

---

From: Chapman Flack <[email-masked]>
Date: Wed, 3 Feb 2016 15:16:16 -0500

On 02/03/2016 02:24 PM, John R Pierce wrote:
> java.sql.SQLException: An attempt was made to call a PostgreSQL
> backend function after an elog(ERROR) had been issued

Ok, what it ostensibly means is: the code had, in the past, made
some call into PostgreSQL that ereport()ed an ERROR. That turns
into an SQLException in PL/Java (not the one we're looking at
here, this had to have happened earlier).  Perhaps the code had
a try/catch block and caught that exception, and now it wants to
proceed and do more stuff.

Trouble is, whenever an ERROR has been thrown from a call into
PostgreSQL, we don't know what possibly gronkled state the
transaction has been left in, and for the remainder of this PL/Java
function call, no more access to PostgreSQL is going to be allowed.
That's what produced *this* SQLException, at the point where the
code seems to be going ahead and setting a parameter in a prepared
statement.

If you are writing PL/Java code that does want to catch an exception
thrown from PostgreSQL, recover and proceed to do more stuff, there
is one way to do that. You have to use sp = conn.setSavepoint()
in advance of where the exception might be thrown, and
conn.releaseSavepoint(sp) in the normal case where your call
is successful. In the catch block if an exception has been thrown,
use conn.rollback(sp) to put the transaction back into a known
non-gronkled state and now you can proceed to make more calls.

That was the official answer. A different interesting question
might be, what was the earlier exception that had been thrown and
caught, and why was it thrown in this case if the same code had
been in use before?  They might want to redo the test with
client_min_messages or log_min_messages (or both) set to DEBUG2
or higher.

-Chap

---

From: Frédéric OLIVIE <[email-masked]>
Date: Wed, 3 Feb 2016 21:38:03 +0000

Whatever is the offical answer by distrib maintainers, José's question seems very legit.
While you guys are struggling for PL/Java to be integrated in distribs, "we" are losing sysadmins which have a harder time every day to justify that the project is still alive.
Why ? Because they have to spend an awful lot of time making it work on new releases of PG.
Guys ! Do you have any idea how "alien" it is to set up a proper maven/compiler environment when you're not a field proven developer ? More even under Windows ?

Why do "we" not at least offer binaries for Windows/Linux, 32/64. Having had to build PL/Java by the past, I don't think I remember there's any external dependency apart from the PG version. But if I'm wrong, then let's just build the .so/.dll static !

To conclude, as far as I understand it, José's request means that, the longer there is no binaries available, the more the user base will shrink with doubts regarding the long term legitimacy of this project. Which is totally unfair in view of you guys amazing (and successful) job to give it a second breath !

Just my 2cts anyways.

Frédéric.

-----Message d'origine-----
De : Pljava-dev [mailto:[email-masked]] De la part de Chapman Flack
Envoyé : mercredi 3 février 2016 23:16
À : [email-masked]
Objet : Re: [Pljava-dev] odd error with 1.5.0-snapshot

> backend function after an elog(ERROR) had been issued

Ok, what it ostensibly means is: the code had, in the past, made some call into PostgreSQL that ereport()ed an ERROR. That turns into an SQLException in PL/Java (not the one we're looking at here, this had to have happened earlier).  Perhaps the code had a try/catch block and caught that exception, and now it wants to proceed and do more stuff.

Trouble is, whenever an ERROR has been thrown from a call into PostgreSQL, we don't know what possibly gronkled state the transaction has been left in, and for the remainder of this PL/Java function call, no more access to PostgreSQL is going to be allowed.
That's what produced *this* SQLException, at the point where the code seems to be going ahead and setting a parameter in a prepared statement.

If you are writing PL/Java code that does want to catch an exception thrown from PostgreSQL, recover and proceed to do more stuff, there is one way to do that. You have to use sp = conn.setSavepoint() in advance of where the exception might be thrown, and
conn.releaseSavepoint(sp) in the normal case where your call is successful. In the catch block if an exception has been thrown, use conn.rollback(sp) to put the transaction back into a known non-gronkled state and now you can proceed to make more calls.

That was the official answer. A different interesting question might be, what was the earlier exception that had been thrown and caught, and why was it thrown in this case if the same code had been in use before?  They might want to redo the test with client_min_messages or log_min_messages (or both) set to DEBUG2 or higher.

-Chap

---

From: John R Pierce <[email-masked]>
Date: Wed, 3 Feb 2016 14:25:15 -0800

> client_min_messages or log_min_messages (or both) set to DEBUG2

thanks, Chapman...    That clarified and solidified what I was 
guessing.    It will likely take me a day or two to get access to the 
postgres logs as well as source code so I can try and figure out the 
details.   14 hour time zone differences, sigh.

---

From: Chapman Flack <[email-masked]>
Date: Wed, 03 Feb 2016 20:48:15 -0500

> question seems very legit.

If I'm reading this right, you have replied to John's message about
a use-after-error exception (the only place I remember saying anything
about "official"), but you are mostly interested in improving the
availability of prebuilt binaries. I agree, José's question is
completely legit. Prebuilt binaries simplify setting up PL/Java,
and ideally, those should be easy to get.

> While you guys are struggling for PL/Java to be integrated in distribs,

As you saw in John's earlier message (the one where he was replying to
José), 'struggling' might be too strong a word. Devrim is ready to have
prebuilt binaries for you, at least for every Linux flavor that uses rpm,
just as soon as we have an official release. That's one short beta period
away.

The same ought to be true for the linux flavors using apt, though I think
Devrim is not part of that team and I'll probably have to send a ping
there myself to make sure they know the release is coming up.

Ok, Windows and OS X.  Those are not really my element.  I am trying
to absorb information here about what will be considered a convenient
way to install on those platforms. The postgresql.org download page
plugs the EnterpriseDB graphical installer for both of those platforms,
and it (IIUC) comes with a thing called StackBuilder to automate the
installation of known extensions. I think Dave Page is the person I
need to contact about what's needed for StackBuilder to know about
PL/Java 1.5.0.

> Which is totally unfair in view of you guys amazing (and successful) job >
to give it a second breath !

This is my favorite part of your message, and I am glad that you think
the progress has been significant and notable. To keep a project vigorous,
you do need easy availability, and you also need enough advancement in
function to keep people interested. The advancement part I think we have,
and the easy-availability part still has some catching up to do. I think
that was the necessary order, because to keep the workload manageable,
some of the work has to be done by different people. There are already
people like Devrim with the infrastructure set up to build packages
and they're good at it, and the packages they build will be most easily
accepted by cautious sysadmins because they will be from a known repo
with a recognized signing key. And for that to happen, the main requirement
is to provide a package worth building. Have you tried the beta yet?  :)

Thanks,
-Chap

---

From: Chapman Flack <[email-masked]>
Date: Fri, 12 Feb 2016 20:46:42 -0500

> there myself to make sure they know the release is coming up.

I've contacted Christoph Berg about .deb packaging. He is looking into
it but there's an extra complication in this case ... the Debian
philosophy and its possible conflict with how Maven builds things.
Who knew? I didn't. Christoph is continuing to seek a solution.

> need to contact about what's needed for StackBuilder to know about

I reached Dave, and essentially StackBuilder will add our installer to
its list and invoke it with options selected, but providing and building
said installer would be PL/Java's job. That'll take making buildfarm
arrangements somewhere to do official MSVC and OS X builds, and developing
a more traditional does-it-all-for-you style of installer, perhaps
built on something like IzPack.

Short of that, it would be nice to see about at least getting
a Homebrew formula and MacPorts port, as options for OS X sites,
especially as El Capitan dropping the openssl headers made the
source build more fiddly.

-Chap

---

From: John R Pierce <[email-masked]>
Date: Fri, 12 Feb 2016 23:08:34 -0800

> built on something like IzPack.

cool.

I see there is a maven for windows, thats a good start :)
https://maven.apache.org/guides/getting-started/windows-prerequisites.html

I guess you'll need to deal with the free visualC compiler commands for 
the C bit to build.   there is a free MS VisualC++ command line compiler 
buried in this IDE package, which should build dll's compatible with the 
eDB postgres binaries....
https://www.visualstudio.com/products/visual-studio-community-vs

I'm surprised stackbuilder doesn't have some simple built-in way to copy 
a few files?    the native Windows method of installing stuff is to 
package it as a .msi file.   I remembering having to wade pretty deep to 
get those working.  freeware msi builder of possible interest: 
http://scalablesmartpackager.com/smart-packager-ce-free-msi-packager/

-- 
john r pierce, twisting bits in santa cruz