search

dbeaver / dbeaver

Free universal database tool and SQL client
https://dbeaver.io
Apache License 2.0
40.21k stars 3.47k forks source link

GaussDB BUG in function DDL - slashes are doubled #35159

Open ling-love-xin opened 2 months ago

ling-love-xin commented 2 months ago

Description

当GaussDB数据库中的函数体出现“\”等字符是，使用PG_GET_FUNCTIONDEF 获取的函数定义，使其反编译过来的，因此这个函数的函数体中的“\”字符，变成了两个“\”这种，改变的函数体，其核心原因如下： PG_GET_FUNCTIONDEF 这个函数返回的是一个 RECORD 数据类型，直接执行 select PG_GET_FUNCTIONDEF(oid) 返回的是一行一列数据 "(headerlines,definition)" 这样的一条值，因次在 definition 中，存在正则时，会对某些特殊字符 如 "\" 进行转义，造成函数体发生了变化，若是使用 select * from PG_GET_FUNCTIONDEF(oid) 返回的是一行连列数据 headerlines,definition ，其中 definition 中的函数体和库中的函数体保持一致了，至于其他PG系列的数据库没有出现这个问题是，因为GaussDB使用的PG架构是9.2版，并修改了一些内部代码造成的，且其他的函数的 PG_GET_FUNCTIONDEF 返回的是text类型而非RECORD 。

DBeaver Version

社区版 24.1.4

Operating System

window10

Database and driver

No response

Steps to reproduce

No response

Additional context

No response

ling-love-xin commented 2 months ago

建议处理方案，在获取 函数定义时，使用：select from pg_get_functiondef('schema.function( 入参类型, 入参类型2 )'::regprocedure); 或者 SELECT (PG_GET_FUNCTIONDEF(OID)). , FROM PG_PROC 使用 (RECORD).，将结果集展开后在进行获取~

ShadelessFox commented 2 months ago

Hello @ling-love-xin,

Please translate your ticket to English.

ling-love-xin commented 2 months ago

My English is poor, so I will use translation tools to translate it, I hope you can understand.

GaussDB database is developed based on the architecture of PG9.2, and some source codes are modified, including the function PG_GET_FUNCTIONDEF. In PG9.2, it returns a text value, but it returns a RECORD in the GaussDB database and its return value is decompiled, so if the "\" character appears in the function body, it will return "\\". But in PG,

In addition, there will be such an interesting phenomenon in GaussDB database, and the above problems will not occur only by expanding RECORD. For example, a value such as "select PG_GET_FUNCTIONDEF(oid)" returns a row and a column of data "(headerlines,definition)" will have the above problems; "select * from Pg_get_functiondef(oid) "returns a two-column value such as" headerlines,definition ",and the above problems will not occur.

Therefore, when obtaining the function definition of GuassDB, it is suggested to use:

"select from pg_get_functiondef(oid);" or "SELECT (PG_GET_FUNCTIONDEF(OID)).*,\ FROM PG_PROC"。 Avoid changes in the function body.

ling-love-xin commented 2 months ago

review

1、 CREATE_FUNCTION

CREATE OR REPLACE FUNCTION test( ) RETURNS VARCHAR LANGUAGE PLPGSQL NOT FENCED NOT SHIPPABLE AS $FUNCTION$ BEGIN select '\GaussDB'; end $FUNCTION$

2、Find out the OID of the function

I query appears in pg_proc, and the oid of the created function is 13304432.

3、 Use the function PG_GET_FUNCTIONDEF to get the definition.

3.1 SELECT PG_GET_FUNCTIONDEF(13304432)

3.2 SELECT from PG_GET_FUNCTIONDEF(13304432) or SELECT (PG_GET_FUNCTIONDEF(13304432)).

ling-love-xin commented 2 months ago

I'm sorry, I can't upload pictures here for various reasons. If you have GaussDB environment here, you can reproduce the problem by following the above process.

I'm sorry, I can't upload pictures here for various reasons. If you have GaussDB environment here, you can reproduce the problem by following the above process.

You will see that the function body returned by 3.1 is select '\\GaussDB', while the function body returned by 3.2 is select '\GaussDB'.

E1izabeth commented 2 months ago

Thank you

ShadelessFox commented 2 months ago

CC @GaussDB-Fans