Re: read only transactions

Chris Browne <cbbrowne(at)acm(dot)org> writes:
> jnelson+pgsql(at)jamponi(dot)net (Jon Nelson) writes:
>> Are there any performance implications (benefits) to executing queries
>> in a transaction where
>> SET TRANSACTION READ ONLY;
>> has been executed?

> Directly? No.

> Indirectly, well, a *leetle* bit...

> Transactions done READ ONLY do not generate actual XIDs, which reduces
> the amount of XID generation (pretty tautological!), which reduces the
> need to do VACUUM to protect against XID wraparound.

You're right that a read-only transaction doesn't generate an XID.
But that is not a function of whether you do SET TRANSACTION READ ONLY;
it's a function of refraining from attempting any database changes.
The SET might be useful for clarifying and enforcing your intent, but
it's not a performance boost to use it, versus just doing the read-only
transaction without it.

Also, I believe that SET TRANSACTION READ ONLY isn't a "hard" read only
restriction anyway --- it'll still allow writes to temp tables for
example, which will cause assignment of an XID.

regards, tom lane