postgresql 11 comando vacuum requiere orden fijo en las opciones

From: Hellmuth Vargas <hivs77(at)gmail(dot)com>
To: Lista Postgres ES <pgsql-es-ayuda(at)postgresql(dot)org>
Subject: postgresql 11 comando vacuum requiere orden fijo en las opciones
Date: 2019-05-17 21:41:07
Message-ID: CAN3Qy4o9Z2HO=i42ATkWFdJTQxnbVX=ZGKeuiKD4J4J7i-ArhQ@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-es-ayuda

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

Browse pgsql-es-ayuda by date

  From Date Subject
Next Message Jorge Sanchez 2019-05-20 23:42:12 Duda con cursor + SELECT FOR UPDATE
Previous Message Alvaro Herrera 2019-05-16 22:10:35 Re: Generar registros