backend crash on DELETE, reproducible locally

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:

I am running this delete statement via psql
DELETE FROM ref.auto_hint WHERE title = 'Kontraindikation: ACE/Sartan <-> Schwangerschaft';

Doesn't look particularly special ...

What else can I provide/test/look into ?

Stack trace would be helpful, self-contained test case even better.

https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

regards, tom lane

I am running this delete statement via psql

DELETE FROM ref.auto_hint WHERE title = 'Kontraindikation: ACE/Sartan <-> Schwangerschaft';

and it crashes the backend it is running on.

For what it is worth I have identified the physical file

gnumed_v21=# select pg_relation_filepath('ref.auto_hint');
pg_relation_filepath
----------------------
base/139580/111088
(1 Zeile)

and phyically copied that to another place (so there's no
read error on the disk - but the DELETE would perhaps ask for
a physical write test ?).

Attached the output of pg_filedump which, to me, doesn't seem
to show anything out of the ordinary either.

Corrupt index should not be the case because the databse is
REINDEXed during the upgrade process before the failing
statement.

Will attempt to get a stack trace ...

Thanks,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 11.0
*
* File: 111088_fsm
* Options used: -v -f -i -k 
*
* Dump created on: Thu Nov  1 15:37:59 2018
*******************************************************************

Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      24 (0x0018)
 Block: Size 8192  Version    4            Upper    8192 (0x2000)
 LSN:  logid      0 recoff 0x00000000      Special  8192 (0x2000)
 Items:    0                      Free Space: 8168
 Checksum: 0x6560  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 24

  0000: 00000000 00000000 60650000 18000020  ........`e..... 
  0010: 00200420 00000000                    . . ....        

<Data> ------ 
 Empty block - no items listed 


Block    1 ********************************************************
<Header> -----
 Block Offset: 0x00002000         Offsets: Lower      24 (0x0018)
 Block: Size 8192  Version    4            Upper    8192 (0x2000)
 LSN:  logid      0 recoff 0x00000000      Special  8192 (0x2000)
 Items:    0                      Free Space: 8168
 Checksum: 0x655f  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 24

  0000: 00000000 00000000 5f650000 18000020  ........_e..... 
  0010: 00200420 00000000                    . . ....        

<Data> ------ 
 Empty block - no items listed 


Block    2 ********************************************************
<Header> -----
 Block Offset: 0x00004000         Offsets: Lower      24 (0x0018)
 Block: Size 8192  Version    4            Upper    8192 (0x2000)
 LSN:  logid      0 recoff 0x465b8208      Special  8192 (0x2000)
 Items:    0                      Free Space: 8168
 Checksum: 0xeb9b  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 24

  0000: 00000000 08825b46 9beb0000 18000020  ......[F....... 
  0010: 00200420 00000000                    . . ....        

<Data> ------ 
 Empty block - no items listed 


*** End of File Encountered. Last Block Read: 2 ***

*******************************************************************
* PostgreSQL File/Block Formatted Dump Utility - Version 11.0
*
* File: 111088
* Options used: -v -f -i -k 
*
* Dump created on: Thu Nov  1 15:37:24 2018
*******************************************************************

Block    0 ********************************************************
<Header> -----
 Block Offset: 0x00000000         Offsets: Lower      68 (0x0044)
 Block: Size 8192  Version    4            Upper    2680 (0x0a78)
 LSN:  logid      0 recoff 0x46a073a0      Special  8192 (0x2000)
 Items:   11                      Free Space: 2612
 Checksum: 0x3086  Prune XID: 0x0001df49  Flags: 0x0000 ()
 Length (including item array): 68

  0000: 00000000 a073a046 86300000 4400780a  .....s.F.0..D.x.
  0010: 00200420 49df0100 009e0004 00800100  . . I...........
  0020: 00800100 e89a2a06 48994003 00800100  ......*.H.@.....
  0030: 14956208 b892b604 b890fa03 3c8df606  ..b.........<...
  0040: 788a8405                             x...            

<Data> ------ 
 Item   1 -- Length:  512  Offset: 7680 (0x1e00)  Flags: NORMAL
  XMIN: 119568  XMAX: 0  CID|XVAC: 0
  Block Id: 0  linp Index: 1   Attributes: 12   Size: 24
  infomask: 0x0902 (HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID) 

  1e00: 10d30100 00000000 00000000 00000000  ................
  1e10: 01000c00 02091800 a6340000 00000000  .........4......
  1e20: 8f2bee84 971c0200 676d2d64 626f0000  .+......gm-dbo..
  1e30: 00000000 00000000 00000000 00000000  ................
  1e40: 00000000 00000000 00000000 00000000  ................
  1e50: 00000000 00000000 00000000 00000000  ................
  1e60: 00000000 00000000 01000000 fb53454c  .............SEL
  1e70: 45435420 45584953 54532853 454c4543  ECT EXISTS(SELEC
  1e80: 54203120 46524f4d 20636c69 6e2e765f  T 1 FROM clin.v_
  1e90: 66616d69 6c795f68 6973746f 72792057  family_history W
  1ea0: 48455245 20636f6e 64697469 6f6e207e  HERE condition ~
  1eb0: 2a202764 656e6576 616e2e2a 70617261  * 'denevan.*para
  1ec0: 73697427 20414e44 20706b5f 70617469  sit' AND pk_pati
  1ed0: 656e7420 3d204944 5f414354 4956455f  ent = ID_ACTIVE_
  1ee0: 50415449 454e5429 3b595374 61727472  PATIENT);YStartr
  1ef0: 656b3a20 44656e65 76616e20 6e657572  ek: Denevan neur
  1f00: 616c2070 61726173 69746520 73637265  al parasite scre
  1f10: 656e696e 67ed5061 7469656e 74732077  ening.Patients w
  1f20: 69746820 6578706f 73757265 20746f20  ith exposure to 
  1f30: 44656e65 76616e20 4e657572 616c2050  Denevan Neural P
  1f40: 61726173 69746520 63617272 69657273  arasite carriers
  1f50: 2073686f 756c6420 756e6465 72676f20   should undergo 
  1f60: 45454720 7363616e 6e696e67 20616e64  EEG scanning and
  1f70: 20627261 696e2069 6d616769 6e672062   brain imaging b
  1f80: 692d616e 6e75616c 6c792e83 68747470  i-annually..http
  1f90: 3a2f2f77 77772e73 74617274 72656b2e  ://www.startrek.
  1fa0: 636f6d2f 64617461 62617365 5f617274  com/database_art
  1fb0: 69636c65 2f64656e 6576616e 2d6e6575  icle/denevan-neu
  1fc0: 72616c2d 70617261 73697465 01615374  ral-parasite.aSt
  1fd0: 6172666c 65657420 43656e74 72616c20  arfleet Central 
  1fe0: 50726576 656e7469 76652054 61736b20  Preventive Task 
  1ff0: 466f7263 65202876 31372e30 2907656e  Force (v17.0).en

 Item   2 -- Length:    0  Offset:    0 (0x0000)  Flags: DEAD
 Item   3 -- Length:    0  Offset:    0 (0x0000)  Flags: DEAD
 Item   4 -- Length:  789  Offset: 6888 (0x1ae8)  Flags: NORMAL
  XMIN: 122698  XMAX: 128765  CID|XVAC: 0
  Block Id: 0  linp Index: 4   Attributes: 12   Size: 24
  infomask: 0x0102 (HASVARWIDTH|XMIN_COMMITTED|KEYS_UPDATED) 

  1ae8: 4adf0100 fdf60100 00000000 00000000  J...............
  1af8: 04000c20 02011800 53350000 00000000  ... ....S5......
  1b08: 69bd669a 971c0200 676d2d64 626f0000  i.f.....gm-dbo..
  1b18: 00000000 00000000 00000000 00000000  ................
  1b28: 00000000 00000000 00000000 00000000  ................
  1b38: 00000000 00000000 00000000 00000000  ................
  1b48: 00000000 00000000 04000000 c0060000  ................
  1b58: 53454c45 43542045 58495354 5320280a  SELECT EXISTS (.
  1b68: 0953454c 45435420 31204652 4f4d2063  .SELECT 1 FROM c
  1b78: 6c696e2e 765f7375 62737461 6e63655f  lin.v_substance_
  1b88: 696e7461 6b657320 57484552 450a0909  intakes WHERE...
  1b98: 706b5f70 61746965 6e74203d 2049445f  pk_patient = ID_
  1ba8: 41435449 56455f50 41544945 4e540a09  ACTIVE_PATIENT..
  1bb8: 0909414e 440a0909 73756273 74616e63  ..AND...substanc
  1bc8: 65207e2a 20272e2a 73617274 616e2e2a  e ~* '.*sartan.*
  1bd8: 270a0909 094f520a 09097375 62737461  '....OR...substa
  1be8: 6e636520 7e2a2027 2e2a616e 67696f74  nce ~* '.*angiot
  1bf8: 656e7369 6e2e2a27 0a090909 4f520a09  ensin.*'....OR..
  1c08: 09737562 7374616e 6365207e 20272e2a  .substance ~ '.*
  1c18: 4143452e 2a270a09 09094f52 0a090973  ACE.*'....OR...s
  1c28: 75627374 616e6365 207e2a20 272e2a70  ubstance ~* '.*p
  1c38: 72696c2e 2a270a09 09094f52 0a090961  ril.*'....OR...a
  1c48: 74635f62 72616e64 207e2a20 275e4330  tc_brand ~* '^C0
  1c58: 392e2a27 0a090909 4f520a09 09617463  9.*'....OR...atc
  1c68: 5f737562 7374616e 6365207e 2a20275e  _substance ~* '^
  1c78: 4330392e 2a270a29 20414e44 20455849  C09.*'.) AND EXI
  1c88: 53545320 280a0953 454c4543 54203120  STS (..SELECT 1 
  1c98: 46524f4d 20636c69 6e2e765f 6e617272  FROM clin.v_narr
  1ca8: 61746976 65347365 61726368 20574845  ative4search WHE
  1cb8: 52450a09 09706b5f 70617469 656e7420  RE...pk_patient 
  1cc8: 3d204944 5f414354 4956455f 50415449  = ID_ACTIVE_PATI
  1cd8: 454e540a 09090941 4e440a09 096e6172  ENT....AND...nar
  1ce8: 72617469 7665207e 2a20272e 2a736368  rative ~* '.*sch
  1cf8: 77616e67 65722e2a 270a293b 634b6f6e  wanger.*'.);cKon
  1d08: 74726169 6e64696b 6174696f 6e3a2041  traindikation: A
  1d18: 43452f53 61727461 6e203c2d 3e205363  CE/Sartan <-> Sc
  1d28: 6877616e 67657273 63686166 74ad4143  hwangerschaft.AC
  1d38: 452d4865 6d6d6572 20756e64 20536172  E-Hemmer und Sar
  1d48: 74616e65 206bc3b6 6e6e656e 20696d20  tane k..nnen im 
  1d58: 322e2075 6e642033 2e205472 696d656e  2. und 3. Trimen
  1d68: 6f6e2073 63687765 72652046 65746f70  on schwere Fetop
  1d78: 61746869 656e2068 6572766f 72727566  athien hervorruf
  1d88: 656e2ea5 68747470 3a2f2f77 77772e61  en..http://www.a
  1d98: 6b646165 2e64652f 41727a6e 65696d69  kdae.de/Arzneimi
  1da8: 7474656c 73696368 65726865 69742f42  ttelsicherheit/B
  1db8: 656b616e 6e746761 62656e2f 41726368  ekanntgaben/Arch
  1dc8: 69762f32 3031302f 32303130 31303135  iv/2010/20101015
  1dd8: 312e7064 66013947 4e556d65 64206465  1.pdf.9GNUmed de
  1de8: 6661756c 74202841 6b64c384 20323031  fault (Akd.. 201
  1df8: 32290764 65                          2).de           

 Item   5 -- Length:  416  Offset: 6472 (0x1948)  Flags: NORMAL
  XMIN: 127397  XMAX: 0  CID|XVAC: 0
  Block Id: 0  linp Index: 5   Attributes: 13   Size: 28
  infomask: 0x0903 (HASNULL|HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID) 
  t_bits: [0]: 0xff [1]: 0x0e 

  1948: a5f10100 00000000 00000000 00000000  ................
  1958: 05000d00 03091cff 0e000000 da350000  .............5..
  1968: 00000000 33f70adf 981c0200 676d2d64  ....3.......gm-d
  1978: 626f0000 00000000 00000000 00000000  bo..............
  1988: 00000000 00000000 00000000 00000000  ................
  1998: 00000000 00000000 00000000 00000000  ................
  19a8: 00000000 00000000 00000000 05000000  ................
  19b8: 80020000 53454c45 4354204e 4f542045  ....SELECT NOT E
  19c8: 58495354 53285345 4c454354 20312046  XISTS(SELECT 1 F
  19d8: 524f4d20 636c696e 2e765f6e 6f6e6272  ROM clin.v_nonbr
  19e8: 616e645f 696e7461 6b657320 57484552  and_intakes WHER
  19f8: 4520706b 5f706174 69656e74 203d2049  E pk_patient = I
  1a08: 445f4143 54495645 5f504154 49454e54  D_ACTIVE_PATIENT
  1a18: 20414e44 20617463 5f737562 7374616e   AND atc_substan
  1a28: 6365203d 20274e30 37424130 31272041  ce = 'N07BA01' A
  1a38: 4e442068 61726d66 756c5f75 73655f74  ND harmful_use_t
  1a48: 79706520 4953204e 4f54204e 554c4c29  ype IS NOT NULL)
  1a58: 4b4c6163 6b206f66 20736d6f 6b696e67  KLack of smoking
  1a68: 20737461 74757320 646f6375 6d656e74   status document
  1a78: 6174696f 6e875468 65726520 68617320  ation.There has 
  1a88: 6e657665 72206265 656e2061 6e792073  never been any s
  1a98: 6d6f6b69 6e672073 74617475 73207265  moking status re
  1aa8: 636f7264 65642066 6f722074 68697320  corded for this 
  1ab8: 70617469 656e742e 01494157 4d46204e  patient..IAWMF N
  1ac8: 564c2053 6368c3a4 646c6963 68657220  VL Sch..dlicher 
  1ad8: 54616261 6b676562 72617563 6807656e  Tabakgebrauch.en

 Item   6 -- Length:    0  Offset:    0 (0x0000)  Flags: DEAD
 Item   7 -- Length: 1073  Offset: 5396 (0x1514)  Flags: NORMAL
  XMIN: 127399  XMAX: 0  CID|XVAC: 0
  Block Id: 0  linp Index: 7   Attributes: 13   Size: 24
  infomask: 0x0902 (HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID) 

  1514: a7f10100 00000000 00000000 00000000  ................
  1524: 07000d00 02091800 dc350000 00000000  .........5......
  1534: 2af90bdf 981c0200 676d2d64 626f0000  *.......gm-dbo..
  1544: 00000000 00000000 00000000 00000000  ................
  1554: 00000000 00000000 00000000 00000000  ................
  1564: 00000000 00000000 00000000 00000000  ................
  1574: 00000000 00000000 07000000 a8090000  ................
  1584: 53454c45 43542045 58495354 5320280a  SELECT EXISTS (.
  1594: 092d2d20 73756273 74616e63 65206368  .-- substance ch
  15a4: 65636b0a 0953454c 45435420 31204652  eck..SELECT 1 FR
  15b4: 4f4d2063 6c696e2e 765f7375 62737461  OM clin.v_substa
  15c4: 6e63655f 696e7461 6b657320 57484552  nce_intakes WHER
  15d4: 450a0909 706b5f70 61746965 6e74203d  E...pk_patient =
  15e4: 2049445f 41435449 56455f50 41544945   ID_ACTIVE_PATIE
  15f4: 4e540a09 0909414e 440a0909 2d2d206f  NT....AND...-- o
  1604: 6e205361 7274616e 206f7220 41434549  n Sartan or ACEI
  1614: 0a090928 0a090909 73756273 74616e63  ...(....substanc
  1624: 65207e2a 20272e2a 73617274 616e2e2a  e ~* '.*sartan.*
  1634: 270a0909 09094f52 0a090909 73756273  '.....OR....subs
  1644: 74616e63 65207e2a 20272e2a 616e6769  tance ~* '.*angi
  1654: 6f74656e 73696e2e 2a270a09 0909094f  otensin.*'.....O
  1664: 520a0909 09737562 7374616e 6365207e  R....substance ~
  1674: 20272e2a 4143452e 2a270a09 0909094f   '.*ACE.*'.....O
  1684: 520a0909 092d2d20 6d696768 74207265  R....-- might re
  1694: 73756c74 20696e20 66616c73 6520706f  sult in false po
  16a4: 73697469 76657320 282e5072 696c6f63  sitives (.Priloc
  16b4: 61727069 6e290a09 09097375 62737461  arpin)....substa
  16c4: 6e636520 7e2a2027 2e2b7072 696c2e2a  nce ~* '.+pril.*
  16d4: 270a0909 09094f52 0a090909 6174635f  '.....OR....atc_
  16e4: 6272616e 64207e2a 20275e43 30392e2a  brand ~* '^C09.*
  16f4: 270a0909 09094f52 0a090909 6174635f  '.....OR....atc_
  1704: 73756273 74616e63 65207e2a 20275e43  substance ~* '^C
  1714: 30392e2a 270a0929 0a292041 4e442045  09.*'..).) AND E
  1724: 58495354 5320280a 092d2d20 70726567  XISTS (..-- preg
  1734: 6e616e63 79206368 65636b0a 0953454c  nancy check..SEL
  1744: 45435420 31204652 4f4d2063 6c696e2e  ECT 1 FROM clin.
  1754: 70617469 656e7420 57484552 450a0909  patient WHERE...
  1764: 666b5f69 64656e74 69747920 3d204944  fk_identity = ID
  1774: 5f414354 4956455f 50415449 454e540a  _ACTIVE_PATIENT.
  1784: 09090941 4e440a09 09636f61 6c657363  ...AND...coalesc
  1794: 65286564 63204245 54574545 4e206e6f  e(edc BETWEEN no
  17a4: 77282920 2d202731 206d6f6e 7468273a  w() - '1 month':
  17b4: 3a696e74 65727661 6c20414e 44206e6f  :interval AND no
  17c4: 77282920 2b202731 31206d6f 6e746873  w() + '11 months
  17d4: 273a3a69 6e746572 76616c2c 2046414c  '::interval, FAL
  17e4: 5345290a 293b5943 6f6e7472 61696e64  SE).);YContraind
  17f4: 69636174 696f6e3a 20414345 492f5361  ication: ACEI/Sa
  1804: 7274616e 203c2d3e 20507265 676e616e  rtan <-> Pregnan
  1814: 6379a741 43454920 616e6420 53617274  cy.ACEI and Sart
  1824: 616e7320 63616e20 63617573 65207365  ans can cause se
  1834: 76657265 20666574 6f706174 68696573  vere fetopathies
  1844: 20696620 75736564 20647572 696e6720   if used during 
  1854: 326e6420 616e6420 33726420 7472696d  2nd and 3rd trim
  1864: 656e6f6e 2ea56874 74703a2f 2f777777  enon..http://www
  1874: 2e616b64 61652e64 652f4172 7a6e6569  .akdae.de/Arznei
  1884: 6d697474 656c7369 63686572 68656974  mittelsicherheit
  1894: 2f42656b 616e6e74 67616265 6e2f4172  /Bekanntgaben/Ar
  18a4: 63686976 2f323031 302f3230 31303130  chiv/2010/201010
  18b4: 3135312e 70646601 39474e55 6d656420  151.pdf.9GNUmed 
  18c4: 64656661 756c7420 28416b64 c3842032  default (Akd.. 2
  18d4: 30313229 07656ed5 53454c45 4354205f  012).en.SELECT _
  18e4: 28274544 433a2027 29207c7c 20746f5f  ('EDC: ') || to_
  18f4: 63686172 28656463 2c202759 59595920  char(edc, 'YYYY 
  1904: 4d6f6e20 44442729 2046524f 4d20636c  Mon DD') FROM cl
  1914: 696e2e70 61746965 6e742057 48455245  in.patient WHERE
  1924: 20666b5f 6964656e 74697479 203d2049   fk_identity = I
  1934: 445f4143 54495645 5f504154 49454e54  D_ACTIVE_PATIENT
  1944: 3b                                   ;               

 Item   8 -- Length:  603  Offset: 4792 (0x12b8)  Flags: NORMAL
  XMIN: 127400  XMAX: 0  CID|XVAC: 0
  Block Id: 0  linp Index: 8   Attributes: 13   Size: 28
  infomask: 0x0803 (HASNULL|HASVARWIDTH|XMAX_INVALID) 
  t_bits: [0]: 0xff [1]: 0x1e 

  12b8: a8f10100 00000000 00000000 00000000  ................
  12c8: 08000d00 03081cff 1e000000 dd350000  .............5..
  12d8: 00000000 a4230cdf 981c0200 676d2d64  .....#......gm-d
  12e8: 626f0000 00000000 00000000 00000000  bo..............
  12f8: 00000000 00000000 00000000 00000000  ................
  1308: 00000000 00000000 00000000 00000000  ................
  1318: 00000000 00000000 00000000 08000000  ................
  1328: 1c040000 53454c45 43542045 58495354  ....SELECT EXIST
  1338: 5320280a 0953454c 45435420 31204652  S (..SELECT 1 FR
  1348: 4f4d2063 6c696e2e 70617469 656e7420  OM clin.patient 
  1358: 57484552 450a0909 666b5f69 64656e74  WHERE...fk_ident
  1368: 69747920 3d204944 5f414354 4956455f  ity = ID_ACTIVE_
  1378: 50415449 454e540a 09090941 4e440a09  PATIENT....AND..
  1388: 09636f61 6c657363 6528280a 0909092d  .coalesce((....-
  1398: 2d206c6f 6e676572 20746861 6e203320  - longer than 3 
  13a8: 6d6f6e74 68732061 676f0a09 09096564  months ago....ed
  13b8: 63203c20 6e6f7728 29202d20 2733206d  c < now() - '3 m
  13c8: 6f6e7468 73273a3a 696e7465 7276616c  onths'::interval
  13d8: 0a090909 094f520a 0909092d 2d203220  .....OR....-- 2 
  13e8: 79656172 7320696e 20746865 20667574  years in the fut
  13f8: 7572650a 09090965 6463203e 206e6f77  ure....edc > now
  1408: 2829202b 20273220 79656172 73273a3a  () + '2 years'::
  1418: 696e7465 7276616c 0a090929 2c204641  interval...), FA
  1428: 4c534529 0a293b3b 4f757464 61746564  LSE).);;Outdated
  1438: 206f7220 71756573 74696f6e 61626c65   or questionable
  1448: 20454443 95546865 20454443 20646f63   EDC.The EDC doc
  1458: 756d656e 74656420 666f7220 74686973  umented for this
  1468: 20706174 69656e74 20697320 6f757464   patient is outd
  1478: 61746564 206f7220 746f6f20 66617220  ated or too far 
  1488: 696e2074 68652066 75747572 652e011f  in the future...
  1498: 474e556d 65642064 65666175 6c740765  GNUmed default.e
  14a8: 6ed55345 4c454354 205f2827 4544433a  n.SELECT _('EDC:
  14b8: 20272920 7c7c2074 6f5f6368 61722865   ') || to_char(e
  14c8: 64632c20 27595959 59204d6f 6e204444  dc, 'YYYY Mon DD
  14d8: 27292046 524f4d20 636c696e 2e706174  ') FROM clin.pat
  14e8: 69656e74 20574845 52452066 6b5f6964  ient WHERE fk_id
  14f8: 656e7469 7479203d 2049445f 41435449  entity = ID_ACTI
  1508: 56455f50 41544945 4e543b             VE_PATIENT;     

 Item   9 -- Length:  509  Offset: 4280 (0x10b8)  Flags: NORMAL
  XMIN: 127401  XMAX: 0  CID|XVAC: 0
  Block Id: 0  linp Index: 9   Attributes: 13   Size: 28
  infomask: 0x0803 (HASNULL|HASVARWIDTH|XMAX_INVALID) 
  t_bits: [0]: 0xff [1]: 0x1e 

  10b8: a9f10100 00000000 00000000 00000000  ................
  10c8: 09000d00 03081cff 1e000000 de350000  .............5..
  10d8: 00000000 244e0cdf 981c0200 676d2d64  ....$N......gm-d
  10e8: 626f0000 00000000 00000000 00000000  bo..............
  10f8: 00000000 00000000 00000000 00000000  ................
  1108: 00000000 00000000 00000000 00000000  ................
  1118: 00000000 00000000 00000000 09000000  ................
  1128: 34030000 53454c45 43542028 0a095345  4...SELECT (..SE
  1138: 4c454354 20645f69 2e67656e 64657220  LECT d_i.gender 
  1148: 3d20276d 27204652 4f4d2064 656d2e69  = 'm' FROM dem.i
  1158: 64656e74 69747920 645f6920 57484552  dentity d_i WHER
  1168: 450a0909 645f692e 706b203d 2049445f  E...d_i.pk = ID_
  1178: 41435449 56455f50 41544945 4e540a29  ACTIVE_PATIENT.)
  1188: 20414e44 20455849 53545320 280a0953   AND EXISTS (..S
  1198: 454c4543 54203120 46524f4d 20636c69  ELECT 1 FROM cli
  11a8: 6e2e7061 7469656e 74205748 4552450a  n.patient WHERE.
  11b8: 0909666b 5f696465 6e746974 79203d20  ..fk_identity = 
  11c8: 49445f41 43544956 455f5041 5449454e  ID_ACTIVE_PATIEN
  11d8: 540a0909 09414e44 0a090965 64632049  T....AND...edc I
  11e8: 53204e4f 54204e55 4c4c0a29 3b235175  S NOT NULL.);#Qu
  11f8: 65737469 6f6e6162 6c652045 44436554  estionable EDCeT
  1208: 68657265 20697320 616e2045 44432064  here is an EDC d
  1218: 6f63756d 656e7465 6420666f 72207468  ocumented for th
  1228: 6973204d 414c4520 70617469 656e742e  is MALE patient.
  1238: 011f474e 556d6564 20646566 61756c74  ..GNUmed default
  1248: 07656ed5 53454c45 4354205f 28274544  .en.SELECT _('ED
  1258: 433a2027 29207c7c 20746f5f 63686172  C: ') || to_char
  1268: 28656463 2c202759 59595920 4d6f6e20  (edc, 'YYYY Mon 
  1278: 44442729 2046524f 4d20636c 696e2e70  DD') FROM clin.p
  1288: 61746965 6e742057 48455245 20666b5f  atient WHERE fk_
  1298: 6964656e 74697479 203d2049 445f4143  identity = ID_AC
  12a8: 54495645 5f504154 49454e54 3b        TIVE_PATIENT;   

 Item  10 -- Length:  891  Offset: 3388 (0x0d3c)  Flags: NORMAL
  XMIN: 127841  XMAX: 0  CID|XVAC: 0
  Block Id: 0  linp Index: 10   Attributes: 13   Size: 28
  infomask: 0x0803 (HASNULL|HASVARWIDTH|XMAX_INVALID) 
  t_bits: [0]: 0xff [1]: 0x1e 

  0d3c: 61f30100 00000000 00000000 00000000  a...............
  0d4c: 0a000d00 03081cff 1e000000 0f360000  .............6..
  0d5c: 00000000 3bc315e2 981c0200 676d2d64  ....;.......gm-d
  0d6c: 626f0000 00000000 00000000 00000000  bo..............
  0d7c: 00000000 00000000 00000000 00000000  ................
  0d8c: 00000000 00000000 00000000 00000000  ................
  0d9c: 00000000 00000000 00000000 0a000000  ................
  0dac: cc050000 53454c45 43542028 0a09092d  ....SELECT (...-
  0dbc: 2d206e6f 74206465 63656173 65640a09  - not deceased..
  0dcc: 0953454c 45435420 64656365 61736564  .SELECT deceased
  0ddc: 20697320 4e554c4c 2046524f 4d206465   is NULL FROM de
  0dec: 6d2e6964 656e7469 74792057 48455245  m.identity WHERE
  0dfc: 20706b20 3d204944 5f414354 4956455f   pk = ID_ACTIVE_
  0e0c: 50415449 454e540a 09292041 4e44204e  PATIENT..) AND N
  0e1c: 4f542045 58495354 5320280a 09092d2d  OT EXISTS (...--
  0e2c: 206e6f20 74657461 6e757320 73686f74   no tetanus shot
  0e3c: 20646f63 756d656e 7465640a 09095345   documented...SE
  0e4c: 4c454354 20312046 524f4d20 636c696e  LECT 1 FROM clin
  0e5c: 2e765f70 61745f76 61636373 34696e64  .v_pat_vaccs4ind
  0e6c: 69636174 696f6e0a 09095748 4552450a  ication...WHERE.
  0e7c: 09090970 6b5f7061 7469656e 74203d20  ...pk_patient = 
  0e8c: 49445f41 43544956 455f5041 5449454e  ID_ACTIVE_PATIEN
  0e9c: 540a0909 0909414e 440a0909 09696e64  T.....AND....ind
  0eac: 69636174 696f6e20 3d202774 6574616e  ication = 'tetan
  0ebc: 7573270a 09090909 414e440a 09090964  us'.....AND....d
  0ecc: 6174655f 67697665 6e203e20 6e6f7728  ate_given > now(
  0edc: 29202d20 27313020 79656172 73273a3a  ) - '10 years'::
  0eec: 696e7465 7276616c 0a09094f 52444552  interval...ORDER
  0efc: 2042590a 09090964 6174655f 67697665   BY....date_give
  0f0c: 6e204445 53430a09 094c494d 49542031  n DESC...LIMIT 1
  0f1c: 0a293b43 54657461 6e75732d 496d7066  .);CTetanus-Impf
  0f2c: 67203e20 3130204a 61687265 20285354  g > 10 Jahre (ST
  0f3c: 494b4f29 774c6574 7a746520 54657461  IKO)wLetzte Teta
  0f4c: 6e757369 6d706675 6e672076 6f72206d  nusimpfung vor m
  0f5c: 65687220 616c7320 3130204a 61687265  ehr als 10 Jahre
  0f6c: 6e20646f 6b756d65 6e746965 72742e01  n dokumentiert..
  0f7c: 17535449 4b4f2032 30313607 64650000  .STIKO 2016.de..
  0f8c: ac040000 53454c45 43542063 6f616c65  ....SELECT coale
  0f9c: 73636520 280a0928 53454c45 43540a09  sce (..(SELECT..
  0fac: 09274c65 747a7465 20546574 616e7573  .'Letzte Tetanus
  0fbc: 696d7066 756e673a 2027207c 7c20746f  impfung: ' || to
  0fcc: 5f636861 72286461 74655f67 6976656e  _char(date_given
  0fdc: 2c202759 59595920 4d6f6e20 44442729  , 'YYYY Mon DD')
  0fec: 0a094652 4f4d2063 6c696e2e 765f7061  ..FROM clin.v_pa
  0ffc: 745f7661 63637334 696e6469 63617469  t_vaccs4indicati
  100c: 6f6e0a09 57484552 450a0909 706b5f70  on..WHERE...pk_p
  101c: 61746965 6e74203d 2049445f 41435449  atient = ID_ACTI
  102c: 56455f50 41544945 4e540a09 0909414e  VE_PATIENT....AN
  103c: 440a0909 696e6469 63617469 6f6e203d  D...indication =
  104c: 20277465 74616e75 73270a09 4f524445   'tetanus'..ORDE
  105c: 52204259 0a090964 6174655f 67697665  R BY...date_give
  106c: 6e204445 53430a09 4c494d49 54203129  n DESC..LIMIT 1)
  107c: 2c0a0927 6b65696e 65205465 74616e75  ,..'keine Tetanu
  108c: 73696d70 66756e67 20646f6b 756d656e  simpfung dokumen
  109c: 74696572 74270a29 20617320 7265636f  tiert'.) as reco
  10ac: 6d6d656e 64617469 6f6e3b             mmendation;     

 Item  11 -- Length:  706  Offset: 2680 (0x0a78)  Flags: NORMAL
  XMIN: 127860  XMAX: 127860  CID|XVAC: 0
  Block Id: 0  linp Index: 11   Attributes: 13   Size: 28
  infomask: 0x2093 (HASNULL|HASVARWIDTH|XMAX_KEYSHR_LOCK|XMAX_LOCK_ONLY|UPDATED) 
  t_bits: [0]: 0xff [1]: 0x0f 

  0a78: 74f30100 74f30100 00000000 00000000  t...t...........
  0a88: 0b000d00 93201cff 0f000000 52350000  ..... ......R5..
  0a98: 01000000 98ba89e2 981c0200 676d2d64  ............gm-d
  0aa8: 626f0000 00000000 00000000 00000000  bo..............
  0ab8: 00000000 00000000 00000000 00000000  ................
  0ac8: 00000000 00000000 00000000 00000000  ................
  0ad8: 00000000 00000000 00000000 03000000  ................
  0ae8: 30070000 53454c45 43542028 0a095345  0...SELECT (..SE
  0af8: 4c454354 0a09092d 2d206b6e 6f776e20  LECT...-- known 
  0b08: 444f420a 0909645f 692e646f 62206973  DOB...d_i.dob is
  0b18: 206e6f74 204e554c 4c0a0909 09414e44   not NULL....AND
  0b28: 0a09092d 2d206e6f 74206465 63656173  ...-- not deceas
  0b38: 65640a09 09645f69 2e646563 65617365  ed...d_i.decease
  0b48: 64206973 204e554c 4c0a0946 524f4d0a  d is NULL..FROM.
  0b58: 09096465 6d2e6964 656e7469 74792064  ..dem.identity d
  0b68: 5f690a09 57484552 450a0909 645f692e  _i..WHERE...d_i.
  0b78: 706b203d 2049445f 41435449 56455f50  pk = ID_ACTIVE_P
  0b88: 41544945 4e540a29 20414e44 20280a09  ATIENT.) AND (..
  0b98: 53454c45 43540a09 09616765 28645f69  SELECT...age(d_i
  0ba8: 2e646f62 29203e3d 20273335 20796561  .dob) >= '35 yea
  0bb8: 7273273a 3a696e74 65727661 6c0a0946  rs'::interval..F
  0bc8: 524f4d0a 09096465 6d2e6964 656e7469  ROM...dem.identi
  0bd8: 74792064 5f690a09 57484552 450a0909  ty d_i..WHERE...
  0be8: 645f692e 706b203d 2049445f 41435449  d_i.pk = ID_ACTI
  0bf8: 56455f50 41544945 4e540a29 20414e44  VE_PATIENT.) AND
  0c08: 204e4f54 20455849 53545320 280a0953   NOT EXISTS (..S
  0c18: 454c4543 54203120 46524f4d 20636c69  ELECT 1 FROM cli
  0c28: 6e2e765f 656d725f 6a6f7572 6e616c0a  n.v_emr_journal.
  0c38: 09574845 52450a09 09706b5f 70617469  .WHERE...pk_pati
  0c48: 656e7420 3d204944 5f414354 4956455f  ent = ID_ACTIVE_
  0c58: 50415449 454e540a 09090941 4e440a09  PATIENT....AND..
  0c68: 096e6172 72617469 7665207e 2a20272e  .narrative ~* '.
  0c78: 2a636865 636b7570 2e2a270a 09090941  *checkup.*'....A
  0c88: 4e440a09 09616765 28636c69 6e5f7768  ND...age(clin_wh
  0c98: 656e2920 3c202732 20796561 7273273a  en) < '2 years':
  0ca8: 3a696e74 65727661 6c0a293b 33474b56  :interval.);3GKV
  0cb8: 2d436865 636b7570 20c3bc62 657266c3  -Checkup ..berf.
  0cc8: a46c6c69 677d4465 72205061 7469656e  .llig}Der Patien
  0cd8: 74206861 74746520 696e2064 656e206c  t hatte in den l
  0ce8: 65747a74 656e2032 204a6168 72656e20  etzten 2 Jahren 
  0cf8: 6b65696e 656e2047 4b562d43 6865636b  keinen GKV-Check
  0d08: 75702e39 64696520 c3bc626c 69636865  up.9die ..bliche
  0d18: 6e205665 7264c3a4 63687469 67656e01  n Verd..chtigen.
  0d28: 1f474e55 6d656420 64656661 756c7407  .GNUmed default.
  0d38: 6465                                 de              



Block    1 ********************************************************
<Header> -----
 Block Offset: 0x00002000         Offsets: Lower      44 (0x002c)
 Block: Size 8192  Version    4            Upper    1108 (0x0454)
 LSN:  logid      0 recoff 0x46a08fc0      Special  8192 (0x2000)
 Items:    5                      Free Space: 1064
 Checksum: 0x67c1  Prune XID: 0x00000000  Flags: 0x0000 ()
 Length (including item array): 44

  0000: 00000000 c08fa046 c1670000 2c005404  .......F.g..,.T.
  0010: 00200420 00000000 2c99a40d 4091d80f  . . ....,...@...
  0020: 588cca09 c0882a07 5484d808           X.....*.T...    

<Data> ------ 
 Item   1 -- Length: 1746  Offset: 6444 (0x192c)  Flags: NORMAL
  XMIN: 127843  XMAX: 0  CID|XVAC: 0
  Block Id: 1  linp Index: 1   Attributes: 13   Size: 28
  infomask: 0x0903 (HASNULL|HASVARWIDTH|XMIN_COMMITTED|XMAX_INVALID) 
  t_bits: [0]: 0xff [1]: 0x1e 

  192c: 63f30100 00000000 00000000 00000100  c...............
  193c: 01000d00 03091cff 1e000000 11360000  .............6..
  194c: 00000000 301228e2 981c0200 676d2d64  ....0.(.....gm-d
  195c: 626f0000 00000000 00000000 00000000  bo..............
  196c: 00000000 00000000 00000000 00000000  ................
  197c: 00000000 00000000 00000000 00000000  ................
  198c: 00000000 00000000 00000000 0b000000  ................
  199c: 000b0000 2d2d203e 36302079 65617273  ....-- >60 years
  19ac: 0a53454c 45435420 45584953 54532028  .SELECT EXISTS (
  19bc: 0a095345 4c454354 20312046 524f4d20  ..SELECT 1 FROM 
  19cc: 64656d2e 6964656e 74697479 20574845  dem.identity WHE
  19dc: 52450a09 0928706b 203d2049 445f4143  RE...(pk = ID_AC
  19ec: 54495645 5f504154 49454e54 290a0909  TIVE_PATIENT)...
  19fc: 09414e44 0a090928 646f6220 3c206e6f  .AND...(dob < no
  1a0c: 77282920 2d202736 30207965 61727327  w() - '60 years'
  1a1c: 3a3a696e 74657276 616c290a 2920414e  ::interval).) AN
  1a2c: 44204558 49535453 20280a2d 2d204173  D EXISTS (.-- As
  1a3c: 74686d61 2f434f50 442f456d 70687973  thma/COPD/Emphys
  1a4c: 656d2f4c 756e6765 6e666962 726f7365  em/Lungenfibrose
  1a5c: 0a53454c 45435420 31204652 4f4d2063  .SELECT 1 FROM c
  1a6c: 6c696e2e 765f7072 6f626c65 6d5f6c69  lin.v_problem_li
  1a7c: 73742057 48455245 0a092870 6b5f7061  st WHERE..(pk_pa
  1a8c: 7469656e 74203d20 49445f41 43544956  tient = ID_ACTIV
  1a9c: 455f5041 5449454e 54290a09 09414e44  E_PATIENT)...AND
  1aac: 0a092809 2d2d2073 686f756c 64206368  ..(.-- should ch
  1abc: 65636b20 49435043 2f494344 31300a09  eck ICPC/ICD10..
  1acc: 09287072 6f626c65 6d20696c 696b6520  .(problem ilike 
  1adc: 27256173 74686d61 2527290a 0909094f  '%asthma%')....O
  1aec: 520a0909 2870726f 626c656d 20696c69  R...(problem ili
  1afc: 6b652027 25434f50 44252729 0a090909  ke '%COPD%')....
  1b0c: 4f520a09 09287072 6f626c65 6d20696c  OR...(problem il
  1b1c: 696b6520 2725656d 70687973 656d2527  ike '%emphysem%'
  1b2c: 290a0909 094f520a 0909280a 09090928  )....OR...(....(
  1b3c: 70726f62 6c656d20 696c696b 65202725  problem ilike '%
  1b4c: 66696272 6f732527 290a0909 0909414e  fibros%').....AN
  1b5c: 440a0909 09280a09 09090928 70726f62  D....(.....(prob
  1b6c: 6c656d20 696c696b 65202725 6c756e67  lem ilike '%lung
  1b7c: 2527290a 09090909 094f520a 09090909  %')......OR.....
  1b8c: 2870726f 626c656d 20696c69 6b652027  (problem ilike '
  1b9c: 2570756c 6d6f6e25 27290a09 0909290a  %pulmon%')....).
  1bac: 0909290a 09290a29 20414e44 204e4f54  ..)..).) AND NOT
  1bbc: 20455849 53545320 280a2d2d 206b6569   EXISTS (.-- kei
  1bcc: 6e652049 6d706675 6e672050 6e65756d  ne Impfung Pneum
  1bdc: 6f6b6f6b 6b656e0a 53454c45 43542031  okokken.SELECT 1
  1bec: 2046524f 4d20636c 696e2e76 5f706174   FROM clin.v_pat
  1bfc: 5f766163 63733469 6e646963 6174696f  _vaccs4indicatio
  1c0c: 6e205748 4552450a 0928706b 5f706174  n WHERE..(pk_pat
  1c1c: 69656e74 203d2049 445f4143 54495645  ient = ID_ACTIVE
  1c2c: 5f504154 49454e54 290a0909 414e440a  _PATIENT)...AND.
  1c3c: 0928696e 64696361 74696f6e 203d2027  .(indication = '
  1c4c: 706e6575 6d6f636f 63637573 27290a29  pneumococcus').)
  1c5c: 4b4c756e 67652d3e 506e6575 6d6f6b6f  KLunge->Pneumoko
  1c6c: 6b6b656e 2d496d70 66672028 4447502f  kken-Impfg (DGP/
  1c7c: 4447494d 29974c75 6e67656e 6b72616e  DGIM).Lungenkran
  1c8c: 6b6520c3 a46c7465 72203630 20736f6c  ke ..lter 60 sol
  1c9c: 6c656e20 65696e65 20506e65 756d6f6b  len eine Pneumok
  1cac: 6f6b6b65 6e696d70 66756e67 20616e67  okkenimpfung ang
  1cbc: 65626f74 656e2062 656b6f6d 6d656e2e  eboten bekommen.
  1ccc: 015f2247 656d6569 6e73616d 206b6c75  ._"Gemeinsam klu
  1cdc: 6720656e 74736368 65696465 6e222028  g entscheiden" (
  1cec: 4447494d 3a204447 502c2032 30313629  DGIM: DGP, 2016)
  1cfc: 07646500 f80b0000 53454c45 43540a28  .de.....SELECT.(
  1d0c: 092d2d20 6578706c 61696e0a 0953454c  .-- explain..SEL
  1d1c: 45435420 45274573 20697374 206b6569  ECT E'Es ist kei
  1d2c: 6e652049 6d706675 6e672067 6567656e  ne Impfung gegen
  1d3c: 20506e65 756d6f6b 6f6b6b65 6e20646f   Pneumokokken do
  1d4c: 6b756d65 6e746965 72742e5c 6e5c6e27  kumentiert.\n\n'
  1d5c: 0a09097c 7c204527 45732073 6f6c6c74  ...|| E'Es sollt
  1d6c: 65206569 6e652049 6d706675 6e672061  e eine Impfung a
  1d7c: 6e676562 6f74656e 20776572 64656e2c  ngeboten werden,
  1d8c: 20776569 6c5c6e27 0a09092d 2d206167   weil\n'...-- ag
  1d9c: 650a0909 7c7c2027 20646173 20416c74  e...|| ' das Alt
  1dac: 65722028 27207c7c 20747269 6d286c65  er (' || trim(le
  1dbc: 6164696e 67202730 27206672 6f6d2074  ading '0' from t
  1dcc: 6f5f6368 6172286a 75737469 66795f69  o_char(justify_i
  1ddc: 6e746572 76616c28 6e6f7728 29202d20  nterval(now() - 
  1dec: 646f6229 2c202759 59592729 29207c7c  dob), 'YYY')) ||
  1dfc: 20452729 203e2036 30204a61 68726520   E') > 60 Jahre 
  1e0c: 69737420 756e645c 6e270a09 46524f4d  ist und\n'..FROM
  1e1c: 2064656d 2e696465 6e746974 79205748   dem.identity WH
  1e2c: 45524520 706b203d 2049445f 41435449  ERE pk = ID_ACTI
  1e3c: 56455f50 41544945 4e540a29 207c7c20  VE_PATIENT.) || 
  1e4c: 280a092d 2d207072 6f626c65 6d0a0953  (..-- problem..S
  1e5c: 454c4543 54202720 65696e20 4c756e67  ELECT ' ein Lung
  1e6c: 656e7072 6f626c65 6d202227 207c7c20  enproblem "' || 
  1e7c: 70726f62 6c656d20 7c7c2027 2220646f  problem || '" do
  1e8c: 6b756d65 6e746965 72742069 7374270a  kumentiert ist'.
  1e9c: 0946524f 4d20636c 696e2e76 5f70726f  .FROM clin.v_pro
  1eac: 626c656d 5f6c6973 74205748 4552450a  blem_list WHERE.
  1ebc: 09092870 6b5f7061 7469656e 74203d20  ..(pk_patient = 
  1ecc: 49445f41 43544956 455f5041 5449454e  ID_ACTIVE_PATIEN
  1edc: 54290a09 0909414e 440a0909 28092d2d  T)....AND...(.--
  1eec: 2073686f 756c6420 63686563 6b204943   should check IC
  1efc: 50432f49 43443130 0a090909 2870726f  PC/ICD10....(pro
  1f0c: 626c656d 20696c69 6b652027 25617374  blem ilike '%ast
  1f1c: 686d6125 27290a09 0909094f 520a0909  hma%').....OR...
  1f2c: 09287072 6f626c65 6d20696c 696b6520  .(problem ilike 
  1f3c: 2725434f 50442527 290a0909 09094f52  '%COPD%').....OR
  1f4c: 0a090909 2870726f 626c656d 20696c69  ....(problem ili
  1f5c: 6b652027 25656d70 68797365 6d252729  ke '%emphysem%')
  1f6c: 0a090909 094f520a 09090928 0a090909  .....OR....(....
  1f7c: 09287072 6f626c65 6d20696c 696b6520  .(problem ilike 
  1f8c: 27256669 62726f73 2527290a 09090909  '%fibros%').....
  1f9c: 09414e44 0a090909 09280a09 09090909  .AND.....(......
  1fac: 2870726f 626c656d 20696c69 6b652027  (problem ilike '
  1fbc: 256c756e 67252729 0a090909 0909094f  %lung%').......O
  1fcc: 520a0909 09090928 70726f62 6c656d20  R......(problem 
  1fdc: 696c696b 65202725 70756c6d 6f6e2527  ilike '%pulmon%'
  1fec: 290a0909 0909290a 09090929 0a090929  ).....)....)...)
  1ffc: 0a29                                 .)              

 Item   2 -- Length: 2028  Offset: 4416 (0x1140)  Flags: NORMAL
  XMIN: 127844  XMAX: 0  CID|XVAC: 0
  Block Id: 1  linp Index: 2   Attributes: 13   Size: 28
  infomask: 0x0803 (HASNULL|HASVARWIDTH|XMAX_INVALID) 
  t_bits: [0]: 0xff [1]: 0x1e 

  1140: 64f30100 00000000 00000000 00000100  d...............
  1150: 02000d00 03081cff 1e000000 12360000  .............6..
  1160: 00000000 645b28e2 981c0200 676d2d64  ....d[(.....gm-d
  1170: 626f0000 00000000 00000000 00000000  bo..............
  1180: 00000000 00000000 00000000 00000000  ................
  1190: 00000000 00000000 00000000 00000000  ................
  11a0: 00000000 00000000 00000000 0c000000  ................
  11b0: 240f0000 0a53454c 4543540a 092d2d20  $....SELECT..-- 
  11c0: 6265796f 6e642041 75677573 74203f20  beyond August ? 
  11d0: 2d3e2070 726f6261 626c7920 6f6e6c79  -> probably only
  11e0: 2076616c 6964206f 6e207468 65204e6f   valid on the No
  11f0: 72746865 726e2048 656d6973 70686572  rthern Hemispher
  1200: 650a0928 65787472 61637428 6d6f6e74  e..(extract(mont
  1210: 68206672 6f6d206e 6f772829 29203e20  h from now()) > 
  1220: 38290a41 4e442045 58495354 5320280a  8).AND EXISTS (.
  1230: 092d2d20 3e363020 79656172 730a0953  .-- >60 years..S
  1240: 454c4543 54203120 46524f4d 2064656d  ELECT 1 FROM dem
  1250: 2e696465 6e746974 79205748 4552450a  .identity WHERE.
  1260: 09092870 6b203d20 49445f41 43544956  ..(pk = ID_ACTIV
  1270: 455f5041 5449454e 54290a09 0909414e  E_PATIENT)....AN
  1280: 440a0909 28646f62 203c206e 6f772829  D...(dob < now()
  1290: 202d2027 36302079 65617273 273a3a69   - '60 years'::i
  12a0: 6e746572 76616c29 0a292041 4e442045  nterval).) AND E
  12b0: 58495354 5320280a 2d2d2041 7374686d  XISTS (.-- Asthm
  12c0: 612f434f 50442f45 6d706879 73656d2f  a/COPD/Emphysem/
  12d0: 4c756e67 656e6669 62726f73 650a5345  Lungenfibrose.SE
  12e0: 4c454354 20312046 524f4d20 636c696e  LECT 1 FROM clin
  12f0: 2e765f70 726f626c 656d5f6c 69737420  .v_problem_list 
  1300: 57484552 450a0928 706b5f70 61746965  WHERE..(pk_patie
  1310: 6e74203d 2049445f 41435449 56455f50  nt = ID_ACTIVE_P
  1320: 41544945 4e54290a 0909414e 440a0928  ATIENT)...AND..(
  1330: 092d2d20 73686f75 6c642063 6865636b  .-- should check
  1340: 20494350 432f4943 4431300a 09092870   ICPC/ICD10...(p
  1350: 726f626c 656d2069 6c696b65 20272561  roblem ilike '%a
  1360: 7374686d 61252729 0a090909 4f520a09  sthma%')....OR..
  1370: 09287072 6f626c65 6d20696c 696b6520  .(problem ilike 
  1380: 2725434f 50442527 290a0909 094f520a  '%COPD%')....OR.
  1390: 09092870 726f626c 656d2069 6c696b65  ..(problem ilike
  13a0: 20272565 6d706879 73656d25 27290a09   '%emphysem%')..
  13b0: 09094f52 0a090928 0a090909 2870726f  ..OR...(....(pro
  13c0: 626c656d 20696c69 6b652027 25666962  blem ilike '%fib
  13d0: 726f7325 27290a09 09090941 4e440a09  ros%').....AND..
  13e0: 0909280a 09090909 2870726f 626c656d  ..(.....(problem
  13f0: 20696c69 6b652027 256c756e 67252729   ilike '%lung%')
  1400: 0a090909 09094f52 0a090909 09287072  ......OR.....(pr
  1410: 6f626c65 6d20696c 696b6520 27257075  oblem ilike '%pu
  1420: 6c6d6f6e 2527290a 09090929 0a090929  lmon%')....)...)
  1430: 0a09290a 2920414e 44204e4f 54204558  ..).) AND NOT EX
  1440: 49535453 20280a2d 2d206b65 696e6520  ISTS (.-- keine 
  1450: 496d7066 756e6720 496e666c 75656e7a  Impfung Influenz
  1460: 6120696e 2064656e 206c6574 7a74656e  a in den letzten
  1470: 2036204d 6f6e6174 656e2072 c3bc636b   6 Monaten r..ck
  1480: 626c6963 6b656e64 2c206661 6c6c7320  blickend, falls 
  1490: 77697220 696d2053 65707465 6d626572  wir im September
  14a0: 2073696e 640a5345 4c454354 20312046   sind.SELECT 1 F
  14b0: 524f4d20 636c696e 2e765f70 61745f76  ROM clin.v_pat_v
  14c0: 61636373 34696e64 69636174 696f6e20  accs4indication 
  14d0: 57484552 450a0928 706b5f70 61746965  WHERE..(pk_patie
  14e0: 6e74203d 2049445f 41435449 56455f50  nt = ID_ACTIVE_P
  14f0: 41544945 4e54290a 0909414e 440a0928  ATIENT)...AND..(
  1500: 696e6469 63617469 6f6e203d 2027696e  indication = 'in
  1510: 666c7565 6e7a6120 28736561 736f6e61  fluenza (seasona
  1520: 6c292729 0a090941 4e440a09 64617465  l)')...AND..date
  1530: 5f676976 656e203e 206e6f77 2829202d  _given > now() -
  1540: 20273620 6d6f6e74 6873273a 3a696e74   '6 months'::int
  1550: 65727661 6c0a4f52 44455220 42590a09  erval.ORDER BY..
  1560: 64617465 5f676976 656e2044 4553430a  date_given DESC.
  1570: 4c494d49 5420310a 29454c75 6e67652d  LIMIT 1.)ELunge-
  1580: 3e496e66 6c75656e 7a612d49 6d706667  >Influenza-Impfg
  1590: 20284447 502f4447 494d2991 4c756e67   (DGP/DGIM).Lung
  15a0: 656e6b72 616e6b65 20c3a46c 74657220  enkranke ..lter 
  15b0: 36302073 6f6c6c65 6e206569 6e652049  60 sollen eine I
  15c0: 6e666c75 656e7a61 696d7066 756e6720  nfluenzaimpfung 
  15d0: 616e6765 626f7465 6e206265 6b6f6d6d  angeboten bekomm
  15e0: 656e2e01 5f224765 6d65696e 73616d20  en.._"Gemeinsam 
  15f0: 6b6c7567 20656e74 73636865 6964656e  klug entscheiden
  1600: 22202844 47494d3a 20444750 2c203230  " (DGIM: DGP, 20
  1610: 31362907 64650000 500c0000 53454c45  16).de..P...SELE
  1620: 43540a28 092d2d20 6578706c 61696e0a  CT.(.-- explain.
  1630: 0953454c 45435420 45274573 20697374  .SELECT E'Es ist
  1640: 20696e20 64656e20 6c65747a 74656e20   in den letzten 
  1650: 36204d6f 6e617465 6e206b65 696e6520  6 Monaten keine 
  1660: 496d7066 756e6720 67656765 6e20496e  Impfung gegen In
  1670: 666c7565 6e7a6120 646f6b75 6d656e74  fluenza dokument
  1680: 69657274 2e5c6e5c 6e270a09 097c7c20  iert.\n\n'...|| 
  1690: 45274573 20736f6c 6c746520 65696e65  E'Es sollte eine
  16a0: 20496d70 66756e67 20616e67 65626f74   Impfung angebot
  16b0: 656e2077 65726465 6e2c2077 65696c5c  en werden, weil\
  16c0: 6e270a09 092d2d20 6167650a 09097c7c  n'...-- age...||
  16d0: 20272064 61732041 6c746572 20282720   ' das Alter (' 
  16e0: 7c7c2074 72696d28 6c656164 696e6720  || trim(leading 
  16f0: 27302720 66726f6d 20746f5f 63686172  '0' from to_char
  1700: 286a7573 74696679 5f696e74 65727661  (justify_interva
  1710: 6c286e6f 77282920 2d20646f 62292c20  l(now() - dob), 
  1720: 27595959 27292920 7c7c2045 2729203e  'YYY')) || E') >
  1730: 20363020 4a616872 65206973 7420756e   60 Jahre ist un
  1740: 645c6e27 0a094652 4f4d2064 656d2e69  d\n'..FROM dem.i
  1750: 64656e74 69747920 57484552 4520706b  dentity WHERE pk
  1760: 203d2049 445f4143 54495645 5f504154   = ID_ACTIVE_PAT
  1770: 49454e54 0a29207c 7c20280a 092d2d20  IENT.) || (..-- 
  1780: 70726f62 6c656d0a 0953454c 45435420  problem..SELECT 
  1790: 27206569 6e204c75 6e67656e 70726f62  ' ein Lungenprob
  17a0: 6c656d20 2227207c 7c207072 6f626c65  lem "' || proble
  17b0: 6d207c7c 20272220 646f6b75 6d656e74  m || '" dokument
  17c0: 69657274 20697374 270a0946 524f4d20  iert ist'..FROM 
  17d0: 636c696e 2e765f70 726f626c 656d5f6c  clin.v_problem_l
  17e0: 69737420 57484552 450a0909 28706b5f  ist WHERE...(pk_
  17f0: 70617469 656e7420 3d204944 5f414354  patient = ID_ACT
  1800: 4956455f 50415449 454e5429 0a090909  IVE_PATIENT)....
  1810: 414e440a 09092809 2d2d2073 686f756c  AND...(.-- shoul
  1820: 64206368 65636b20 49435043 2f494344  d check ICPC/ICD
  1830: 31300a09 09092870 726f626c 656d2069  10....(problem i
  1840: 6c696b65 20272561 7374686d 61252729  like '%asthma%')
  1850: 0a090909 094f520a 09090928 70726f62  .....OR....(prob
  1860: 6c656d20 696c696b 65202725 434f5044  lem ilike '%COPD
  1870: 2527290a 09090909 4f520a09 09092870  %').....OR....(p
  1880: 726f626c 656d2069 6c696b65 20272565  roblem ilike '%e
  1890: 6d706879 73656d25 27290a09 0909094f  mphysem%').....O
  18a0: 520a0909 09280a09 09090928 70726f62  R....(.....(prob
  18b0: 6c656d20 696c696b 65202725 66696272  lem ilike '%fibr
  18c0: 6f732527 290a0909 09090941 4e440a09  os%')......AND..
  18d0: 09090928 0a090909 09092870 726f626c  ...(......(probl
  18e0: 656d2069 6c696b65 2027256c 756e6725  em ilike '%lung%
  18f0: 27290a09 09090909 094f520a 09090909  ').......OR.....
  1900: 09287072 6f626c65 6d20696c 696b6520  .(problem ilike 
  1910: 27257075 6c6d6f6e 2527290a 09090909  '%pulmon%').....
  1920: 290a0909 09290a09 09290a29           )....)...).)    

 Item   3 -- Length: 1253  Offset: 3160 (0x0c58)  Flags: NORMAL
  XMIN: 127845  XMAX: 0  CID|XVAC: 0
  Block Id: 1  linp Index: 3   Attributes: 13   Size: 28
  infomask: 0x0803 (HASNULL|HASVARWIDTH|XMAX_INVALID) 
  t_bits: [0]: 0xff [1]: 0x1e 

  0c58: 65f30100 00000000 00000000 00000100  e...............
  0c68: 03000d00 03081cff 1e000000 13360000  .............6..
  0c78: 00000000 b18228e2 981c0200 676d2d64  ......(.....gm-d
  0c88: 626f0000 00000000 00000000 00000000  bo..............
  0c98: 00000000 00000000 00000000 00000000  ................
  0ca8: 00000000 00000000 00000000 00000000  ................
  0cb8: 00000000 00000000 00000000 0d000000  ................
  0cc8: 3c080000 53454c45 43542045 58495354  <...SELECT EXIST
  0cd8: 5320280a 092d2d20 74616b65 73206f78  S (..-- takes ox
  0ce8: 7967656e 0a095345 4c454354 20312046  ygen..SELECT 1 F
  0cf8: 524f4d20 636c696e 2e765f73 75627374  ROM clin.v_subst
  0d08: 616e6365 5f696e74 616b6573 20574845  ance_intakes WHE
  0d18: 52450a09 0928706b 5f706174 69656e74  RE...(pk_patient
  0d28: 203d2049 445f4143 54495645 5f504154   = ID_ACTIVE_PAT
  0d38: 49454e54 290a0909 09414e44 0a090928  IENT)....AND...(
  0d48: 6174635f 73756273 74616e63 65203d20  atc_substance = 
  0d58: 27563033 414e3031 2729092d 2d206f78  'V03AN01').-- ox
  0d68: 7967656e 0a090909 414e440a 09092828  ygen....AND...((
  0d78: 64697363 6f6e7469 6e756564 20495320  discontinued IS 
  0d88: 4e554c4c 29204f52 20286469 73636f6e  NULL) OR (discon
  0d98: 74696e75 6564203e 206e6f77 28292929  tinued > now()))
  0da8: 0a292041 4e44204e 4f542045 58495354  .) AND NOT EXIST
  0db8: 5320280a 092d2d20 6e6f2042 4741206f  S (..-- no BGA o
  0dc8: 72205370 4f322077 69746869 6e206c61  r SpO2 within la
  0dd8: 73742033 206d6f6e 7468730a 0953454c  st 3 months..SEL
  0de8: 45435420 31204652 4f4d2063 6c696e2e  ECT 1 FROM clin.
  0df8: 765f7465 73745f72 6573756c 74732057  v_test_results W
  0e08: 48455245 0a090928 706b5f70 61746965  HERE...(pk_patie
  0e18: 6e74203d 2049445f 41435449 56455f50  nt = ID_ACTIVE_P
  0e28: 41544945 4e54290a 09090941 4e440a09  ATIENT)....AND..
  0e38: 0928756e 69666965 645f6c6f 696e6320  .(unified_loinc 
  0e48: 494e2028 73656c65 63742063 6f646520  IN (select code 
  0e58: 66726f6d 20726566 2e6c6f69 6e632077  from ref.loinc w
  0e68: 68657265 20746572 6d20696c 696b6520  here term ilike 
  0e78: 27256f78 7967656e 25272061 6e642074  '%oxygen%' and t
  0e88: 65726d20 696c696b 65202725 70726573  erm ilike '%pres
  0e98: 73757265 25272929 0a090909 414e440a  sure%'))....AND.
  0ea8: 09092863 6c696e5f 7768656e 203e206e  ..(clin_when > n
  0eb8: 6f772829 202d2027 33206d6f 6e746873  ow() - '3 months
  0ec8: 273a3a69 6e746572 76616c29 0a293b3f  '::interval).);?
  0ed8: 4fc2b22d 47616265 2d3e4247 412f5370  O..-Gabe->BGA/Sp
  0ee8: 4fc2b220 28444750 2f444749 4d29a142  O.. (DGP/DGIM).B
  0ef8: 65692061 6d62756c 616e7465 72204fc2  ei ambulanter O.
  0f08: b22d5468 65726170 69652073 6f6c6c20  .-Therapie soll 
  0f18: 616c6c65 72203320 4d6f6e61 74652064  aller 3 Monate d
  0f28: 69652049 6e64696b 6174696f 6e206765  ie Indikation ge
  0f38: 7072c3bc 66742077 65726465 6e2e015f  pr..ft werden.._
  0f48: 2247656d 65696e73 616d206b 6c756720  "Gemeinsam klug 
  0f58: 656e7473 63686569 64656e22 20284447  entscheiden" (DG
  0f68: 494d3a20 4447502c 20323031 36290764  IM: DGP, 2016).d
  0f78: 65000000 04070000 0a53454c 45435420  e........SELECT 
  0f88: 636f616c 65736365 20280a09 2853454c  coalesce (..(SEL
  0f98: 4543540a 09092744 69652048 79706f78  ECT...'Die Hypox
  0fa8: c3a46d69 65207775 72646520 7a756c65  ..mie wurde zule
  0fb8: 747a7420 696d2027 207c7c20 746f5f63  tzt im ' || to_c
  0fc8: 68617228 636c696e 5f776865 6e2c2027  har(clin_when, '
  0fd8: 4d6f6e20 59595959 2729207c 7c202720  Mon YYYY') || ' 
  0fe8: c3bc6265 727072c3 bc66742e 270a0946  ..berpr..ft.'..F
  0ff8: 524f4d20 636c696e 2e765f74 6573745f  ROM clin.v_test_
  1008: 72657375 6c747320 57484552 450a0909  results WHERE...
  1018: 28706b5f 70617469 656e7420 3d204944  (pk_patient = ID
  1028: 5f414354 4956455f 50415449 454e5429  _ACTIVE_PATIENT)
  1038: 0a090909 414e440a 09092875 6e696669  ....AND...(unifi
  1048: 65645f6c 6f696e63 20494e20 2873656c  ed_loinc IN (sel
  1058: 65637420 636f6465 2066726f 6d207265  ect code from re
  1068: 662e6c6f 696e6320 77686572 65207465  f.loinc where te
  1078: 726d2069 6c696b65 2027256f 78796765  rm ilike '%oxyge
  1088: 6e252720 616e6420 7465726d 20696c69  n%' and term ili
  1098: 6b652027 25707265 73737572 65252729  ke '%pressure%')
  10a8: 290a094f 52444552 2042590a 0909636c  )..ORDER BY...cl
  10b8: 696e5f77 68656e20 44455343 0a094c49  in_when DESC..LI
  10c8: 4d495420 310a0929 3a3a7465 78742c0a  MIT 1..)::text,.
  10d8: 09285345 4c454354 20274573 20697374  .(SELECT 'Es ist
  10e8: 206b6569 6e6520c3 9c626572 7072c3bc   keine ..berpr..
  10f8: 66756e67 20646572 20487970 6f78c3a4  fung der Hypox..
  1108: 6d696520 696e2064 656e206c 65747a74  mie in den letzt
  1118: 656e2033 204d6f6e 6174656e 20646f6b  en 3 Monaten dok
  1128: 756d656e 74696572 742e273a 3a746578  umentiert.'::tex
  1138: 74290a29 3b                          t).);           

 Item   4 -- Length:  917  Offset: 2240 (0x08c0)  Flags: NORMAL
  XMIN: 127847  XMAX: 0  CID|XVAC: 0
  Block Id: 1  linp Index: 4   Attributes: 13   Size: 28
  infomask: 0x0803 (HASNULL|HASVARWIDTH|XMAX_INVALID) 
  t_bits: [0]: 0xff [1]: 0x1e 

  08c0: 67f30100 00000000 00000000 00000100  g...............
  08d0: 04000d00 03081cff 1e000000 15360000  .............6..
  08e0: 00000000 b42e3be2 981c0200 676d2d64  ......;.....gm-d
  08f0: 626f0000 00000000 00000000 00000000  bo..............
  0900: 00000000 00000000 00000000 00000000  ................
  0910: 00000000 00000000 00000000 00000000  ................
  0920: 00000000 00000000 00000000 0e000000  ................
  0930: fc060000 2d2d2070 7265676e 616e6379  ....-- pregnancy
  0940: 20636865 636b0a53 454c4543 54204558   check.SELECT EX
  0950: 49535453 20280a09 53454c45 43542031  ISTS (..SELECT 1
  0960: 2046524f 4d20636c 696e2e70 61746965   FROM clin.patie
  0970: 6e742057 48455245 0a090966 6b5f6964  nt WHERE...fk_id
  0980: 656e7469 7479203d 2049445f 41435449  entity = ID_ACTI
  0990: 56455f50 41544945 4e540a09 0909414e  VE_PATIENT....AN
  09a0: 440a0909 636f616c 65736365 28656463  D...coalesce(edc
  09b0: 20424554 5745454e 206e6f77 2829202d   BETWEEN now() -
  09c0: 20273120 6d6f6e74 68273a3a 696e7465   '1 month'::inte
  09d0: 7276616c 20414e44 206e6f77 2829202b  rval AND now() +
  09e0: 20273131 206d6f6e 74687327 3a3a696e   '11 months'::in
  09f0: 74657276 616c2c20 46414c53 45290a2d  terval, FALSE).-
  0a00: 2d206275 74206e6f 206a6f64 20696e20  - but no jod in 
  0a10: 6d656469 63617469 6f6e0a29 20414e44  medication.) AND
  0a20: 204e4f54 20455849 53545320 280a0953   NOT EXISTS (..S
  0a30: 454c4543 54203120 46524f4d 20636c69  ELECT 1 FROM cli
  0a40: 6e2e765f 73756273 74616e63 655f696e  n.v_substance_in
  0a50: 74616b65 73205748 4552450a 09092870  takes WHERE...(p
  0a60: 6b5f7061 7469656e 74203d20 49445f41  k_patient = ID_A
  0a70: 43544956 455f5041 5449454e 54290a09  CTIVE_PATIENT)..
  0a80: 0909414e 440a0909 2d2d2069 6f64696e  ..AND...-- iodin
  0a90: 650a0909 28617463 5f737562 7374616e  e...(atc_substan
  0aa0: 6365203d 20274430 38414730 3327290a  ce = 'D08AG03').
  0ab0: 09090941 4e440a09 09282864 6973636f  ...AND...((disco
  0ac0: 6e74696e 75656420 4953204e 554c4c29  ntinued IS NULL)
  0ad0: 204f5220 28646973 636f6e74 696e7565   OR (discontinue
  0ae0: 64203e20 6e6f7728 2929290a 09293b41  d > now()))..);A
  0af0: 53636877 616e6765 72736368 6166742d  Schwangerschaft-
  0b00: 3e4a6f64 20284447 452f4447 494d2977  >Jod (DGE/DGIM)w
  0b10: 53636877 616e6765 72656e20 736f6c6c  Schwangeren soll
  0b20: 2065696e 65204a6f 64737570 706c656d   eine Jodsupplem
  0b30: 656e7461 74696f6e 20616e67 65626f74  entation angebot
  0b40: 656e2077 65726465 6e2e015f 2247656d  en werden.._"Gem
  0b50: 65696e73 616d206b 6c756720 656e7473  einsam klug ents
  0b60: 63686569 64656e22 20284447 494d3a20  cheiden" (DGIM: 
  0b70: 4447452c 20323031 36290764 65000000  DGE, 2016).de...
  0b80: 54030000 53454c45 43540a09 09275061  T...SELECT...'Pa
  0b90: 7469656e 74696e20 69737420 73636877  tientin ist schw
  0ba0: 616e6765 72202827 0a090909 7c7c2027  anger ('....|| '
  0bb0: 5465726d 696e3a20 270a0909 097c7c20  Termin: '....|| 
  0bc0: 746f5f63 68617228 6564632c 20275959  to_char(edc, 'YY
  0bd0: 5959204d 6f6e2044 4427290a 0909097c  YY Mon DD')....|
  0be0: 7c202729 2c206e69 6d6d7420 61626572  | '), nimmt aber
  0bf0: 206b6569 6e204a6f 64205b44 30384147   kein Jod [D08AG
  0c00: 30335d2e 270a0909 61732072 65636f6d  03].'...as recom
  0c10: 6d656e64 6174696f 6e0a0946 524f4d20  mendation..FROM 
  0c20: 636c696e 2e706174 69656e74 0a095748  clin.patient..WH
  0c30: 45524520 666b5f69 64656e74 69747920  ERE fk_identity 
  0c40: 3d204944 5f414354 4956455f 50415449  = ID_ACTIVE_PATI
  0c50: 454e543b 0a                          ENT;.           

 Item   5 -- Length: 1132  Offset: 1108 (0x0454)  Flags: NORMAL
  XMIN: 127858  XMAX: 0  CID|XVAC: 0
  Block Id: 1  linp Index: 5   Attributes: 13   Size: 28
  infomask: 0x0803 (HASNULL|HASVARWIDTH|XMAX_INVALID) 
  t_bits: [0]: 0xff [1]: 0x1e 

  0454: 72f30100 00000000 00000000 00000100  r...............
  0464: 05000d00 03081cff 1e000000 1d360000  .............6..
  0474: 00000000 df6d77e2 981c0200 676d2d64  .....mw.....gm-d
  0484: 626f0000 00000000 00000000 00000000  bo..............
  0494: 00000000 00000000 00000000 00000000  ................
  04a4: 00000000 00000000 00000000 00000000  ................
  04b4: 00000000 00000000 00000000 0f000000  ................
  04c4: fc040000 53454c45 43542045 58495354  ....SELECT EXIST
  04d4: 5320280a 09095345 4c454354 20312046  S (...SELECT 1 F
  04e4: 524f4d20 636c696e 2e765f6e 6f6e6272  ROM clin.v_nonbr
  04f4: 616e645f 696e7461 6b657320 57484552  and_intakes WHER
  0504: 450a0909 0928706b 5f706174 69656e74  E....(pk_patient
  0514: 203d2049 445f4143 54495645 5f504154   = ID_ACTIVE_PAT
  0524: 49454e54 290a0909 0909414e 440a0909  IENT).....AND...
  0534: 09286174 635f7375 62737461 6e636520  .(atc_substance 
  0544: 3d20274e 30374241 30312729 0a090909  = 'N07BA01')....
  0554: 09414e44 0a090909 28636f61 6c657363  .AND....(coalesc
  0564: 65286861 726d6675 6c5f7573 655f7479  e(harmful_use_ty
  0574: 70652c20 2d312920 494e2028 312c3229  pe, -1) IN (1,2)
  0584: 290a0909 0909414e 440a0909 09282864  ).....AND....((d
  0594: 6973636f 6e74696e 75656420 4953204e  iscontinued IS N
  05a4: 554c4c29 204f5220 28646973 636f6e74  ULL) OR (discont
  05b4: 696e7565 64203e20 6e6f7728 2929290a  inued > now())).
  05c4: 09090909 414e440a 09090928 6c617374  ....AND....(last
  05d4: 5f636865 636b6564 5f776865 6e203c20  _checked_when < 
  05e4: 6e6f7728 29202d20 27312079 65617227  now() - '1 year'
  05f4: 3a3a696e 74657276 616c290a 09293b4d  ::interval)..);M
  0604: 4f757464 61746564 20736d6f 6b696e67  Outdated smoking
  0614: 20737461 74757320 646f6375 6d656e74   status document
  0624: 6174696f 6e93536d 6f6b696e 67207374  ation.Smoking st
  0634: 61747573 20776173 206c6173 74207265  atus was last re
  0644: 636f7264 6564206d 6f726520 7468616e  corded more than
  0654: 206f6e65 20796561 72206167 6f20666f   one year ago fo
  0664: 72207468 69732073 6d6f6b65 722e0149  r this smoker..I
  0674: 41574d46 204e564c 20536368 c3a4646c  AWMF NVL Sch..dl
  0684: 69636865 72205461 62616b67 65627261  icher Tabakgebra
  0694: 75636807 656e0000 90080000 53454c45  uch.en......SELE
  06a4: 43540a09 095f2827 536d6f6b 696e6720  CT..._('Smoking 
  06b4: 73746174 75732729 207c7c20 45275c6e  status') || E'\n
  06c4: 270a0909 7c7c2027 2027207c 7c205f28  '...|| ' ' || _(
  06d4: 274c6173 74206368 65636b65 643a2729  'Last checked:')
  06e4: 207c7c20 27202720 7c7c2074 6f5f6368   || ' ' || to_ch
  06f4: 6172286c 6173745f 63686563 6b65645f  ar(last_checked_
  0704: 7768656e 2c20274d 6f6e2059 59595927  when, 'Mon YYYY'
  0714: 290a0909 7c7c2028 63617365 0a090909  )...|| (case....
  0724: 09776865 6e206861 726d6675 6c5f7573  .when harmful_us
  0734: 655f7479 7065203d 20312074 68656e20  e_type = 1 then 
  0744: 45275c6e 27207c7c 205f2827 6861726d  E'\n' || _('harm
  0754: 66756c20 75736527 290a0909 09097768  ful use').....wh
  0764: 656e2068 61726d66 756c5f75 73655f74  en harmful_use_t
  0774: 79706520 3d203220 7468656e 2045275c  ype = 2 then E'\
  0784: 6e27207c 7c205f28 27616464 69637469  n' || _('addicti
  0794: 6f6e2729 0a090909 09776865 6e206861  on').....when ha
  07a4: 726d6675 6c5f7573 655f7479 7065203d  rmful_use_type =
  07b4: 20332074 68656e20 45275c6e 27207c7c   3 then E'\n' ||
  07c4: 205f2827 70726576 696f7573 20616464   _('previous add
  07d4: 69637469 6f6e2729 0a090909 656e6429  iction')....end)
  07e4: 0a09097c 7c20636f 616c6573 63652845  ...|| coalesce(E
  07f4: 275c6e20 27207c7c 205f2827 51756974  '\n ' || _('Quit
  0804: 20646174 653a2729 207c7c20 27202720   date:') || ' ' 
  0814: 7c7c2074 6f5f6368 61722864 6973636f  || to_char(disco
  0824: 6e74696e 7565642c 20275959 5959204d  ntinued, 'YYYY M
  0834: 6f6e2044 4427292c 20272729 0a09097c  on DD'), '')...|
  0844: 7c20636f 616c6573 63652845 275c6e20  | coalesce(E'\n 
  0854: 27207c7c 205f2827 4e6f7465 733a2729  ' || _('Notes:')
  0864: 207c7c20 27202720 7c7c206e 6f746573   || ' ' || notes
  0874: 2c202727 290a0946 524f4d0a 0909636c  , '')..FROM...cl
  0884: 696e2e76 5f6e6f6e 6272616e 645f696e  in.v_nonbrand_in
  0894: 74616b65 730a0957 48455245 20706b5f  takes..WHERE pk_
  08a4: 70617469 656e7420 3d204944 5f414354  patient = ID_ACT
  08b4: 4956455f 50415449 454e543b           IVE_PATIENT;    



*** End of File Encountered. Last Block Read: 1 ***

On Thu, Nov 01, 2018 at 03:42:57PM +0100, Karsten Hilbert wrote:

Will attempt to get a stack trace ...

Meanwhile, in case it helps, an strace of the crash.

	epoll_wait(7, [{EPOLLIN, {u32=47607120, u64=35184419695952}}], 1, -1) = 1
	recv(13, "Q\0\0\0`DELETE FROM ref.auto_hint W"..., 8192, 0) = 97
	gettimeofday({tv_sec=1541084179, tv_usec=197395}, NULL) = 0
	gettimeofday({tv_sec=1541084179, tv_usec=197442}, NULL) = 0
	write(2, "2018-11-01 14:56:19 GMT LOG:  00"..., 209) = 209
	_llseek(45, 0, [16384], SEEK_END)       = 0
	_llseek(47, 0, [16384], SEEK_END)       = 0
	_llseek(48, 0, [16384], SEEK_END)       = 0
	_llseek(49, 0, [16384], SEEK_END)       = 0
	_llseek(45, 0, [16384], SEEK_END)       = 0
	kill(10377, SIGUSR1)                    = 0
	_llseek(45, 0, [0], SEEK_SET)           = 0
	read(45, "\0\0\0\0\234\270\246F\10\373\0\0D\0x\n\0 \4 I\337\1\0\0\236\0\4\0\200\1\0"..., 8192) = 8192
	_llseek(43, 81920, [81920], SEEK_SET)   = 0
	read(43, "\0\0\0\0x{pF8o\0\0\264\1\34\33\360\37\4 \0\0\0\0\344\237\30\0\330\237\30\0"..., 8192) = 8192
	_llseek(44, 925696, [925696], SEEK_SET) = 0
	read(44, "\0\0\0\0\310\324\231FE\231\1\0L\0l\1\0 \4 \0\0\0\0\24\234\326\7<\231\254\5"..., 8192) = 8192
	openat(AT_FDCWD, "base/139580/139695", O_RDWR|O_LARGEFILE) = 56
	read(56, "\0\0\0\0\350\347\220E`B\0\0<\0\360\37\360\37\4 \0\0\0\0b1\5\0\3\0\0\0"..., 8192) = 8192
	read(56, "\0\0\0\0h\347\220E\1,\0\0(\0\300\37\360\37\4 \0\0\0\0\344\237\30\0\330\237\30\0"..., 8192) = 8192
	openat(AT_FDCWD, "base/139580/2612", O_RDWR|O_LARGEFILE) = 57
	read(57, "\0\0\0\0\200\334s\1a\6\4\0(\0@\36\0 \4 \0\0\0\0\220\237\340\0 \237\340\0"..., 8192) = 8192
	_llseek(43, 65536, [65536], SEEK_SET)   = 0
	read(43, "\0\0\0\0\214e^F\215w\0\0H\7`\n\360\37\4 \0\0\0\0x\212\30\0\344\237\30\0"..., 8192) = 8192
	_llseek(44, 606208, [606208], SEEK_SET) = 0
	read(44, "\0\0\0\0\20\206\237FVP\1\0p\0\230\3\0 \4 \361\360\1\0000\237\232\1T\236\266\1"..., 8192) = 8192
	stat64("/usr/lib/postgresql/11/lib/plpgsql", 0xbfbda4ec) = -1 ENOENT (No such file or directory)
	stat64("/usr/lib/postgresql/11/lib/plpgsql.so", {st_mode=S_IFREG|0644, st_size=197944, ...}) = 0
	stat64("/usr/lib/postgresql/11/lib/plpgsql.so", {st_mode=S_IFREG|0644, st_size=197944, ...}) = 0
	futex(0xb78b904c, FUTEX_WAKE_PRIVATE, 2147483647) = 0
	openat(AT_FDCWD, "/usr/lib/postgresql/11/lib/plpgsql.so", O_RDONLY|O_CLOEXEC) = 58
	read(58, "\177ELF\1\1\1\0\0\0\0\0\0\0\0\0\3\0\3\0\1\0\0\0\300a\0\0004\0\0\0"..., 512) = 512
	fstat64(58, {st_mode=S_IFREG|0644, st_size=197944, ...}) = 0
	mmap2(NULL, 201108, PROT_READ, MAP_PRIVATE|MAP_DENYWRITE, 58, 0) = 0xb293f000
	mmap2(0xb2944000, 102400, PROT_READ|PROT_EXEC, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 58, 0x5000) = 0xb2944000
	mmap2(0xb295d000, 69632, PROT_READ, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 58, 0x1e000) = 0xb295d000
	mmap2(0xb296e000, 12288, PROT_READ|PROT_WRITE, MAP_PRIVATE|MAP_FIXED|MAP_DENYWRITE, 58, 0x2e000) = 0xb296e000
	close(58)                               = 0
	mprotect(0xb296e000, 8192, PROT_READ)   = 0
	openat(AT_FDCWD, "global/139651", O_RDWR|O_LARGEFILE) = 58
	read(58, "\0\0\0\0\320\200zE\324\16\0\0<\0\360\37\360\37\4 \0\0\0\0b1\5\0\3\0\0\0"..., 8192) = 8192
	read(58, "\0\0\0\0P\200zE.+\0\0\320\0\310\35\360\37\4 \0\0\0\0\344\237\30\0\330\237\30\0"..., 8192) = 8192
	openat(AT_FDCWD, "base/139580/139719", O_RDWR|O_LARGEFILE) = 59
	read(59, "\0\0\0\0x\347\232E\226s\0\0<\0\360\37\360\37\4 \0\0\0\0b1\5\0\3\0\0\0"..., 8192) = 8192
	read(59, "\0\0\0\0l\250\16F\22\340\0\0h\0\0\37\360\37\4 \0\0\0\0\344\237\30\0\330\237\30\0"..., 8192) = 8192
	read(42, "\0\0\0\0`\235^\1\177\242\4\0\320\0\30\1\0 \4 \0\0\0\0T\237X\1\250\236X\1"..., 8192) = 8192
	openat(AT_FDCWD, "base/139580/139606", O_RDWR|O_LARGEFILE) = 60
	read(60, "\0\0\0\0\244E{F\206q\0\0<\0\360\37\360\37\4 \0\0\0\0b1\5\0\3\0\0\0"..., 8192) = 8192
	_llseek(60, 24576, [24576], SEEK_SET)   = 0
	read(60, "\0\0\0\0\224\272wE\re\0\0008\0\20\37\360\37\4 \0\0\0\0\350\237\20\0\320\2370\0"..., 8192) = 8192
	read(60, "\0\0\0\0tD{F\34S\0\0\220\3\324\6\360\37\4 \0\0\0\0t\2078\0\300\237`\0"..., 8192) = 8192
	brk(0x2e07000)                          = 0x2e07000
	_llseek(41, 32768, [32768], SEEK_SET)   = 0
	read(41, "\0\0\0\0\320B{F\23I\0\0\230\6p\f\360\37\4 \0\0\0\0p\214\30\0\344\237\30\0"..., 8192) = 8192
	_llseek(42, 335872, [335872], SEEK_SET) = 0
	read(42, "\0\0\0\0 \"|FB\177\5\0\340\0\320\7\0 \4 Z\362\1\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
	openat(AT_FDCWD, "base/139580/139666", O_RDWR|O_LARGEFILE) = 61
	read(61, "\0\0\0\0\244\326\212E\354\30\0\0<\0\360\37\360\37\4 \0\0\0\0b1\5\0\3\0\0\0"..., 8192) = 8192
	_llseek(61, 24576, [24576], SEEK_SET)   = 0
	read(61, "\0\0\0\0$\326\212E\217\235\0\0\220\0`\31\360\37\4 \0\0\0\0\350\237\20\0\254\237x\0"..., 8192) = 8192
	_llseek(61, 8192, [8192], SEEK_SET)     = 0
	read(61, "\0\0\0\0008\365XF\253\246\0\0\30\2\214\3\360\37\4 \0\0\0\0000\205x\0\270\237p\0"..., 8192) = 8192
	read(43, "\0\0\0\0H>mFL\204\0\0D\7l\n\360\37\4 \0\0\0\0\344\237\30\0\330\237\30\0"..., 8192) = 8192
	brk(0x2e29000)                          = 0x2e29000
	_llseek(31, 40960, [40960], SEEK_SET)   = 0
	read(31, "\0\0\0\0\270\244.F}8\0\0\220\1\354\22\360\37\4 \0\0\0\0\320\237@\0\254\237H\0"..., 8192) = 8192
	_llseek(16, 2605056, [2605056], SEEK_SET) = 0
	read(16, "\0\0\0\0\304\347\220FX\37\5\0\10\1d\7\0 \4 \0\0\0\0\0\0\0\0\0\0\0\0"..., 8192) = 8192
	read(33, "\0\0\0\0\230x\375<b\341\0\0\204\0\240\1\0 \4 \0\0\0\0\364\235\30\4<\235j\1"..., 8192) = 8192
	_llseek(34, 98304, [98304], SEEK_SET)   = 0
	read(34, "\0\0\0\0<\214vF\266\210\0\0\204\1\260\r\360\37\4 \0\0\0\0\250\237\220\0t\237h\0"..., 8192) = 8192
	_llseek(35, 253952, [253952], SEEK_SET) = 0
	read(35, "\0\0\0\0\304\35\240F\367\6\5\0x\0|\0\0 \4 \347\360\1\0\0\0\0\0\3\0\1\0"..., 8192) = 8192
	_llseek(35, 1187840, [1187840], SEEK_SET) = 0
	read(35, "\0\0\0\0\314=\240F\2\203\5\0\224\0\324\0\0 \4 \352\344\1\0T\227\376\4\0\0\0\0"..., 8192) = 8192
	_llseek(35, 1122304, [1122304], SEEK_SET) = 0
	read(35, "\0\0\0\0\\\\\240Fm\252\5\0H\0\0\2\0 \4 \0\0\0\0\200\235\374\4\0\233\374\4"..., 8192) = 8192
	_llseek(35, 450560, [450560], SEEK_SET) = 0
	read(35, "\0\0\0\0\360e\240FO\212\5\0\204\0008\27\0 \4 \347\360\1\0\0\0\0\0\4\0\1\0"..., 8192) = 8192
	_llseek(44, 73728, [73728], SEEK_SET)   = 0
	read(44, "\0\0\0\0\330\206d\1\35\252\4\0\310\0\4\1\0 \4 \0\0\0\0P\237^\1\240\236`\1"..., 8192) = 8192
	_llseek(43, 32768, [32768], SEEK_SET)   = 0
	read(43, "\0\0\0\0\214\311\206E\"\331\0\0@\7x\n\360\37\4 \0\0\0\0x\212\30\0\344\237\30\0"..., 8192) = 8192
	_llseek(44, 188416, [188416], SEEK_SET) = 0
	read(44, "\0\0\0\0`'f\1/4\4\0\304\0\360\0\0 \4 \0\0\0\0L\237h\1\230\236h\1"..., 8192) = 8192
	_llseek(23, 49152, [49152], SEEK_SET)   = 0
	read(23, "\0\0\0\0$\236.F*Q\0\0\374\3D\24\360\37\4 \0\0\0\0\344\237\30\0\330\237\30\0"..., 8192) = 8192
	_llseek(24, 73728, [73728], SEEK_SET)   = 0
	read(24, "\0\0\0\0\244\34\203E\314\225\0\0\324\5\0\t\360\37\4 \0\0\0\0\0\211 \0\340\237 \0"..., 8192) = 8192
	_llseek(16, 417792, [417792], SEEK_SET) = 0
	read(16, "\0\0\0\0\234\5\210F\2474\5\0\30\1\34\4\0 \4 \0\0\0\0t\237\30\1\350\236\30\1"..., 8192) = 8192
	openat(AT_FDCWD, "base/139580/139770", O_RDWR|O_LARGEFILE) = 62
	read(62, "\0\0\0\0P_\277E\233!\0\0<\0\360\37\360\37\4 \0\0\0\0b1\5\0\3\0\0\0"..., 8192) = 8192
	read(62, "\0\0\0\0\320^\277E/S\0\0\224\2|\30\360\37\4 \0\0\0\0\344\237\30\0\330\237\30\0"..., 8192) = 8192
	openat(AT_FDCWD, "base/139580/2224", O_RDWR|O_LARGEFILE) = 63
	read(63, "\0\0\0\0\270s\330<\311\265\1\0\34\3x\6\0 \4 -\250\1\0\2\0\1\0\264\237\222\0"..., 8192) = 8192
	openat(AT_FDCWD, "base/139580/90709", O_RDWR|O_LARGEFILE) = 64
	read(64, "\0\0\0\0Tf\240F\375\242\0\0\34\0\320\37\374\37\4 \0\0\0\0\320\237R\0\0\0\0\0"..., 8192) = 8192
	_llseek(43, 16384, [16384], SEEK_SET)   = 0
	read(43, "\0\0\0\0|\254\206EW\310\0\0@\7x\n\360\37\4 \0\0\0\0x\212\30\0\344\237\30\0"..., 8192) = 8192
	_llseek(44, 131072, [131072], SEEK_SET) = 0
	read(44, "\0\0\0\0\344Fe\1\305\372\4\0\300\0\f\1\0 \4 \0\0\0\0D\237r\1\210\236r\1"..., 8192) = 8192
	_llseek(43, 40960, [40960], SEEK_SET)   = 0
	read(43, "\0\0\0\0\234\346\206E\245\361\0\0@\7x\n\360\37\4 \0\0\0\0x\212\30\0\344\237\30\0"..., 8192) = 8192
	_llseek(44, 147456, [147456], SEEK_SET) = 0
	read(44, "\0\0\0\0\360\206e\1\r\331\4\0\274\0H\1\0 \4 \0\0\0\0P\237`\1\210\236\216\1"..., 8192) = 8192
	gettimeofday({tv_sec=1541084179, tv_usec=208768}, NULL) = 0
	gettimeofday({tv_sec=1541084179, tv_usec=208829}, NULL) = 0
	openat(AT_FDCWD, "base/139580/111805_fsm", O_RDWR|O_LARGEFILE) = -1 ENOENT (No such file or directory)
	openat(AT_FDCWD, "base/139580/111805", O_RDWR|O_LARGEFILE) = 65
	_llseek(65, 0, [8192], SEEK_END)        = 0
	_llseek(65, 0, [0], SEEK_SET)           = 0
	read(65, "\0\0\0\0hs\240FT.\0\0(\0L\23\0 \4 \0\0\0\0\224\234\330\6\374\231*\5"..., 8192) = 8192
	read(45, "\0\0\0\0\274\324\246F^a\0\0,\0T\4\0 \4 \0\0\0\0,\231\244\r@\221\330\17"..., 8192) = 8192
	openat(AT_FDCWD, "base/139580/139675", O_RDWR|O_LARGEFILE) = 66
	read(66, "\0\0\0\0\274\264/F\4\214\0\0<\0\360\37\360\37\4 \0\0\0\0b1\5\0\3\0\0\0"..., 8192) = 8192
	_llseek(66, 24576, [24576], SEEK_SET)   = 0
	read(66, "\0\0\0\0d^uF\2\250\0\0000\0\254\37\360\37\4 \0\0\0\0\350\237\20\0\304\237\30\0"..., 8192) = 8192
	_llseek(66, 49152, [49152], SEEK_SET)   = 0
	read(66, "\0\0\0\0t\264/F\247\n\0\0\f\3\24\27\360\37\4 \0\0\0\0\344\237\30\0\330\237\30\0"..., 8192) = 8192
	_llseek(35, 114688, [114688], SEEK_SET) = 0
	read(35, "\0\0\0\0\204\241\240F\376\212\5\0\230\0\34\17\0 \4 \347\360\1\0\0\0\0\0d\2378\1"..., 8192) = 8192
	_llseek(24, 303104, [303104], SEEK_SET) = 0
	read(24, "\0\0\0\0\220\213\207F5\241\0\0\\\3\340\22\360\37\4 \0\0\0\0\340\222 \0\340\237 \0"..., 8192) = 8192
	_llseek(16, 2834432, [2834432], SEEK_SET) = 0
	read(16, "\0\0\0\0\370\226\221F\305\271\5\0\f\1\f\17\0 \4 \371\360\1\0t\237\30\1\350\236\30\1"..., 8192) = 8192
	_llseek(33, 352256, [352256], SEEK_SET) = 0
	read(33, "\0\0\0\0\320WKC\3462\0\0\200\0D\7\0 \4 \0\0\0\0\360\235\32\0048\235j\1"..., 8192) = 8192
	_llseek(34, 65536, [65536], SEEK_SET)   = 0
	read(34, "\0\0\0\0h\266vFx\34\0\0\220\0\200\31\360\37\4 \0\0\0\0\250\237\220\0t\237h\0"..., 8192) = 8192
	_llseek(35, 1064960, [1064960], SEEK_SET) = 0
	read(35, "\0\0\0\0\304a\33F\16\340\5\0H\0\360\1\0 \4 \0\0\0\0|\235\4\5\370\232\4\5"..., 8192) = 8192
	_llseek(35, 122880, [122880], SEEK_SET) = 0
	read(35, "\0\0\0\0p\274/FnR\5\0x\0\220\26\0 \4 \347\360\1\0\5\0\1\0\0\0\0\0"..., 8192) = 8192
	_llseek(35, 49152, [49152], SEEK_SET)   = 0
	read(35, "\0\0\0\0`IrF\236\337\1\0t\0$\33\0 \4 \347\360\1\0\0\0\0\0d\2378\1"..., 8192) = 8192
	_llseek(35, 40960, [40960], SEEK_SET)   = 0
	read(35, "\0\0\0\0\234\304vF\267\306\1\0\310\0\364\7\0 \4 \347\360\1\0|\231\n\1\364\230\n\1"..., 8192) = 8192
	_llseek(35, 73728, [73728], SEEK_SET)   = 0
	read(35, "\0\0\0\0004\215xF\277l\0\0\204\0\200\10\0 \4 \347\360\1\0\0\200\1\0\0\200\1\0"..., 8192) = 8192
	_llseek(36, 106496, [106496], SEEK_SET) = 0
	read(36, "\0\0\0\0|\n\224F\252V\0\0\\\0\334\34\360\37\4 \0\0\0\0\254\237\210\0|\237`\0"..., 8192) = 8192
	_llseek(37, 376832, [376832], SEEK_SET) = 0
	read(37, "\0\0\0\0\20\310vFmN\5\0\350\0\304\30\0 \4 \0\0\0\0\20\237\332\1\0\236 \2"..., 8192) = 8192
	--- SIGSEGV {si_signo=SIGSEGV, si_code=SEGV_MAPERR, si_addr=0x10} ---
	+++ killed by SIGSEGV +++

Karsten Hilbert
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

On Thu, Nov 01, 2018 at 04:11:33PM +0100, Karsten Hilbert wrote:

Will attempt to get a stack trace ...

Eventually, the stack trace (attached).

Thanks for insights,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

On 2018-Nov-01, Karsten Hilbert wrote:

Program received signal SIGSEGV, Segmentation fault.
heap_attisnull (tup=0x0, attnum=5, tupleDesc=0xb2990ef4) at ./build/../src/backend/access/common/heaptuple.c:403
403 ./build/../src/backend/access/common/heaptuple.c: Datei oder Verzeichnis nicht gefunden.
(gdb) thread apply all bt full

Thread 1 (Thread 0xb4c429c0 (LWP 22367)):
#0 heap_attisnull (tup=0x0, attnum=5, tupleDesc=0xb2990ef4) at ./build/../src/backend/access/common/heaptuple.c:403
__func__ = "heap_attisnull"
#1 0x0087690d in ri_NullCheck (tupDesc=0xb2990ef4, tup=0x0, riinfo=0x2e1d548, rel_is_pk=true) at ./build/../src/backend/utils/adt/ri_triggers.c:2894
attnums = 0x2e1d5a4
i = 0
allnull = true
nonenull = true
#2 0x00879bf7 in RI_FKey_cascade_del (fcinfo=0xbfbda9e4) at ./build/../src/backend/utils/adt/ri_triggers.c:917

Ah, now this is interesting. Can you please supply the definition of
the table? I'm wondering if there is a partitioned table with an FK to
this one. I'm not quite seeing how come 'tup' is NULL there. Can you
'print trigdata' in frame 2?

In general terms, this bug report would have been more actionable if you
had shown the definition of the tables involved right from the start.

--
�lvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On Thu, Nov 01, 2018 at 12:27:23PM -0300, Alvaro Herrera wrote:

In general terms, this bug report would have been more actionable if you
had shown the definition of the tables involved right from the start.

Sorry for that, will supply.

Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

On Thu, Nov 01, 2018 at 12:27:23PM -0300, Alvaro Herrera wrote:

Ah, now this is interesting. Can you please supply the definition of
the table?

Attached.

I'm wondering if there is a partitioned table with an FK to
this one.

There is. Both ref.auto_hint and clin.suppressed_hint are
using inheritance (from audit.audit_fields). However, GNUmed
does not use inheritance for explicit partitioning but rather
similar to how classes and subclasses are used in OO languages.

I'm not quite seeing how come 'tup' is NULL there. Can you
'print trigdata' in frame 2?

Sure, how ? :-)

