my problem is related to Jasperserver 5.1.0 from WAR file installation + Postgresql 9.1 +Tomcat 7 on Ubuntu 12.04 LTE
I have a report that uses a plpgsql function. This function uses temporary tables. It runs properly in iReport but fails when connected to jasperserver report on Tomcat.
The error message is:
org.postgresql.util.PSQLException: ERROR: cannot execute DROP TABLE in a read-only transaction
I knew this problem: in Jasperserver 4.7 + Postgresql 8.3 I successfuly used a workaround for it: I'd place 'set transaction read write;' at the beginning of the function.
but now when I place this statement in a function, another error comes out:
org.postgresql.util.PSQLException: ERROR: transaction read-write mode must be set before any query Gdzie: SQL statement "set transaction read write"
Ghrrrr ... ;) And again: no problem when the function is called from cmdline, it fails just in jasper report.
btw. 'defaultReadOnly=false' statements in context.xml and/or in postgresql connection string don't work
----- PROBLEM SOLVED -----
I got it! I misunderstood the concept of connecting via JDBC directly vs connecting by JNDI!
I created a JNDI connection dedicated for the database (I'd previously connected by JDBC) in context.xml and the problem is solved!
Another option is to set the JDBC flag to false.
In your datasource, add this to the JDBC url:
With version 6, you can use attributes to get dynamic datasource URLs, which we use, so JNDI was not an option.
There is also an article here with a server level configuraiton approach (I haven't tried it)