lazy detoasting

Chapman Flack <chap@anastigmatix.net> writes:

If I copy an out-of-line, on-disk TOAST pointer into a memory context
with transaction lifetime, with an eye to detoasting it later in the
same transaction, are there circumstances where it wouldn't work?

Should be safe *as long as you hold onto a snapshot that can see the
toast value's parent row*. Otherwise, it's not.

For a counterexample, see the changes we had to make to avoid depending
on out-of-line toast values in the catcaches, commit 08e261cbc.

regards, tom lane

On 04/01/18 01:19, Tom Lane wrote:

Chapman Flack <chap@anastigmatix.net> writes:

If I copy an out-of-line, on-disk TOAST pointer into a memory context
with transaction lifetime, with an eye to detoasting it later in the
same transaction, are there circumstances where it wouldn't work?

Should be safe *as long as you hold onto a snapshot that can see the
toast value's parent row*. Otherwise, it's not.

For a counterexample, see the changes we had to make to avoid depending
on out-of-line toast values in the catcaches, commit 08e261cbc.

Thanks. I think I see two approaches happening in that commit: retaining
a snapshot, if the tuples will be used within the transaction, or eagerly
detoasting, when persisting a holdable portal so tuples can be used after
the transaction.

Please bear with me as I check my understanding of snapshot management
by looking at PersistHoldablePortal(). There's a
PushActiveSnapshot(queryDesc->snapshot) in there. Is that because:

(a) that snapshot might not be on the active stack at this point, and it
needs to be be there to keep it alive during this executor re-run, or

(b) it's known to be somewhere on the active stack already, but not
necessarily on top, and needs to be pushed so it is topmost while
re-running this executor (does the top snapshot on the active stack
affect which tuples the executor sees? or is this stack's only purpose
to keep snapshots alive?), or

(c) it's known to be somewhere on the stack already, but needs to be
be pushed to make sure some stack-depth invariant is preserved
(perhaps because of an implied pop in case of an error), or

(d) some other reason I haven't thought of ?

I *think* I'm smart enough to rule out choice (a), because it wouldn't
make sense to have queryDesc->snapshot refer to a snapshot that isn't
already on the stack (unless it's also been registered, in which case,
why would it need to be pushed on the stack too?), as then I would be
reckless to assume it's still alive to *be* pushed. Am I close?

I haven't yet gone to track down the code that assigns a snapshot to
queryDesc->snapshot.

Thanks,
-Chap

On Thu, Apr 5, 2018 at 8:04 AM, Chapman Flack <chap@anastigmatix.net> wrote:

On 04/01/18 01:19, Tom Lane wrote:

Chapman Flack <chap@anastigmatix.net> writes:

If I copy an out-of-line, on-disk TOAST pointer into a memory context
with transaction lifetime, with an eye to detoasting it later in the
same transaction, are there circumstances where it wouldn't work?

Should be safe *as long as you hold onto a snapshot that can see the
toast value's parent row*. Otherwise, it's not.

For a counterexample, see the changes we had to make to avoid depending
on out-of-line toast values in the catcaches, commit 08e261cbc.

Thanks. I think I see two approaches happening in that commit: retaining
a snapshot, if the tuples will be used within the transaction, or eagerly
detoasting, when persisting a holdable portal so tuples can be used after
the transaction.

Please bear with me as I check my understanding of snapshot management
by looking at PersistHoldablePortal(). There's a
PushActiveSnapshot(queryDesc->snapshot) in there. Is that because:

(a) that snapshot might not be on the active stack at this point, and it
needs to be be there to keep it alive during this executor re-run, or

(b) it's known to be somewhere on the active stack already, but not
necessarily on top, and needs to be pushed so it is topmost while
re-running this executor (does the top snapshot on the active stack
affect which tuples the executor sees? or is this stack's only purpose
to keep snapshots alive?), or

