Início / dba / Perguntas / 342360
Accepted
Justin Lowen
Asked: 2024-09-14 06:07:56 +0800 CST

PostgreSQL: pg_dump falha em executar pg_restore corretamente devido a função personalizada ausente ou errônea, mas presente no dump

  • 772

Estou executando o PostgreSQL 13.15 em um contêiner docker local, com versões pg_dump/pg_restore correspondentes. Ao despejar e restaurar o banco de dados, uma das funções personalizadas que estava presente anteriormente parece estar faltando quando se trata de criar a tabela. A tabela de interesse é ignorada durante seu COPYcomando. Após a restauração do banco de dados, a mesma função com a mesma entrada pode ser executada com sucesso sem problemas.

Reduzi o pg_dump de texto simples para o exemplo reproduzível mínimo. Aqui está o erro que ocorre durante a restauração:

CREATE TABLE
ALTER TABLE
COMMENT
WARNING:  Value: Equipment
WARNING:  TYPE: text
ERROR:  function try_cast_jsonb(text) does not exist
LINE 1: SELECT try_cast_jsonb(raw_text)
               ^
HINT:  No function matches the given name and argument types. You might need to add explicit type casts.
QUERY:  SELECT try_cast_jsonb(raw_text)
CONTEXT:  PL/pgSQL function public.content_aware_tsvector(regconfig,text[],character) line 18 at assignment
COPY searchable_items, line 1: "84      name    {}      {}      {Equipment}     A       app_user"

Aqui está a execução da função após a conclusão da restauração no shell psql:

mydb=# SELECT try_cast_jsonb('Equipment');
 try_cast_jsonb 
----------------

A função retorna nulo se a string não puder ser convertida em jsonb.

O dump do banco de dados foi executado com o seguinte comando:

pg_dump --host localhost --port 1487 --dbname mydb --clean --if-exists > backuptest.sql

A restauração foi realizada da seguinte forma em um banco de dados limpo:

pg_restore --host localhost --port 1487 --dbname mydb --clean --if-exists --no-owner backuptest.sql

Isso ocorre tanto no formato simples quanto no formato C.

Aqui está o pg_dump em texto simples:

--
-- PostgreSQL database dump
--

-- Dumped from database version 13.15 (Debian 13.15-1.pgdg120+1)
-- Dumped by pg_dump version 13.15 (Debian 13.15-1.pgdg120+1)

SET statement_timeout = 0;
SET lock_timeout = 0;
SET idle_in_transaction_session_timeout = 0;
SET client_encoding = 'UTF8';
SET standard_conforming_strings = on;
SELECT pg_catalog.set_config('search_path', '', false);
SET check_function_bodies = false;
SET xmloption = content;
SET client_min_messages = warning;
SET row_security = off;

-- DROP ALL CONSTRAINTS, INDEXES, FUNCTIONS, EXTENSIONS if existing
DROP FUNCTION IF EXISTS public.try_cast_jsonb(_text text);

DROP FUNCTION IF EXISTS public.content_aware_tsvector(_lang text, _raw_text text, _tsweight character);
DROP FUNCTION IF EXISTS public.content_aware_tsvector(_lang regconfig, _raw_text_arr text[], _tsweight character);
DROP EXTENSION IF EXISTS pg_trgm;
DROP EXTENSION IF EXISTS btree_gist;
--
-- Name: btree_gist; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS btree_gist WITH SCHEMA public;


--
-- Name: EXTENSION btree_gist; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION btree_gist IS 'support for indexing common datatypes in GiST';


--
-- Name: pg_trgm; Type: EXTENSION; Schema: -; Owner: -
--

CREATE EXTENSION IF NOT EXISTS pg_trgm WITH SCHEMA public;


--
-- Name: EXTENSION pg_trgm; Type: COMMENT; Schema: -; Owner: 
--

COMMENT ON EXTENSION pg_trgm IS 'text similarity measurement and index searching based on trigrams';


--
-- Name: try_cast_jsonb(text); Type: FUNCTION; Schema: public; Owner: app_user
--

