Re: lazy detoasting

On 04/11/18 11:27, Chapman Flack wrote: > In most cases I can easily imagine, a function that gets an SQLXML > object is going to read it "pretty soon" ... > 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

Re: lazy detoasting

Peter Eisentraut writes: > In reviewing the committed patch, I noticed that in ER_get_flat_size() > you have removed the PG_DETOAST_DATUM() call and let > expanded_record_set_field_internal() do the de-toasting work. I had > considered that too, but my

Re: lazy detoasting

On 5/12/18 17:25, Tom Lane wrote: > Anyway, attached is a revised patch. I found a test case for > expanded_record_set_fields(), too. Thank you for fixing this up. In reviewing the committed patch, I noticed that in ER_get_flat_size() you have removed the PG_DETOAST_DATUM() call and let

Re: lazy detoasting

Peter Eisentraut writes: > Here is a more complete patch. I made a call graph to get to the > bottom, literally, of how variable assignments happen in PL/pgSQL. (See > attached.) There are four leaf functions to patch up. > Also, I wrote some isolation tests

Re: lazy detoasting

> "Peter" == Peter Eisentraut writes: Peter> The attached test fixes this issue by flattening the toast Peter> values before storing them into PL/pgSQL variables. It could use Peter> another check to see if there are other code paths that need Peter>

Re: lazy detoasting

On 5/1/18 19:56, Andrew Gierth wrote: > Peter> insert into test1 values (1, repeat('foo', 2000)); > > That value is no good because it's too compressible; it'll be left > inline in the main table rather than being externalized, so the value of > 'x' in the DO-block is still self-contained

Re: lazy detoasting

> "Andrew" == Andrew Gierth writes: Andrew> ERROR: no known snapshots Andrew> CONTEXT: PL/pgSQL function inline_code_block line 1 at RAISE Andrew> This is another issue that was mentioned before in relation to Andrew> procedures. See

Re: lazy detoasting

> "Peter" == Peter Eisentraut writes: Peter> Is there a more self-contained way to test this? I have been Peter> trying with something like Peter> create table test1 (a int, b text); Peter> insert into test1 values (1, repeat('foo', 2000)); That value

Re: lazy detoasting

On 4/25/18 07:50, Andrew Gierth wrote: > do $$ > declare a text; > begin > select f1.a into a from f1; > delete from f1; > commit; > perform pg_sleep(10); -- vacuum f1 in another session while it sleeps > call p1(a); > end; $$; > INFO: a: (t,t,f,"missing chunk number

Re: lazy detoasting

> "Andrew" == Andrew Gierth writes: > "Peter" == Peter Eisentraut writes: >> On 4/11/18 11:33, Tom Lane wrote: >>> (Wanders away wondering what Peter has done about toasted parameter >>> values for procedures in general

Re: lazy detoasting

On 4/11/18 11:33, Tom Lane wrote: > (Wanders away wondering what Peter has done about toasted parameter > values for procedures in general ...) I'm not sure. How can a procedure have a toasted parameter value? -- Peter Eisentraut http://www.2ndQuadrant.com/ PostgreSQL Development,

Re: lazy detoasting

> "Chapman" == Chapman Flack writes: Chapman> There's precedent for that kind of thing in PL/Java already Chapman> ... objects that Java considers alive as long as some code Chapman> holds a reference to them, but proxy for things in PG that may Chapman> only have

Re: lazy detoasting

On 04/11/2018 03:04 PM, Tom Lane wrote: > Chapman Flack writes: >> that it might *not* be sufficient to find an applicable snapshot at >> the time of constructing the object, and register that snapshot >> on TopTransactionResourceOwner? > > The problem is to know which

Re: lazy detoasting

Chapman Flack writes: > But let me return to the earlier idea for a moment: are you saying > that it might *not* be sufficient to find an applicable snapshot at > the time of constructing the object, and register that snapshot > on TopTransactionResourceOwner? The problem

Re: lazy detoasting

On 04/11/2018 01:55 PM, Tom Lane wrote: > Chapman Flack writes: >> Well, the devilsAdvocate() function would stash the object >> in a static, then try to look at it some time in a later call >> in the same transaction. > > If you're worried about that, you should also

Re: lazy detoasting

Chapman Flack writes: > On 04/11/2018 11:33 AM, Tom Lane wrote: >> 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

Re: lazy detoasting

On 04/11/2018 11:33 AM, Tom Lane wrote: > Chapman Flack writes: >> 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

Re: lazy detoasting

Chapman Flack 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

Re: lazy detoasting

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

Re: lazy detoasting

Chapman Flack 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

Re: lazy detoasting

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

Re: lazy detoasting

On Wed, Apr 11, 2018 at 2:34 AM, Chapman Flack 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,

Re: lazy detoasting

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

Re: lazy detoasting

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

Re: lazy detoasting

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

Re: lazy detoasting

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

Re: lazy detoasting

On Tue, Apr 10, 2018 at 11:26 AM, Chapman Flack 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

Re: lazy detoasting

On 04/10/2018 10:06 AM, Tom Lane wrote: > Chapman Flack 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

Re: lazy detoasting

Chapman Flack 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

Re: lazy detoasting

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

Re: lazy detoasting

> "Chapman" == Chapman Flack 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

Re: lazy detoasting

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

Re: lazy detoasting

> "Chapman" == Chapman Flack 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:

Re: lazy detoasting

On Thu, Apr 5, 2018 at 8:04 AM, Chapman Flack wrote: > On 04/01/18 01:19, Tom Lane wrote: >> Chapman Flack 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

Re: lazy detoasting

Chapman Flack 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