On Mon, 28 Jul 2003, Josh Berkus wrote:
> Robert,
>
> > Aside from spec compliance, whats the bonus for having it then? Or put a
> > better way, why/when would I want to use this?
>
> One scenario: You have 5 PostgreSQL servers connecting to one SAN or NAS
> which is your /data directory for a single database. You use your
> middleware to distribute requests among the servers; One server gets data
> write requests, the other 4 get read-only requests.
>
> However, you want to make sure that if your middleware hiccups you don't
> corrupt the database files. For this, setting 4 of the servers to "Read Only
> Transactions" would be useful.
That will not work because the writer maintains a cache of data to write
and worse case scenario, the data only gets written to data files every
CHECKPOINT. This means that your four readers are returning corrupted
data. Moreover, the readers do not expect the data to change undernearth
them, as they maintain caches and would have no mechanism to invalidate
that cache upon external file system changes. Load balancing and
clustering is hard :P.
It is, however, generally used with READ UNCOMMITTED transactions. In
fact, SQL99 necessitates READ ONLY for READ UNCOMMITTED isolation level
(READ UNCOMMITTED allows you to see uncommitted data modifications).
Gavin