Re: [Gnumed-devel] Corrupt database

[Jerzy Luszawski]

On 01-06-2013 10:53 Karsten Hilbert <address@hidden> wrote:

> On Sat, Jun 01, 2013 at 08:44:02AM +0200, Jerzy Luszawski wrote:
> 
> > > > 2. How to drop a readonly table 
> > > 
> > > set default_transaction_read_only to off;
> > > drop table ...;
> > 
> > If I understand this option correctly, using it is insecure, as any
> > transaction (may be from all over the world) started before resetting
> > it to ON becomes read-write.
> 
> *started before* ?   I can't believe that to be true.

... started before RESETTING (this option) TO ON.

> 
> To my knowledge it affect transactions started *after*
> issuing this WITHIN THIS CONNECTION ONLY.
This is database-wide option and is preserved between
connections, which you can easily test.
> 
> Maybe I am wrong ?
> 
>       default_transaction_read_only (boolean)
> 
>       A read-only SQL transaction cannot alter non-temporary
>       tables. This parameter controls the default read-only
>       status of each new transaction. The default is off (
>       read/write).
> 
> [...] of each NEW transaction [...]
> 
> Now, when a new connection is made it will read several
> sources to determine the state: postgresql.conf, database
> configuration (which is what GNUmed sets at bootstrap and
> checks at startup), user configuration. So, new transactions
> very likely have it to ON (unless a user stores a different
> default for themselves).
> 
> All that does not say anything about NEW transactiosn in
> EXISTING connections but I am really hard-pressed to believe
> that that should be the case because PostgreSQL is coded
> extremely conservative.

Imagine the case when you have set default_transaction_read_only to OFF
and have long running transaction. Until you reset
default_transaction_read_only to ON at the end of your long
transaction, every new transaction is RW. (I'm not postgres expert
though, and may be wrong)

> 
> > That's why I use 
> > 
> > set session characteristics as transaction READ WRITE ;
> 
> Nonetheless, this is the safer option, indeed !
> 
> (Because it applies to the one transaction one runs it in ONLY.)

Not transaction but SESSION. You can have many transactions within a
session (connection).


-- 
Regards,
Jerzy Luszawski