(I can surely type "print trigdata" but does that already
auto-select from "frame 2" ?)

Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

Alvaro Herrera <alvherre@2ndquadrant.com> writes:

Ah, now this is interesting. Can you please supply the definition of
the table? I'm wondering if there is a partitioned table with an FK to
this one. I'm not quite seeing how come 'tup' is NULL there.

Yeah, apparently we've passed a null OLD tuple to an RI_FKey_cascade_del
trigger, which surely shouldn't happen. It'd be interesting to look at
the set of triggers on this table. I don't entirely trust psql's \d
to show us reality if there's something screwed up about the triggers,
so in addition to \d output, could we see

select * from pg_trigger where tgrelid = 'ref.auto_hint'::regclass;

regards, tom lane

On Thu, Nov 01, 2018 at 12:27:23PM -0300, Alvaro Herrera wrote:

Program received signal SIGSEGV, Segmentation fault.
heap_attisnull (tup=0x0, attnum=5, tupleDesc=0xb2990ef4) at ./build/../src/backend/access/common/heaptuple.c:403
403 ./build/../src/backend/access/common/heaptuple.c: Datei oder Verzeichnis nicht gefunden.
(gdb) thread apply all bt full

Thread 1 (Thread 0xb4c429c0 (LWP 22367)):
#0 heap_attisnull (tup=0x0, attnum=5, tupleDesc=0xb2990ef4) at ./build/../src/backend/access/common/heaptuple.c:403
__func__ = "heap_attisnull"
#1 0x0087690d in ri_NullCheck (tupDesc=0xb2990ef4, tup=0x0, riinfo=0x2e1d548, rel_is_pk=true) at ./build/../src/backend/utils/adt/ri_triggers.c:2894
attnums = 0x2e1d5a4
i = 0
allnull = true
nonenull = true
#2 0x00879bf7 in RI_FKey_cascade_del (fcinfo=0xbfbda9e4) at ./build/../src/backend/utils/adt/ri_triggers.c:917