CREATE FUNCTION public.try_cast_jsonb(_text text) RETURNS jsonb
    LANGUAGE plpgsql STABLE
    AS $$
        DECLARE
            trimmed_text text;
            casted_json jsonb;
        BEGIN
            -- Remove leading whitespace
            trimmed_text := ltrim(_text);

            -- Check if the string doesn't start with '{' or '['
            IF NOT (left(trimmed_text, 1) = '{' OR left(trimmed_text, 1) = '[') THEN
                RETURN NULL;
            END IF;

            BEGIN
                SELECT trimmed_text::jsonb INTO casted_json;
            EXCEPTION WHEN OTHERS THEN
                RETURN NULL;
        END;

        RETURN casted_json;
        END;
    $$;


ALTER FUNCTION public.try_cast_jsonb(_text text) OWNER TO app_user;


--
-- Name: content_aware_tsvector(regconfig, text[], character); Type: FUNCTION; Schema: public; Owner: app_user
--

CREATE FUNCTION public.content_aware_tsvector(_lang regconfig, _raw_text_arr text[], _tsweight character DEFAULT NULL::bpchar) RETURNS tsvector
    LANGUAGE plpgsql IMMUTABLE
    AS $_$
        DECLARE
            vec tsvector;
            i integer;
            raw_text text;
            json_text jsonb;
            debug text;
        BEGIN
            vec := '';

            FOR i IN array_lower(_raw_text_arr, 1) .. array_upper(_raw_text_arr, 1) LOOP

                raw_text := COALESCE(_raw_text_arr[i], '');

                RAISE WARNING 'Value: %', raw_text;
                SELECT pg_typeof(raw_text) INTO debug; 
                RAISE WARNING 'TYPE: %', debug;
                json_text := try_cast_jsonb(raw_text);

                IF json_text IS NOT NULL THEN
                    -- JSON aware tsvector only indexes contents and not structure.
                    vec := vec || jsonb_to_tsvector(_lang, json_text, '["string", "numeric", "boolean"]'::jsonb);
                ELSEIF raw_text ~ '^[a-fA-F0-9]{8}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{4}-[a-fA-F0-9]{12}$' THEN
                    -- Makes UUIDs a single element in the index for only exact matches.
                    vec := vec || array_to_tsvector(ARRAY[raw_text]);
                ELSE
                    vec := vec || to_tsvector(_lang, raw_text);
                END IF;

            END LOOP;

            IF _tsweight IS NOT NULL THEN
                RETURN setweight(vec, _tsweight::"char");
            END IF;

            RETURN vec;
        END;
    $_$;


ALTER FUNCTION public.content_aware_tsvector(_lang regconfig, _raw_text_arr text[], _tsweight character) OWNER TO app_user;

--
-- Name: content_aware_tsvector(text, text, character); Type: FUNCTION; Schema: public; Owner: app_user
--

CREATE FUNCTION public.content_aware_tsvector(_lang text, _raw_text text, _tsweight character DEFAULT NULL::bpchar) RETURNS tsvector
    LANGUAGE plpgsql IMMUTABLE
    AS $$
            DECLARE
                raw_json json;
                vec tsvector;
            BEGIN
                BEGIN
                    raw_json = _raw_text::json;
                EXCEPTION
                    WHEN others THEN
                        raw_json = NULL;
                END;

                vec = (
                    CASE
                        WHEN raw_json IS NOT NULL THEN json_to_tsvector(_lang::regconfig, raw_json, '["string", "numeric", "boolean"]')
                        ELSE to_tsvector(_lang::regconfig, _raw_text)
                    END
                );

                IF _tsweight IS NOT NULL THEN
                    RETURN setweight(vec, _tsweight::"char");
                ELSE
                    RETURN vec;
                END IF;

            END;
        $$;


ALTER FUNCTION public.content_aware_tsvector(_lang text, _raw_text text, _tsweight character) OWNER TO app_user;



SET default_tablespace = '';

SET default_table_access_method = heap;

--
-- Name: searchable_items; Type: TABLE; Schema: public; Owner: app_user
--

CREATE TABLE public.searchable_items (
    item_id integer NOT NULL,
    item_type text NOT NULL,
    pk_meta jsonb DEFAULT '{}'::jsonb NOT NULL,
    meta jsonb DEFAULT '{}'::jsonb NOT NULL,
    raw_text text[] NOT NULL,
    tsweight character(1) DEFAULT NULL::bpchar,
    tenant character varying DEFAULT CURRENT_USER NOT NULL,
    var_id integer GENERATED ALWAYS AS ((((meta || pk_meta) ->> 'resource_var_id'::text))::integer) STORED,
    val_id integer GENERATED ALWAYS AS ((((meta || pk_meta) ->> 'resource_val_id'::text))::integer) STORED,
    search_tsv_english tsvector GENERATED ALWAYS AS (public.content_aware_tsvector('english'::regconfig, raw_text, tsweight)) STORED
);


ALTER TABLE public.searchable_items OWNER TO app_user;

--
-- Name: TABLE searchable_items; Type: COMMENT; Schema: public; Owner: app_user
--

COMMENT ON TABLE public.searchable_items IS 'Table for optimizing text based search';

--
-- Data for Name: searchable_items; Type: TABLE DATA; Schema: public; Owner: app_user
--

COPY public.searchable_items (item_id, item_type, pk_meta, meta, raw_text, tsweight, tenant) FROM stdin;
84  name    {}  {}  {Equipment} A   app_user
84  description {}  {}  {"core resource class for the scheduling of equipment"} C   app_user


--
-- Name: searchable_items searchable_items_pkey; Type: CONSTRAINT; Schema: public; Owner: app_user
--

ALTER TABLE ONLY public.searchable_items
    ADD CONSTRAINT searchable_items_pkey PRIMARY KEY (item_id, item_type, pk_meta);


--
-- Name: trgm_search_idx; Type: INDEX; Schema: public; Owner: app_user
--

CREATE INDEX trgm_search_idx ON public.searchable_items USING gin (((array_to_tsvector(array_remove(raw_text, NULL::text)))::text) public.gin_trgm_ops);



--
-- PostgreSQL database dump complete
--

O que estou esquecendo? O que pode fazer com que um pg_dump falhe quando restaurado nas mesmas versões de ferramentas PostgreSQL e psql?

A tabela que não está sendo restaurada é a seguinte (também incorporada no dump acima):

CREATE TABLE public.searchable_items (
    item_id integer NOT NULL,
    item_type text NOT NULL,
    pk_meta jsonb DEFAULT '{}'::jsonb NOT NULL,
    meta jsonb DEFAULT '{}'::jsonb NOT NULL,
    raw_text text[] NOT NULL,
    tsweight character(1) DEFAULT NULL::bpchar,
    tenant character varying DEFAULT CURRENT_USER NOT NULL,
    var_id integer GENERATED ALWAYS AS ((((meta || pk_meta) ->> 'resource_var_id'::text))::integer) STORED,
    val_id integer GENERATED ALWAYS AS ((((meta || pk_meta) ->> 'resource_val_id'::text))::integer) STORED,
    search_tsv_english tsvector GENERATED ALWAYS AS (public.content_aware_tsvector('english'::regconfig, raw_text, tsweight)) STORED
);

A search_tsv_englishcoluna calculada que é derivada das colunas raw_texte tsweightcausa a falha. Esta função personalizada deve ser definida antes que o COPYcomando para restaurar a tabela seja executado, mas o erro relata que a função não está presente ou que há uma incompatibilidade de tipo de argumento.

Executar o COPYcomando manualmente após a restauração funciona sem problemas, como se a função não tivesse sido persistida antes do COPYcomando.

postgresql

1 respostas

  1. Best Answer

    O preâmbulo do seu arquivo de despejo contém esta linha:

    SELECT pg_catalog.set_config('search_path', '', false);

    Ele redefine o conteúdo do caminho de pesquisa do objeto de banco de dados para uma string vazia durante a sessão.

    Mais abaixo no dump você cria a função public.try_cast_jsonb()e tenta chamá-la em outra função, public.content_aware_tsvector(), assim:

      json_text := try_cast_jsonb(raw_text);

    Isso falha, porque o esquema publicnão está search_pathmais em e não é mais usado para resolver nomes de objetos não qualificados. Em outras palavras, public.try_cast_jsonb()existe, mas o PostgreSQL foi explicitamente informado para não procurar em public.

    A redefinição search_pathpara despejo e restauração é uma precaução de segurança, pelos motivos explicados neste artigo do PostgreSQL Wiki .

    Para uma resolução imediata, use o nome totalmente qualificado ao chamar a função:

      json_text := public.try_cast_jsonb(raw_text);

    No entanto, além disso, você realmente deve evitar criar objetos de usuário no publicesquema, pelos motivos explicados no artigo referenciado acima. Se necessário, objetos públicos devem ser mantidos pelo superusuário ou outra função confiável, e mesmo assim referências programáticas a tais objetos devem ser explícitas. search_pathexiste principalmente para reduzir a carga de digitação em usuários de sessões interativas

    A partir do PostgreSQL 15, os usuários não podem mais criar objetos no publicesquema por padrão, aplicando esta prática recomendada.

    • 3

relate perguntas