From: | Salil Wadnerkar <rohshall(at)gmail(dot)com> |
---|---|
To: | Josh Kupershmidt <schmiddy(at)gmail(dot)com> |
Cc: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: transaction is read-only error |
Date: | 2010-09-20 10:33:51 |
Message-ID: | AANLkTimfs37EhkeavmxDTdFcByzXK=eyamQJZwUNbejC@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
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(at)gmail(dot)com> wrote:
> On Fri, Sep 17, 2010 at 6:14 AM, Salil Wadnerkar <rohshall(at)gmail(dot)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
>
From | Date | Subject | |
---|---|---|---|
Next Message | Thomas Uzunoff | 2010-09-20 14:31:15 | Re: PostgreSQL article online - PDF |
Previous Message | Josh Kupershmidt | 2010-09-17 15:57:39 | Re: transaction is read-only error |