Can you 'print trigdata' in frame 2?

Does this help at all ?

(gdb) frame 2
#2 0x00879bf7 in RI_FKey_cascade_del (fcinfo=0xbfbda9e4) at ./build/../src/backend/utils/adt/ri_triggers.c:917
917 ./build/../src/backend/utils/adt/ri_triggers.c: Datei oder Verzeichnis nicht gefunden.
(gdb) print trigdata
$1 = (TriggerData *) 0xbfbdaca4

It is from another run, however.

Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

On Thu, Nov 01, 2018 at 11:43:56AM -0400, Tom Lane wrote:

Alvaro Herrera <alvherre@2ndquadrant.com> writes:

Ah, now this is interesting. Can you please supply the definition of
the table? I'm wondering if there is a partitioned table with an FK to
this one. I'm not quite seeing how come 'tup' is NULL there.

Yeah, apparently we've passed a null OLD tuple to an RI_FKey_cascade_del
trigger, which surely shouldn't happen. It'd be interesting to look at
the set of triggers on this table. I don't entirely trust psql's \d
to show us reality if there's something screwed up about the triggers,
so in addition to \d output, could we see

select * from pg_trigger where tgrelid = 'ref.auto_hint'::regclass;

Ausgabeformat ist �wrapped�.
tgrelid | tgname | tgfoid | tgtype | tgenabled | tgisinternal | tgconstrrelid | tgconstrindid | tgconstraint | tgdeferrable | tginitdeferred | tgnargs | tgattr | tgargs | tgqual | tgoldtable | tgnewtable
---------+-------------------------------+--------+--------+-----------+--------------+---------------+---------------+--------------+--------------+----------------+---------+--------+--------+--------+------------+------------
111088 | RI_ConstraintTrigger_a_116970 | 1646 | 9 | O | t | 116789 | 111099 | 116969 | f | f | 0 | | \x | | |
111088 | RI_ConstraintTrigger_a_116971 | 1649 | 17 | O | t | 116789 | 111099 | 116969 | f | f | 0 | | \x | | |
111088 | zt_ins_auto_hint | 141652 | 7 | O | f | 0 | 0 | 0 | f | f | 0 | | \x | | |
111088 | zt_upd_auto_hint | 141654 | 19 | O | f | 0 | 0 | 0 | f | f | 0 | | \x | | |
111088 | zt_del_auto_hint | 141656 | 11 | O | f | 0 | 0 | 0 | f | f | 0 | | \x | | |
(5 Zeilen)

