Re: pg11.1 jit segv - Mailing list pgsql-hackers

On Fri, Nov 16, 2018 at 08:38:26AM -0600, Justin Pryzby wrote:
> Are you referring to this  a future commit ?
this OR a future commit

> The table is not too special, but was probably ALTERed to add columns a good
> number of times by one of our processes.  It has ~1100 columns, including
> arrays, and some with null_frac=1.  I'm trying to come up with a test case
> involving column types and order.

I don't have a failing test case yet but here's what the columns look like:

ts=# SELECT attnum, null_frac, atttypid::regtype, attnotnull, attname, attalign , attstorage, attbyval, attlen,
attislocalFROM pg_stats s JOIN pg_attribute a USING(attname) JOIN pg_class c ON s.tablename=c.relname WHERE
c.oid=a.attrelidAND tablename='daily_eric_umts_rnc_utrancell_view_201804' AND (attnotnull OR null_frac>0.9 OR
atttypid::regtype::textLIKE '%[]' OR NOT atttypid::regtype::text~'int$|integer|double|numeric' OR attlen=-1 OR NOT
attbyvalOR atthasmissing OR attisdropped OR attnum BETWEEN 80 AND 99) ORDER BY 1;
 

 attnum | null_frac |          atttypid           | attnotnull |                     attname                     |
attalign| attstorage | attbyval | attlen | attislocal
 

--------+-----------+-----------------------------+------------+-------------------------------------------------+----------+------------+----------+--------+------------
      1 |         0 | timestamp without time zone | t          | start_time                                      | d
   | p          | t        |      8 | t
 
      2 |         0 | integer                     | t          | site_id                                         | i
   | p          | t        |      4 | t
 
      3 |         0 | integer                     | t          | sect_id                                         | i
   | p          | t        |      4 | t
 
      4 |         0 | integer                     | t          | rnc_id                                          | i
   | p          | t        |      4 | t
 
      5 |         0 | text                        | t          | utrancell                                       | i
   | x          | f        |     -1 | t
 
     30 |         1 | bigint                      | f          | dl_alt_chcode_alloc                             | d
   | p          | t        |      8 | t
 
     31 |         1 | integer                     | f          | dl_alt_chcode_alloc_min                         | i
   | p          | t        |      4 | t
 
     32 |         1 | integer                     | f          | dl_alt_chcode_alloc_max                         | i
   | p          | t        |      4 | t
 
     45 |         0 | integer[]                   | f          | dch_ul_rlc_user_tput_samples                    | i
   | x          | f        |     -1 | t
 
     46 |         0 | integer[]                   | f          | dch_ul_rlc_user_tput_samples_min                | i
   | x          | f        |     -1 | t
 
     47 |         0 | integer[]                   | f          | dch_ul_rlc_user_tput_samples_max                | i
   | x          | f        |     -1 | t
 
     51 |         0 | numeric                     | f          | ps_int_sum_latency_2                            | i
   | m          | f        |     -1 | t
 
     69 |         0 | numeric                     | f          | mbytes_ul_srb_only_eul                          | i
   | m          | f        |     -1 | t
 
[...]
     87 |         0 | numeric                     | f          | mbytes_dl_active_cs57                           | i
   | m          | f        |     -1 | t
 
     88 |         0 | numeric                     | f          | mbytes_dl_active_cs57_min                       | i
   | m          | f        |     -1 | t
 
     89 |         0 | numeric                     | f          | mbytes_dl_active_cs57_max                       | i
   | m          | f        |     -1 | t
 

If I query for cs57, it doesen't crash (in 500ms), but if I query for the next
column, cs57_min, it does (in 18000ms).

Here's a new error message instead of a crash this time:
ts=# SET jit=on;SET jit_above_cost=0;explain(analyze on,verbose off) SELECT b.mbytes_dl_active_cs57_min FROM
child.daily_eric_umts_rnc_utrancell_view_201804a JOIN child.daily_eric_umts_rnc_utrancell_view_201804 b
USING(start_time,sect_id)WHERE a.start_time BETWEEN '2018-04-30' AND '2018-05-04' AND b.start_time BETWEEN '2018-04-30'
AND'2018-05-04';
 
SET
SET
ERROR:  out of memory            
DETAIL:  Failed on request of size 425170160 in memory context "HashBatchContext".

Here's verbose output you requested, sans expressions:

ts=# SET jit=on;SET jit_above_cost=0;explain(analyze off,verbose) SELECT b.mbytes_dl_active_cs57_min FROM
child.daily_eric_umts_rnc_utrancell_view_201804a JOIN child.daily_eric_umts_rnc_utrancell_view_201804 b
USING(start_time,sect_id)WHERE a.start_time BETWEEN '2018-04-30' AND '2018-05-04' AND b.start_time BETWEEN '2018-04-30'
AND'2018-05-04';
 
SET
SET
                                                                                   QUERY PLAN
                                                       
 

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 Hash Join  (cost=2224.45..4491.19 rows=22 width=3)
   Output: b.mbytes_dl_active_cs57_min
   Hash Cond: ((a.start_time = b.start_time) AND (a.sect_id = b.sect_id))
   ->  Index Only Scan using daily_eric_umts_rnc_utrancell_view_201804_unique_idx on
child.daily_eric_umts_rnc_utrancell_view_201804a  (cost=0.29..2214.33 rows=656 width=12)
 
         Output: a.start_time, a.site_id, a.rnc_id, a.sect_id, a.utrancell
         Index Cond: ((a.start_time >= '2018-04-30 00:00:00'::timestamp without time zone) AND (a.start_time <=
'2018-05-0400:00:00'::timestamp without time zone))
 
   ->  Hash  (cost=2214.33..2214.33 rows=656 width=15)
         Output: b.mbytes_dl_active_cs57_min, b.start_time, b.sect_id
         ->  Index Scan using daily_eric_umts_rnc_utrancell_view_201804_unique_idx on
child.daily_eric_umts_rnc_utrancell_view_201804b  (cost=0.29..2214.33 rows=656 width=15)
 
               Output: b.mbytes_dl_active_cs57_min, b.start_time, b.sect_id
               Index Cond: ((b.start_time >= '2018-04-30 00:00:00'::timestamp without time zone) AND (b.start_time <=
'2018-05-0400:00:00'::timestamp without time zone))
 
 JIT:
   Functions: 19
   Options: Inlining false, Optimization false, Expressions true, Deforming true

If I query instead for a.mbytes_dl_active_cs57_min, I get no crash (yet).

Justin