> While I don't doubt your analysis is correct for the case you've
> uncovered, it doesn't explain why surrounding a bunch of selects
> with a begin/end block greatly descreases disk activity for tables
> that don't change. I'm pulling out "select" lists (html <select>)
> from small tables of counties, states, countries for the project
> I'm working on. The two countries, for instance, are "USA" and
> "CA" and the table's not been updated in two months :). I'm
> building a form and doing a very simple "select * from county_locales"
> type selects, then building a <select> list containing all of the
> possible values (not as many as you might think, this project
> involves only the Pacific Northwest). There are several of
> these selects executed for each form. Without the transaction
> block, there's a lot of disk activity. With it, much less.
>
> I can go pull out the begin/end blocks, they're conditionalized
> in my Tcl scripts based on a "postgres" predicate so they'll
> disappear if I migrate the database to another engine. Maybe
> I'll have time this afternoon, if you'd like me to confirm, I'm
> going to a brunch right now...
PostgreSQL writes into pg_log each time a transaction gets committed
even if it is a read only one. Once whatever file writings happen in
the transaction, fsync() would be forced at the commit time. Probably
that's why you observe less disk activity when you surround some
selects in begin/end blocks.
By the way, may I ask more question regarding Oracle? You mentioned
the magic of no-fsync in Oracle is actually a bug. Ok, I understand. I
also heard that Oracle does some kind of redo-log bufferings. Does
this mean certain committed data might be lost if the system crashed
before the buffered data is written into the disk?
---
Tatsuo Ishii