BTW, do you want me to attach text files or include into the
body (potential wrapping issues on display) ?

Thanks,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:

On Thu, Nov 01, 2018 at 11:43:56AM -0400, Tom Lane wrote:

Yeah, apparently we've passed a null OLD tuple to an RI_FKey_cascade_del
trigger, which surely shouldn't happen. It'd be interesting to look at
the set of triggers on this table. I don't entirely trust psql's \d
to show us reality if there's something screwed up about the triggers,
so in addition to \d output, could we see
select * from pg_trigger where tgrelid = 'ref.auto_hint'::regclass;

[ pretty normal-looking trigger entries ]

I was feeling baffled about this, but it suddenly occurs to me that maybe
the bug fixed in 040a1df61/372102b81 explains this. If the particular
tuple you're trying to delete predates the last ALTER TABLE ADD COLUMN
operation on the table, then this bug would result in t_self getting
set to "invalid", which'd cause AfterTriggerSaveEvent to save "ate_ctid1"
as "invalid", which'd lead to a null tuple getting passed when the trigger
eventually gets invoked.

regards, tom lane

On Fri, Nov 02, 2018 at 11:56:58PM -0400, Tom Lane wrote:

On Thu, Nov 01, 2018 at 11:43:56AM -0400, Tom Lane wrote:

