Re: [BUGS] [HACKERS] Re: Postgresql bug report - unexpected behavior of suppress_redundant_updates_trigger
On 06/19/2017 05:19 PM, David G. Johnston wrote: > At first glance I think I'd rather have it do the correct thing all of > the time, even if it takes longer, so that my only trade-off decision > is whether to improve performance by fixing the application. > > Ideally if the input tuple wouldn't require compression we wouldn't > bother to decompress the stored tuple. That looks like one reasonable elimination check. I wonder how much closer it could get with some changes that wouldn't necessarily use many more cycles. One might be a less_easy queue; marching through the tuple comparing fields, if one is found to be TOASTed, throw it on the queue and march on. Only if all the easy ones matched is there any point in looking at the queue. At that point, there could be a tunable for how much effort to expend. Perhaps I'm willing to decompress an inline value, but not retrieve an out-of-line one? For the TOAST compression algorithm I'm not sure of the balance between compression and decompression effort; I know gzip decompression is pretty cheap. -Chap -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [BUGS] [HACKERS] Re: Postgresql bug report - unexpected behavior of suppress_redundant_updates_trigger
On Mon, Jun 19, 2017 at 2:10 PM, Tom Lanewrote: > Alvaro Herrera writes: >> Tom Lane wrote: >>> ... 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. > >> ISTM the whole point of suppress_redundant_updates_trigger is to cope >> with application stupidity. > > I think it's a suitable band-aid for limited amounts of stupidity. > But it eliminates only a small fraction of the total overhead involved > in a useless update command. So I remain of the opinion that if that's > happening a lot, you're better off fixing the problem somewhere upstream. At first glance I think I'd rather have it do the correct thing all of the time, even if it takes longer, so that my only trade-off decision is whether to improve performance by fixing the application. Ideally if the input tuple wouldn't require compression we wouldn't bother to decompress the stored tuple. David J. -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [BUGS] [HACKERS] Re: Postgresql bug report - unexpected behavior of suppress_redundant_updates_trigger
Alvaro Herrerawrites: > Tom Lane wrote: >> ... 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. > ISTM the whole point of suppress_redundant_updates_trigger is to cope > with application stupidity. I think it's a suitable band-aid for limited amounts of stupidity. But it eliminates only a small fraction of the total overhead involved in a useless update command. So I remain of the opinion that if that's happening a lot, you're better off fixing the problem somewhere upstream. regards, tom lane -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [BUGS] [HACKERS] Re: Postgresql bug report - unexpected behavior of suppress_redundant_updates_trigger
Tom Lane wrote: > 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. ISTM the whole point of suppress_redundant_updates_trigger is to cope with application stupidity. -- Álvaro Herrerahttps://www.2ndQuadrant.com/ PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
Re: [HACKERS] Re: Postgresql bug report - unexpected behavior of suppress_redundant_updates_trigger
J Chapman Flackwrites: > 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 -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers
[HACKERS] Re: Postgresql bug report - unexpected behavior of suppress_redundant_updates_trigger
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? Is the compression algorithm nondeterministic? Is there some way that compressing the same 2001*'a' on two occasions would produce compressed strings that don't match? What exactly is s_r_u_t() comparing, in the case where the TOASTed value has been compressed, but not out-of-lined? -Chap -- Sent via pgsql-hackers mailing list (pgsql-hackers@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-hackers