Hi all,

One of the things which the AS3AP benchmark does is have multiple users
access a table with hash indexes on it.

With the OSDB (Open Source Database Benchmark: http://osdb.sf.net) we've
found on PG 7.1 that multiple clients hitting a table using a hash index
generates locking problems.  I remember Tom mentioning that this is a
known thing, but I'm not sure if this has been fixed since then.

Does anyone have any ideas?  If not, would someone be willing to take
the time to fix it?

:-)

Regards and best wishes,

Justin Clift

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi

Wed, 06 Mar 2002 13:20:21 +1100
Justin Clift <justin@postgresql.org>
Do we still have locking problems with concurrent users of hash
	tables?
Justin Clift wrote:
> Hi all,
> 
> One of the things which the AS3AP benchmark does is have multiple users
> access a table with hash indexes on it.
> 
> With the OSDB (Open Source Database Benchmark: http://osdb.sf.net) we've
> found on PG 7.1 that multiple clients hitting a table using a hash index
> generates locking problems.  I remember Tom mentioning that this is a
> known thing, but I'm not sure if this has been fixed since then.
> 
> Does anyone have any ideas?  If not, would someone be willing to take
> the time to fix it?

It has not been fixed.  One TODO item is to either stop mentioning hash
at all or get it improved.  We have been sitting on the fence for too
long.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 5 Mar 2002 21:59:51 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Do we still have locking problems with concurrent users
On Tue, 2002-03-05 at 21:59, Bruce Momjian wrote:
> Justin Clift wrote:
> > Hi all,
> > 
> > One of the things which the AS3AP benchmark does is have multiple users
> > access a table with hash indexes on it.
> > 
> > With the OSDB (Open Source Database Benchmark: http://osdb.sf.net) we've
> > found on PG 7.1 that multiple clients hitting a table using a hash index
> > generates locking problems.  I remember Tom mentioning that this is a
> > known thing, but I'm not sure if this has been fixed since then.

No, it hasn't been fixed yet.

> > Does anyone have any ideas?  If not, would someone be willing to take
> > the time to fix it?
> 
> It has not been fixed.  One TODO item is to either stop mentioning hash
> at all or get it improved.  We have been sitting on the fence for too
> long.

I'll be working on fixing this. I'm also going to try to add more
features to the hash index implementation: for example, allow UNIQUE
hash indexes, hash indexes over multiple keys, etc. My first improvement
to the hash code, replacing the hash function with a better one, is on
the unapplied patches list and should be in CVS soon. Bruce, can you add
my name to the TODO list next to this item?

BTW, does anyone have any tips for debugging deadlock conditions? I
normally debug problems by running a backend under gdb in standalone
mode, but that obviously won't help for this problem. Any further advice
on improving hash index concurrency would be very welcome...

Justin: 

Cheers,

Neil

-- 
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC


05 Mar 2002 22:13:25 -0500
Neil Conway <nconway@klamath.dyndns.org>
Re: Do we still have locking problems with concurrent
> > > Does anyone have any ideas?  If not, would someone be willing to take
> > > the time to fix it?
> > 
> > It has not been fixed.  One TODO item is to either stop mentioning hash
> > at all or get it improved.  We have been sitting on the fence for too
> > long.
> 
> I'll be working on fixing this. I'm also going to try to add more
> features to the hash index implementation: for example, allow UNIQUE
> hash indexes, hash indexes over multiple keys, etc. My first improvement
> to the hash code, replacing the hash function with a better one, is on
> the unapplied patches list and should be in CVS soon. Bruce, can you add
> my name to the TODO list next to this item?

TODO updated.  Done.
-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 5 Mar 2002 22:15:09 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Do we still have locking problems with concurrent users
> It has not been fixed.  One TODO item is to either stop mentioning hash
> at all or get it improved.  We have been sitting on the fence for too
> long.

Could someone give me a quick rundown on where using a hash index would be
advantageous over using a btree index?

Thanks,

Chris


Wed, 6 Mar 2002 11:16:50 +0800
"Christopher Kings-Lynne" <chriskl@familyhealth.com.au>
Re: Do we still have locking problems with concurrent users
Awesome!

Thanks Neil.

:-)

Regards and best wishes,

Justin Clift


Neil Conway wrote:
> 
> On Tue, 2002-03-05 at 21:59, Bruce Momjian wrote:
> > Justin Clift wrote:
> > > Hi all,
> > >
> > > One of the things which the AS3AP benchmark does is have multiple users
> > > access a table with hash indexes on it.
> > >
> > > With the OSDB (Open Source Database Benchmark: http://osdb.sf.net) we've
> > > found on PG 7.1 that multiple clients hitting a table using a hash index
> > > generates locking problems.  I remember Tom mentioning that this is a
> > > known thing, but I'm not sure if this has been fixed since then.
> 
> No, it hasn't been fixed yet.
> 
> > > Does anyone have any ideas?  If not, would someone be willing to take
> > > the time to fix it?
> >
> > It has not been fixed.  One TODO item is to either stop mentioning hash
> > at all or get it improved.  We have been sitting on the fence for too
> > long.
> 
> I'll be working on fixing this. I'm also going to try to add more
> features to the hash index implementation: for example, allow UNIQUE
> hash indexes, hash indexes over multiple keys, etc. My first improvement
> to the hash code, replacing the hash function with a better one, is on
> the unapplied patches list and should be in CVS soon. Bruce, can you add
> my name to the TODO list next to this item?
> 
> BTW, does anyone have any tips for debugging deadlock conditions? I
> normally debug problems by running a backend under gdb in standalone
> mode, but that obviously won't help for this problem. Any further advice
> on improving hash index concurrency would be very welcome...
> 
> Justin:
> 
> Cheers,
> 
> Neil
> 
> --
> Neil Conway <neilconway@rogers.com>
> PGP Key ID: DB3C29FC

-- 
"My grandfather once told me that there are two kinds of people: those
who work and those who take the credit. He told me to try to be in the
first group; there was less competition there."
   - Indira Gandhi

Wed, 06 Mar 2002 14:17:24 +1100
Justin Clift <justin@postgresql.org>
Re: Do we still have locking problems with concurrentusers
Christopher Kings-Lynne wrote:
> > It has not been fixed.  One TODO item is to either stop mentioning hash
> > at all or get it improved.  We have been sitting on the fence for too
> > long.
> 
> Could someone give me a quick rundown on where using a hash index would be
> advantageous over using a btree index?

That is the issue, right now, there is little or no advantage.  If we
can improve it, it may become better than btree for cases where you are
only doing equal comparisons, rather than > which only btree can do.

-- 
  Bruce Momjian                        |  http://candle.pha.pa.us
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Tue, 5 Mar 2002 22:17:53 -0500 (EST)
Bruce Momjian <pgman@candle.pha.pa.us>
Re: Do we still have locking problems with concurrent users
Justin Clift writes:

> One of the things which the AS3AP benchmark does is have multiple users
> access a table with hash indexes on it.

In a closed-source system, we could get away with making hash and B-tree
indexes the same internally and tell onlookers that they're different, so
as to satisfy the AS3AP requirements.  ;-)

-- 
Peter Eisentraut   peter_e@gmx.net


Sun, 10 Mar 2002 20:53:02 -0500 (EST)
Peter Eisentraut <peter_e@gmx.net>
Re: Do we still have locking problems with concurrent