Yeah, apparently we've passed a null OLD tuple to an RI_FKey_cascade_del
trigger, which surely shouldn't happen. It'd be interesting to look at
the set of triggers on this table. I don't entirely trust psql's \d
to show us reality if there's something screwed up about the triggers,
so in addition to \d output, could we see
select * from pg_trigger where tgrelid = 'ref.auto_hint'::regclass;

[ pretty normal-looking trigger entries ]

I was feeling baffled about this, but it suddenly occurs to me that maybe
the bug fixed in 040a1df61/372102b81 explains this. If the particular
tuple you're trying to delete predates the last ALTER TABLE ADD COLUMN
operation on the table, then this bug would result in t_self getting
set to "invalid", which'd cause AfterTriggerSaveEvent to save "ate_ctid1"
as "invalid", which'd lead to a null tuple getting passed when the trigger
eventually gets invoked.

Now, that is a theory I can follow up on -- all the
bootstrapping SQL scripts are under version control so I can
pin down the exact sequence of events.

... goes off to do some checks ...

And sure enough there's an ALTER TABLE ADD COLUMN related to
that table:

alter table ref.auto_hint
add column recommendation_query text;

-- (audit log table needs to get the new column, too)
alter table audit.log_auto_hint
add column recommendation_query text;

before the DELETE of the pre-existing tuple.

When running a rigged upgrade that stops right before those
ALTER TABLEs and then doing the following:

begin;
-- instrument:
insert into ref.auto_hint (title, query, hint, source, lang) values (
'DELETE test',
'select 1;',
'insertion before ADD COLUMN',
'testing',
'en'
);
-- works:
DELETE from ref.auto_hint where title = 'DELETE test';
-- instrument, again:
insert into ref.auto_hint (title, query, hint, source, lang) values (
'DELETE test',
'select 1;',
'insertion before ADD COLUMN',
'testing',
'en'
);
alter table ref.auto_hint
add column recommendation_query text;
-- audit log table needs to get the message
alter table audit.log_auto_hint
add column recommendation_query text;
-- fails:
DELETE from ref.auto_hint where title = 'DELETE test';
rollback;

the expected segfault does indeed occur.

Conversely, moving the offending

DELETE FROM ref.auto_hint WHERE title = 'Kontraindikation: ACE/Sartan <-> Schwangerschaft';

to right before the ALTER TABLEs makes the full upgrade run
through without further problems.

Looking at 040a1df61/372102b81 feels like it fits the bill.

So, I guess I can work around the issue by the above
manoeuvre and report back once 040a1df61/372102b81 is
released.

Anything else you'd want me to look into ?

Many thanks,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:

On Fri, Nov 02, 2018 at 11:56:58PM -0400, Tom Lane wrote:

I was feeling baffled about this, but it suddenly occurs to me that maybe
the bug fixed in 040a1df61/372102b81 explains this.

So, I guess I can work around the issue by the above
manoeuvre and report back once 040a1df61/372102b81 is
released.
Anything else you'd want me to look into ?

Don't suppose you'd want to build a server with 372102b81 applied
and see if it works?

regards, tom lane

On Sat, Nov 03, 2018 at 11:39:49AM -0400, Tom Lane wrote:

Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:

On Fri, Nov 02, 2018 at 11:56:58PM -0400, Tom Lane wrote:

I was feeling baffled about this, but it suddenly occurs to me that maybe
the bug fixed in 040a1df61/372102b81 explains this.

So, I guess I can work around the issue by the above
manoeuvre and report back once 040a1df61/372102b81 is
released.
Anything else you'd want me to look into ?

Don't suppose you'd want to build a server with 372102b81 applied
and see if it works?

Yeah, I feared that answer ;-)

However, I lack the skills and the machine(s) to do so ...

I will, at any rate, report back when the existing fix is released.

Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B

Hello,

we reached the exactly same problem after upgrading to PostgreSQL 11 - the
server crashed on a DELETE statement with a trigger. We also observed an
AFTER DELETE trigger receiving NULL values in OLD. Now I see the problem
seems to be solved (theoretically). Unfortunately, we are not able to build
the server with the patch, so we cannot confirm that. However, when we just
copied the database (within the same server), the same DELETE executed on
the copy with no problems.

I would like to ask, however: could the same problem arise from an UPDATE
statement (also on a table with an AFTER trigger), or would that be another
bug (technical details below)?

As the bug causes our production servers segfault several times a day, we'd
like to solve the problem as soon as possible. Do I understand it correctly
that if we dump and restore the database, the bug should not occur (until
the next ALTER TABLE ADD COLUMN - which we'll avoid until the patch is
released)?

With the update, we caught this (we also have the core dump and could
provide it privately if useful):

#0  __memcmp_sse4_1 () at ../sysdeps/x86_64/multiarch/memcmp-sse4.S:793

No locals.

#1  0x00007f6a6b993296 in datumIsEqual (value1=7, value2=7, typByVal=<
optimized out>, typLen=16) at /build/postgresql-11-oDwOcQ/postgresql-11-
11.0/build/../src/backend/utils/adt/datum.c:249

        size1 = 16

        size2 = <optimized out>

        s1 = 0x7 <error: Cannot access memory at address 0x7>

        s2 = 0x7 <error: Cannot access memory at address 0x7>

        res = <optimized out>

