#1 2016-04-15 03:54:45

senthil.sundar
Member
Registered: 2016-04-15

Read-only transaction error - stored procedure execute - Postgresql

I have created a stored procedure in postgresql database and am calling this from my jasper report and have set the fields in report.
However since a temp table is created during execution, the report fails when ran from report server with the error below.
org.postgresql.util.PSQLException: ERROR: cannot execute DROP TABLE in a read-only transaction Where: SQL statement "DROP TABLE IF EXISTS msg_stats"

How to fix this error? is there a configuration change i could make to allow reportserver execute the stored procedure call?
Am using a JDBC datasource and passing readonly=true as part of the JDBC URL did not help.
Please advise, thanks.

CREATE OR REPLACE FUNCTION app_summary(
    IN startdate timestamp without time zone,
    IN enddate timestamp without time zone,
    OUT param1 character varying,
    OUT param2 character varying,
    OUT param3 bigint,
    OUT param4 bigint,
    OUT param5 bigint,
    OUT param6 bigint,
    OUT param7 bigint,
    OUT param8 bigint,
    OUT param9 bigint,
    OUT param10 bigint,
    OUT param11 bigint,
    OUT param12 bigint)
  RETURNS SETOF record AS
$BODY$
DECLARE
	rec RECORD;
BEGIN
	DROP TABLE IF EXISTS msg_stats;
	CREATE TEMP TABLE msg_stats (param1 varchar(100),
					param2 varchar(20),
					param3 bigint,
					param4 bigint, 
					param5 bigint,
					param6 bigint, 
					param7 bigint,
					param8 bigint, 
					param9 bigint,
					param10 bigint, 
					param11 bigint, 
					param12 bigint);
	insert into msg_stats(param1,param2, param3,
				param4 , 
					param5 ,
					param6 , 
					param7 ,
					param8 , 
					param9 ,
					param10, 
					param11, 
					param12) (select c.param1 param1, c.param2 param2, 
			sum(case when a.messagetype='D' then 1 else 0 end) param3,
			sum(case when a.messagetype='MMixed' then 1 else 0 end) param4,
			sum(case when a.messagetype='Merge' then 1 else 0 end) param5,
			sum(case when a.messagetype='MMessage' then 1 else 0 end) param6,
			sum(case when a.messagetype='MApp' then 1 else 0 end) param7,
			sum(case when a.messagetype='NRepeat' then 1 else 0 end) param8,
			sum(case when a.messagetype='LRepeat' then 1 else 0 end) param9,
			sum(case when a.messagetype='Expiring' then 1 else 0 end) param10,
			from table2 c
			inner join  table4 b on c.username=b.pid 
			inner join table5 a on a.pid=b.pid and a.datereceived >=  $1 and a.datereceived <= $2
			group by c.username);
	for rec in select a.pid p1, p.pname p2, count(b.activated) activations from 
					pharmacies p
					inner join table4 a on p.username=a.pid
					inner join table5 b on a.pid=b.id 
					and b.activated=True and b.datecreated >=  $1 and b.datecreated <= $2
					group by a.pid, p.pname  
		LOOP
			IF EXISTS (select * from msg_stats where msg_stats.pid=rec.pid) THEN
				update msg_stats set param11=rec.activations where msg_stats.pid=rec.pid;
			ELSE
				insert into msg_stats(pid, pname, param11) values (rec.pid, rec.pname, rec.activations);
			END IF;
		END LOOP;
	for rec in select b.pid pid, p.pname, count(1) totalactivations from pharmacies p
				inner join table4 b on p.uname=b.pid
				inner join table5 c on b.id=c.pid 
				and c.activated=True group by b.pid, p.pname
		LOOP
			IF EXISTS (select * from msg_stats where msg_stats.pid=rec.pid) THEN
				update msg_stats set param12=rec.totalactivations where msg_stats.pid=rec.pid;
			ELSE
				insert into msg_stats(pid, pharmacyname, param12) values (rec.pid, rec.pname, rec.totalactivations);
			END IF;
		END LOOP;
	RETURN QUERY SELECT * FROM msg_stats;