(c) it's known to be somewhere on the stack already, but needs to be
be pushed to make sure some stack-depth invariant is preserved
(perhaps because of an implied pop in case of an error), or

(d) some other reason I haven't thought of ?

I *think* I'm smart enough to rule out choice (a), because it wouldn't
make sense to have queryDesc->snapshot refer to a snapshot that isn't
already on the stack (unless it's also been registered, in which case,
why would it need to be pushed on the stack too?), as then I would be
reckless to assume it's still alive to *be* pushed. Am I close?

I think it is somewhat close to what you have mentioned in (b).
Basically, it will help executor to use that snapshot for tuple
visibility.

I haven't yet gone to track down the code that assigns a snapshot to
queryDesc->snapshot.

See CreateQueryDesc().

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

"Chapman" == Chapman Flack <chap@anastigmatix.net> writes:

Chapman> Please bear with me as I check my understanding of snapshot
Chapman> management by looking at PersistHoldablePortal(). There's a
Chapman> PushActiveSnapshot(queryDesc->snapshot) in there. Is that
Chapman> because:

Chapman> (d) some other reason I haven't thought of ?

It has to be pushed as the active snapshot so that it's the snapshot
that the executor uses to run the query to populate the tuplestore which
becomes the "held" portal content.

I think you're confusing the stack of active snapshots with the registry
of snapshots. The snapshot of a portal that's not currently running in
the executor won't be on the stack, but it will be registered (which is
enough to maintain the session's reported xmin, which is what prevents
visible data from being vacuumed away).

--
Andrew (irc:RhodiumToad)

On 04/08/2018 02:01 AM, Andrew Gierth wrote:

Chapman> (d) some other reason I haven't thought of ?

It has to be pushed as the active snapshot so that it's the snapshot
that the executor uses to run the query to populate the tuplestore which
becomes the "held" portal content.

That seems closest to my (b) guess.

I think you're confusing the stack of active snapshots with the registry
of snapshots. The snapshot of a portal that's not currently running in
the executor won't be on the stack, but it will be registered (which is
enough to maintain the session's reported xmin, which is what prevents
visible data from being vacuumed away).

That's why I was asking. The first paragraph in snapmgr.c seems to say
that the registry and the active stack both exist as ways to keep the
snapshot alive, with reachability from either one being sufficient:

* We keep track of snapshots in two ways: those "registered" by
* resowner.c, and the "active snapshot" stack. All snapshots in
* either of them live in persistent memory. When a snapshot is
* no longer in any of these lists (tracked by separate refcounts
* on each snapshot), its memory can be freed.

AFAICS, that is *all* that comment block has to say about why there's
an active snapshot stack. I believe you are saying it has another
important function, namely that its top element is what tells the
executor what can be seen. That makes sense, and to clarify that
was why I was asking.

I suppose the reason that's not mentioned in the comments is that it
was so obviously the ultimate purpose of the whole scheme that nobody
writing or reviewing the comments could imagine not knowing it. :)

-Chap

"Chapman" == Chapman Flack <chap@anastigmatix.net> writes:

Chapman> AFAICS, that is *all* that comment block has to say about why
Chapman> there's an active snapshot stack. I believe you are saying it
Chapman> has another important function, namely that its top element is
Chapman> what tells the executor what can be seen.

That's not precisely true - ultimately, the routines that do actual
scans take the snapshot to use as a parameter, and the executor mostly
references the snapshot from the EState; but a bunch of places do
require that ActiveSnapshot be set to the currently applicable snapshot
(e.g. for queries inside stable functions nested inside the main query).

--
Andrew (irc:RhodiumToad)

On 04/10/18 00:30, Andrew Gierth wrote:

That's not precisely true - ultimately, the routines that do actual
scans take the snapshot to use as a parameter, and the executor mostly
references the snapshot from the EState; but a bunch of places do
require that ActiveSnapshot be set to the currently applicable snapshot
(e.g. for queries inside stable functions nested inside the main query).

