Re: Edit Grid and default_transaction_read_only

On 06/28/2012 03:11 AM, Guillaume Lelarge wrote:

>> What sounds like the smartest way to make updates in the Edit Grid
>> not fail when connected to a database with default_transaction_read_only
>> set to on? :
>>
>> 1. Just send every update wrapped in START TRANSACTION READ WRITE
>> ... COMMIT ?
>>...
>
> Well, if the DBA sets default_transaction_read_only to off, it's for a
> good reason.

Well, it is for a good reason, but the reason is concurrency
optimization and convenience for manually typed SQL. The backend
can optimize locking if you tell it which transactions will or won't
change anything, by always typing START TRANSACTION READ WRITE or
START TRANSACTION READ ONLY. But that's a lot of typing, so there's
a way to set what the backend assumes you intend when you just
type BEGIN or START TRANSACTION without saying what type of
transaction.

For a program, like the Edit Grid, there just isn't any reason
not to always give an accurate START TRANSACTION READ ONLY or START
TRANSACTION READ WRITE depending on whether the transaction contains
changes. Then you get the appropriate locking behavior no matter
how the default is set, and you don't have goofy failures if the
default happens to be set read only. The purpose of the default is
not to stop you updating things. It's just about letting the backend
know when you are going to and when you're not.

> So, we shouldn't override this by default. It would be
> better that the edit tool finds that default_transaction_read_only is
> on, and asks if the user wants to turn it off so that he could make
> INSERT/DELETE/UPDATE.

On philosophical grounds I don't think that's necessary; it seems to
be treating a default setting for transaction characteristics as if
it had some permission-like meaning. Permissions are a different
consideration. If you wanted to set something up that couldn't be
updated without the user taking extra action, you could do it by
granting update permission only to a different role and making the
user switch roles.

On the other hand, if on technical grounds it's easier to hack in
a check and SET SESSION ... up front than it would be to get the
Edit Grid operations to be wrapped in accurate START TRANSACTION
statements, then whatever's easy is probably ok.

-Chap