Re: CLUSTER, REINDEX, VACUUM in "read only" transaction?

On Tue, 2008-10-14 at 19:18 +0300, Peter Eisentraut wrote:
> Tom Lane wrote:
> > So I was looking for other omissions in utility.c, and I noticed that
> > check_xact_readonly() doesn't reject CLUSTER, REINDEX, or VACUUM.
> > Now the notion of "read only" that we're trying to enforce is pretty
> > weak (I think it's effectively "no writes to non-temp tables").
> > But I can't see that CLUSTER is a read-only operation even under the
> > weakest definitions, and I'm not seeing the rationale for REINDEX or
> > VACUUM here either.
>
> I think the way the SQL standard meant the read-only flag is that the
> transaction doesn't change the structure of or the data in the database
> as seen by the next guy. So all of these commands are OK, I think.
>
> A theoretical use case is that you should be able to do the maximum set
> of useful work in read-only mode on a Slony-I slave. No I haven't
> checked what Slony does with these three commands, so let me have it. :-)

Well, read-only applies to queries on the Slony slave, not to other
necessary work, which cannot be read only.

In general, if one transaction is fully read-only I don't see why that
should prevent other parts of the system from working normally.

So I would say ban all the utilities mentioned from read-only
transactions, and don't be influenced by what non-read only transactions
do.

--
Simon Riggs www.2ndQuadrant.com
PostgreSQL Training, Services and Support