Re: [HACKERS] Re: Postgresql bug report - unexpected behavior of suppress_redundant_updates_trigger

J Chapman Flack <jflack(at)math(dot)purdue(dot)edu> writes:
> On 06/19/2017 11:40 AM, Dilip Kumar wrote:
>> ... Artus de benque ... wrote:
>>> ...=# UPDATE test_table SET field = rpad('', 2001, 'a') WHERE id = 1;

>> Seems like in "suppress_redundant_updates_trigger" we are comparing
>> toasted tuple with the new tuple and that is the cause of the bug.

> Something still puzzles me about this, though, maybe only because
> I don't know enough about TOAST.

> The size of 'field' ends up 2001, or just over the threshold where
> TOASTing will be attempted at all. The report doesn't mention changing
> the strategy from the default EXTENDED, so won't the first thing
> attempted be compression? Won't that succeed spectacularly, since the
> test string is a single character 2001 times, probably producing
> a compressed string a handful of bytes long, well under the threshold,
> obviating any need to go further with TOAST pointers?

Right, given the facts at hand, the stored old tuple has probably
got a compressed-in-line version of "field". However, the *new*
tuple is a transient tuple containing the uncompressed result of
rpad(). We don't bother to try to compress fields or shove them
out-of-line until after all the BEFORE ROW triggers are done ---
if we did, the effort might just be wasted, if the triggers change
those fields or cancel the update altogether. So the trigger is
seeing a compressed vs. an uncompressed version of the field value,
and since it's just doing a dumb bitwise compare, they don't look
equal.

As I mentioned upthread, it'd certainly be possible for the trigger
to iterate through the fields in a datatype-aware fashion and undo
compression or out-of-lineing before the comparison. But that would
eat a lot more cycles than the current implementation, and it seems
dubious that it's worth it. If the trigger is succeeding (ie,
detecting a no-op update) often enough that it would be worth that,
you've really got an application-stupidity problem to fix.

> Is the compression algorithm nondeterministic?

I don't think so.

regards, tom lane