END
$BODY$
  LANGUAGE plpgsql;

Offline

#2 2016-04-15 06:49:07

Arno Mittelbach
datenwerke
Registered: 2012-02-14

Re: Read-only transaction error - stored procedure execute - Postgresql

Hi Senthil,

welcome to the ReportServer forums.

With connections given to reporting engines we have, indeed, taken the conservative road and made them read-only by default. Regrettably, there is also no easy config switch that allows to change this. This means that we need to be a bit creative to solve this. In other words we need some scripting. The downside is that the following solution only works with ReportServer Enterprise.

The way ReportServer handles mapping of datasources to reporting engines is via a transformer layer that on the one end gets a ReportServer datasource object and on the other outputs whatever the reporting engine expects. In the case of jasper this would be a JasperDBDataSource object. Now, what we can do is to hook into this transformer layer and add our own transformer. Following is the necessary script.

import java.sql.Connection;

import com.google.inject.Inject;

import net.datenwerke.dbpool.DbPoolService;
import net.datenwerke.dbpool.config.predefined.StandardConnectionConfig;
import net.datenwerke.rs.base.service.datasources.definitions.DatabaseDatasource;
import net.datenwerke.rs.base.service.datasources.transformers.DataSourceDefinitionTransformer;
import net.datenwerke.rs.base.service.reportengines.jasper.JasperDBDataSource;
import net.datenwerke.rs.core.service.datasourcemanager.entities.DatasourceContainer;
import net.datenwerke.rs.core.service.datasourcemanager.entities.DatasourceContainerProvider;
import net.datenwerke.rs.core.service.reportmanager.exceptions.DatabaseConnectionException;
import net.datenwerke.rs.core.service.reportmanager.parameters.ParameterSet;
import net.sf.jasperreports.engine.JRDataSource;
import net.datenwerke.hookhandler.shared.hookhandler.HookHandlerService;

def transformer = [
	consumes : {containerProvider, dst ->
		def container = containerProvider.getDatasourceContainer();
		return (null != container && container.getDatasource() instanceof DatabaseDatasource && dst.isAssignableFrom(JasperDBDataSource.class));
	},
	transform :{containerProvider, dst, parameters ->
		def container = containerProvider.getDatasourceContainer();
		def ds = container.getDatasource();
		
		def dbPoolService = GLOBALS.getInstance(DbPoolService.class);
		
		/* open connection */
		try {
			def conn = dbPoolService.getConnection(ds.getConnectionConfig(), new StandardConnectionConfig()).get();
			return new JasperDBDataSource(conn);
		} catch (Exception e) {
			def dce = new DatabaseConnectionException(ds.getUrl(), ds.getUsername(),e);
			throw dce;
		}
	}
] as DataSourceDefinitionTransformer;

def HOOK_NAME = "READ_WRITE_DB_2_JASPER_TRANSFOMER"
GLOBALS.services.callbackRegistry.forceRegisterHook(HOOK_NAME, DataSourceDefinitionTransformer.class,transformer)
GLOBALS.getInstance(HookHandlerService.class).attachHooker(DataSourceDefinitionTransformer.class, transformer, HookHandlerService.PRIORITY_HIGH)

return HOOK_NAME;

When you execute the script (place it somewhere beneath the bin folder in the fileserver, open the terminal with CTRL+ALT+T, go to the parent folder via "cd PATH", and execute the script via "exec SCRIPT") this will hook our custom transformer into the transformer queue and overwrite the standard transformer. The key point is where the connection is created where we tell the pool service not to create a read-only connection (the StandardConnectionConfig does that).

Finally, if you want this transformer to be always active, you can place the script in the bin/onstartup.d folder which tells ReportServer to execute it on startup.

Hope this helps,

Cheers,
Arno

Offline

#3 2017-05-24 10:47:09

cchris26
Member
From: Munich
Registered: 2016-05-09

Re: Read-only transaction error - stored procedure execute - Postgresql

Hi Arno,

I have the same problem with a BIRT report. Can you please provide the respective script for a BIRT data source? Thanks a lot.

Greetings,
Cristian

Offline

#4 2017-05-31 07:56:11

