Re: Index-only scans and non-MVCC snapshots

Hi,

As part of a research project, I'm trying to change Read Committed
isolation to use HeapTupleSatisfiesNow rather than acquiring a new
snapshot at every command [1]. Things appear to have gone reasonably
well so far, except certain queries fail with "ERROR: non-MVCC
snapshots are not supported in index-only scans."

I'm using v9.3.2, and the docs claim that index-only scans work without
MVCC, but require some extra locking to avoid races [2]. Is this not
actually implemented? If that is the case, shouldn't the query optimizer
avoid selecting index-only scans for non-MVCC snapshots?

I realize I'm playing with fire here, but any pointers to sections of
code I might look at to either work around or fix this issue would be
greatly appreciated. I've been looking around in index_fetch_heap
(indexam.c) as well as other locations that use scan->xs_continue_hot;
there seems to be code in place to detect when a non-MVCC snapshot is in
use, as if that were nothing out of the ordinary, but nothing prevents
the error from arising if a hot chain is actually encountered.

Thanks,
Ryan

[1] Right now, Read Committed is significantly *slower* than Repeatable
Read---for transactions involving multiple short commands---because the
former acquires multiple snapshots per transaction and causes a lwlock
bottleneck on my 12-core machine.

[2] http://www.postgresql.org/docs/9.3/static/index-locking.html:
> with a non-MVCC-compliant snapshot (such as SnapshotNow), it would be
> possible to accept and return a row that does not in fact match the
> scan keys ... [so] we use a pin on an index page as a proxy to
> indicate that the reader might still be "in flight" from the index
> entry to the matching heap entry. Making ambulkdelete block on such a
> pin ensures that VACUUM cannot delete the heap entry before the reader
> is done with it. ... This solution requires that index scans be
> "synchronous": we have to fetch each heap tuple immediately after
> scanning the corresponding index entry. This is expensive for a number
> of reasons. An "asynchronous" scan in which we collect many TIDs from
> the index, and only visit the heap tuples sometime later, requires
> much less index locking overhead and can allow a more efficient heap
> access pattern. Per the above analysis, we must use the synchronous
> approach for non-MVCC-compliant snapshots.

Ryan Johnson wrote:

> As part of a research project, I'm trying to change Read Committed
> isolation to use HeapTupleSatisfiesNow rather than acquiring a new
> snapshot at every command [1].

Are you aware of this?

commit 813fb0315587d32e3b77af1051a0ef517d187763
Author: Robert Haas <rhaas(at)postgresql(dot)org>
Date: Thu Aug 1 10:46:19 2013 -0400

Remove SnapshotNow and HeapTupleSatisfiesNow.

We now use MVCC catalog scans, and, per discussion, have eliminated
all other remaining uses of SnapshotNow, so that we can now get rid of
it. This will break third-party code which is still using it, which
is intentional, as we want such code to be updated to do things the
new way.

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 26/06/2014 11:04 PM, Alvaro Herrera wrote:
> Ryan Johnson wrote:
>> As part of a research project, I'm trying to change Read Committed
>> isolation to use HeapTupleSatisfiesNow rather than acquiring a new
>> snapshot at every command [1].
> Are you aware of this?
>
> commit 813fb0315587d32e3b77af1051a0ef517d187763
> Author: Robert Haas <rhaas(at)postgresql(dot)org>
> Date: Thu Aug 1 10:46:19 2013 -0400
>
> Remove SnapshotNow and HeapTupleSatisfiesNow.
That would be wonderful news... if snapshots weren't so darned expensive
to create.

I guess there's no avoiding that bottleneck now, though.

Ryan

On 2014-06-26 22:47:47 -0600, Ryan Johnson wrote:
> Hi,
>
> As part of a research project, I'm trying to change Read Committed isolation
> to use HeapTupleSatisfiesNow rather than acquiring a new snapshot at every
> command [1]. Things appear to have gone reasonably well so far, except
> certain queries fail with "ERROR: non-MVCC snapshots are not supported in
> index-only scans."

You're aware that unless you employ additional locking you can simply
miss individual rows or see them several times because of concurrent
updates?
The reason it has worked (< 9.4) for system catalogs is that updates of
rows were only performed while objects were locked access exclusively -
that's the reason why some places in the code use inplace updates btw...

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

