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

On 2/17/23 5:22 AM, vignesh C wrote:
> 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)

Thanks for reproducing and demonstrating that this was more generally 
applicable. For context, this was initially discovered when testing the 
DDL replication patch[1] under that context.

> 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.

Good catch. Do you have thoughts on how we can adjust the naming logic 
to handle cases like this?

Jonathan

[1] 
https://www.postgresql.org/message-id/e947fa21-24b2-f922-375a-d4f763ef3e4b%40postgresql.org