#2  0x00007f6a6b6b89de in ProjIndexIsUnchanged (newtup=0x7f6a6d504e80,
oldtup=0x7ffc9c1c1270, relation=0x7f6a6b4c1738) at /build/postgresql-11-
oDwOcQ/postgresql-11-11.0/build/../src/backend/access/heap/heapam.c:4539

        att = <optimized out>

        indexOid = <optimized out>

        indexDesc = 0x7f6a6b4c5008

        indexInfo = 0x7f6a6d505ca8

        i = 0

        indexoidlist = <optimized out>

        econtext = 0x7f6a6d4c5080

        new_isnull = {false, false, false, 156, 252, 127, false, false, 44,
29, 128, 107, 106, 127, false, false, 116, 54, 76, 107, 106, 127, false,
false, 123, false, false, false, false, false, false, false}

        slot = 0x7f6a6d507b78

        equals = true

        old_isnull = {false, false, false, 109, 106, 127, false, false, 165,
199, 80, 109, 106, 127, false, false, 160, 199, 80, 109, 106, 127, false,
false, 168, 199, 80, 109, 106, 127, false, false}

        new_values = {7, 4, 140095077240522, 16, 140722927572064,
140095077271456, 140095077272720, 140722927572079, 0, 0, 140722927572048,
140095054460096, 140095077060032, 140095077242520, 5, 4, 140722927572096,
140095046814123, 

          140722927572096, 140095046825162, 140095043410520,
140095077060880, 140722927572224, 140095046865098, 60129542543, 23274985272,
140095043410520, 140095077060032, 140095043409720, 140095077060304,
140095077237568, 

          140095043414504}

        indexno = 1

        l = 0x7f6a6d50d180

        estate = 0x7f6a6d4c4e70

        old_values = {7, 5, 140078657614882, 1, 140095077060032, 1024,
140095077271456, 0, 140722927571760, 140095049517261, 140722927571808,
140722927571776, 140722927571792, 140095046814329, 18, 140095077271464,
140095076986224, 

          140095077237568, 22, 100, 140722927571856, 140722927571920,
140095046831813, 140722927571856, 140095046842624, 0, 140095077265656, 3,
140095077271456, 140095077271792, 140095077271456, 140095077242520}

#3  heap_update (relation=relation@entry=0x7f6a6b4c1738, otid=otid@entry=0x7
ffc9c1c1690, newtup=newtup@entry=0x7f6a6d504e80, cid=0, crosscheck=<
optimized out>, wait=wait@entry=true, hufd=hufd@entry=0x7ffc9c1c15b0, 

    lockmode=lockmode@entry=0x7ffc9c1c15a4) at /build/postgresql-11-oDwOcQ/
postgresql-11-11.0/build/../src/backend/access/heap/heapam.c:4230

        result = <optimized out>

        xid = 245028971

        hot_attrs = 0x7f6a6d50d0a0

        proj_idx_attrs = 0x7f6a6d50d0b8

        key_attrs = 0x7f6a6d50d0d0

        id_attrs = 0x7f6a6d50d0e8

        interesting_attrs = 0x7f6a6d50d100

        lp = <optimized out>

        oldtup = {t_len = 81, t_self = {ip_blkid = {bi_hi = 0, bi_lo = 9},
ip_posid = 71}, t_tableOid = 65471913, t_data = 0x7f669aa0b3f0}

        heaptup = 0x7f6a6d504e80

        old_key_tuple = 0x0

        old_key_copied = false

        page = 0x7f669aa0b180 "=="

        block = <optimized out>

        mxact_status = <optimized out>

        buffer = <optimized out>

        newbuf = 17858

        vmbuffer = 0

        vmbuffer_new = 0

        need_toast = <optimized out>

        newtupsize = <optimized out>

        pagefree = <optimized out>

        have_tuple_lock = false

        iscombo = false

        use_hot_update = false

        hot_attrs_checked = <optimized out>

        key_intact = <optimized out>

        all_visible_cleared = false

        all_visible_cleared_new = false

        checked_lockers = <optimized out>

        locker_remains = <optimized out>

        xmax_new_tuple = <optimized out>

        xmax_old_tuple = 245028971

        infomask_old_tuple = 0

        infomask2_old_tuple = 8192

        infomask_new_tuple = 144

        infomask2_new_tuple = 0

        __func__ = "heap_update"

#4  0x00007f6a6b82f2c9 in ExecUpdate (mtstate=mtstate@entry=0x7f6a6d4da3f0,
tupleid=0x7ffc9c1c1690, oldtuple=0x0, slot=0x7f6a6d504340, planSlot=
planSlot@entry=0x7f6a6d5685e8, epqstate=epqstate@entry=0x7f6a6d4da4b0,

    estate=estate@entry=0x7f6a6d4d8ed0, canSetTag=true) at /build/postgresql
-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/executor/nodeModifyTable.
c:1209

        lockmode = LockTupleNoKeyExclusive

        partition_constraint_failed = false

        tuple = 0x7f6a6d504e80

        resultRelInfo = 0x7f6a6d4d9110

        resultRelationDesc = 0x7f6a6b4c1738

        result = <optimized out>

        hufd = {ctid = {ip_blkid = {bi_hi = 5616, bi_lo = 39964}, ip_posid =
32764}, xmax = 1803594893, cmax = 32618}

        recheckIndexes = 0x0

        saved_tcs_map = 0x0

        __func__ = "ExecUpdate"

#5  0x00007f6a6b82f978 in ExecModifyTable (pstate=0x7f6a6d4da3f0) at /build/
postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/executor/
nodeModifyTable.c:2166

        node = 0x7f6a6d4da3f0

        proute = 0x0

        estate = 0x7f6a6d4d8ed0

        operation = CMD_UPDATE

        saved_resultRelInfo = 0x0

        resultRelInfo = 0x7f6a6d4d9110

        subplanstate = 0x7f6a6d5679f8

        junkfilter = 0x7f6a6d504218

        slot = <optimized out>

        tupleid = <optimized out>

        tuple_ctid = {ip_blkid = {bi_hi = 0, bi_lo = 9}, ip_posid = 71}

        oldtupdata = {t_len = 2619086544, t_self = {ip_blkid = {bi_hi =
32764, bi_lo = 0}, ip_posid = 9204}, t_tableOid = 32618, t_data = 0x7ffc9c1c
16d0}

        oldtuple = <optimized out>

        __func__ = "ExecModifyTable"

#6  0x00007f6a6b80b25b in ExecProcNode (node=0x7f6a6d4da3f0) at /build/
postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/include/executor/
executor.h:237

No locals.

#7  ExecutePlan (execute_once=<optimized out>, dest=0x7f6a6d5ed298,
direction=<optimized out>, numberTuples=0, sendTuples=false, operation=CMD_
UPDATE, use_parallel_mode=<optimized out>, planstate=0x7f6a6d4da3f0, estate=
0x7f6a6d4d8ed0)

    at /build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/
executor/execMain.c:1723

        slot = <optimized out>

        current_tuple_count = 0

#8  standard_ExecutorRun (queryDesc=0x7f6a6d5f5b80, direction=<optimized out

, count=0, execute_once=<optimized out>) at /build/postgresql-11-oDwOcQ/

postgresql-11-11.0/build/../src/backend/executor/execMain.c:364

        estate = 0x7f6a6d4d8ed0

        operation = CMD_UPDATE

        dest = 0x7f6a6d5ed298

        sendTuples = <optimized out>

        __func__ = "standard_ExecutorRun"

#9  0x00007f6a6b95b41d in ProcessQuery (plan=<optimized out>, 

    sourceText=0x7f6a6d3b45a0 "UPDATE public.schedulecard SET ext_ident=
NULL, finalreport_absence_cnt=NULL, period_day=4, period_num=1, rotates=
false, rotationgroup_id=NULL, schedulecard_id=NULL, schedulecardblock_id=
NULL, schedule"..., 

    params=0x0, queryEnv=0x0, dest=0x7f6a6d5ed298, completionTag=0x7ffc9c1c
19d0 "") at /build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/
backend/tcop/pquery.c:161

        queryDesc = 0x7f6a6d5f5b80

#10 0x00007f6a6b95b668 in PortalRunMulti (portal=portal@entry=0x7f6a6d47f
230, isTopLevel=isTopLevel@entry=true, setHoldSnapshot=setHoldSnapshot@entry
=false, dest=dest@entry=0x7f6a6d5ed298, altdest=altdest@entry=0x7f6a6d5ed
298, 

    completionTag=completionTag@entry=0x7ffc9c1c19d0 "") at /build/
postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/tcop/pquery.c:
1286

        pstmt = 0x7f6a6d5ed138

        active_snapshot_set = true

        stmtlist_item = 0x7f6a6d5ed248

#11 0x00007f6a6b95c2d5 in PortalRun (portal=portal@entry=0x7f6a6d47f230,
count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true, run
_once=run_once@entry=true, dest=dest@entry=0x7f6a6d5ed298, 

    altdest=altdest@entry=0x7f6a6d5ed298, completionTag=completionTag@entry=
0x7ffc9c1c19d0 "") at /build/postgresql-11-oDwOcQ/postgresql-11-11.0/
build/../src/backend/tcop/pquery.c:799

        save_exception_stack = 0x7ffc9c1c1c00

        save_context_stack = 0x0

        local_sigjmp_buf = {{__jmpbuf = {140722927574016,
7153736072036697610, 140095075869696, 140722927573456, 140095076692528,
140095075869744, 7153736071992657418, 7091693995611473418}, __mask_was_saved
= 0, __saved_mask = {__val = {

                0, 140723125634669, 0, 140095051070678, 64, 112,
140095075869696, 140095075861648, 140095078191720, 140722927573328,
140095049517496, 140722927573360, 2, 140095075869696, 2, 140722927573360}}}}

        result = <optimized out>

        nprocessed = <optimized out>

        saveTopTransactionResourceOwner = 0x7f6a6d4182c8

        saveTopTransactionContext = 0x7f6a6d490150

        saveActivePortal = 0x0

        saveResourceOwner = 0x7f6a6d4182c8

        savePortalContext = 0x0

        saveMemoryContext = 0x7f6a6d490150

        __func__ = "PortalRun"

#12 0x00007f6a6b957f7f in exec_simple_query (

    query_string=0x7f6a6d3b45a0 "UPDATE public.schedulecard SET ext_ident=
NULL, finalreport_absence_cnt=NULL, period_day=4, period_num=1, rotates=
false, rotationgroup_id=NULL, schedulecard_id=NULL, schedulecardblock_id=
NULL, schedule"...) at /build/postgresql-11-oDwOcQ/postgresql-11-11.0/
build/../src/backend/tcop/postgres.c:1122

        parsetree = 0x7f6a6d3b6400

        portal = 0x7f6a6d47f230

        snapshot_set = <optimized out>

        commandTag = <optimized out>

        completionTag = "\000\032\034\234\374\177\000\000\341\356\247kj\177\
000\000\000\000\000\000\000\000\000\000\060\316\367kj\177\000\000\340&\360
kj\177\000\000\234U\357kj\177\000\000\240E;mj\177\000\000Q\000\000\000\000\
000\000"

        querytree_list = <optimized out>

        plantree_list = <optimized out>

        receiver = 0x7f6a6d5ed298

        format = 0

        dest = DestRemote

        parsetree_list = 0x7f6a6d3b6450

        parsetree_item = 0x7f6a6d3b6430

        save_log_statement_stats = false

        was_logged = false

        use_implicit_block = false

        msec_str = "\000\032\034\234\374\177\000\000\341\356\247kj\177\000\
000\000\000\000\000\000\000\000\000\060\316\367kj\177\000"

        __func__ = "exec_simple_query"

#13 0x00007f6a6b95930a in PostgresMain (argc=<optimized out>, argv=argv@
entry=0x7f6a6d400220, dbname=0x7f6a6d4001b0 "mydb", username=<optimized out>
)

    at /build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/
tcop/postgres.c:4159

        query_string = 0x7f6a6d3b45a0 "UPDATE public.schedulecard SET ext_
