On 12/5/2011 3:41 PM, John R Pierce wrote:
On 12/05/11 1:34 PM, C. Mundi wrote:
So that's my concern. I'm doing 80% reads which are all non-blocking
with 20% writes mixed in, and I need to avoid the effect of writes
blocking queries which do not need to traverse branches affected by
the write.
postgres does no blocking on inserts/updates. the commonest lock is if
you're doing a transaction, and need to select something prior to
updating it, then you use a SELECT ... FOR UPDATE; this locks just the
rows you're going to update so noone else can update them (but other
clients can still read the existing value prior to your COMMIT).
As an addition to this, Reads and Writes wont block each other, but
you'll need to watch the overlap if its a problem. There are many ways
to go about it depending on what you want (transaction isolation levels,
locking, etc).
In general, I think it might look like:
connection1:
start transaction
select * from table where the_geom && POINT(a b)
connection2:
start transaction
update table set the_geom = POLYGON(a b c d) where rowid = 5;
connection1: (in the same transaction it started above)
select the_geom from table where rowid = 5;
-- gets the origional geom, NOT the one from connection2!
There are transaction options for read committed, read un-committed,
etc, etc. I don't rightly understand them all, but it sounds like
you'll want to.
> traverse branches affected by the write
I assume that's a reference to building an underlying tree structure.
You wont need to worry about it. On the other hand, if that's a
reference to some geo-boxing thing where one row is included in another
and you need to update multiple rows, and I'm starting to confuse
myself, then you might have a problem.
Also, as John points out, you'll want a connection pooler. I've heard
good things about pgPool. It'll also spread read's across multiple
computers just incase you need a faster response. (writes go to all
computers, read's round-robin).
-Andy
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
