Re: Problems with inconsistant query performance.

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)