ident=NULL, finalreport_absence_cnt=NULL, period_day=4, period_num=1,
rotates=false, rotationgroup_id=NULL, schedulecard_id=NULL,
schedulecardblock_id=NULL, schedule"...

        firstchar = 81

        input_message = {

          data = 0x7f6a6d3b45a0 "UPDATE public.schedulecard SET ext_ident=
NULL, finalreport_absence_cnt=NULL, period_day=4, period_num=1, rotates=
false, rotationgroup_id=NULL, schedulecard_id=NULL, schedulecardblock_id=
NULL, schedule"..., len = 319, maxlen = 1024, cursor = 319}

        local_sigjmp_buf = {{__jmpbuf = {140095054460088,
7153736071902479882, 140095054460096, 1, 0, 140095076133088,
7153736071933937162, 7091693993451144714}, __mask_was_saved = 1, __saved_
mask = {__val = {0, 140095076133088, 

                140095004765576, 844424930131970, 206158430256,
140722927574360, 140722927574144, 293, 140095076133088, 0, 0,
140095075861648, 1024, 140722927574468, 0, 140722927574240}}}}

        send_ready_for_query = false

        disable_idle_in_transaction_timeout = false

        __func__ = "PostgresMain"

#14 0x00007f6a6b67867d in BackendRun (port=0x7f6a6d3f68e0) at /build/
postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/postmaster/
postmaster.c:4361

        ac = 1

        secs = 594476990

        usecs = 933865

        i = 1

        av = 0x7f6a6d400220

        maxac = <optimized out>

#15 BackendStartup (port=0x7f6a6d3f68e0) at /build/postgresql-11-oDwOcQ/
postgresql-11-11.0/build/../src/backend/postmaster/postmaster.c:4033

        bn = <optimized out>

        pid = <optimized out>

#16 ServerLoop () at /build/postgresql-11-oDwOcQ/postgresql-11-11.0/
build/../src/backend/postmaster/postmaster.c:1706

        rmask = {fds_bits = {8, 0 <repeats 15 times>}}

        selres = <optimized out>

        now = <optimized out>

        readmask = {fds_bits = {200, 0 <repeats 15 times>}}

        last_lockfile_recheck_time = 1541161751

        last_touch_time = 1541161147

        __func__ = "ServerLoop"

#17 0x00007f6a6b8e7644 in PostmasterMain (argc=5, argv=<optimized out>) at /
build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/
postmaster/postmaster.c:1379

        opt = <optimized out>

        status = <optimized out>

        userDoption = <optimized out>

        listen_addr_saved = true

        i = <optimized out>

        output_config_variable = <optimized out>

        __func__ = "PostmasterMain"

#18 0x00007f6a6b679616 in main (argc=5, argv=0x7f6a6d3af1f0) at /build/
postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/main/main.c:228

No locals.

Best regards, 
Ondřej Bouda

---------- Původní e-mail ----------
Od: Tom Lane <tgl@sss.pgh.pa.us>
Komu: Karsten Hilbert <Karsten.Hilbert@gmx.net>
Datum: 3. 11. 2018 4:57:19
Předmět: Re: backend crash on DELETE, reproducible locally
"Karsten Hilbert <Karsten.Hilbert@gmx.net> writes:

On Thu, Nov 01, 2018 at 11:43:56AM -0400, Tom Lane wrote:

Yeah, apparently we've passed a null OLD tuple to an RI_FKey_cascade_del
trigger, which surely shouldn't happen. It'd be interesting to look at
the set of triggers on this table. I don't entirely trust psql's \d
to show us reality if there's something screwed up about the triggers,
so in addition to \d output, could we see
select * from pg_trigger where tgrelid = 'ref.auto_hint'::regclass;

[ pretty normal-looking trigger entries ]

I was feeling baffled about this, but it suddenly occurs to me that maybe
the bug fixed in 040a1df61/372102b81 explains this. If the particular
tuple you're trying to delete predates the last ALTER TABLE ADD COLUMN
operation on the table, then this bug would result in t_self getting
set to "invalid", which'd cause AfterTriggerSaveEvent to save "ate_ctid1"
as "invalid", which'd lead to a null tuple getting passed when the trigger
eventually gets invoked.

regards, tom lane

"

<obouda@email.cz> writes:

we reached the exactly same problem after upgrading to PostgreSQL 11 - the
server crashed on a DELETE statement with a trigger. We also observed an
AFTER DELETE trigger receiving NULL values in OLD. Now I see the problem
seems to be solved (theoretically). Unfortunately, we are not able to build
the server with the patch, so we cannot confirm that. However, when we just
copied the database (within the same server), the same DELETE executed on
the copy with no problems.

Yeah, dump/reload would make the problem go away.

I would like to ask, however: could the same problem arise from an UPDATE
statement (also on a table with an AFTER trigger), or would that be another
bug (technical details below)?

Same bug.

regards, tom lane

On 11/03/2018 02:19 PM, obouda@email.cz wrote:

Hello,

we reached the exactly same problem after upgrading to PostgreSQL 11 - the
server crashed on a DELETE statement with a trigger.We also observed an
AFTER DELETE trigger receiving NULL values in OLD. Now I see the problem
seems to be solved (theoretically). Unfortunately, we are not able to
build the server with the patch, so we cannot confirm that. However, when
we just copied the database (within the same server), the same DELETE
executed on the copy with no problems.

I would like to ask, however: could the same problemarise from an UPDATE
statement (also on a table with an AFTER trigger), or would that be
another bug (technical details below)?
As the bug causes our production servers segfault several times a day,
we'd like to solve the problem as soon as possible. Do I understand it
correctly that if we dump and restore the database, the bug should not
occur (until the next ALTER TABLE ADD COLUMN - which we'll avoid until the
patch is released)?

You can dump that single table, truncate the table (presuming no FKs) and
then restore it.

--
Angular momentum makes the world go 'round.

Dne 3.11.2018 v 20:38 Tom Lane napsal(a):

Yeah, dump/reload would make the problem go away.

Same bug.

So we dumped and restored all our databases. After that, the crash on
DELETE never occurred (before, it was several times a day). However, the
crash on UPDATE still occurs on specific rows. We are quite certain no
ALTER TABLE statement was executed on the table after the restore.
There are two AFTER INSERT OR UPDATE constraint triggers and one BEFORE
INSERT OR UPDATE trigger on the table, all of which are implemented in
plpgsql. Multiple physical servers, on separate databases with identical
schema, crash on the same type of UPDATE query (different just in
concrete values to be updated). The same code worked perfectly on 10.x.

See the attached backtrace below. Can we do something else to catch the
bug? Or can we hope for this bug to be already fixed and released in the
upcoming version?

Regards,
Ondřej Bouda

[New LWP 8307]
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib/x86_64-linux-gnu/libthread_db.so.1".
Core was generated by `postgres: 11/main: usr mydb 127.0.0.1(56829)
UPDATE '.
Program terminated with signal SIGSEGV, Segmentation fault.
#0 __memcmp_sse4_1 () at ../sysdeps/x86_64/multiarch/memcmp-sse4.S:793
#0 __memcmp_sse4_1 () at ../sysdeps/x86_64/multiarch/memcmp-sse4.S:793
No locals.
#1 0x00007f6a6b993296 in datumIsEqual (value1=12, value2=12,
typByVal=<optimized out>, typLen=16) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/utils/adt/datum.c:249
size1 = 16
size2 = <optimized out>
s1 = 0xc <error: Cannot access memory at address 0xc>
s2 = 0xc <error: Cannot access memory at address 0xc>
res = <optimized out>
#2 0x00007f6a6b6b89de in ProjIndexIsUnchanged (newtup=0x7f6a6d5ccdd0,
oldtup=0x7ffc9c1c1270, relation=0x7f6a6b4c3680) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/access/heap/heapam.c:4539
att = <optimized out>
indexOid = <optimized out>
indexDesc = 0x7f6a6b4c7cf0
indexInfo = 0x7f6a6d5cdbf8
i = 0
indexoidlist = <optimized out>
econtext = 0x7f6a6d4c2c10
new_isnull = {false, false, false, 156, 252, 127, false, false,
44, 29, 128, 107, 106, 127, false, false, 203, 89, 76, 107, 106, 127,
false, false, 123, false, false, false, false, false, false, false}
slot = 0x7f6a6d5cf8b8
equals = true
old_isnull = {false, false, false, 109, 106, 127, false, false,
69, 46, 80, 109, 106, 127, false, false, 64, 46, 80, 109, 106, 127,
false, false, 72, 46, 80, 109, 106, 127, false, false}
new_values = {12, 2, 140095078059546, 16, 140722927572064,
140095077232192, 140095077233456, 140722927572079, 0, 0,
140722927572048, 140095054460096, 140095077050704, 140095078061816, 5,
4, 140722927572096, 140095046814123, 140722927572096, 140095046825162,
140095043418528, 140095077051552, 140722927572224, 140095046865098,
60129542543, 23274993280, 140095043418528, 140095077050704,
140095043417728, 140095077050976, 140095078056288, 140095043422512}
indexno = 2
l = 0x7f6a6d503840
estate = 0x7f6a6d4c2a00
old_values = {12, 1, 140078631611970, 1, 140095077050704, 1024,
140095077232192, 0, 140722927571760, 140095049517261, 140722927571808,
140722927571776, 140722927571792, 140095046814329, 18, 140095077232200,
140095076976896, 140095078056288, 22, 100, 140722927571856,
140722927571920, 140095046831813, 140722927571856, 140095046842624, 0,
140095077226392, 3, 140095077232192, 140095077232528, 140095077232192,
140095078061816}
#3 heap_update (relation=relation@entry=0x7f6a6b4c3680,
otid=otid@entry=0x7ffc9c1c1690, newtup=newtup@entry=0x7f6a6d5ccdd0,
cid=0, crosscheck=<optimized out>, wait=wait@entry=true,
hufd=hufd@entry=0x7ffc9c1c15b0, lockmode=lockmode@entry=0x7ffc9c1c15a4)
at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/access/heap/heapam.c:4230
result = <optimized out>
xid = 245762845
hot_attrs = 0x7f6a6d503740
proj_idx_attrs = 0x7f6a6d503758
key_attrs = 0x7f6a6d503770
id_attrs = 0x7f6a6d503788
interesting_attrs = 0x7f6a6d5037a0
lp = <optimized out>
oldtup = {t_len = 85, t_self = {ip_blkid = {bi_hi = 0, bi_lo =
45}, ip_posid = 10}, t_tableOid = 557732818, t_data = 0x7f669913ee10}
heaptup = 0x7f6a6d5ccdd0
old_key_tuple = 0x0
old_key_copied = false
page = 0x7f669913d180 "]="
block = <optimized out>
mxact_status = <optimized out>
buffer = <optimized out>
newbuf = 14683
vmbuffer = 0
vmbuffer_new = 0
need_toast = <optimized out>
newtupsize = <optimized out>
pagefree = <optimized out>
have_tuple_lock = false
iscombo = false
use_hot_update = false
hot_attrs_checked = <optimized out>
key_intact = <optimized out>
all_visible_cleared = false
all_visible_cleared_new = false
checked_lockers = <optimized out>
locker_remains = <optimized out>
xmax_new_tuple = <optimized out>
xmax_old_tuple = 245762845
infomask_old_tuple = 0
infomask2_old_tuple = 8192
infomask_new_tuple = 144
infomask2_new_tuple = 0
__func__ = "heap_update"
#4 0x00007f6a6b82f2c9 in ExecUpdate
(mtstate=mtstate@entry=0x7f6a6d4d81c0, tupleid=0x7ffc9c1c1690,
oldtuple=0x0, slot=0x7f6a6d5cc160,
planSlot=planSlot@entry=0x7f6a6d567578,
epqstate=epqstate@entry=0x7f6a6d4d8280,
estate=estate@entry=0x7f6a6d4d6a60, canSetTag=true) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/executor/nodeModifyTable.c:1209
lockmode = LockTupleNoKeyExclusive
partition_constraint_failed = false
tuple = 0x7f6a6d5ccdd0
resultRelInfo = 0x7f6a6d4d6ca0
resultRelationDesc = 0x7f6a6b4c3680
result = <optimized out>
hufd = {ctid = {ip_blkid = {bi_hi = 5616, bi_lo = 39964},
ip_posid = 32764}, xmax = 1803594893, cmax = 32618}
recheckIndexes = 0x0
saved_tcs_map = 0x0
__func__ = "ExecUpdate"
#5 0x00007f6a6b82f978 in ExecModifyTable (pstate=0x7f6a6d4d81c0) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/executor/nodeModifyTable.c:2166
node = 0x7f6a6d4d81c0
proute = 0x0
estate = 0x7f6a6d4d6a60
operation = CMD_UPDATE
saved_resultRelInfo = 0x0
resultRelInfo = 0x7f6a6d4d6ca0
subplanstate = 0x7f6a6d566988
junkfilter = 0x7f6a6d5cc038
slot = <optimized out>
tupleid = <optimized out>
tuple_ctid = {ip_blkid = {bi_hi = 0, bi_lo = 45}, ip_posid = 10}
oldtupdata = {t_len = 2619086544, t_self = {ip_blkid = {bi_hi =
32764, bi_lo = 0}, ip_posid = 9204}, t_tableOid = 32618, t_data =
0x7ffc9c1c16d0}
oldtuple = <optimized out>
__func__ = "ExecModifyTable"
#6 0x00007f6a6b80b25b in ExecProcNode (node=0x7f6a6d4d81c0) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/include/executor/executor.h:237
No locals.
#7 ExecutePlan (execute_once=<optimized out>, dest=0x7f6a6d5e9448,
direction=<optimized out>, numberTuples=0, sendTuples=false,
operation=CMD_UPDATE, use_parallel_mode=<optimized out>,
planstate=0x7f6a6d4d81c0, estate=0x7f6a6d4d6a60) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/executor/execMain.c:1723
slot = <optimized out>
current_tuple_count = 0
#8 standard_ExecutorRun (queryDesc=0x7f6a6d566040, direction=<optimized
out>, count=0, execute_once=<optimized out>) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/executor/execMain.c:364
estate = 0x7f6a6d4d6a60
operation = CMD_UPDATE
dest = 0x7f6a6d5e9448
sendTuples = <optimized out>
__func__ = "standard_ExecutorRun"
#9 0x00007f6a6b95b41d in ProcessQuery (plan=<optimized out>,
sourceText=0x7f6a6d3b45a0 "UPDATE public.schedulecard SET
ext_ident=NULL, finalreport_absence_cnt=NULL, period_day=2,
period_num=1, rotates=false, rotationgroup_id=NULL,
schedulecard_id=NULL, schedulecardblock_id=NULL, schedule"...,
params=0x0, queryEnv=0x0, dest=0x7f6a6d5e9448,
completionTag=0x7ffc9c1c19d0 "") at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/tcop/pquery.c:161
queryDesc = 0x7f6a6d566040
#10 0x00007f6a6b95b668 in PortalRunMulti
(portal=portal@entry=0x7f6a6d479d00, isTopLevel=isTopLevel@entry=true,
setHoldSnapshot=setHoldSnapshot@entry=false,
dest=dest@entry=0x7f6a6d5e9448, altdest=altdest@entry=0x7f6a6d5e9448,
completionTag=completionTag@entry=0x7ffc9c1c19d0 "") at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/tcop/pquery.c:1286
pstmt = 0x7f6a6d5e92e8
active_snapshot_set = true
stmtlist_item = 0x7f6a6d5e93f8
#11 0x00007f6a6b95c2d5 in PortalRun (portal=portal@entry=0x7f6a6d479d00,
count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true,
run_once=run_once@entry=true, dest=dest@entry=0x7f6a6d5e9448,
altdest=altdest@entry=0x7f6a6d5e9448,
completionTag=completionTag@entry=0x7ffc9c1c19d0 "") at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/tcop/pquery.c:799
save_exception_stack = 0x7ffc9c1c1c00
save_context_stack = 0x0
local_sigjmp_buf = {{__jmpbuf = {140722927574016,
7153736072036697610, 140095075869728, 140722927573456, 140095076670720,
140095075869776, 7153736071992657418, 7091693995611473418},
__mask_was_saved = 0, __saved_mask = {__val = {0, 140723125634669, 0,
140095051070678, 64, 112, 140095075869728, 140095075861648,
140095078175768, 140722927573328, 140095049517496, 140722927573360, 2,
140095075869728, 2, 140722927573360}}}}
result = <optimized out>
nprocessed = <optimized out>
saveTopTransactionResourceOwner = 0x7f6a6d41b148
saveTopTransactionContext = 0x7f6a6d48d8d0
saveActivePortal = 0x0
saveResourceOwner = 0x7f6a6d41b148
savePortalContext = 0x0
saveMemoryContext = 0x7f6a6d48d8d0
__func__ = "PortalRun"
#12 0x00007f6a6b957f7f in exec_simple_query (query_string=0x7f6a6d3b45a0
"UPDATE public.schedulecard SET ext_ident=NULL,
finalreport_absence_cnt=NULL, period_day=2, period_num=1, rotates=false,
rotationgroup_id=NULL, schedulecard_id=NULL, schedulecardblock_id=NULL,
schedule"...) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/tcop/postgres.c:1122
parsetree = 0x7f6a6d3b6420
portal = 0x7f6a6d479d00
snapshot_set = <optimized out>
commandTag = <optimized out>
completionTag =
"\000\032\034\234\374\177\000\000\341\356\247kj\177\000\000\000\000\000\000\000\000\000\000\060\316\367kj\177\000\000\340&\360kj\177\000\000\234U\357kj\177\000\000\240E;mj\177\000\000Q\000\000\000\000\000\000"
querytree_list = <optimized out>
plantree_list = <optimized out>
receiver = 0x7f6a6d5e9448
format = 0
dest = DestRemote
parsetree_list = 0x7f6a6d3b6470
parsetree_item = 0x7f6a6d3b6450
save_log_statement_stats = false
was_logged = false
use_implicit_block = false
msec_str =
"\000\032\034\234\374\177\000\000\341\356\247kj\177\000\000\000\000\000\000\000\000\000\000\060\316\367kj\177\000"
__func__ = "exec_simple_query"
#13 0x00007f6a6b95930a in PostgresMain (argc=<optimized out>,
argv=argv@entry=0x7f6a6d400400, dbname=0x7f6a6d4003e0 "mydb",
username=<optimized out>) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/tcop/postgres.c:4159
query_string = 0x7f6a6d3b45a0 "UPDATE public.schedulecard SET
ext_ident=NULL, finalreport_absence_cnt=NULL, period_day=2,
period_num=1, rotates=false, rotationgroup_id=NULL,
schedulecard_id=NULL, schedulecardblock_id=NULL, schedule"...
firstchar = 81
input_message = {data = 0x7f6a6d3b45a0 "UPDATE
public.schedulecard SET ext_ident=NULL, finalreport_absence_cnt=NULL,
period_day=2, period_num=1, rotates=false, rotationgroup_id=NULL,
schedulecard_id=NULL, schedulecardblock_id=NULL, schedule"..., len =
328, maxlen = 1024, cursor = 328}
local_sigjmp_buf = {{__jmpbuf = {140095054460088,
7153736071902479882, 140095054460096, 1, 0, 140095076143920,
7153736071933937162, 7091693993451144714}, __mask_was_saved = 1,
__saved_mask = {__val = {0, 140095076143920, 140095004765576,
844424930131970, 206158430256, 140722927574360, 140722927574144, 293,
140095076143920, 0, 0, 140095075861648, 1024, 140722927574468, 0,
140722927574240}}}}
send_ready_for_query = false
disable_idle_in_transaction_timeout = false
__func__ = "PostgresMain"
#14 0x00007f6a6b67867d in BackendRun (port=0x7f6a6d3f9330) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/postmaster/postmaster.c:4361
ac = 1
secs = 594683937
usecs = 521905
i = 1
av = 0x7f6a6d400400
maxac = <optimized out>
#15 BackendStartup (port=0x7f6a6d3f9330) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/postmaster/postmaster.c:4033
bn = <optimized out>
pid = <optimized out>
#16 ServerLoop () at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/postmaster/postmaster.c:1706
rmask = {fds_bits = {8, 0 <repeats 15 times>}}
selres = <optimized out>
now = <optimized out>
readmask = {fds_bits = {200, 0 <repeats 15 times>}}
last_lockfile_recheck_time = 1541368681
last_touch_time = 1541366480
__func__ = "ServerLoop"
#17 0x00007f6a6b8e7644 in PostmasterMain (argc=5, argv=<optimized out>)
at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/postmaster/postmaster.c:1379
opt = <optimized out>
status = <optimized out>
userDoption = <optimized out>
listen_addr_saved = true
i = <optimized out>
output_config_variable = <optimized out>
__func__ = "PostmasterMain"
#18 0x00007f6a6b679616 in main (argc=5, argv=0x7f6a6d3af1f0) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/main/main.c:228
No locals.

Dne 3.11.2018 v 20:38 Tom Lane napsal(a):

Show quoted text

<obouda@email.cz> writes:

we reached the exactly same problem after upgrading to PostgreSQL 11 - the
server crashed on a DELETE statement with a trigger. We also observed an
AFTER DELETE trigger receiving NULL values in OLD. Now I see the problem
seems to be solved (theoretically). Unfortunately, we are not able to build
the server with the patch, so we cannot confirm that. However, when we just
copied the database (within the same server), the same DELETE executed on
the copy with no problems.

Yeah, dump/reload would make the problem go away.

I would like to ask, however: could the same problem arise from an UPDATE
statement (also on a table with an AFTER trigger), or would that be another
bug (technical details below)?

Same bug.

regards, tom lane

On 2018-Nov-06, Ondřej Bouda wrote:

So we dumped and restored all our databases. After that, the crash on DELETE
never occurred (before, it was several times a day). However, the crash on
UPDATE still occurs on specific rows. We are quite certain no ALTER TABLE
statement was executed on the table after the restore.
There are two AFTER INSERT OR UPDATE constraint triggers and one BEFORE
INSERT OR UPDATE trigger on the table, all of which are implemented in
plpgsql. Multiple physical servers, on separate databases with identical
schema, crash on the same type of UPDATE query (different just in concrete
values to be updated). The same code worked perfectly on 10.x.

See the attached backtrace below. Can we do something else to catch the bug?
Or can we hope for this bug to be already fixed and released in the upcoming
version?

Hmm, this one smells like c203d6cf81b4 -- haven't seen any fixes for
that one. Can you share more details on this? I think the failing
update is on table with oid=557732818, but I might be wrong.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

Hmm, this one smells like c203d6cf81b4 -- haven't seen any fixes for
that one. Can you share more details on this? I think the failing
update is on table with oid=557732818, but I might be wrong.

That's exactly the table, public.schedulecard.
We issue an UPDATE changing some of its columns. E.g.,

UPDATE public.schedulecard SET ext_ident=null,
rotates=false,period_num=1,period_day=2 WHERE id=3817

lets the server crash. See the main log:

2018-11-06 17:29:40.031 CET [30202] LOG: server process (PID 29879) was
terminated by signal 11: Segmentation fault
2018-11-06 17:29:40.031 CET [30202] DETAIL: Failed process was running:
UPDATE public.schedulecard SET ext_ident=null,
rotates=false,period_num=1,period_day=2 WHERE id=3817;
select * from schedulecard where id = 3817
2018-11-06 17:29:40.031 CET [30202] LOG: terminating any other active
server processes

The query is reproducible - it always lets the server segfault. It
crashes on multiple rows on that table -- actually, I haven't found any
non-failing row yet.

I thought triggers should be suspected. However, even when all the three
triggers have been disabled (ALTER TABLE DISABLE TRIGGER), the UPDATE
crashed the server.

What else could I try?

Regards,
Ondřej Bouda

Dne 6.11.2018 v 19:52 Alvaro Herrera napsal(a):

Show quoted text

On 2018-Nov-06, Ondřej Bouda wrote:

So we dumped and restored all our databases. After that, the crash on DELETE
never occurred (before, it was several times a day). However, the crash on
UPDATE still occurs on specific rows. We are quite certain no ALTER TABLE
statement was executed on the table after the restore.
There are two AFTER INSERT OR UPDATE constraint triggers and one BEFORE
INSERT OR UPDATE trigger on the table, all of which are implemented in
plpgsql. Multiple physical servers, on separate databases with identical
schema, crash on the same type of UPDATE query (different just in concrete
values to be updated). The same code worked perfectly on 10.x.

See the attached backtrace below. Can we do something else to catch the bug?
Or can we hope for this bug to be already fixed and released in the upcoming
version?

Hmm, this one smells like c203d6cf81b4 -- haven't seen any fixes for
that one. Can you share more details on this? I think the failing
update is on table with oid=557732818, but I might be wrong.

On 2018-Nov-06, Ondřej Bouda wrote:

Hmm, this one smells like c203d6cf81b4 -- haven't seen any fixes for
that one. Can you share more details on this? I think the failing
update is on table with oid=557732818, but I might be wrong.

That's exactly the table, public.schedulecard.
We issue an UPDATE changing some of its columns. E.g.,

UPDATE public.schedulecard SET ext_ident=null,
rotates=false,period_num=1,period_day=2 WHERE id=3817

What indexes are there in this table? Indexes on expressions are
particularly suspect.

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

=?UTF-8?Q?Ond=c5=99ej_Bouda?= <obouda@email.cz> writes:

I thought triggers should be suspected. However, even when all the three
triggers have been disabled (ALTER TABLE DISABLE TRIGGER), the UPDATE
crashed the server.

Foreign-key triggers too?

What else could I try?

A stack trace would be really helpful.

https://wiki.postgresql.org/wiki/Generating_a_stack_trace_of_a_PostgreSQL_backend

regards, tom lane

Dne 6.11.2018 v 20:45 Alvaro Herrera napsal(a):

What indexes are there in this table? Indexes on expressions are
particularly suspect.

There are some simple btree indexes due to foreign keys, and this one,
which seems as the cause for the crashes:

CREATE INDEX schedulecard_overlap_idx
ON public.schedulecard USING gist
(scheduletemplate_id, (period_day::integer % 7), timerange)
TABLESPACE pg_default;

When I drop it, the UPDATE statement gets executed without any problems.
When I create the index again, the same UPDATE crashes the server.

Regards,
Ondřej Bouda

Alvaro Herrera <alvherre@2ndquadrant.com> writes:

What indexes are there in this table? Indexes on expressions are
particularly suspect.

I had not looked at the "projection index" code before, and now that
I have, I am desperately unhappy with it. It's seriously buggy,
badly underdocumented, unbelievably inefficient, and it looks like
it creates a whole new set of reasons for unwanted recursion inside
the relcache. The relcache has got NO business calling cost_qual_eval,
for example.

Having said that, I'm not real sure how it ends up with this crash.
It looks like ProjIndexIsUnchanged is getting the wrong value for
the index column's typbyval, but how could that be?

regards, tom lane

=?UTF-8?Q?Ond=c5=99ej_Bouda?= <obouda@email.cz> writes:

There are some simple btree indexes due to foreign keys, and this one,
which seems as the cause for the crashes:

CREATE INDEX schedulecard_overlap_idx
ON public.schedulecard USING gist
(scheduletemplate_id, (period_day::integer % 7), timerange)
TABLESPACE pg_default;

When I drop it, the UPDATE statement gets executed without any problems.
When I create the index again, the same UPDATE crashes the server.

Hm, what are the data types of those columns?

And I assume you've got btree_gist installed?

regards, tom lane

Foreign-key triggers too?

There are four trivial foreign keys from public.schedulecard like this:

ALTER TABLE public.schedulecard
ADD CONSTRAINT fk_schedulecard_schedulecard FOREIGN KEY
(schedulecard_id)
REFERENCES public.schedulecard (id) MATCH SIMPLE
ON UPDATE NO ACTION
ON DELETE RESTRICT;

(two of them having ON UPDATE CASCADE, but all other characteristics are
the same)

Also, several tables have foreign keys to public.schedulecard.id, some
of which are ON UPDATE CASCADE, others NO ACTION.

A stack trace would be really helpful.

Already sent earlier, maybe it got lost. Resending it:

[New LWP 8307]
[Thread debugging using libthread_db enabled]
Using host libthread_db library "/lib/x86_64-linux-gnu/libthread_db.so.1".
Core was generated by `postgres: 11/main: usr mydb 127.0.0.1(56829)
UPDATE '.
Program terminated with signal SIGSEGV, Segmentation fault.
#0 __memcmp_sse4_1 () at ../sysdeps/x86_64/multiarch/memcmp-sse4.S:793
#0 __memcmp_sse4_1 () at ../sysdeps/x86_64/multiarch/memcmp-sse4.S:793
No locals.
#1 0x00007f6a6b993296 in datumIsEqual (value1=12, value2=12,
typByVal=<optimized out>, typLen=16) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/utils/adt/datum.c:249
size1 = 16
size2 = <optimized out>
s1 = 0xc <error: Cannot access memory at address 0xc>
s2 = 0xc <error: Cannot access memory at address 0xc>
res = <optimized out>
#2 0x00007f6a6b6b89de in ProjIndexIsUnchanged (newtup=0x7f6a6d5ccdd0,
oldtup=0x7ffc9c1c1270, relation=0x7f6a6b4c3680) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/access/heap/heapam.c:4539
att = <optimized out>
indexOid = <optimized out>
indexDesc = 0x7f6a6b4c7cf0
indexInfo = 0x7f6a6d5cdbf8
i = 0
indexoidlist = <optimized out>
econtext = 0x7f6a6d4c2c10
new_isnull = {false, false, false, 156, 252, 127, false, false,
44, 29, 128, 107, 106, 127, false, false, 203, 89, 76, 107, 106, 127,
false, false, 123, false, false, false, false, false, false, false}
slot = 0x7f6a6d5cf8b8
equals = true
old_isnull = {false, false, false, 109, 106, 127, false, false,
69, 46, 80, 109, 106, 127, false, false, 64, 46, 80, 109, 106, 127,
false, false, 72, 46, 80, 109, 106, 127, false, false}
new_values = {12, 2, 140095078059546, 16, 140722927572064,
140095077232192, 140095077233456, 140722927572079, 0, 0,
140722927572048, 140095054460096, 140095077050704, 140095078061816, 5,
4, 140722927572096, 140095046814123, 140722927572096, 140095046825162,
140095043418528, 140095077051552, 140722927572224, 140095046865098,
60129542543, 23274993280, 140095043418528, 140095077050704,
140095043417728, 140095077050976, 140095078056288, 140095043422512}
indexno = 2
l = 0x7f6a6d503840
estate = 0x7f6a6d4c2a00
old_values = {12, 1, 140078631611970, 1, 140095077050704, 1024,
140095077232192, 0, 140722927571760, 140095049517261, 140722927571808,
140722927571776, 140722927571792, 140095046814329, 18, 140095077232200,
140095076976896, 140095078056288, 22, 100, 140722927571856,
140722927571920, 140095046831813, 140722927571856, 140095046842624, 0,
140095077226392, 3, 140095077232192, 140095077232528, 140095077232192,
140095078061816}
#3 heap_update (relation=relation@entry=0x7f6a6b4c3680,
otid=otid@entry=0x7ffc9c1c1690, newtup=newtup@entry=0x7f6a6d5ccdd0,
cid=0, crosscheck=<optimized out>, wait=wait@entry=true,
hufd=hufd@entry=0x7ffc9c1c15b0, lockmode=lockmode@entry=0x7ffc9c1c15a4)
at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/access/heap/heapam.c:4230
result = <optimized out>
xid = 245762845
hot_attrs = 0x7f6a6d503740
proj_idx_attrs = 0x7f6a6d503758
key_attrs = 0x7f6a6d503770
id_attrs = 0x7f6a6d503788
interesting_attrs = 0x7f6a6d5037a0
lp = <optimized out>
oldtup = {t_len = 85, t_self = {ip_blkid = {bi_hi = 0, bi_lo =
45}, ip_posid = 10}, t_tableOid = 557732818, t_data = 0x7f669913ee10}
heaptup = 0x7f6a6d5ccdd0
old_key_tuple = 0x0
old_key_copied = false
page = 0x7f669913d180 "]="
block = <optimized out>
mxact_status = <optimized out>
buffer = <optimized out>
newbuf = 14683
vmbuffer = 0
vmbuffer_new = 0
need_toast = <optimized out>
newtupsize = <optimized out>
pagefree = <optimized out>
have_tuple_lock = false
iscombo = false
use_hot_update = false
hot_attrs_checked = <optimized out>
key_intact = <optimized out>
all_visible_cleared = false
all_visible_cleared_new = false
checked_lockers = <optimized out>
locker_remains = <optimized out>
xmax_new_tuple = <optimized out>
xmax_old_tuple = 245762845
infomask_old_tuple = 0
infomask2_old_tuple = 8192
infomask_new_tuple = 144
infomask2_new_tuple = 0
__func__ = "heap_update"
#4 0x00007f6a6b82f2c9 in ExecUpdate
(mtstate=mtstate@entry=0x7f6a6d4d81c0, tupleid=0x7ffc9c1c1690,
oldtuple=0x0, slot=0x7f6a6d5cc160,
planSlot=planSlot@entry=0x7f6a6d567578,
epqstate=epqstate@entry=0x7f6a6d4d8280,
estate=estate@entry=0x7f6a6d4d6a60, canSetTag=true) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/executor/nodeModifyTable.c:1209
lockmode = LockTupleNoKeyExclusive
partition_constraint_failed = false
tuple = 0x7f6a6d5ccdd0
resultRelInfo = 0x7f6a6d4d6ca0
resultRelationDesc = 0x7f6a6b4c3680
result = <optimized out>
hufd = {ctid = {ip_blkid = {bi_hi = 5616, bi_lo = 39964},
ip_posid = 32764}, xmax = 1803594893, cmax = 32618}
recheckIndexes = 0x0
saved_tcs_map = 0x0
__func__ = "ExecUpdate"
#5 0x00007f6a6b82f978 in ExecModifyTable (pstate=0x7f6a6d4d81c0) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/executor/nodeModifyTable.c:2166
node = 0x7f6a6d4d81c0
proute = 0x0
estate = 0x7f6a6d4d6a60
operation = CMD_UPDATE
saved_resultRelInfo = 0x0
resultRelInfo = 0x7f6a6d4d6ca0
subplanstate = 0x7f6a6d566988
junkfilter = 0x7f6a6d5cc038
slot = <optimized out>
tupleid = <optimized out>
tuple_ctid = {ip_blkid = {bi_hi = 0, bi_lo = 45}, ip_posid = 10}
oldtupdata = {t_len = 2619086544, t_self = {ip_blkid = {bi_hi =
32764, bi_lo = 0}, ip_posid = 9204}, t_tableOid = 32618, t_data =
0x7ffc9c1c16d0}
oldtuple = <optimized out>
__func__ = "ExecModifyTable"
#6 0x00007f6a6b80b25b in ExecProcNode (node=0x7f6a6d4d81c0) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/include/executor/executor.h:237
No locals.
#7 ExecutePlan (execute_once=<optimized out>, dest=0x7f6a6d5e9448,
direction=<optimized out>, numberTuples=0, sendTuples=false,
operation=CMD_UPDATE, use_parallel_mode=<optimized out>,
planstate=0x7f6a6d4d81c0, estate=0x7f6a6d4d6a60) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/executor/execMain.c:1723
slot = <optimized out>
current_tuple_count = 0
#8 standard_ExecutorRun (queryDesc=0x7f6a6d566040, direction=<optimized
out>, count=0, execute_once=<optimized out>) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/executor/execMain.c:364
estate = 0x7f6a6d4d6a60
operation = CMD_UPDATE
dest = 0x7f6a6d5e9448
sendTuples = <optimized out>
__func__ = "standard_ExecutorRun"
#9 0x00007f6a6b95b41d in ProcessQuery (plan=<optimized out>,
sourceText=0x7f6a6d3b45a0 "UPDATE public.schedulecard SET
ext_ident=NULL, finalreport_absence_cnt=NULL, period_day=2,
period_num=1, rotates=false, rotationgroup_id=NULL,
schedulecard_id=NULL, schedulecardblock_id=NULL, schedule"...,
params=0x0, queryEnv=0x0, dest=0x7f6a6d5e9448,
completionTag=0x7ffc9c1c19d0 "") at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/tcop/pquery.c:161
queryDesc = 0x7f6a6d566040
#10 0x00007f6a6b95b668 in PortalRunMulti
(portal=portal@entry=0x7f6a6d479d00, isTopLevel=isTopLevel@entry=true,
setHoldSnapshot=setHoldSnapshot@entry=false,
dest=dest@entry=0x7f6a6d5e9448, altdest=altdest@entry=0x7f6a6d5e9448,
completionTag=completionTag@entry=0x7ffc9c1c19d0 "") at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/tcop/pquery.c:1286
pstmt = 0x7f6a6d5e92e8
active_snapshot_set = true
stmtlist_item = 0x7f6a6d5e93f8
#11 0x00007f6a6b95c2d5 in PortalRun (portal=portal@entry=0x7f6a6d479d00,
count=count@entry=9223372036854775807, isTopLevel=isTopLevel@entry=true,
run_once=run_once@entry=true, dest=dest@entry=0x7f6a6d5e9448,
altdest=altdest@entry=0x7f6a6d5e9448,
completionTag=completionTag@entry=0x7ffc9c1c19d0 "") at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/tcop/pquery.c:799
save_exception_stack = 0x7ffc9c1c1c00
save_context_stack = 0x0
local_sigjmp_buf = {{__jmpbuf = {140722927574016,
7153736072036697610, 140095075869728, 140722927573456, 140095076670720,
140095075869776, 7153736071992657418, 7091693995611473418},
__mask_was_saved = 0, __saved_mask = {__val = {0, 140723125634669, 0,
140095051070678, 64, 112, 140095075869728, 140095075861648,
140095078175768, 140722927573328, 140095049517496, 140722927573360, 2,
140095075869728, 2, 140722927573360}}}}
result = <optimized out>
nprocessed = <optimized out>
saveTopTransactionResourceOwner = 0x7f6a6d41b148
saveTopTransactionContext = 0x7f6a6d48d8d0
saveActivePortal = 0x0
saveResourceOwner = 0x7f6a6d41b148
savePortalContext = 0x0
saveMemoryContext = 0x7f6a6d48d8d0
__func__ = "PortalRun"
#12 0x00007f6a6b957f7f in exec_simple_query (query_string=0x7f6a6d3b45a0
"UPDATE public.schedulecard SET ext_ident=NULL,
finalreport_absence_cnt=NULL, period_day=2, period_num=1, rotates=false,
rotationgroup_id=NULL, schedulecard_id=NULL, schedulecardblock_id=NULL,
schedule"...) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/tcop/postgres.c:1122
parsetree = 0x7f6a6d3b6420
portal = 0x7f6a6d479d00
snapshot_set = <optimized out>
commandTag = <optimized out>
completionTag =
"\000\032\034\234\374\177\000\000\341\356\247kj\177\000\000\000\000\000\000\000\000\000\000\060\316\367kj\177\000\000\340&\360kj\177\000\000\234U\357kj\177\000\000\240E;mj\177\000\000Q\000\000\000\000\000\000"
querytree_list = <optimized out>
plantree_list = <optimized out>
receiver = 0x7f6a6d5e9448
format = 0
dest = DestRemote
parsetree_list = 0x7f6a6d3b6470
parsetree_item = 0x7f6a6d3b6450
save_log_statement_stats = false
was_logged = false
use_implicit_block = false
msec_str =
"\000\032\034\234\374\177\000\000\341\356\247kj\177\000\000\000\000\000\000\000\000\000\000\060\316\367kj\177\000"
__func__ = "exec_simple_query"
#13 0x00007f6a6b95930a in PostgresMain (argc=<optimized out>,
argv=argv@entry=0x7f6a6d400400, dbname=0x7f6a6d4003e0 "mydb",
username=<optimized out>) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/tcop/postgres.c:4159
query_string = 0x7f6a6d3b45a0 "UPDATE public.schedulecard SET
ext_ident=NULL, finalreport_absence_cnt=NULL, period_day=2,
period_num=1, rotates=false, rotationgroup_id=NULL,
schedulecard_id=NULL, schedulecardblock_id=NULL, schedule"...
firstchar = 81
input_message = {data = 0x7f6a6d3b45a0 "UPDATE
public.schedulecard SET ext_ident=NULL, finalreport_absence_cnt=NULL,
period_day=2, period_num=1, rotates=false, rotationgroup_id=NULL,
schedulecard_id=NULL, schedulecardblock_id=NULL, schedule"..., len =
328, maxlen = 1024, cursor = 328}
local_sigjmp_buf = {{__jmpbuf = {140095054460088,
7153736071902479882, 140095054460096, 1, 0, 140095076143920,
7153736071933937162, 7091693993451144714}, __mask_was_saved = 1,
__saved_mask = {__val = {0, 140095076143920, 140095004765576,
844424930131970, 206158430256, 140722927574360, 140722927574144, 293,
140095076143920, 0, 0, 140095075861648, 1024, 140722927574468, 0,
140722927574240}}}}
send_ready_for_query = false
disable_idle_in_transaction_timeout = false
__func__ = "PostgresMain"
#14 0x00007f6a6b67867d in BackendRun (port=0x7f6a6d3f9330) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/postmaster/postmaster.c:4361
ac = 1
secs = 594683937
usecs = 521905
i = 1
av = 0x7f6a6d400400
maxac = <optimized out>
#15 BackendStartup (port=0x7f6a6d3f9330) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/postmaster/postmaster.c:4033
bn = <optimized out>
pid = <optimized out>
#16 ServerLoop () at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/postmaster/postmaster.c:1706
rmask = {fds_bits = {8, 0 <repeats 15 times>}}
selres = <optimized out>
now = <optimized out>
readmask = {fds_bits = {200, 0 <repeats 15 times>}}
last_lockfile_recheck_time = 1541368681
last_touch_time = 1541366480
__func__ = "ServerLoop"
#17 0x00007f6a6b8e7644 in PostmasterMain (argc=5, argv=<optimized out>)
at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/postmaster/postmaster.c:1379
opt = <optimized out>
status = <optimized out>
userDoption = <optimized out>
listen_addr_saved = true
i = <optimized out>
output_config_variable = <optimized out>
__func__ = "PostmasterMain"
#18 0x00007f6a6b679616 in main (argc=5, argv=0x7f6a6d3af1f0) at
/build/postgresql-11-oDwOcQ/postgresql-11-11.0/build/../src/backend/main/main.c:228
No locals.

