Thread: transaction is read-only error

Hi,

I am experimenting with GNUMed EMR which uses a Mirth (HL7 engine)
channel to write data into postgreSQL table (JDBC) and I am getting
the error - "transaction is read-only".
I think it is because "default_transaction_read_only" is set to true
for this database.
The javascript (Mirth uses Javascript which it conerts to Java I
think) for this is:

var dbConn =
DatabaseConnectionFactory.createDatabaseConnection('org.postgresql.Driver','jdbc:postgresql://localhost:5432/gnumed_v10','gm-dbo','gm-dbpass');

[some code to calculate the update parameters]


       sqlquery = "INSERT INTO clin.incoming_data_unmatched
(request_id, firstnames, lastnames, dob, postcode, other_info, gender,
requestor, data) VALUES ('" + request_id + "', '" +
$('patient_firstname') + "', '" + $('patient_lastname') + "', '" +
$('patient_dob') + "', '" + postcode + "', '" + other_info + "', '" +
$('patient_gender') + "', '" + requestor + "', '" + msg + "')";
       result = dbConn.executeUpdate(sqlquery);


This last line generates the error. So, I gave superuser permission to
this user gm-dbo because I think non-superusers cannot change
"default_transaction_read_only" to true.
And I added the following lines before this update command:

       // Salil: added alter database command
       sqlquery = "alter database gnumed_v10 set
default_transaction_read_only to off";
       result = dbConn.executeUpdate(sqlquery);


But, PostgreSQL generates the error for the executeUpdate of this
newly added command now.

How should I go about updating the table in this database?


regards
Salil

On Tue, Sep 14, 2010 at 5:31 AM, Salil Wadnerkar <rohshall@gmail.com> wrote:
[snip]
> This last line generates the error. So, I gave superuser permission to
> this user gm-dbo because I think non-superusers cannot change
> "default_transaction_read_only" to true.

I think any user should be able to issue:
  SET TRANSACTION READ WRITE;
at the beginning of a transaction, have you tried this?

Josh

Hi Josh,

Thanks for replying. I tried the "set transaction" command. I still
get the "transaction is read-only" error when I issue the "insert
into" command.
If there is no other solution, how do I set the value of the variable
"default_transaction_read_only" to false from the admin console?

regards
Salil


On Wed, Sep 15, 2010 at 6:04 AM, Josh Kupershmidt <schmiddy@gmail.com> wrote:
> On Tue, Sep 14, 2010 at 5:31 AM, Salil Wadnerkar <rohshall@gmail.com> wrote:
> [snip]
>> This last line generates the error. So, I gave superuser permission to
>> this user gm-dbo because I think non-superusers cannot change
>> "default_transaction_read_only" to true.
>
> I think any user should be able to issue:
>  SET TRANSACTION READ WRITE;
> at the beginning of a transaction, have you tried this?
>
> Josh
>

On Fri, Sep 17, 2010 at 6:14 AM, Salil Wadnerkar <rohshall@gmail.com> wrote:
> Hi Josh,
>
> Thanks for replying. I tried the "set transaction" command. I still
> get the "transaction is read-only" error when I issue the "insert
> into" command.
> If there is no other solution, how do I set the value of the variable
> "default_transaction_read_only" to false from the admin console?

How about trying this using the psql client to connect to your database:

  BEGIN;
     SET TRANSACTION READ WRITE;
     -- try your insert statement here
  COMMIT;

As for permanently turning off default_transaction_read_only, you can
do it either with an ALTER DATABASE or by editing postgresql.conf,
setting default_transaction_read_only = off, and restarting or
reloading the server. You said the ALTER DATABASE didn't work before,
but maybe that was because you tried the ALTER DATABASE inside a
transaction? Either way.. if you're still having problems, use psql
directly and post exactly what you entered and what error
messages/other output you see.

Use these commands:
  SHOW default_transaction_read_only;
  SELECT name, setting, context, source FROM pg_settings WHERE name =
'default_transaction_read_only';

to help troubleshoot further.

Josh

Hi Josh,

Thanks a lot for your help. I used psql to set the transaction to
"read write" mode and it worked. So, my problem reduced to finding out
why it does not work with JDBC. I googled on the relationship between
JDBC connection and database transaction and came to know that:
"When a connection is created, by default it is in the auto-commit
mode. This means that each individual SQL statement is treated as a
transaction by itself, and will be committed as soon as it's execution
finished. ".
So, basically in the JDBC script, my "set transaction" command was
being executed in a separate transaction and the following "update" in
another. So, no wonder the update was not working. When I grouped all
the statements together by setting "auto commit" to false, it worked
like a charm.

Thanks so much.

regards
Salil


On Fri, Sep 17, 2010 at 11:57 PM, Josh Kupershmidt <schmiddy@gmail.com> wrote:
> On Fri, Sep 17, 2010 at 6:14 AM, Salil Wadnerkar <rohshall@gmail.com> wrote:
>> Hi Josh,
>>
>> Thanks for replying. I tried the "set transaction" command. I still
>> get the "transaction is read-only" error when I issue the "insert
>> into" command.
>> If there is no other solution, how do I set the value of the variable
>> "default_transaction_read_only" to false from the admin console?
>
> How about trying this using the psql client to connect to your database:
>
>  BEGIN;
>     SET TRANSACTION READ WRITE;
>     -- try your insert statement here
>  COMMIT;
>
> As for permanently turning off default_transaction_read_only, you can
> do it either with an ALTER DATABASE or by editing postgresql.conf,
> setting default_transaction_read_only = off, and restarting or
> reloading the server. You said the ALTER DATABASE didn't work before,
> but maybe that was because you tried the ALTER DATABASE inside a
> transaction? Either way.. if you're still having problems, use psql
> directly and post exactly what you entered and what error
> messages/other output you see.
>
> Use these commands:
>  SHOW default_transaction_read_only;
>  SELECT name, setting, context, source FROM pg_settings WHERE name =
> 'default_transaction_read_only';
>
> to help troubleshoot further.
>
> Josh
>