eduardo
Administrator
Registered: 2016-11-01
Website

Re: Read-only transaction error - stored procedure execute - Postgresql

Hi Cristian,

we are working on this and will update here.

Regards,
Eduardo

Offline

#5 2017-06-06 14:25:43

Rodion
Member
Registered: 2017-05-17

Re: Read-only transaction error - stored procedure execute - Postgresql

Hi Christian,
here is the code for your BIRT-issue

import java.sql.Connection;

import net.datenwerke.dbpool.DbPoolService;
import net.datenwerke.dbpool.config.predefined.StandardConnectionConfig;
import net.datenwerke.hookhandler.shared.hookhandler.HookHandlerService;
import net.datenwerke.rs.base.service.datasources.definitions.DatabaseDatasource;
import net.datenwerke.rs.base.service.datasources.transformers.DataSourceDefinitionTransformer;
import net.datenwerke.rs.core.service.datasourcemanager.entities.DatasourceContainer;
import net.datenwerke.rs.core.service.datasourcemanager.entities.DatasourceContainerProvider;
import net.datenwerke.rs.core.service.reportmanager.exceptions.DatabaseConnectionException;
import net.datenwerke.rs.core.service.reportmanager.exceptions.UnsupportedDriverException;
import net.datenwerke.rs.core.service.reportmanager.parameters.ParameterSet;
import net.datenwerke.rs.scripting.service.scripting.scriptservices.CallbackRegistry;



def CreateBirtRW_Object = [
	consumes : {containerProvider, dst ->
		def container = containerProvider.getDatasourceContainer();
		return (null != container && container.getDatasource() instanceof DatabaseDatasource && dst.isAssignableFrom(Connection.class));
	},
	transform :{containerProvider, dst, parameters ->
		def container = containerProvider.getDatasourceContainer();
		def ds = (DatabaseDatasource) container.getDatasource();
		
		def dbPoolService = GLOBALS.getInstance(DbPoolService.class);
		
		/* open connection */
		try {
			def conn = dbPoolService.getConnection(ds.getConnectionConfig(), new StandardConnectionConfig()).get();
			return conn;
		} catch (Exception e) {
			def dce = new DatabaseConnectionException(ds.getUrl(), ds.getUsername(),e);
			throw dce;
		}
	}
] as DataSourceDefinitionTransformer;

		def HOOK_NAME = "READ_WRITE_DB_2_BIRT_TRANSFOMER";

		GLOBALS.services.callbackRegistry.forceRegisterHook(HOOK_NAME,DataSourceDefinitionTransformer.class,CreateBirtRW_Object); 
		GLOBALS.getInstance(HookHandlerService.class).attachHooker(DataSourceDefinitionTransformer.class, CreateBirtRW_Object, HookHandlerService.PRIORITY_HIGH);

return HOOK_NAME;

Greetings,
Rodion

Offline

#6 2017-06-19 14:34:27

cchris26
Member
From: Munich
Registered: 2016-05-09

Re: Read-only transaction error - stored procedure execute - Postgresql

Thanks a lot! It works well.

Offline

#7 2018-10-11 07:56:48

zach.mathew
Member
Registered: 2015-04-17

Re: Read-only transaction error - stored procedure execute - Postgresql

Is there no workaround for using Jasper and stored procedures with ReportServer Community Edition ?

Offline

#8 2018-10-16 07:30:27

eduardo
Administrator
Registered: 2016-11-01
Website

Re: Read-only transaction error - stored procedure execute - Postgresql

Hi Zach,

I see on your other thread (https://forum.reportserver.net/viewtopic.php?id=1239)  that you get the same error: Caused by: org.postgresql.util.PSQLException: ERROR: cannot execute CREATE TABLE AS in a read-only transaction
Please try the script in this thread with the reportserver enterprise evaluation.

Regards,
Eduardo

Offline

#9 2019-09-19 10:31:22

asmirnov
Member
Registered: 2018-06-05

Re: Read-only transaction error - stored procedure execute - Postgresql

Can you, please, provide  the same for dynamic list report?

Offline

Board footer

Powered by FluxBB