--

Regards,
Ond�ej Bouda

Hm, what are the data types of those columns?

scheduletemplate_id bigint NOT NULL,
period_day smallint NOT NULL,
timerange timerange NOT NULL,

where timerange is defined as follows:
CREATE TYPE public.timerange AS RANGE
(
SUBTYPE=time,
SUBTYPE_OPCLASS = time_ops
);

And I assume you've got btree_gist installed?

Yes, version 1.5.

Regards,
Ond�ej Bouda

=?UTF-8?Q?Ond=c5=99ej_Bouda?= <obouda@email.cz> writes:

Hm, what are the data types of those columns?

scheduletemplate_id bigint NOT NULL,
period_day smallint NOT NULL,
timerange timerange NOT NULL,

OK, so here's a minimal reproducer:

drop table schedulecard;

create table schedulecard (
scheduletemplate_id bigint NOT NULL,
period_day smallint NOT NULL
);

CREATE INDEX schedulecard_overlap_idx
ON schedulecard USING gist
(scheduletemplate_id, (period_day::integer % 7));

insert into schedulecard values(12, 1);

update schedulecard set period_day = period_day + 7;

Interestingly, it doesn't crash if I change the index type to btree,
which I was not expecting because the crashing code seems pretty
independent of the index type.

Haven't traced further than that yet.

regards, tom lane

I wrote:

Interestingly, it doesn't crash if I change the index type to btree,
which I was not expecting because the crashing code seems pretty
independent of the index type.

Oh ... duh. The problem here is that ProjIndexIsUnchanged thinks that
the type of the index column is identical to the type of the source
datum for it, which is not true for any opclass making use of the
opckeytype property.

Ondřej, as a short-term workaround you could prevent the crash
by setting that index's recheck_on_update property to false.

regards, tom lane

Ondřej, as a short-term workaround you could prevent the crash
by setting that index's recheck_on_update property to false.

Thanks for the tip. I am unsuccessful using it, though:

# ALTER INDEX public.schedulecard_overlap_idx SET (recheck_on_update =
FALSE);

ERROR: unrecognized parameter "recheck_on_update"

Creating a new index is wrong, too:

# CREATE INDEX schedulecard_overlap_idx2
ON public.schedulecard USING gist
(scheduletemplate_id, (period_day::integer % 7), timerange)
WITH (recheck_on_update = FALSE);

ERROR: unrecognized parameter "recheck_on_update"

It only succeeds if not USING gist:

# CREATE INDEX schedulecard_overlap_idx2
ON public.schedulecard
(scheduletemplate_id, (period_day::integer % 7), timerange)
WITH (recheck_on_update = FALSE);

CREATE INDEX

Is there any other workaround for a gist index, please?
Maybe we will just drop the index until the bug gets fixed - better slow
queries than crashing servers...

Thanks,
Ondřej Bouda

=?UTF-8?Q?Ond=c5=99ej_Bouda?= <obouda@email.cz> writes:

Ondřej, as a short-term workaround you could prevent the crash
by setting that index's recheck_on_update property to false.

Thanks for the tip. I am unsuccessful using it, though:
# ALTER INDEX public.schedulecard_overlap_idx SET (recheck_on_update =
FALSE);
ERROR: unrecognized parameter "recheck_on_update"

Oh, for crying out loud. That's yet a different bug.
I'm not sure that it's the fault of the recheck_on_update
feature proper though; it might be a pre-existing bug in
the reloptions code. Looks like somebody forgot to list
RELOPT_KIND_GIST in RELOPT_KIND_INDEX, but is that the
fault of commit c203d6cf8 or was it busted before?

regards, tom lane

On 2018-Nov-06, Tom Lane wrote:

=?UTF-8?Q?Ond=c5=99ej_Bouda?= <obouda@email.cz> writes:

Ondřej, as a short-term workaround you could prevent the crash
by setting that index's recheck_on_update property to false.

Thanks for the tip. I am unsuccessful using it, though:
# ALTER INDEX public.schedulecard_overlap_idx SET (recheck_on_update =
FALSE);
ERROR: unrecognized parameter "recheck_on_update"

Oh, for crying out loud. That's yet a different bug.
I'm not sure that it's the fault of the recheck_on_update
feature proper though; it might be a pre-existing bug in
the reloptions code. Looks like somebody forgot to list
RELOPT_KIND_GIST in RELOPT_KIND_INDEX, but is that the
fault of commit c203d6cf8 or was it busted before?

RELOPT_KIND_INDEX was invented by that commit, looks like :-(

--
Álvaro Herrera https://www.2ndQuadrant.com/
PostgreSQL Development, 24x7 Support, Remote DBA, Training & Services

On 2018-11-06 16:47:20 -0500, Tom Lane wrote:

=?UTF-8?Q?Ond=c5=99ej_Bouda?= <obouda@email.cz> writes:

Ondřej, as a short-term workaround you could prevent the crash
by setting that index's recheck_on_update property to false.

Thanks for the tip. I am unsuccessful using it, though:
# ALTER INDEX public.schedulecard_overlap_idx SET (recheck_on_update =
FALSE);
ERROR: unrecognized parameter "recheck_on_update"

Oh, for crying out loud. That's yet a different bug.
I'm not sure that it's the fault of the recheck_on_update
feature proper though; it might be a pre-existing bug in
the reloptions code. Looks like somebody forgot to list
RELOPT_KIND_GIST in RELOPT_KIND_INDEX, but is that the
fault of commit c203d6cf8 or was it busted before?

Looks new:
+ RELOPT_KIND_INDEX = RELOPT_KIND_BTREE|RELOPT_KIND_HASH|RELOPT_KIND_GIN|RELOPT_KIND_SPGIST,

there aren't any other "for all indexes" type options, so the whole
category didn't exist before.

It also strikes me as a really bad idea, even if RELOPT_KIND_GIST
wouldn't have been omitted: It breaks index am extensibility.

Greetings,

Andres Freund

On 11/6/18 10:54 PM, Andres Freund wrote:

On 2018-11-06 16:47:20 -0500, Tom Lane wrote:

=?UTF-8?Q?Ond=c5=99ej_Bouda?= <obouda@email.cz> writes:

Ondřej, as a short-term workaround you could prevent the crash
by setting that index's recheck_on_update property to false.

Thanks for the tip. I am unsuccessful using it, though:
# ALTER INDEX public.schedulecard_overlap_idx SET (recheck_on_update =
FALSE);
ERROR: unrecognized parameter "recheck_on_update"

Oh, for crying out loud. That's yet a different bug.
I'm not sure that it's the fault of the recheck_on_update
feature proper though; it might be a pre-existing bug in
the reloptions code. Looks like somebody forgot to list
RELOPT_KIND_GIST in RELOPT_KIND_INDEX, but is that the
fault of commit c203d6cf8 or was it busted before?

Looks new:
+ RELOPT_KIND_INDEX = RELOPT_KIND_BTREE|RELOPT_KIND_HASH|RELOPT_KIND_GIN|RELOPT_KIND_SPGIST,

there aren't any other "for all indexes" type options, so the whole
category didn't exist before.

It also strikes me as a really bad idea, even if RELOPT_KIND_GIST
wouldn't have been omitted: It breaks index am extensibility.

Does it? The RELOPT_KIND_* stuff is hard-coded in reloptions.h anyway,
so I'm not sure how this particular thing makes it less extensible?

That being said, we also have RELOPT_KIND_BRIN, and that seems to be
missing from RELOPT_KIND_INDEX too (and AFAICS the optimization works
for all index types).

regards

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

Andres Freund <andres@anarazel.de> writes:

On 2018-11-06 16:47:20 -0500, Tom Lane wrote:

Looks like somebody forgot to list
RELOPT_KIND_GIST in RELOPT_KIND_INDEX, but is that the
fault of commit c203d6cf8 or was it busted before?

Looks new:
+ RELOPT_KIND_INDEX = RELOPT_KIND_BTREE|RELOPT_KIND_HASH|RELOPT_KIND_GIN|RELOPT_KIND_SPGIST,
there aren't any other "for all indexes" type options, so the whole
category didn't exist before.

It also strikes me as a really bad idea, even if RELOPT_KIND_GIST
wouldn't have been omitted: It breaks index am extensibility.

Hm, well, that enum already knows about all index types, doesn't it?

regards, tom lane

Hi,

On 2018-11-06 23:11:29 +0100, Tomas Vondra wrote:

On 11/6/18 10:54 PM, Andres Freund wrote:

Looks new:
+ RELOPT_KIND_INDEX = RELOPT_KIND_BTREE|RELOPT_KIND_HASH|RELOPT_KIND_GIN|RELOPT_KIND_SPGIST,

there aren't any other "for all indexes" type options, so the whole
category didn't exist before.

It also strikes me as a really bad idea, even if RELOPT_KIND_GIST
wouldn't have been omitted: It breaks index am extensibility.

Does it? The RELOPT_KIND_* stuff is hard-coded in reloptions.h anyway,
so I'm not sure how this particular thing makes it less extensible?

Well, you can create new index AMs in extensions these days, but given
the relopt design above, the feature cannot be disabled for them. Yes,
there's *currently* probably no great way to have reloptions across all
potential index types, but that's not an excuse for adding something
broken.

Greetings,

Andres Freund

Hi,

On 2018-11-06 17:11:40 -0500, Tom Lane wrote:

Andres Freund <andres@anarazel.de> writes:

On 2018-11-06 16:47:20 -0500, Tom Lane wrote:

Looks like somebody forgot to list
RELOPT_KIND_GIST in RELOPT_KIND_INDEX, but is that the
fault of commit c203d6cf8 or was it busted before?

Looks new:
+ RELOPT_KIND_INDEX = RELOPT_KIND_BTREE|RELOPT_KIND_HASH|RELOPT_KIND_GIN|RELOPT_KIND_SPGIST,
there aren't any other "for all indexes" type options, so the whole
category didn't exist before.

It also strikes me as a really bad idea, even if RELOPT_KIND_GIST
wouldn't have been omitted: It breaks index am extensibility.

Hm, well, that enum already knows about all index types, doesn't it?

Not quite sure what you mean.

Before c203d6cf8 there weren't reloptions across index types. But after
it, if one adds a new index AM via an extension, one can't set
recheck_on_update = false for indexes using it, even though the feature
affects such indexes. We support adding indexes AMs at runtime these
days, including WAL logging (even though that's a bit
voluminous). There's even a contrib index AM...

The list of AMs is supposed to be extensible at runtime, cf
add_reloption_kind().

Greetings,

Andres Freund

For the record:

Regarding backend crash when DELETEing tuples older than
a recent ALTER TABLE ADD COLUMN:

On Fri, Nov 02, 2018 at 11:56:58PM -0400, Tom Lane wrote:

I was feeling baffled about this, but it suddenly occurs to me that maybe
the bug fixed in 040a1df61/372102b81 explains this.

So, I guess I can work around the issue by the above
manoeuvre and report back once 040a1df61/372102b81 is
released.

I will, at any rate, report back when the existing fix is released.

Now that PG11.1 is released on Debian

postgresql-11:
Installiert: 11.1-1
Installationskandidat: 11.1-1
Versionstabelle:
*** 11.1-1 500
500 http://httpredir.debian.org/debian unstable/main i386 Packages
100 /var/lib/dpkg/status
11.0-1+b1 990
990 http://httpredir.debian.org/debian buster/main i386 Packages

I can report that my issued is fixed by that version.

Thanks to all,
Karsten
--
GPG 40BE 5B0E C98E 1713 AFA6 5BC0 3BEA AC80 7D4F C89B