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

[Jonathan S. Katz]

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.

Jonathan

Good catch. Do you have thoughts on how we can adjust the naming logic to handle cases like this? [1] https://www.postgresql.org/message-id/e947fa21-24b2-f922-375a-d4f763ef3e4b%40postgresql.org

OpenPGP_signature
Description: OpenPGP digital signature