Re: Problems with inconsistant query performance.

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....

>
> 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?

>
> 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.

>
> 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.

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.

Thanks again,
schu