Re: transaction is read-only error

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
>