The output sql generated by pg_dump for a create function refers to a modified table name

Поиск
Список
Период
Сортировка
От vignesh C
Тема The output sql generated by pg_dump for a create function refers to a modified table name
Дата
Msg-id CALDaNm1MMntjmT_NJGp-Z=xbF02qHGAyuSHfYHias3TqQbPF2w@mail.gmail.com
обсуждение исходный текст
Ответы Re: The output sql generated by pg_dump for a create function refers to a modified table name  ("Jonathan S. Katz" <jkatz@postgresql.org>)
Список pgsql-hackers
Hi,

The output sql generated by pg_dump for the below function refers to a
modified table name:
create table t1 (c1 int);
create table t2 (c1 int);

CREATE OR REPLACE FUNCTION test_fun(c1 int)
RETURNS void
LANGUAGE SQL
BEGIN  ATOMIC
     WITH delete_t1 AS (
         DELETE FROM t1 WHERE c1 = $1
     )
     INSERT INTO t1 (c1) SELECT $1 FROM t2;
END;

The below sql output created by pg_dump refers to t1_1 which should
have been t1:
CREATE FUNCTION public.test_fun(c1 integer) RETURNS void
    LANGUAGE sql
    BEGIN ATOMIC
 WITH delete_t1 AS (
          DELETE FROM public.t1
           WHERE (t1_1.c1 = test_fun.c1)
         )
  INSERT INTO public.t1 (c1)  SELECT test_fun.c1
            FROM public.t2;
END;

pg_get_function_sqlbody also returns similar result:
select proname, pg_get_function_sqlbody(oid) from pg_proc where
proname = 'test_fun';
 proname  |          pg_get_function_sqlbody
----------+-------------------------------------------
 test_fun | BEGIN ATOMIC                             +
          |  WITH delete_t1 AS (                     +
          |           DELETE FROM t1                 +
          |            WHERE (t1_1.c1 = test_fun.c1) +
          |          )                               +
          |   INSERT INTO t1 (c1)  SELECT test_fun.c1+
          |             FROM t2;                     +
          | END
(1 row)

I felt the problem here is with set_rtable_names function which
changes the relation name t1 to t1_1 while parsing the statement:
/*
* If the selected name isn't unique, append digits to make it so, and
* make a new hash entry for it once we've got a unique name.  For a
* very long input name, we might have to truncate to stay within
* NAMEDATALEN.
*/

During the query generation we will set the table names before
generating each statement, in our case the table t1 would have been
added already to the hash table during the first insert statement
generation. Next time it will try to set the relation names again for
the next statement, i.e delete statement, if the entry with same name
already exists, it will change the name to t1_1 by appending a digit
to keep the has entry unique.

Regards,
Vignesh



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

Предыдущее
От: Amit Kapila
Дата:
Сообщение: Re: Support logical replication of global object commands
Следующее
От: Amit Kapila
Дата:
Сообщение: Re: pg_upgrade and logical replication