Hi!
We encountered a bug in our systems with update statement, but long story short, here's the self-containing test case which results in segmentation fault.
CREATE TABLE t1 (a VARCHAR(1));
CREATE TABLE t2 (b VARCHAR(1));
INSERT INTO t1 VALUES ('A');
INSERT INTO t2 VALUES ('A');
COMMIT;
UPDATE t1
SET (a) = (SELECT b FROM t2 WHERE t2.b = t1.a)
WHERE 'X' NOT IN ('Y', 'Z');
Running this (reliably) will result in:
2018-12-12 17:20:27.542 EET [7211] LOG: server process (PID 20620) was terminated by signal 11: Segmentation fault
2018-12-12 17:20:27.542 EET [7211] DETAIL: Failed process was running: UPDATE t1
SET (a) = (SELECT b FROM t2 WHERE t2.b = t1.a)
WHERE 'X' NOT IN ('Y', 'Z')
2018-12-12 17:20:27.542 EET [7211] LOG: terminating any other active server processes
2018-12-12 17:20:27.542 EET [20617] WARNING: terminating connection because of crash of another server process
2018-12-12 17:20:27.542 EET [20617] DETAIL: The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.
2018-12-12 17:20:27.542 EET [20617] HINT: In a moment you should be able to reconnect to the database and repeat your command.
2018-12-12 17:20:27.546 EET [7211] LOG: all server processes terminated; reinitializing
2018-12-12 17:20:27.623 EET [20626] LOG: database system was interrupted; last known up at 2018-12-12 17:20:05 EET
OS logs show:
[2336281.740086] postgres[20477]: segfault at 8 ip 00005640b1892902 sp 00007ffdaa8427e0 error 4 in postgres[5640b17d5000+6da000]
It happened for us in ubuntu 16.04 with 4.19.2 kernel as well as CentOS 7 3.10.0 kernel, so I assume it's OS independent, although I can not verify it on windows.
Postgres version: postgresql-10, 10.5-2.pgdg16.04+1 on Ubuntu and postgresql10-server-10.5-1PGDG.rhel7 on CentOS.
Doing changes like these works fine:
changing SET clause "SET (a) = " to "SET a = " works fine (delete the brackets)
taking WHERE clause away works fine (delete "WHERE 'X' NOT IN ('Y', 'Z')")
rewriting SET value clause ("(SELECT b FROM t2 WHERE t2.b = t1.a)" to constant "(SELECT b FROM t2 WHERE t2.b = 'A')" works fine
--
regards
Eduards Bezverhijs
Technical PM & Lead Consultant / Architect @ Tieto Latvia
