On Wed, May 30, 2012 at 7:00 PM, Stephen Frost <sfrost@snowman.net> wrote:
> Robert,
>
> * Robert Haas (robertmhaas@gmail.com) wrote:
>> On Wed, May 30, 2012 at 9:10 PM, Sergey Koposov <koposov@ast.cam.ac.uk> wrote:
>> > I understand the need of significant locking when there concurrent writes,
>> > but not when there only reads. But I'm not a RDBMS expert, so that's maybe
>> > that's misunderstanding on my side.
>>
>> If we knew in advance that no writes would come along during the
>> execution of a particular test case, then we could skip a lot of
>> locking on the reads. But we don't, so we have to be prepared for the
>> possibility of writes at any time, which means doing things taking
>> share-locks on data while it's actively being read.
>
> Uh, we have a read-only transaction mode, don't we? Or does that not
> help, because someone else, in another transaction, could take a
> read-write lock?
Yes, exactly. You would have to put the entire cluster into a read
only mode, and the entire code base almost from top to bottom would
have to be rewritten to detect and support such a mode. Even then,
either you have a unified buffer cache (which needs locking even if
the queries are read only), or each process uses its own local memory.In the latter case, how do you avoid
oversubscribingphysical memory?Also, there are certain house-keeping tasks that can take place
anytime, even in read-only transactions. You would either need to
turn those off, or you would need to make a pass through the entire
system doing all the house-keeping up front during the transition from
read-write to read-only.
It would basically be like writing an entirely new database management
system, which just happens to share PG's on-disk format and PG's
flavor of SLQ syntax and semantics. And is read only. (And since the
OP is trying to create tables based on his read-only query, such a
system wouldn't be very well suited.)
Cheers,
Jeff