I'm becoming increasingly glad I asked (or less embarrassed that I hadn't
figured it all out yet). :)

Am I right in thinking that, for my original purpose of detoasting something
later in a transaction, all that matters is that I registered a snapshot
from the time at which I copied the toasted datum, and the resource owner
I registered it to has not been released yet, so rows referred to in the
snapshot haven't been vacuumed away? Is that a sufficient condition for
detoast to work?

Or would I need to do something more, like push and pop that snapshot
around the detoast call?

This would be in a PL function (or the handler for the PL function),
if the context matters.

-Chap

Chapman Flack <chap@anastigmatix.net> writes:

I'm becoming increasingly glad I asked (or less embarrassed that I hadn't
figured it all out yet). :)

Am I right in thinking that, for my original purpose of detoasting something
later in a transaction, all that matters is that I registered a snapshot
from the time at which I copied the toasted datum, and the resource owner
I registered it to has not been released yet, so rows referred to in the
snapshot haven't been vacuumed away? Is that a sufficient condition for
detoast to work?

I believe so.

Or would I need to do something more, like push and pop that snapshot
around the detoast call?

You shouldn't need that; toast reads intentionally use a non-MVCC-aware
"snapshot" to handle exactly this type of situation, where somebody is
trying to pull data out of a tuple that would no longer be visible to
the transaction's current snapshot.

Wouldn't be a bad idea to test this, of course ;-)

regards, tom lane

On 04/10/2018 10:06 AM, Tom Lane wrote:

Chapman Flack <chap@anastigmatix.net> writes:

Am I right in thinking that, for my original purpose of
detoasting something later in a transaction, all that matters
is that I registered a snapshot from the time at which I copied
the toasted datum, and the resource owner I registered it to
has not been released yet, ...

I believe so.

Ok.

I see I have about half a dozen choices for the snapshot that
I choose to squirrel away at the same time as the copied datum.
(That's not counting SnapshotToast, which I didn't know was a thing
until Pavan's thread this morning, but it's not a thing I can get,
just something constructed on the fly in the tuptoaster.)

Out of the six GetFooSnapshot()s, would I want to squirrel away
Active? Oldest? Transaction? This should be happening in a PL
function not doing anything arcane; the datum in question might
be passed to it as a parameter or retrieved from a query done
within the function.

GetOldestTransaction() is what the tuptoaster will eventually use
to construct SnapshotToast when looking for the data, but it's
probably overkill for me to save the oldest one in sight at the
time I save the datum. Or is it? Should I be confident that, at
the time I'm handed this datum, its toasted content must be
retrievable through the (Active? Transaction?) snapshot at that
moment, and it's enough to register that, while to register the
Oldest would be to pin something unnecessarily far back?

Wouldn't be a bad idea to test this, of course ;-)

Mm-hmm. (Thunderbird has just corrected my spelling of mm-hmm.)
I'm still not sure I know enough to construct a meaningful test...

I assume that, while I'm doing this for a backend PL at the
moment, some of the same considerations will apply if a future
wire protocol is to support Craig's "v4 protocol TODO item -
Lazy fetch/stream of TOASTed valued" suggestion in
/messages/by-id/53FF0EF8.100@2ndquadrant.com

-Chap

On Tue, Apr 10, 2018 at 11:26 AM, Chapman Flack <chap@anastigmatix.net> wrote:

Out of the six GetFooSnapshot()s, would I want to squirrel away
Active? Oldest? Transaction?

I suspect you want, or maybe need, to use the same snapshot as the
scan that retrieved the tuple containing the toasted datum.

(This advice may be worth what you paid for it.)

--
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company

On 04/10/2018 04:03 PM, Robert Haas wrote:

I suspect you want, or maybe need, to use the same snapshot as the
scan that retrieved the tuple containing the toasted datum.

I'm sure it's worth more than that, but I don't know if it's
implementable.

