Postgresql bug report - unexpected behavior of suppress_redundant_updates_trigger

From: Artus de benque <artusdebenque(at)gmail(dot)com>
To: pgsql-bugs(at)postgresql(dot)org
Subject: Postgresql bug report - unexpected behavior of suppress_redundant_updates_trigger
Date: 2017-06-19 11:50:47
Message-ID: CAHhcnVQESG2XkF+JATAQF20TXUHDcvzw4k4JAsLhF7bJzhfriQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs pgsql-hackers

Summary:
*The trigger suppress_redundant_updates_trigger does not suppress updates
of strings larger than 16000 bit (or 2000 octet) with equal values*

Platform information

- PostgreSQL version: 9.6.3, server 9.5.6
- C library version: glibc 2.19 on docker container running
PostgreSQL server (glibc 2.25 on hosting linux)
- uname -a => Linux adb 4.11.3-1-ARCH #1 SMP PREEMPT Sun May 28
10:40:17 CEST 2017 x86_64 GNU/Linux

Although the behavior was observed on various systems.

Full steps to reproduce executed
$ psql -h localhost -U postgres
psql (9.6.3, server 9.5.6)
Type "help" for help.
postgres=# CREATE DATABASE test_db;
CREATE DATABASE
postgres=# CREATE TABLE test_table (id int, field text);
CREATE TABLE
postgres=# INSERT INTO test_table VALUES (1, 'hi');
INSERT 0 1
postgres=# UPDATE test_table SET field = 'hi' WHERE id = 1;
UPDATE 1
postgres=# CREATE TRIGGER suppress_redundant_updates BEFORE UPDATE ON
test_table FOR EACH ROW EXECUTE PROCEDURE
suppress_redundant_updates_trigger();
CREATE TRIGGER
postgres=# UPDATE test_table SET field = 'hi' WHERE id = 1;
UPDATE 0
test_db=# UPDATE test_table SET field = rpad('', 2001, 'a') WHERE id = 1;
UPDATE 1
test_db=# UPDATE test_table SET field = rpad('', 2001, 'a') WHERE id = 1;
UPDATE 1 *<--- BUG: expected 0, as we ran the same update twice*

// other tests:
test_db=# UPDATE test_table SET field = rpad('', 2000, 'a') WHERE id = 1;
UPDATE 1
test_db=# UPDATE test_table SET field = rpad('', 2000, 'a') WHERE id = 1;
UPDATE 0 *<--- exactly 2000 octets, so OK*

test_db=# UPDATE test_table SET field = rpad('', 1000, 'ó') || 'a' WHERE id
= 1;
UPDATE 1
test_db=# UPDATE test_table SET field = rpad('', 1000, 'ó') || 'a' WHERE id
= 1;
UPDATE 1 *<--- BUG: because 'ó' is 2 octet long*

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message Private 2017-06-19 14:10:50 Re: Too many files in pg_replslot folder
Previous Message Ahsan Hadi 2017-06-19 11:01:49 Re: BUG #14709: inconsistent answers with foreign data wrappers to mysql

Browse pgsql-hackers by date

  From Date Subject
Next Message Konstantin Knizhnik 2017-06-19 11:57:27 Re: ASOF join
Previous Message Dean Rasheed 2017-06-19 11:19:53 Rules on table partitions