Re: pg_restore remap schema

Поиск
Список
Период
Сортировка
От Fabrice Chapuis
Тема Re: pg_restore remap schema
Дата
Msg-id CAA5-nLD0+WpbxB+5z7_mcp9sFjO4vUvSFZc18C9nb64i5Eshdw@mail.gmail.com
обсуждение исходный текст
Ответ на Re: pg_restore remap schema  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-general
Дерево обсуждения
Postgres allows us to rename a schema with the command alter schema schema_orig rename to schema_dest. however the definition of functions belonging to the original schema are not modified.
CREATE OR REPLACE FUNCTION foo.fcount() RETURNS integer AS $$
declare
   v_count integer; 
        BEGIN
    select count(*)
   into v_count
   from foo.test;
  return v_count; 
   -- raise notice 'The number is: %', v_count;
        END;
$$ LANGUAGE plpgsql;

select fcount();
 fcount 
      2    
(1 row)

alter schema foo rename to bar;
select fcount();
ERROR:  relation "foo.test" does not exist
LINE 2:                         from foo.test
                                     ^
Implementing the remap feature seemed to me feasible since this logic was similar to that of the native rename function.
Oracle provides this option for its export tool.
But I will not go further in this way I don't want to go against the advice of experts who have been working on the postgres code for years. 

Thank you for your comments

Regards,

Fabrice




On Wed, Nov 16, 2022 at 3:42 PM Tom Lane <tgl@sss.pgh.pa.us> wrote:
Guillaume Lelarge <guillaume@lelarge.info> writes:
> Le mer. 16 nov. 2022 à 13:08, Fabrice Chapuis <fabrice636861@gmail.com> a
> écrit :
>> I worked on the pg_dump source code to add remap schema functionality to
>> use it internally where I work. This is a first version that allows to
>> remap tables, views and sequences (only to export schema). Is this
>> development likely to interest the PG community and to continue this
>> development further?

> If it doesn't work on functions, that will be a big issue. And to be
> honest, I don't think you can do it reliably on functions, especially with
> dynamic queries in PL/pgsql.

Yeah --- I fear there is no hope of making a feature like this that
works reliably enough that we'd accept it.  pg_restore is just not
that smart about what is in the chunks of DDL that it processes,
and trying to make it smart enough is a losing game.

                        regards, tom lane

В списке pgsql-general по дате отправления:

Предыдущее
От: Вадим Самохин
Дата:
Сообщение: Postgresql 11.3 doesn't use gist index on polygon column
Следующее
От: Tom Lane
Дата:
Сообщение: Re: Postgresql 11.3 doesn't use gist index on polygon column