[Pgpool-general] General pgpool locking issues

[Tatsuo Ishii]

> We have been testing with pgpool for a little while here and have run into
> various locking (or lack there of) issues.
> 
> Our setup is 2 databases (pg v8.1.4) on seperate machines with all db
> connections connected via pgpool.  We have run into situations where we the
> 1st or 2nd database was not getting the update or insert fast enough, and a
> select query hitting pgpool at the same time causing the a "mismatch" error
> causing pgpool to degrade and stop replicating.
> 
> Although I assume this must be quite a normal issue, I do not see many
> people talking about it here.
> 
> Can someone give me a basic rundown of the "best practices" for pg locking
> on a pretty database intensive web app?
> 
> On Inserts the best practice is to lock the entire table (LOCK TABLE ...) ?
> 
> On Updates the best practice is to do a row lock (SELECT ... FOR UPDATE  or
> FOR SHARE) ?
> Should you do some sort of lock on SELECTS?
> Do you recommend everything be wrapped in Transactions?
> 
> Any advice is appreciated.
> 
> Thank you.

What you have seen is a table updating timing difference between
master and secondary as yo suspected. Although there's no way to avoid
the timing difference itself, I think there are some ways to avoid
this problem.

1) use load balancing. Since this will SELECT one of the servers,
   there will be no chance to see the "kind mismatch error".

2) use table locking. This will prevent the SELECT from seeing yet
   updated data in the secondary server.

I don't think SELECT FOR UPDATE will work, since the lock does not
block SELECT.
--
Tatsuo Ishii
SRA OSS, Inc. Japan