Re: transaction_read_only effects on performance, actual meaning (was: How to reduce impact of a query)

On Thu, 20 Nov 2008 20:24:42 +0900
Craig Ringer <craig(at)postnewspapers(dot)com(dot)au> wrote:

> Howard Cole wrote:

> > You'll be pleased to know that changing the transaction to read
> > only (SET TRANSACTION READ ONLY)
> > as you suggested seemed to prevent the server from slowing to a
> > halt.

> Actually, I'm mostly surprised by that. I primarily suggested
> issuing the command to ensure that if your transaction was doing
> UPDATes or similar via triggers or function side-effects you
> weren't aware of, the transaction would fail and help you
> pin-point the problem area.

> I'm quite curious about why setting the transaction to readonly
> helped its performance. Could it be to do with setting hint bits
> or something along those lines, perhaps? Anyone?

Function happens in transactions. I'd be curious to know if
declaring inside a function SET TRANSACTION READ ONLY has any
meaning/effect once you've declared that function stable/immutable.

> I would not have expected the following to work:

> CREATE TABLE a ( i serial primary key, j text );
>
> BEGIN;
>
> INSERT INTO a (j) VALUES ('oats'), ('wheat'), ('beans');
>
> SET transaction_read_only = 1;
>
> SELECT * FROM a;
>
> COMMIT;
>
>
>
> but it does.

Interesting. Thank you for pointing it out.

--
Ivan Sergio Borgonovo
http://www.webthatworks.it