Re: Toast issues with OldestXmin going backwards

On Sun, Apr 29, 2018 at 11:56 AM, Andrew Gierth wrote: >> "Amit" == Amit Kapila writes: > > >>> (Or do we need to track it across restarts? maybe we do, to deal with > >>> replication slaves without slots, or changes in parameters) > >

Re: Toast issues with OldestXmin going backwards

On Fri, Apr 27, 2018 at 8:33 PM, Robert Haas wrote: > On Thu, Apr 26, 2018 at 9:03 PM, Andrew Gierth > wrote: >>(Or do we need to track it across restarts? maybe we do, to deal with >>replication slaves without slots, or changes in

Re: Toast issues with OldestXmin going backwards

> "Stephen" == Stephen Frost writes: Stephen> I tend to agree.. However, this isn't something that's been Stephen> happening a lot, from what I gather, Right now I can't prove it happens in the wild at all - I think I've ruled it out as an explanation for the original

Re: Toast issues with OldestXmin going backwards

Greetings, * Robert Haas (robertmh...@gmail.com) wrote: > On Fri, Apr 27, 2018 at 11:35 AM, Andrew Gierth > wrote: > >> "Robert" == Robert Haas writes: > > Robert> One idea that occurred to me is to somehow record -- I guess in > >

Re: Toast issues with OldestXmin going backwards

On Fri, Apr 27, 2018 at 11:35 AM, Andrew Gierth wrote: >> "Robert" == Robert Haas writes: > Robert> One idea that occurred to me is to somehow record -- I guess in > Robert> pg_class using non-transactional updates -- the last cutoff XID

Re: Toast issues with OldestXmin going backwards

> "Robert" == Robert Haas writes: Robert> One idea that occurred to me is to somehow record -- I guess in Robert> pg_class using non-transactional updates -- the last cutoff XID Robert> used to vacuum any given table. Then we could just make a rule Robert> that you

Re: Toast issues with OldestXmin going backwards

On Thu, Apr 26, 2018 at 9:03 PM, Andrew Gierth wrote: >(Or do we need to track it across restarts? maybe we do, to deal with >replication slaves without slots, or changes in parameters) Yeah, I'm worried that it might need to be persistent across restarts.

Re: Toast issues with OldestXmin going backwards

> "Tom" == Tom Lane writes: Tom> Have we given up on the angle of "prevent OldestXmin from Tom> retreating"? I haven't, and I'm thinking about it, but I don't have an immediate solution. Thinking-out-loud mode: 1) we could store minimum values for OldestXmin for each

Re: Toast issues with OldestXmin going backwards

On Thu, Apr 26, 2018 at 03:09:01PM -0400, Tom Lane wrote: > Have we given up on the angle of "prevent OldestXmin from retreating"? > That seems like it'd be far more bulletproof than trying to work > around places that break one-by-one. Strong +1 on that. -- Michael signature.asc Description:

Re: Toast issues with OldestXmin going backwards

Robert Haas writes: > I wonder if perhaps get_actual_variable_range() has a hazard of this > type as well. If OldestXmin retreats, then the special snapshot type > which it uses could see a row whose TOAST entry has been removed > meanwhile. Hm, yeah. I wonder if we

Re: Toast issues with OldestXmin going backwards

On Mon, Apr 23, 2018 at 11:21 PM, Amit Kapila wrote: > If I read correctly, it seems one of the main reason [1] is to save > the extra pass over the heap and improve the code. Now, ideally, the > extra pass over heap should also free up some space (occupied by the > rows

Re: Toast issues with OldestXmin going backwards

On 4/23/18 00:33, Amit Kapila wrote: > Yeah, right, I have missed the point that they can be vacuumed > separately, however, I think that decision is somewhat questionable. Manually vacuuming the TOAST table was a way to repair the recently fixed TOAST bug, so it's kind of useful. -- Peter

Re: Toast issues with OldestXmin going backwards

On Mon, Apr 23, 2018 at 1:34 PM, Andrew Gierth wrote: >> "Amit" == Amit Kapila writes: > > >> Your patch would actually be needed if (and only if) autovacuum was > >> changed back to its old behavior of never vacuuming toast tables >

Re: Toast issues with OldestXmin going backwards

> "Amit" == Amit Kapila writes: >> Your patch would actually be needed if (and only if) autovacuum was >> changed back to its old behavior of never vacuuming toast tables >> independently, and if manual VACUUM pg_toast.*; was disabled. But in >> the presence of

Re: Toast issues with OldestXmin going backwards

On Mon, Apr 23, 2018 at 1:55 AM, Andrew Gierth wrote: >> "Amit" == Amit Kapila writes: > > Amit> by computing it before scanning the main heap (lazy_vacuum_rel) > Amit> and then pass it down. I have tried it and came up with the >

Re: Toast issues with OldestXmin going backwards

> "Andrew" == Andrew Gierth writes: Andrew> Since the tuple was vacuumable at that point, it will never Andrew> again become visible in any snapshot, so the only code that Andrew> should ever access its toast values is vacuum full/cluster, and, unfortunately,

Re: Toast issues with OldestXmin going backwards

> "Amit" == Amit Kapila writes: Amit> Yeah, that's right, but it gives some uneasy feeling that we are Amit> attaching the wrong toast value. Oh, forgot to stress this before: referencing the wrong toast value in this case is something that can already happen, my

Re: Toast issues with OldestXmin going backwards

> "Amit" == Amit Kapila writes: Amit> I haven't tried to reproduce it, but I can see the possibility of Amit> the problem described by you. What should we do next? I could see Amit> few possibilities: (a) Vacuum for main and toast table should Amit> always use

Re: Toast issues with OldestXmin going backwards

On Sat, Apr 21, 2018 at 1:26 AM, Andrew Gierth wrote: >> "Amit" == Amit Kapila writes: > > Amit> I haven't tried to reproduce it, but I can see the possibility of > Amit> the problem described by you. What should we do next? I could see

Re: Toast issues with OldestXmin going backwards

> "Andrew" == Andrew Gierth writes: Andrew> There is still some overhead in the check, of course; I will Andrew> see about doing some benchmarks. Preliminary result suggests that the user-CPU cost of vacuum full increases by ~16% when the entire table is

Re: Toast issues with OldestXmin going backwards

> "Andres" == Andres Freund writes: Andres> Please wait for a bit. This isn't a trivial change, and I would Andres> like to wrap my head around it. Sure. Andres> At the very least this seems like it could cause extreme Andres> slowdowns for large tables that have

Re: Toast issues with OldestXmin going backwards

Hi, On 2018-04-21 14:16:27 +0100, Andrew Gierth wrote: > > "Amit" == Amit Kapila writes: > > Amit> (c) Change the logic during rewrite such that it can detect this > Amit> situation and skip copying the tuple in the main table, > > So I dug into this one and it

Re: Toast issues with OldestXmin going backwards

On Sat, 21 Apr 2018 at 6:46 PM, Andrew Gierth wrote: > > > So I dug into this one and it looks to me like the best approach. Here's > a draft patch against 10-stable, if nobody can think of any showstoppers > then I'll do the rest of the versions and commit them. >

Re: Toast issues with OldestXmin going backwards

> "Amit" == Amit Kapila writes: Amit> (c) Change the logic during rewrite such that it can detect this Amit> situation and skip copying the tuple in the main table, So I dug into this one and it looks to me like the best approach. Here's a draft patch against

Re: Toast issues with OldestXmin going backwards

> "Amit" == Amit Kapila writes: Amit> I haven't tried to reproduce it, but I can see the possibility of Amit> the problem described by you. What should we do next? I could see Amit> few possibilities: (a) Vacuum for main and toast table should Amit> always use

Re: Toast issues with OldestXmin going backwards

On Thu, Apr 19, 2018 at 4:07 PM, Andrew Gierth wrote: > Various comments in GetOldestXmin mention the possibility of the oldest > xmin going backward, and assert that this is actually safe. It's not. > > Consider: > > A table has a toastable column. A row is updated