vacuumdb -Z can't find function declared on functional index with inline sql function

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: Raw Message | Whole Thread | 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.

Responses

Browse pgsql-bugs by date

  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