hola Lista
en un PostgreSQL 11 (11.2), Habia ejecutado varias sentencias update y
delete sobre una tabla y decidí ejecutar un mantenimiento con VACUUM, lo
quería hacer con VERBOSE pues la tabla tiene establecido un fill factor y
quería verificar qeu tal funciona, pero me sacaba error al colocar la
opción VERBOSE donde 'no es'..
Hice la siguiente pruebas en el PostgreSQL 11 (11.2):
psql (11.2)
Type "help" for help.
test=# create table prueba as select a.dato as
id,chr(trunc(a.dato/255)::int+1) as valor from generate_series(32,4000) as
a(dato);
SELECT 3969
test=#
test=#
test=# vacuum analyze verbose prueba;
ERROR: syntax error at or near "verbose"
LINE 1: vacuum analyze verbose prueba;
^
test=# vacuum verbose analyze prueba;
INFO: vacuuming "public.prueba"
INFO: "prueba": found 0 removable, 3969 nonremovable row versions in 18
out of 18 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 263121865
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: vacuuming "pg_toast.pg_toast_627673732"
INFO: index "pg_toast_627673732_index" now contains 0 row versions in 1
pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: "pg_toast_627673732": found 0 removable, 0 nonremovable row versions
in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet, oldest xmin: 263121865
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins, 0 frozen pages.
0 pages are entirely empty.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: analyzing "public.prueba"
INFO: "prueba": scanned 18 of 18 pages, containing 3969 live rows and 0
dead rows; 3969 rows in sample, 3969 estimated total rows
VACUUM
test=# vacuum verbose analyze full prueba;
ERROR: syntax error at or near "full"
LINE 1: vacuum verbose analyze full prueba;
^
bd_cll72c=# vacuum verbose full prueba;
ERROR: syntax error at or near "full"
LINE 1: vacuum verbose full prueba;
^
test=# vacuum full verbose prueba;
INFO: vacuuming "public.prueba"
INFO: "prueba": found 0 removable, 3969 nonremovable row versions in 18
pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
VACUUM
test=# vacuum full analyze verbose prueba;
ERROR: syntax error at or near "verbose"
LINE 1: vacuum full analyze verbose prueba;
^
test=# vacuum full verbose analyze prueba;
INFO: vacuuming "public.prueba"
INFO: "prueba": found 0 removable, 3969 nonremovable row versions in 18
pages
DETAIL: 0 dead row versions cannot be removed yet.
CPU: user: 0.00 s, system: 0.00 s, elapsed: 0.00 s.
INFO: analyzing "public.prueba"
INFO: "prueba": scanned 18 of 18 pages, containing 3969 live rows and 0
dead rows; 3969 rows in sample, 3969 estimated total rows
VACUUM
*test=# vacuum full analyze verbose prueba;*
*ERROR: syntax error at or near "verbose"*
*LINE 1: vacuum full analyze verbose prueba;*
Pero hago la misma pruebas en PsotgreSQL 9.5 y no importa el orden de las
opciones del vacuum:
psql (9.5.9)
Type "help" for help.
test=# create table prueba as select a.dato as
id,chr(trunc(a.dato/255)::int+1) as valor from generate_series(32,4000) as
a(dato); SELECT 3969
bd_cll72c=# vacuum analyze verbose prueba;
INFO: vacuuming "public.prueba"
INFO: "prueba": found 0 removable, 3969 nonremovable row versions in 18
out of 18 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: vacuuming "pg_toast.pg_toast_664650871"
INFO: index "pg_toast_664650871_index" now contains 0 row versions in 1
pages
DETAIL: 0 index row versions were removed.
0 index pages have been deleted, 0 are currently reusable.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: "pg_toast_664650871": found 0 removable, 0 nonremovable row versions
in 0 out of 0 pages
DETAIL: 0 dead row versions cannot be removed yet.
There were 0 unused item pointers.
Skipped 0 pages due to buffer pins.
0 pages are entirely empty.
CPU 0.00s/0.00u sec elapsed 0.00 sec.
INFO: analyzing "public.prueba"
INFO: "prueba": scanned 18 of 18 pages, containing 3969 live rows and 0
dead rows; 3969 rows in sample, 3969 estimated total rows
VACUUM
t*est=# vacuum full analyze verbose prueba;*
*INFO: vacuuming "public.prueba"*
*INFO: "prueba": found 0 removable, 3969 nonremovable row versions in 18
pages*
*DETAIL: 0 dead row versions cannot be removed yet.*
*CPU 0.00s/0.00u sec elapsed 0.00 sec.*
*INFO: analyzing "public.prueba"*
*INFO: "prueba": scanned 18 of 18 pages, containing 3969 live rows and 0
dead rows; 3969 rows in sample, 3969 estimated total rows*
*VACUUM*
Consideraba que se podía colocar en cualquier orden estas opciones al
ejecutar un VACUUM...
--
Cordialmente,
Ing. Hellmuth I. Vargas S.
Esp. Telemática y Negocios por Internet
