search

dataegret / pgcompacttable

BSD 3-Clause "New" or "Revised" License
314 stars 48 forks source link

fix alter index retry #9

Closed Melkij closed 7 years ago

Melkij commented 7 years ago

before return from function alter_index, it perform query "END;". This last query was correct and no more errstr present

I add also set lc_messages TO 'C'; to work check correctly on any locale

Melkij commented 7 years ago 
melkij@melkij:~/tmp/pgcompacttable/bin$ ./pgcompacttable -t compact -f -u postgres --dbname melkij -v
[Wed Jun 28 15:14:07 2017] (melkij) Connecting to database
[Wed Jun 28 15:14:07 2017] (melkij) Postgress backend pid: 16035
[Wed Jun 28 15:14:07 2017] (melkij) It is recomennded to set ionice -c 3 for pgcompacttable: ionice -c 3 -p 16035
[Wed Jun 28 15:14:07 2017] (melkij) Handling tables. Attempt 1
[Wed Jun 28 15:14:07 2017] (melkij:public.compact) Start handling table public.compact
[Wed Jun 28 15:14:12 2017] (melkij:public.compact) Vacuum initial: 444 pages left, duration 5.032 seconds.
[Wed Jun 28 15:14:12 2017] (melkij:public.compact) Bloat statistics with pgstattuple: duration 0.016 seconds.
[Wed Jun 28 15:14:12 2017] (melkij:public.compact) Statistics: 444 pages (724 pages including toasts and indexes) , approximately 0.660% (2 pages) can be compacted reducing the size by 23.391KB.
[Wed Jun 28 15:14:12 2017] (melkij:public.compact) Processing forced.
[Wed Jun 28 15:14:12 2017] (melkij:public.compact) Update by column: val.
[Wed Jun 28 15:14:12 2017] (melkij:public.compact) Set pages/round: 1.
[Wed Jun 28 15:14:12 2017] (melkij:public.compact) Set pages/vacuum: 28.
[Wed Jun 28 15:14:13 2017] (melkij:public.compact) Vacuum final: cannot clean 1 pages, 444 pages left, duration 1.014 seconds.
[Wed Jun 28 15:14:13 2017] (melkij:public.compact) Analyze final: duration 0.025 second.
[Wed Jun 28 15:14:13 2017] (melkij:public.compact) Bloat statistics with pgstattuple: duration 0.013 seconds.
[Wed Jun 28 15:14:14 2017] (melkij:public.compact) Reindex forced: public.compact_pkey, lock retry 1
[Wed Jun 28 15:14:15 2017] (melkij:public.compact) Reindex forced: public.compact_pkey, lock retry 2
[Wed Jun 28 15:14:16 2017] (melkij:public.compact) Reindex forced: public.compact_pkey, lock retry 3
[Wed Jun 28 15:14:17 2017] (melkij:public.compact) Reindex forced: public.compact_pkey, lock retry 4
[Wed Jun 28 15:14:18 2017] (melkij:public.compact) Reindex forced: public.compact_pkey, lock retry 5
[Wed Jun 28 15:14:19 2017] (melkij:public.compact) Reindex forced: public.compact_pkey, lock retry 6
[Wed Jun 28 15:14:20 2017] (melkij:public.compact) Reindex forced: public.compact_pkey, initial size 276 pages(2.156MB), has been reduced by 0% (0.000B), duration 6 seconds, attempts 6.
[Wed Jun 28 15:14:20 2017] (melkij:public.compact) Processing complete.
[Wed Jun 28 15:14:20 2017] (melkij:public.compact) Processing results: 444 pages left (724 pages including toasts and indexes), size reduced by 0.000B (0.000B including toasts and indexes) in total.
[Wed Jun 28 15:14:20 2017] (melkij:public.compact) Finish handling table public.compact
[Wed Jun 28 15:14:20 2017] (melkij) Processing complete.
[Wed Jun 28 15:14:20 2017] (melkij) Processing results: size reduced by 0.000B (0.000B including toasts and indexes) in total.
[Wed Jun 28 15:14:20 2017] (melkij) Disconnecting from database
[Wed Jun 28 15:14:20 2017] Processing complete: 1 retries to process has been done
[Wed Jun 28 15:14:20 2017] Processing results: size reduced by 0.000B (0.000B including toasts and indexes) in total.
Melkij commented 7 years ago

I add three options to control reindex retry. Also fix bug in drop temporary index function