Transctional readOnly=true & saving objects in Grails and Postgres

1
votes

We are using Grails [Spring+Hibernate] and have one service method with @Transactional(readOnly=true). During development & tests we were using HSQLDB and the behaviour was such that we could use .save(flush:true) and object would be saved to the database, but all other objects would be rolled back. Everything was great accept for when we switched to Postgres. Now this settings throws back: ERROR - transaction is read only.

EDIT:

We have to be able to rollback all the data in the transaction but still be able to save the data we require to be saved [some additional stuff, not related to the transaction, like something for logging purposes]. It don't want to use any DB triggers to do that. We also don't want to get Stale Object Exceptions when saving stuff after the original request was served. Any ideas on how we could achieve that? The only thing that comes to my mind is to have some kind a registry where we would store references to modified objects and at the end drop all changes, but this is the last resort. I am sure there's a way to tackle this issue without doing stupid things.

3
hibernatespringpostgresqlgrails

3 Answers

2
votes

I think spring just calls connection.setReadOnly(true) and then it's up to the jdbc driver. Maybe HSQLDB jdbc takes that only as a hint to optimize something. Posgresql docs are clear on the read-only semantics:

When a transaction is read-only, the following SQL commands are disallowed: INSERT, UPDATE, DELETE, and COPY FROM if the table they would write to is not a temporary table; all CREATE, ALTER, and DROP commands; COMMENT, GRANT, REVOKE, TRUNCATE; and EXPLAIN ANALYZE and EXECUTE if the command they would execute is among those listed.

0
votes

milan. I work with Krystian and on this particular problem. I`ve tested all the propagation fields to solve it and none of them worked (PROPAGATION_REQUIRED, REQUIRES_NEW and NESTED looked worth trying).