Changeset 191

Show
Ignore:
Timestamp:
10/14/10 22:07:09 (4 years ago)
Author:
depesz
Message:

add ability to turn on/off vacuums, and choose better field for updates

Files:

Legend:

Unmodified
Added
Removed
Modified
Copied
Moved
  • trunk/tools/compact_table

    r189 r191  
    7373    TUPLES_PER_PAGE=$( psql -q -A -t -X -c "select current_setting('block_size')::int4 / sum(attlen) from pg_attribute where attrelid = '$TABLE_SCHEMA.$TABLE_NAME'::regclass and attnum < 0" ) 
    7474    verbose_msg "At most, we can have %d tuples per page.\n" "$TUPLES_PER_PAGE" 
    75     COLUMN_TO_UPDATE=$( psql -q -A -t -X -c "select quote_ident(attname) from pg_attribute where attrelid = '$TABLE_SCHEMA.$TABLE_NAME'::regclass and attnum > 0 and not attisdropped order by attnum asc limit 1" ) 
     75    COLUMN_TO_UPDATE=$( psql -q -A -t -X -c "select quote_ident(attname) from pg_attribute where attrelid = '$TABLE_SCHEMA.$TABLE_NAME'::regclass and attnum > 0 and not attisdropped and attnum::text not in (select regexp_split_to_table(indkey::text, ' ') from pg_index where indrelid = '$TABLE_SCHEMA.$TABLE_NAME'::regclass) order by attnum asc limit 1" ) 
     76    if [[ -z "$COLUMN_TO_UPDATE" ]] 
     77    then 
     78        COLUMN_TO_UPDATE=$( psql -q -A -t -X -c "select quote_ident(attname) from pg_attribute where attrelid = '$TABLE_SCHEMA.$TABLE_NAME'::regclass and attnum > 0 and not attisdropped order by attnum asc limit 1" ) 
     79    fi 
    7680    if [[ -z "$COLUMN_TO_UPDATE" ]] 
    7781    then 
     
    107111    -c       : path to psql program 
    108112    -v       : show information while processing log files 
     113    -i       : turn on initial vacuum 
     114    -I       : turn off initial vacuum 
     115    -f       : turn on final vacuum 
     116    -F       : turn off final vacuum 
    109117 
    110118Defaults: 
    111     -c psql -n public -k 10 
     119    -c psql -n public -k 10 -f 
    112120 
    113121Description: 
     
    134142#             : default values are in "MAIN PROGRAM" to simplify finding them 
    135143read_arguments () { 
    136     while getopts ':U:h:p:d:c:n:t:k:xv' opt "$@" 
     144    while getopts ':U:h:p:d:c:n:t:k:xviIfF' opt "$@" 
    137145    do 
    138146        case "$opt" in 
     
    160168            k) 
    161169                export CLEAN_PAGES="$OPTARG" 
     170                ;; 
     171            i) 
     172                export INITIAL_VACUUM="1" 
     173                ;; 
     174            I) 
     175                export INITIAL_VACUUM="0" 
     176                ;; 
     177            f) 
     178                export FINAL_VACUUM="1" 
     179                ;; 
     180            F) 
     181                export FINAL_VACUUM="0" 
    162182                ;; 
    163183            x) 
     
    205225export CLEAN_PAGES=10 
    206226export PSQL='psql' 
     227export FINAL_VACUUM=1 
    207228 
    208229# Set locale to sane one, to speed up comparisons, and be sure that < and > on 
     
    217238  - CLEAN_PAGES       : $CLEAN_PAGES 
    218239  - EXTENDED_DEBUG    : $EXTENDED_DEBUG 
     240  - FINAL_VACUUM      : $FINAL_VACUUM 
     241  - INITIAL_VACUUM    : $INITIAL_VACUUM 
    219242  - PGDATABASE        : $PGDATABASE 
    220243  - PGHOST            : $PGHOST 
     
    241264verbose_msg "Entering main loop.\n" 
    242265 
    243 verbose_msg "Initial vacuuming\n" 
    244 $PSQL -q -A -t -X -c "VACUUM $TABLE_SCHEMA.$TABLE_NAME" 
     266if [[ "$INITIAL_VACUUM" -eq 1 ]] 
     267then 
     268    verbose_msg "Initial vacuuming\n" 
     269    $PSQL -q -A -t -X -c "VACUUM $TABLE_SCHEMA.$TABLE_NAME" 
     270fi 
    245271 
    246272get_relation_page_count 
     
    260286done 
    261287 
    262 verbose_msg "Final vacuuming\n" 
    263 $PSQL -q -A -t -X -c "VACUUM $TABLE_SCHEMA.$TABLE_NAME" 
     288if [[ "$FINAL_VACUUM" -eq 1 ]] 
     289then 
     290    verbose_msg "Final vacuuming\n" 
     291    $PSQL -q -A -t -X -c "VACUUM $TABLE_SCHEMA.$TABLE_NAME" 
     292fi 
    264293 
    265294get_relation_page_count