On Thu, 20 Nov 2008 20:24:42 +0900
Craig Ringer <craig@postnewspapers.com.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