| From: | Jaime Soler <jaime(dot)soler(at)gmail(dot)com> |
|---|---|
| To: | pgsql-bugs <pgsql-bugs(at)postgresql(dot)org> |
| Subject: | vacuumdb -Z can't find function declared on functional index with inline sql function |
| Date: | 2020-11-19 17:43:11 |
| Message-ID: | CAKVUGgQpLQWfwG2MLdJWO8p+sfMVyp8769kgsXr2h+krUOtadw@mail.gmail.com |
| Views: | Whole Thread | Raw Message | Download mbox | Resend email |
| Thread: | |
| Lists: | 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.
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Jaime Soler | 2020-11-19 17:54:55 | Re: vacuumdb -Z can't find function declared on functional index with inline sql function |
| Previous Message | Andres Freund | 2020-11-19 17:38:15 | Re: BUG #16707: Memory leak |