I had an idea on a possible way to increase performance under query
loads with a lot of short-term locking. I haven't looked at the
implementation of this at all, so if someone could tell me why this
wouldn't work, that would save me some time ;-)

AFAIK, current Postgres behavior when processing SELECT queries is like
this:

	(1) for each tuple in the result set, try to get an
            AccessShareLock on it
	
	(2) if it can't acqure the lock, wait until it can

	(3) read the data on the previously locked row and continue
            onward

i.e. when it encounters a locked row, it waits for the lock to be
released and then continued the scan.

Instead, why not modify the behavior in (2) so that instead of waiting
for the lock to be released, Postgres would instead continue the scan,
keeping a note that it has skipped over the locked tuple. When it has
finished the scan (and so it has the entire result set, except for the
locked tuples), it should return to each of the previously locked
tuples. Since most locks are relatively short-term (AFAIK), there's a
good chance that during the time it took to scan the rest of the table,
the lock on the tuple has been released -- so it can read the value and
add it into the result set at the appropriate place without needing to
do nothing while waiting for the lock to be released.

This is probably stupid for some reason: can someone let me know what
that reason is? ;-)

Cheers,

Neil

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


15 Mar 2002 17:55:24 -0500
Neil Conway <nconway@klamath.dyndns.org>
question on index access
Neil Conway <nconway@klamath.dyndns.org> writes:
> AFAIK, current Postgres behavior when processing SELECT queries is like
> this:
> 	(1) for each tuple in the result set, try to get an
>             AccessShareLock on it

Uh, no.  There are no per-tuple locks, other than SELECT FOR UPDATE
which doesn't affect SELECT at all.  AccessShareLock is taken on the
entire table, mainly as a means of ensuring the table doesn't disappear
from under us.

			regards, tom lane

Fri, 15 Mar 2002 18:23:47 -0500
Tom Lane <tgl@sss.pgh.pa.us>
Re: question on index access 
On Fri, 2002-03-15 at 18:23, Tom Lane wrote:
> Neil Conway <nconway@klamath.dyndns.org> writes:
> > AFAIK, current Postgres behavior when processing SELECT queries is like
> > this:
> > 	(1) for each tuple in the result set, try to get an
> >             AccessShareLock on it
> 
> Uh, no.  There are no per-tuple locks, other than SELECT FOR UPDATE
> which doesn't affect SELECT at all.  AccessShareLock is taken on the
> entire table, mainly as a means of ensuring the table doesn't disappear
> from under us.

Ah, that makes sense. My mistake -- thanks for the info.

Cheers,

Neil

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


15 Mar 2002 18:32:26 -0500
Neil Conway <nconway@klamath.dyndns.org>
Re: question on index access