首页 » PostgreSQL/GaussDB » Alert: openGauss V5.0 vs. V3 keywords增加了 “charset” bug

Alert: openGauss V5.0 vs. V3 keywords增加了 “charset” bug

  • 2023/05/11
  • PostgreSQL/GaussDB
  • 57 views
  • Alert: openGauss V5.0 vs. V3 keywords增加了 “charset” bug已关闭评论

前一段时间发布了openGauss 5.0，做为激进派的我们生产环境立即安装一套，可以在使用MTK工具迁移库时提示”charset”语法错误，为关键字KeyWord，在关键字有一个限制，所以关键字越少那从其它库迁移时在SQL文本、对象名上限制改动就越少， 每个版本关键字数量也在变化，不过最新的Postgresql要比openGauss少约1/3， 之前这套库从oracle迁移到opengauss3.1不存在该问题， 如果有数据库迁移时使用该关键字当心。

anbob=# select 1 as charset;
ERROR:  syntax error at or near "charset"
LINE 1: select 1 as charset;
                    ^
anbob=# select version();
                                                                       version
------------------------------------------------------------------------------------------------------------------------------------------------------
 (openGauss 5.0.0 build a07d57c3) compiled at 2023-03-29 03:07:56 commit 0 last mr   on x86_64-unknown-linux-gnu, compiled by g++ (GCC) 7.3.0, 64-bit
(1 row)

anbob=# select 1 as check;
 check
-------
     1
(1 row)

使用pg_get_keywords()这个系统函数可以列出所有的keyword。

但keyword的分类不同，限制不一样。

# opengauss 5.0

anbob=# select catcode,count(*) from pg_get_keywords() group by catcode;
 catcode | count
---------+-------
 R       |    93
 T       |    27
 U       |   470
 C       |    63
(4 rows)

# postgresql 13

weejar=# select catcode,count(*) from pg_get_keywords() group by catcode;
 catcode | count
---------+-------
 T       |    23
 R       |    77
 U       |   299
 C       |    51

1、unreserved，不保留，可以用于任何identity（视图、表、函数、类型、索引、字段、类型 等名称）。
2、reserved，保留，不可用于任何identity。 但注意rserved 是可以做为column labels使用如(select 1 as all)
3、reserved (can be function or type name)，保留，但是可用于函数、类型名。
4、unreserved (cannot be function or type name)，不保留，但是不可用于函数、类型名。

keyword的源码在https://gitee.com/opengauss/openGauss-server/blob/master/src/include/parser/kwlist.h

PG_KEYWORD("char", CHAR_P, COL_NAME_KEYWORD)
PG_KEYWORD("character", CHARACTER, COL_NAME_KEYWORD)
PG_KEYWORD("characteristics", CHARACTERISTICS, UNRESERVED_KEYWORD)
PG_KEYWORD("characterset", CHARACTERSET, UNRESERVED_KEYWORD)
PG_KEYWORD("charset", CHARSET, UNRESERVED_KEYWORD)
PG_KEYWORD("check", CHECK, RESERVED_KEYWORD)

可以看到CHARSET是UNRESERVED，及时是RESERVED也应该可以做为column lable, 对比一个og3.1和og5除了这个值还有多少?

分别记录og5和og3到文件
openGauss=# \o /tmp/kw5.0
openGauss=# select * from pg_get_keywords();
openGauss=# \q

[og@oel7db1 tmp]$ wc -l kw3.1
636 kw3.1
[og@oel7db1 tmp]$ wc -l kw5.0
657 kw5.0

[og@oel7db1 tmp]$ diff kw5.0 kw3.1
70d69
< change | U | unreserved
75d73
< charset | U | unreserved
 92d89
< columns | U | unreserved
100d96
< completion | U | unreserved
114d109
< convert | U | unreserved
175d169
< dumpfile | U | unreserved
188d181
< ends | U | unreserved
194d186
< escaped | U | unreserved
196,197d187
< event | U | unreserved
< events | U | unreserved
255d244
< ignore | U | unreserved
287d275
< invisible | U | unreserved
312d299
< lines | U | unreserved
395d381
< outfile | U | unreserved
493d478
< schedule | U | unreserved
520d504
< slave | U | unreserved
534,535d517
< starting | U | unreserved
< starts | U | unreserved
549d530
< subpartitions | U | unreserved
605d585
< use | U | unreserved
626d605
< visible | U | unreserved
656c635

anbob=# select 1 as columns,2 as completion,3 as convert,4 as dumpfile, 5 as  ends,6 as escaped, 7 as event, 8 as  events,9 as ignore ,10 as invisible, 11 as lines ,12 as outfile, 13 as schedule, 14 as slave, 15 as change ,16 as starting, 17 as starts, 18 as subpartitions, 19 as use, 20 as visble;
ERROR:  syntax error at or near "ignore"
LINE 1: ... ends,6 as escaped, 7 as event, 8 as  events,9 as ignore ,10...


anbob=# select 1 as columns,2 as completion,3 as convert,4 as dumpfile, 5 as  ends,6 as escaped, 7 as event, 8 as  events,10 as invisible, 11 as lines ,12 as outfile, 13 as schedule, 14 as slave, 15 as change ,16 as starting, 17 as starts, 18 as subpartitions, 19as use, 20 as visble;
 columns | completion | convert | dumpfile | ends | escaped | event | events | invisible | lines | outfile | schedule | slave | change | starting | starts | s
ubpartitions | use | visble
---------+------------+---------+----------+------+---------+-------+--------+-----------+-------+---------+----------+-------+--------+----------+--------+--
-------------+-----+--------
       1 |          2 |       3 |        4 |    5 |       6 |     7 |      8 |        10 |    11 |      12 |       13 |    14 |     15 |       16 |     17 |
          18 |  19 |     20
(1 row)

anbob=# select 1 as ignore;
ERROR:  syntax error at or near "ignore"
LINE 1: select 1 as ignore;

Note:
除了”charset”外还有”ignore”

后续bug应该会修复，当前如果使用关键字可以增加””

anbob=# select 1 as "ignore" ,2 as "charset";
 ignore | charset
--------+---------
      1 |       2
(1 row)

查询数据库里使用了系统keyword的对象列名

SELECT nspname, relname, attname 
FROM pg_namespace JOIN pg_class ON (pg_namespace.oid = relnamespace)
      JOIN pg_attribute ON (attrelid = pg_class.oid)      
      JOIN pg_get_keywords() ON (word = attname) 
WHERE nspname NOT IN ('pg_catalog', 'information_schema')

— over

打赏

对不起，这篇文章暂时关闭评论。

上一篇：

下一篇：