Ryan Johnson wrote:
> On 26/06/2014 11:04 PM, Alvaro Herrera wrote:
> >Ryan Johnson wrote:
> >>As part of a research project, I'm trying to change Read Committed
> >>isolation to use HeapTupleSatisfiesNow rather than acquiring a new
> >>snapshot at every command [1].
> >Are you aware of this?
> >
> >commit 813fb0315587d32e3b77af1051a0ef517d187763
> >Author: Robert Haas <rhaas(at)postgresql(dot)org>
> >Date: Thu Aug 1 10:46:19 2013 -0400
> >
> > Remove SnapshotNow and HeapTupleSatisfiesNow.
>
> That would be wonderful news... if snapshots weren't so darned
> expensive to create.

I take it you aren't aware of this other effort, either:
http://archives.postgresql.org/message-id/539AD153.9000004@vmware.com

--
Álvaro Herrera http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 27/06/2014 3:14 AM, Andres Freund wrote:
> On 2014-06-26 22:47:47 -0600, Ryan Johnson wrote:
>> Hi,
>>
>> As part of a research project, I'm trying to change Read Committed isolation
>> to use HeapTupleSatisfiesNow rather than acquiring a new snapshot at every
>> command [1]. Things appear to have gone reasonably well so far, except
>> certain queries fail with "ERROR: non-MVCC snapshots are not supported in
>> index-only scans."
> You're aware that unless you employ additional locking you can simply
> miss individual rows or see them several times because of concurrent
> updates?
> The reason it has worked (< 9.4) for system catalogs is that updates of
> rows were only performed while objects were locked access exclusively -
> that's the reason why some places in the code use inplace updates btw...
Yes, I was aware of the need for locking. The documentation just made it
sound that locking was already in place for non-MVCC index scans. I was
hoping I'd missed some easy way to activate it.

Regards,
Ryan

On 2014-06-27 08:39:13 -0600, Ryan Johnson wrote:
> On 27/06/2014 3:14 AM, Andres Freund wrote:
> >On 2014-06-26 22:47:47 -0600, Ryan Johnson wrote:
> >>Hi,
> >>
> >>As part of a research project, I'm trying to change Read Committed isolation
> >>to use HeapTupleSatisfiesNow rather than acquiring a new snapshot at every
> >>command [1]. Things appear to have gone reasonably well so far, except
> >>certain queries fail with "ERROR: non-MVCC snapshots are not supported in
> >>index-only scans."
> >You're aware that unless you employ additional locking you can simply
> >miss individual rows or see them several times because of concurrent
> >updates?
> >The reason it has worked (< 9.4) for system catalogs is that updates of
> >rows were only performed while objects were locked access exclusively -
> >that's the reason why some places in the code use inplace updates btw...

> Yes, I was aware of the need for locking. The documentation just made it
> sound that locking was already in place for non-MVCC index scans. I was
> hoping I'd missed some easy way to activate it.

Well, it is/was for the places (i.e. DDL) that actually use non-MVCC
scans.

Greetings,

Andres Freund

--
Andres Freund http://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Training & Services

On 27/06/2014 8:20 AM, Alvaro Herrera wrote:
> Ryan Johnson wrote:
>> On 26/06/2014 11:04 PM, Alvaro Herrera wrote:
>>> Ryan Johnson wrote:
>>>> As part of a research project, I'm trying to change Read Committed
>>>> isolation to use HeapTupleSatisfiesNow rather than acquiring a new
>>>> snapshot at every command [1].
>>> Are you aware of this?
>>>
>>> commit 813fb0315587d32e3b77af1051a0ef517d187763
>>> Author: Robert Haas <rhaas(at)postgresql(dot)org>
>>> Date: Thu Aug 1 10:46:19 2013 -0400
>>>
>>> Remove SnapshotNow and HeapTupleSatisfiesNow.
>> That would be wonderful news... if snapshots weren't so darned
>> expensive to create.
> I take it you aren't aware of this other effort, either:
> http://archives.postgresql.org/message-id/539AD153.9000004@vmware.com
That is good news, though from reading the thread it sounds like proc
array accesses are being exchanged for accesses to an SLRU, so a lot of
lwlock calls will remain. It will definitely help, though. SLRU will get
ex-locked a lot less often, so the main source of contention will be for
the actual lwlock acquire/release operations.

Regards,
Ryan