Re: ANALYZE: ERROR: tuple already updated by self

On Mon, Jul 29, 2019 at 10:15:36AM +0100, Dean Rasheed wrote:
>On Sun, 28 Jul 2019 at 11:15, Tomas Vondra <tomas.vondra@2ndquadrant.com> wrote:
>>
>> Attached is a patch fixing the error by not building extended stats for
>> the inh=true case (as already proposed in this thread). That's about the
>> simplest way to resolve this issue for v12. It should add a simple
>> regression test too, I guess.
>>
>
>Seems like a reasonable thing to do for 10, 11 and possibly also 12
>(actually, as you noted, I think it's the only thing that can be done
>for 10 and 11).
>

OK, will do.

>> To fix this properly we need to add a flag similar to stainherit
>> somewhere. And I've started working on that, thinking that maybe we
>> could do that even for v12 - it'd be yet another catversion bump, but
>> there's already been one since beta2 so maybe it would be OK.
>>
>
>Yeah, I think that makes sense, if it's not too hard. Since v12 is
>where the stats definition is split out from the stats data, this
>might work out quite neatly, since the inh flag would apply only to
>the stats data.
>

Agreed, we need to add the inh flag to the pg_statistic_ext_data
catalog. The trouble is this makes the maintenance somewhat more
complicated, because we suddenly don't have 1:1 mapping :-(

But if we want to address this in master only, I think that's fine.

>> But it's actually a bit more complicated than just adding a column to
>> the catalog, for two reasons:
>>
>> 1) The optimizer part has to be tweaked to pick the right object, with
>> the flag set to either true/false. Not trivial, but doable.
>>
>
>Isn't it just a matter of passing the inh flag to
>get_relation_statistics() from get_relation_info(), so then the
>optimiser would get the right kind of stats data, depending on whether
>or not inheritance was requested in the query.
>

Yes, you're right. I've only skimmed how the existing code uses the inh
flag (for regular stats) and it seemed somewhat more complex, but you're
right for extended stats it'd be much simpler.

regards

-- 
Tomas Vondra                  http://www.2ndQuadrant.com
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services