Thread: Edit Grid and default_transaction_read_only

Hi,

I've never hacked on pgAdmin yet, so I'm curious from those who have:

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 ?

2. Don't do that by default, but trap the "error: transaction is
    read only" and reissue the update with START TRANSACTION READ WRITE
    in that case?

3. Query the backend for SHOW DEFAULT_TRANSACTION_READ_ONLY and then
    issue updates wrapped in START TRANSACTION READ WRITE only if it
    was ON?

4. Just send a SET SESSION DEFAULT_TRANSACTION_READ_ONLY TO OFF and
    forget about it?

I'm asking because we noticed in 1.14.2 that it doesn't do any of
these things yet, but just surprises the innocent user with the
"error: transaction is read only".

Thanks,
Chapman Flack
Dept. of Mathematics, Purdue

On Wed, 2012-06-20 at 09:55 -0400, J Chapman Flack wrote:
> Hi,
>
> I've never hacked on pgAdmin yet, so I'm curious from those who have:
>

Sorry for not answering sooner. Way overloaded here.

> 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 ?
>
> 2. Don't do that by default, but trap the "error: transaction is
>     read only" and reissue the update with START TRANSACTION READ WRITE
>     in that case?
>
> 3. Query the backend for SHOW DEFAULT_TRANSACTION_READ_ONLY and then
>     issue updates wrapped in START TRANSACTION READ WRITE only if it
>     was ON?
>
> 4. Just send a SET SESSION DEFAULT_TRANSACTION_READ_ONLY TO OFF and
>     forget about it?
>
> I'm asking because we noticed in 1.14.2 that it doesn't do any of
> these things yet, but just surprises the innocent user with the
> "error: transaction is read only".
>

Well, if the DBA sets default_transaction_read_only to off, it's for a
good reason. 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.


--
Guillaume
http://blog.guillaume.lelarge.info
http://www.dalibo.com

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