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