If I'm a function, and the datum came to me as a parameter, I may
have no way to determine what snapshot the enclosing query used to
obtain the thing passed to me. Or, if I found it myself, say by an
SPI query within the function, usually that's at a level of abstraction
somewhere above what-snapshot-was-used-in-the-scan.

But in both cases, it's expected that I could successfully detoast
either datum if I did so right there on the spot, as that's the usual
convention, right? So at that moment, something in the set of
registered || active snapshots is protecting the tuples I need.

If it's impractical to determine which snapshot is needed (or just
enough work to obviate any benefit of lazy detoasting), I wonder if
there's at least a cheap way to check a particular snapshot
for suitability wrt a given toast pointer. Check a couple usual
suspects, find one most of the time, fall back to eager detoasting
otherwise?

Guess I need to go back for a deeper dive on just what constitutes
a toast pointer. I was skimming last time....

-Chap

On 04/10/2018 05:04 PM, Chapman Flack wrote:

If I'm a function, and ... I found [the datum] myself, say by an
SPI query within the function, usually that's at a level of abstraction
somewhere above what-snapshot-was-used-in-the-scan.

It looks like for that case (since the commit 08e261cbc that Tom
mentioned earlier), there can sometimes be a portal->holdSnapshot
that will be the right one.

-Chap

On 04/10/2018 05:04 PM, Chapman Flack wrote:

... I wonder if
there's at least a cheap way to check a particular snapshot
for suitability wrt a given toast pointer. Check a couple usual
suspects, find one most of the time, fall back to eager detoasting
otherwise?

Guess I need to go back for a deeper dive on just what constitutes
a toast pointer. I was skimming last time....

I see all I have in a toast pointer is a relation id and a value
oid, so probably no way to check that a given snapshot 'works' to
find it, at least no more cheaply than by opening the toast relation
and trying to find it.

Welp, what tuptoaster does to construct its SnapshotToast is to
grab GetOldestSnapshot():

* since we don't know which one to use, just use the oldest one.
* This is safe: at worst, we will get a "snapshot too old" error
* that might have been avoided otherwise.

... which means, I take it, that a more recent snapshot
might work, but this conservative choice would only fail
if the oldest snapshot has really been around for many days,
under typical old_snapshot_threshold settings?

... and if I'm getting a value from a portal that happens to have
a non-null holdSnapshot, then that would be the one to use, in
preference to just conservatively grabbing the oldest?

-Chap

... am I right that the init_toast_snapshot construction is really
making only one change to the snapshot data, namely changing the
'satisfies' condition to HeapTupleSatisfiesToast ?

The lsn and whenTaken seem to be fetched from the original data
and stored right back without change.

Maybe I'm missing something here, but let me put $.02 in anyway.

