pg.ddx.io pgsql-bugs@postgresql.org mailing list archive
help / color / mirror / Atom feedvacuumdb -Z can't find function declared on functional index with inline sql function
6+ messages / 3 participants
[nested] [flat]
* vacuumdb -Z can't find function declared on functional index with inline sql function
@ 2020-11-19 17:43 Jaime Soler <jaime.soler@gmail.com>
2020-11-19 17:54 ` Re: vacuumdb -Z can't find function declared on functional index with inline sql function Jaime Soler <jaime.soler@gmail.com>
2020-11-19 18:48 ` Re: vacuumdb -Z can't find function declared on functional index with inline sql function Alvaro Herrera <alvherre@alvh.no-ip.org>
0 siblings, 2 replies; 6+ messages in thread
From: Jaime Soler @ 2020-11-19 17:43 UTC (permalink / raw)
To: pgsql-bugs
Hi,
Vacuumdb rise an error when refreshing statistics of a table that has a
functional index.
Here is the error:
vacuumdb -Z -d postgres -p 5433
vacuumdb: limpiando la base de datos «postgres»
vacuumdb: error: falló la limpieza de la tabla «public.test» en la base de
datos «postgres»: ERROR: no existe la función sinacentos(text)
LINE 2: select tipotec || '_' || upper(sinacentos(trim(nombre)))...
^
HINT: Ninguna función coincide en el nombre y tipos de argumentos. Puede
ser necesario agregar conversión explícita de tipos.
QUERY:
select tipotec || '_' || upper(sinacentos(trim(nombre)));
CONTEXT: función SQL «tecnologia_index_func», durante expansión en línea
This is the definition of the functions, index and table:
CREATE OR REPLACE FUNCTION public.sinacentos(text character varying)
RETURNS character
LANGUAGE sql
IMMUTABLE
AS $function$
select translate($1,'áéíóúÁÉÍÓÚäëïöüÄËÏÖÜ','aeiouAEIOUaeiouAEIOU');
$function$
CREATE OR REPLACE FUNCTION public.tecnologia_index_func(nombre character
varying, tipotec bigint)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $function$
select tipotec || '_' || upper(sinacentos(trim(nombre)));
$function$
create index funct_index on test ( tecnologia_index_func(test.nombre));
create table test ( id serial, nombre text) ;
select version();
version
----------------------------------------------------------------------------------------------------------------------------------
PostgreSQL 13.1 (Ubuntu 13.1-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
database instance was initialized with default settings values.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: vacuumdb -Z can't find function declared on functional index with inline sql function
2020-11-19 17:43 vacuumdb -Z can't find function declared on functional index with inline sql function Jaime Soler <jaime.soler@gmail.com>
@ 2020-11-19 17:54 ` Jaime Soler <jaime.soler@gmail.com>
1 sibling, 0 replies; 6+ messages in thread
From: Jaime Soler @ 2020-11-19 17:54 UTC (permalink / raw)
To: pgsql-bugs
I forgot to mention that vacuum verbose analyze on this table was correct.
vacuum (verbose, analyze) test
postgres-# ;
INFO: haciendo vacuum a «public.test»
INFO: el índice «funct_index» ahora contiene 0 versiones de filas en 1
páginas
DETAIL: 0 versiones de filas del índice fueron eliminadas.
0 páginas de índice han sido eliminadas, 0 son reusables.
CPU: usuario: 0.00 s, sistema: 0.00 s, transcurrido: 0.00 s.
INFO: «test»: se encontraron 0 versiones de filas eliminables y 0 no
eliminables en 0 de 0 páginas
DETAIL: 0 versiones muertas de filas no pueden ser eliminadas aún, xmin
máx antiguo: 529
Hubo 0 identificadores de ítem sin usar.
Omitiendo 0 páginas debido a «pins» de página, 0 páginas marcadas «frozen».
0 páginas están completamente vacías.
CPU: usuario: 0.00 s, sistema: 0.00 s, transcurrido: 0.00 s.
INFO: haciendo vacuum a «pg_toast.pg_toast_16386»
INFO: el índice «pg_toast_16386_index» ahora contiene 0 versiones de filas
en 1 páginas
DETAIL: 0 versiones de filas del índice fueron eliminadas.
0 páginas de índice han sido eliminadas, 0 son reusables.
CPU: usuario: 0.00 s, sistema: 0.00 s, transcurrido: 0.00 s.
INFO: «pg_toast_16386»: se encontraron 0 versiones de filas eliminables y
0 no eliminables en 0 de 0 páginas
DETAIL: 0 versiones muertas de filas no pueden ser eliminadas aún, xmin
máx antiguo: 529
Hubo 0 identificadores de ítem sin usar.
Omitiendo 0 páginas debido a «pins» de página, 0 páginas marcadas «frozen».
0 páginas están completamente vacías.
CPU: usuario: 0.00 s, sistema: 0.00 s, transcurrido: 0.00 s.
INFO: analizando «public.test»
INFO: «test»: se procesaron 0 de 0 páginas, que contenían 0 filas vigentes
y 0 filas no vigentes; 0 filas en la muestra, 0 total de filas estimadas
VACUUM
Thanks
El jue, 19 nov 2020 a las 18:43, Jaime Soler (<jaime.soler@gmail.com>)
escribió:
> Hi,
>
> Vacuumdb rise an error when refreshing statistics of a table that has a
> functional index.
> Here is the error:
>
> vacuumdb -Z -d postgres -p 5433
> vacuumdb: limpiando la base de datos «postgres»
> vacuumdb: error: falló la limpieza de la tabla «public.test» en la base de
> datos «postgres»: ERROR: no existe la función sinacentos(text)
> LINE 2: select tipotec || '_' || upper(sinacentos(trim(nombre)))...
> ^
> HINT: Ninguna función coincide en el nombre y tipos de argumentos. Puede
> ser necesario agregar conversión explícita de tipos.
> QUERY:
> select tipotec || '_' || upper(sinacentos(trim(nombre)));
>
> CONTEXT: función SQL «tecnologia_index_func», durante expansión en línea
>
>
> This is the definition of the functions, index and table:
> CREATE OR REPLACE FUNCTION public.sinacentos(text character varying)
> RETURNS character
> LANGUAGE sql
> IMMUTABLE
> AS $function$
> select translate($1,'áéíóúÁÉÍÓÚäëïöüÄËÏÖÜ','aeiouAEIOUaeiouAEIOU');
> $function$
>
> CREATE OR REPLACE FUNCTION public.tecnologia_index_func(nombre character
> varying, tipotec bigint)
> RETURNS text
> LANGUAGE sql
> IMMUTABLE
> AS $function$
> select tipotec || '_' || upper(sinacentos(trim(nombre)));
> $function$
>
> create index funct_index on test ( tecnologia_index_func(test.nombre));
>
> create table test ( id serial, nombre text) ;
>
> select version();
> version
>
>
> ----------------------------------------------------------------------------------------------------------------------------------
> PostgreSQL 13.1 (Ubuntu 13.1-1.pgdg20.04+1) on x86_64-pc-linux-gnu,
> compiled by gcc (Ubuntu 9.3.0-17ubuntu1~20.04) 9.3.0, 64-bit
>
> database instance was initialized with default settings values.
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: vacuumdb -Z can't find function declared on functional index with inline sql function
2020-11-19 17:43 vacuumdb -Z can't find function declared on functional index with inline sql function Jaime Soler <jaime.soler@gmail.com>
@ 2020-11-19 18:48 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2020-11-19 22:53 ` Re: vacuumdb -Z can't find function declared on functional index with inline sql function Jaime Soler <jaime.soler@gmail.com>
1 sibling, 1 reply; 6+ messages in thread
From: Alvaro Herrera @ 2020-11-19 18:48 UTC (permalink / raw)
To: Jaime Soler <jaime.soler@gmail.com>; +Cc: pgsql-bugs
On 2020-Nov-19, Jaime Soler wrote:
> CREATE OR REPLACE FUNCTION public.tecnologia_index_func(nombre character
> varying, tipotec bigint)
> RETURNS text
> LANGUAGE sql
> IMMUTABLE
> AS $function$
> select tipotec || '_' || upper(sinacentos(trim(nombre)));
> $function$
This should be fixable by either schema-qualifying function
'sinacentos', or adding a SET search_path clause to the function.
CREATE OR REPLACE FUNCTION public.tecnologia_index_func(nombre character
varying, tipotec bigint)
RETURNS text
LANGUAGE sql
IMMUTABLE
AS $function$
select tipotec || '_' || upper(public.sinacentos(trim(nombre)));
$function$;
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: vacuumdb -Z can't find function declared on functional index with inline sql function
2020-11-19 17:43 vacuumdb -Z can't find function declared on functional index with inline sql function Jaime Soler <jaime.soler@gmail.com>
2020-11-19 18:48 ` Re: vacuumdb -Z can't find function declared on functional index with inline sql function Alvaro Herrera <alvherre@alvh.no-ip.org>
@ 2020-11-19 22:53 ` Jaime Soler <jaime.soler@gmail.com>
2020-11-19 23:22 ` Re: vacuumdb -Z can't find function declared on functional index with inline sql function David G. Johnston <david.g.johnston@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Jaime Soler @ 2020-11-19 22:53 UTC (permalink / raw)
To: Alvaro Herrera <alvherre@alvh.no-ip.org>; +Cc: pgsql-bugs
thanks Alvaro, I don't know why I suppose that prior releases of vacuumdb
will use the user's search_path .
El jue, 19 nov 2020 a las 19:48, Alvaro Herrera (<alvherre@alvh.no-ip.org>)
escribió:
> On 2020-Nov-19, Jaime Soler wrote:
>
>
> > CREATE OR REPLACE FUNCTION public.tecnologia_index_func(nombre character
> > varying, tipotec bigint)
> > RETURNS text
> > LANGUAGE sql
> > IMMUTABLE
> > AS $function$
> > select tipotec || '_' || upper(sinacentos(trim(nombre)));
> > $function$
>
> This should be fixable by either schema-qualifying function
> 'sinacentos', or adding a SET search_path clause to the function.
>
> CREATE OR REPLACE FUNCTION public.tecnologia_index_func(nombre character
> varying, tipotec bigint)
> RETURNS text
> LANGUAGE sql
> IMMUTABLE
> AS $function$
> select tipotec || '_' || upper(public.sinacentos(trim(nombre)));
> $function$;
>
>
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: vacuumdb -Z can't find function declared on functional index with inline sql function
2020-11-19 17:43 vacuumdb -Z can't find function declared on functional index with inline sql function Jaime Soler <jaime.soler@gmail.com>
2020-11-19 18:48 ` Re: vacuumdb -Z can't find function declared on functional index with inline sql function Alvaro Herrera <alvherre@alvh.no-ip.org>
2020-11-19 22:53 ` Re: vacuumdb -Z can't find function declared on functional index with inline sql function Jaime Soler <jaime.soler@gmail.com>
@ 2020-11-19 23:22 ` David G. Johnston <david.g.johnston@gmail.com>
2020-11-20 08:20 ` Re: vacuumdb -Z can't find function declared on functional index with inline sql function Jaime Soler <jaime.soler@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: David G. Johnston @ 2020-11-19 23:22 UTC (permalink / raw)
To: Jaime Soler <jaime.soler@gmail.com>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-bugs
On Thu, Nov 19, 2020 at 3:54 PM Jaime Soler <jaime.soler@gmail.com> wrote:
> thanks Alvaro, I don't know why I suppose that prior releases of vacuumdb
> will use the user's search_path .
>
search_path for non-interactive uses became (IMO) functionally obsolete a
while back:
https://www.postgresql.org/about/news/postgresql-103-968-9512-9417-and-9322-released-1834/
David J.
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: vacuumdb -Z can't find function declared on functional index with inline sql function
2020-11-19 17:43 vacuumdb -Z can't find function declared on functional index with inline sql function Jaime Soler <jaime.soler@gmail.com>
2020-11-19 18:48 ` Re: vacuumdb -Z can't find function declared on functional index with inline sql function Alvaro Herrera <alvherre@alvh.no-ip.org>
2020-11-19 22:53 ` Re: vacuumdb -Z can't find function declared on functional index with inline sql function Jaime Soler <jaime.soler@gmail.com>
2020-11-19 23:22 ` Re: vacuumdb -Z can't find function declared on functional index with inline sql function David G. Johnston <david.g.johnston@gmail.com>
@ 2020-11-20 08:20 ` Jaime Soler <jaime.soler@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Jaime Soler @ 2020-11-20 08:20 UTC (permalink / raw)
To: David G. Johnston <david.g.johnston@gmail.com>; +Cc: Alvaro Herrera <alvherre@alvh.no-ip.org>; pgsql-bugs
Great info, thanks for your help. This was that case, a migration database
from v9.6 to v13.1
El vie, 20 nov 2020 a las 0:22, David G. Johnston (<
david.g.johnston@gmail.com>) escribió:
> On Thu, Nov 19, 2020 at 3:54 PM Jaime Soler <jaime.soler@gmail.com> wrote:
>
>> thanks Alvaro, I don't know why I suppose that prior releases of vacuumdb
>> will use the user's search_path .
>>
>
> search_path for non-interactive uses became (IMO) functionally obsolete a
> while back:
>
>
> https://www.postgresql.org/about/news/postgresql-103-968-9512-9417-and-9322-released-1834/
>
> David J.
>
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2020-11-20 08:20 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2020-11-19 17:43 vacuumdb -Z can't find function declared on functional index with inline sql function Jaime Soler <jaime.soler@gmail.com>
2020-11-19 17:54 ` Jaime Soler <jaime.soler@gmail.com>
2020-11-19 18:48 ` Alvaro Herrera <alvherre@alvh.no-ip.org>
2020-11-19 22:53 ` Jaime Soler <jaime.soler@gmail.com>
2020-11-19 23:22 ` David G. Johnston <david.g.johnston@gmail.com>
2020-11-20 08:20 ` Jaime Soler <jaime.soler@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox