Обсуждение: 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 >