TOAST reuses entries. If a toasted value is unchanged on UPDATE (i.e. the
toast pointer didn't get replaced by a new value), the new tuple points to
the same toast slices as the old. If it is changed, the current transaction
DELETEs the old toast slices and INSERTs new ones (if needed).

If your session has a transaction snapshot that protects the old toast
slices from being vacuumed away, you are fine. Even under READ COMMITTED
(that is why it uses that other visibility, so they don't go away when the
concurrent UPDATE commits and rather behave like REPEATABLE READ).

At least that is what the code was intended to do originally.

Regards, Jan

On Tue, Apr 10, 2018 at 6:24 PM, Chapman Flack <chap@anastigmatix.net>
wrote:

On 04/10/2018 05:04 PM, Chapman Flack wrote:

... I wonder if
there's at least a cheap way to check a particular snapshot
for suitability wrt a given toast pointer. Check a couple usual
suspects, find one most of the time, fall back to eager detoasting
otherwise?

Guess I need to go back for a deeper dive on just what constitutes
a toast pointer. I was skimming last time....

I see all I have in a toast pointer is a relation id and a value
oid, so probably no way to check that a given snapshot 'works' to
find it, at least no more cheaply than by opening the toast relation
and trying to find it.

Welp, what tuptoaster does to construct its SnapshotToast is to
grab GetOldestSnapshot():

* since we don't know which one to use, just use the oldest one.
* This is safe: at worst, we will get a "snapshot too old" error
* that might have been avoided otherwise.

... which means, I take it, that a more recent snapshot
might work, but this conservative choice would only fail
if the oldest snapshot has really been around for many days,
under typical old_snapshot_threshold settings?

... and if I'm getting a value from a portal that happens to have
a non-null holdSnapshot, then that would be the one to use, in
preference to just conservatively grabbing the oldest?

-Chap

... am I right that the init_toast_snapshot construction is really
making only one change to the snapshot data, namely changing the
'satisfies' condition to HeapTupleSatisfiesToast ?

The lsn and whenTaken seem to be fetched from the original data
and stored right back without change.

--
Jan Wieck
Senior Postgres Architect
http://pgblog.wi3ck.info

On Wed, Apr 11, 2018 at 2:34 AM, Chapman Flack <chap@anastigmatix.net> wrote:

On 04/10/2018 04:03 PM, Robert Haas wrote:

I suspect you want, or maybe need, to use the same snapshot as the
scan that retrieved the tuple containing the toasted datum.

I'm sure it's worth more than that, but I don't know if it's
implementable.

If I'm a function, and the datum came to me as a parameter, I may
have no way to determine what snapshot the enclosing query used to
obtain the thing passed to me.

Before query execution, we push the active snapshot, so any time
during execution, if you get the active snapshot via
GetActiveSnapshot(), you can access it. So, I think during a function
execution, if you use GetActiveSnapshot, you should get the snapshot
used by enclosing query.

--
With Regards,
Amit Kapila.
EnterpriseDB: http://www.enterprisedb.com

On 04/10/2018 10:17 PM, Jan Wieck wrote:

If your session has a transaction snapshot that protects the old toast
slices from being vacuumed away, you are fine.

That harks back to my earlier (naïve?) thought that, as long as
my lazy datum doesn't have to outlive the transaction, lazy
detoasting might Just Work.

Tom seems to say otherwise, in
/messages/by-id/23711.1522559987@sss.pgh.pa.us

The message of the commit he mentions there includes:

I believe this code was all right when written, because our
management of a session's exposed xmin was such that the TOAST
references were safe until end of transaction. But that's
no longer true now that we can advance or clear our PGXACT.xmin
intra-transaction.

So perhaps my original thought really would have Just Worked,
in PG versions before that changed? When did that change, btw?

On 04/11/2018 09:28 AM, Amit Kapila wrote:

Before query execution, we push the active snapshot, so any time
during execution, if you get the active snapshot via
GetActiveSnapshot(), you can access it. So, I think during a function
execution, if you use GetActiveSnapshot, you should get the snapshot
used by enclosing query.

So perhaps GetActiveSnapshot is the right choice to accompany a datum
that came as a function parameter.

For something retrieved from an SPI query within the function, it
looks like I will have a portal->holdSnapshot in certain cases
(PORTAL_ONE_RETURNING, PORTAL_ONE_MOD_WITH, PORTAL_UTIL_SELECT).

The comments added to portal.h in that commit say:

* Snapshot under which tuples in the holdStore were read. We must keep
* a reference to this snapshot if there is any possibility that the
* tuples contain TOAST references, because releasing the snapshot ...

Soo, is that telling me that the three cases named above, where
holdSnapshot gets set, are in fact the only cases where "there is any
possibility that the tuples contain TOAST references", and therefore
I could count on holdSnapshot to be nonnull whenever it matters?

-Chap

Chapman Flack <chap@anastigmatix.net> writes:

On 04/10/2018 10:17 PM, Jan Wieck wrote:

If your session has a transaction snapshot that protects the old toast
slices from being vacuumed away, you are fine.

That harks back to my earlier (naïve?) thought that, as long as
my lazy datum doesn't have to outlive the transaction, lazy
detoasting might Just Work.

Tom seems to say otherwise, in
/messages/by-id/23711.1522559987@sss.pgh.pa.us

The message of the commit he mentions there includes:

I believe this code was all right when written, because our
management of a session's exposed xmin was such that the TOAST
references were safe until end of transaction. But that's
no longer true now that we can advance or clear our PGXACT.xmin
intra-transaction.

So perhaps my original thought really would have Just Worked,
in PG versions before that changed? When did that change, btw?

When snapmgr.c came in, which seems to have been 8.4.

The core of the problem now is that in a READ COMMITTED transaction, we
may not be holding any snapshot at all between statements. So if you're
hanging onto a toast pointer across statements you're at risk.

On the other hand, it's also arguable that you shouldn't be interested
in dereferencing such a pointer later than the statement in which it
was read, precisely because it no longer represents accessible data.
So maybe we need to take two steps back and talk about the semantics
of what you're doing.

regards, tom lane

On 04/11/2018 10:41 AM, Tom Lane wrote:

The core of the problem now is that in a READ COMMITTED transaction, we
may not be holding any snapshot at all between statements. So if you're
hanging onto a toast pointer across statements you're at risk.

On the other hand, it's also arguable that you shouldn't be interested
in dereferencing such a pointer later than the statement in which it
was read, precisely because it no longer represents accessible data.
So maybe we need to take two steps back and talk about the semantics
of what you're doing.

The mission is to implement java.sql.SQLXML, which has long been
missing from PL/Java.

https://docs.oracle.com/javase/6/docs/api/index.html?java/sql/SQLXML.html

The API spec includes this: "An SQLXML object is valid for
the duration of the transaction in which it was created."

This is the class of object your Java code can retrieve from a
ResultSet row from a query with an XML column type. (It's also
what can be passed to you as a function parameter, if your
function's SQL declaration has a parameter type XML.)

An SQLXML object represents the XML value. You get to read the
object (exactly once, or not at all) for the content it represents,
in your choice of a half-dozen different ways corresponding to
available Java XML-processing APIs. (The SQLXML implementation knows
what the underlying stored form is, so it encapsulates the knowledge
of how most efficiently to get it from there to the form the program
wants to work with.)

In most cases I can easily imagine, a function that gets an SQLXML
object is going to read it "pretty soon" ... surely some time during
the function call, if it was passed as a parameter, and probably
within the same row loop iteration, for one retrieved from a query.
However, the spec does explicitly provide that you could, for whatever
reason, sit on the thing for a while, then read it later in the same
transaction. You should get the same stuff you would have if you had
read it right away, whether or not stuff has changed in the database
since. Eager detoasting at the time of creating the object, into a
transaction-scoped memory context, would trivially have that behavior,
but on the chance that XML values might be large, and a function might
conceivably never read the thing at all on certain code paths, I'd
rather defer detoasting until the code holding the SQLXML object
actually wants to read it.

-Chap

Chapman Flack <chap@anastigmatix.net> writes:

On 04/11/2018 10:41 AM, Tom Lane wrote:

So maybe we need to take two steps back and talk about the semantics
of what you're doing.

The mission is to implement java.sql.SQLXML, which has long been
missing from PL/Java.
This is the class of object your Java code can retrieve from a
ResultSet row from a query with an XML column type. (It's also
what can be passed to you as a function parameter, if your
function's SQL declaration has a parameter type XML.)

OK, but if this object only lives within a single function call,
what's the problem? The underlying row must be visible to the
calling query, and that condition won't change for the duration
of the call.

Things could get interesting if you consider a Java *procedure*,
but I think you could afford to detoast-on-entry for that case.

(Wanders away wondering what Peter has done about toasted parameter
values for procedures in general ...)

regards, tom lane