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