Date: Wed, 27 Sep 2006 17:28:57 -0500
From: "Jim C. Nasby" <jim@nasby.net>
To: Matthew Schumacher <matt.s@aptalaska.net>
Cc: pgsql-performance@postgresql.org
Subject: Re: Problems with inconsistant query performance.
Message-ID: <20060927222857.GC19827@nasby.net>
References: <451AC4E2.90106@aptalaska.net> <20060927203155.GT19827@nasby.net>
	<451AEE15.6090103@aptalaska.net> <20060927214221.GA19827@nasby.net>
	<451AF873.1010306@aptalaska.net>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline
In-Reply-To: <451AF873.1010306@aptalaska.net>
User-Agent: Mutt/1.5.11

On Wed, Sep 27, 2006 at 02:17:23PM -0800, Matthew Schumacher wrote:
> Jim C. Nasby wrote:
> >  
> > It can cause a race if another process could be performing those same
> > inserts or updates at the same time.
> 
> There are inserts and updates running all of the time, but never the
> same data.  I'm not sure how I can get around this since the queries are
> coming from my radius system which is not able to queue this stuff up
> because it waits for a successful query before returning an OK packet
> back to the client.
> 
> > 
> > I know the UPDATE case can certainly cause a race. 2 connections try to
> > update, both hit NOT FOUND, both try to insert... only one will get to
> > commit.
> 
> Why is that?  Doesn't the first update lock the row causing the second
> one to wait, then the second one stomps on the row allowing both to
> commit?  I must be confused....

What if there's no row to update?

Process A               Process B
UPDATE .. NOT FOUND
                        UPDATE .. NOT FOUND
                        INSERT
INSERT blocks
                        COMMIT
UNIQUE_VIOLATION

That's assuming that there's a unique index. If there isn't one, you'd
get duplicate records.

> > I think that the UNIQUE_VIOLATION case should be safe, since a second
> > inserter should block if there's another insert that's waiting to
> > commit.
> 
> Are you saying that inserts inside of an EXCEPTION block, but normal
> inserts don't?

No... if there's a unique index, a second INSERT attempting to create a
duplicate record will block until the first INSERT etiher commits or
rollsback.

> > DELETEs are something else to think about for both cases.
> 
> I only do one delete and that is every night when I move the data to the
> primary table and remove that days worth of data from the tmp table.
> This is done at non-peak times with a vacuum, so I think I'm good here.

Except that you might still have someone fire off that function while
the delete's running, or vice-versa. So there could be a race condition
(I haven't thought enough about what race conditions that could cause).

> > If you're certain that only one process will be performing DML on those
> > tables at any given time, then what you have is safe. But if that's the
> > case, I'm thinking you should be able to group things into chunks, which
> > should be more efficient.
> 
> Yea, I wish I could, but I really need to do one at a time because of
> how radius waits for a successful query before telling the access server
> all is well.  If the query fails, the access server won't get the 'OK'
> packet and will send the data to the secondary radius system where it
> gets queued.
 
In that case, the key is to do the absolute smallest amount of work
possible as part of that transaction. Ideally, you would only insert a
record into a queue table somewhere, and then periodically process
records out of that table in batches.

> Do you know of a way to see what is going on with the locking system
> other than "select * from pg_locks"?  I can't ever seem to catch the
> system when queries start to lag.

No. Your best bet is to open two psql sessions and step through things
in different combinations (make sure and do this in transactions).
-- 
Jim Nasby                                            jim@nasby.net
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)