Re: PG10.1 autovac crashed building extended stats - Mailing list pgsql-hackers

On Fri, Nov 17, 2017 at 01:27:49PM -0300, Alvaro Herrera wrote:
> Justin Pryzby wrote:
> > After adding extended/MV stats to a few of our tables a few days ago, it looks
> > like I wasn't been paying attention and this first crashed 2 nights ago.  Why
> > at 1am?  not sure.  I have an "reindex" job which runs at 1am, and an
> > vacuum/analyze job which runs at 2am, but I don't use cron to change
> > autovac/analyze thresholds..
> 
> Can you please show the definition of the table and of the extended
> stats?

gtt=# SELECT stxrelid::regclass, stxname, stxkind FROM pg_statistic_ext ORDER BY 1;                   stxrelid
          |                          stxname                          | stxkind 
 

-------------------------------------------------+-----------------------------------------------------------+---------daily_umts_eric_cell_traffic_hs_view_201603
   | daily_umts_eric_cell_traffic_hs_view_201603_key_stats     | {d,f}daily_umts_eric_cell_traffic_hs_eul_view_201603 |
daily_umts_eric_cell_traffic_hs_eul_view_201603_key_stats| {d,f}daily_eric_umts_rnc_utrancell_view_201603       |
daily_eric_umts_rnc_utrancell_view_201603_key_stats      | {d,f}daily_umts_eric_cell_traffic_hs_view_201504     |
daily_umts_eric_cell_traffic_hs_view_201504_key_stats    | {d,f}daily_umts_eric_cell_traffic_hs_eul_view_201504 |
daily_umts_eric_cell_traffic_hs_eul_view_201504_key_stats| {d,f}daily_eric_umts_rnc_utrancell_view_201504       |
daily_eric_umts_rnc_utrancell_view_201504_key_stats      | {d,f}daily_enodeb_ncell_view_201603                  |
daily_enodeb_ncell_view_201603_key_stats                 | {d,f}daily_enodeb_ncell_view_201503                  |
daily_enodeb_ncell_view_201503_key_stats                 | {d,f}daily_enodeb_ncell_view_201502                  |
daily_enodeb_ncell_view_201502_key_stats                 | {d,f}daily_enodeb_ncell_view_201501                  |
daily_enodeb_ncell_view_201501_key_stats                 | {d,f}daily_enodeb_baseband_view_201603               |
daily_enodeb_baseband_view_201603_key_stats              | {d,f}daily_enodeb_baseband_view_201503               |
daily_enodeb_baseband_view_201503_key_stats              | {d,f}daily_enodeb_baseband_view_201502               |
daily_enodeb_baseband_view_201502_key_stats              | {d,f}daily_enodeb_baseband_view_201501               |
daily_enodeb_baseband_view_201501_key_stats              | {d,f}daily_enodeb_cell_view_201603                   |
daily_enodeb_cell_view_201603_key_stats                  | {d,f}daily_enodeb_cell_view_201502                   |
daily_enodeb_cell_view_201502_key_stats                  | {d,f}daily_enodeb_201603                             |
daily_enodeb_201603_key_stats                            | {d,f}daily_enodeb_201503                             |
daily_enodeb_201503_key_stats                            | {d,f}daily_enodeb_201502                             |
daily_enodeb_201502_key_stats                            | {d,f}daily_enodeb_201501                             |
daily_enodeb_201501_key_stats                            | {d,f}daily_enodeb_cell_view_201710                   | x
                                                   | {d,f}daily_cdr_pstn_user_201711                      |
daily_cdr_pstn_user_201711_key_stats                     | {d,f}daily_umts_eric_cell_traffic_hs_eul_view_201711 |
daily_umts_eric_cell_traffic_hs_eul_view_201711_key_stats| {d,f}daily_umts_eric_cell_traffic_hs_view_201711     |
daily_umts_eric_cell_traffic_hs_view_201711_key_stats    | {d,f}daily_eric_umts_rnc_utrancell_view_201711       |
daily_eric_umts_rnc_utrancell_view_201711_key_stats      | {d,f}daily_enodeb_baseband_view_201711               |
daily_enodeb_baseband_view_201711_key_stats              | {d,f}daily_enodeb_cell_view_201711                   |
daily_enodeb_cell_view_201711_key_stats                  | {d,f}daily_enodeb_ncell_view_201711                  |
daily_enodeb_ncell_view_201711_key_stats                 | {d,f}daily_enodeb_201711                             |
daily_enodeb_201711_key_stats                            | {d,f}
 
(29 rows)

Here's the table which was 1) reindexed (including its toast) and 2)
autovacuumed(crashed):

gtt=# \d+ daily_enodeb_baseband_view_201711                                             Table
"public.daily_enodeb_baseband_view_201711"              Column                |            Type             | Collation
|Nullable | Default | Storage  | Stats target | Description 
 

--------------------------------------+-----------------------------+-----------+----------+---------+----------+--------------+-------------device_id
                          | smallint                    |           | not null |         | plain    | 400          |
site_id                             | smallint                    |           | not null |         | plain    | 400
    | start_time                           | timestamp without time zone |           | not null |         | plain    |
0           | bbprocessingresource                 | text                        |           | not null |         |
extended| 400          | interval_seconds                     | bigint                      |           |          |
    | plain    |              | interval_seconds_min                 | smallint                    |           |
 |         | plain    |              | interval_seconds_max                 | smallint                    |           |
        |         | plain    |              | nbevt                                | bigint                      |
    |          |         | plain    |              | nbevt_min                            | integer
|          |          |         | plain    |              | nbevt_max                            | integer
      |           |          |         | plain    |              | 
 
[...]

Indexes:   "daily_enodeb_baseband_view_201711_unique_idx" UNIQUE, btree (start_time, site_id, device_id,
bbprocessingresource)
Check constraints:   "daily_enodeb_baseband_view_201711_start_time_check" CHECK (start_time >= '2017-11-01
00:00:00'::timestampwithout time zone AND start_time < '2017-12-01 00:00:00'::timestamp without time zone)
 
Statistics objects:   "public"."daily_enodeb_baseband_view_201711_key_stats" (ndistinct, dependencies) ON device_id,
site_id,start_time, bbprocessingresource FROM daily_enodeb_baseband_view_201711
 
Inherits: daily_enodeb_baseband_view
Options: autovacuum_analyze_scale_factor=0.005, autovacuum_analyze_threshold=2

gtt=# SELECT * FROM pg_statistic_ext WHERE stxname='daily_enodeb_baseband_view_201711_key_stats';
stxrelid        | 691157026
stxname         | daily_enodeb_baseband_view_201711_key_stats
stxnamespace    | 2200
stxowner        | 18819
stxkeys         | 1 2 3 4
stxkind         | {d,f}
stxndistinct    | 
stxdependencies | 

Justin