暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

PostgreSQL问答-第27期

原创 多米爸比 2023-07-01
1576

本期问答如下:

  • 下划线数据类型
  • 备份锁等待问题
  • 单独备份分区结构
  • NCR字符转义问题

Q1.下划线数据类型

问题描述

有客户询问_varchar和_int这种带下划线的数据类型是否为可用的数据类型,语法层面建表时可以创建成功。

postgres=# create table ttt(a _varchar); CREATE TABLE
复制

问题解答

带下划线的数据类型可以理解为对象数组的一种内部别名形式.

除了基本的数据对象类型有下划线类型,当我们新建table时,pg_type系统表也会产生两个类型,一个是table本身的类型,另一个是带下划线的数组类型,示例如下:

postgres=# select typname from pg_type where typname like '%ttt'; ┌─────────┐ │ typname │ ├─────────┤ │ ttt │ │ _ttt │ └─────────┘ (2 rows)
复制

不管是基本数据对象还是我们创建的对象,都可以正常使用下划线类型:

postgres=# insert into ttt values('{abc}'::varchar[]); INSERT 0 1 postgres=# select '{100,200}'::_varchar; ┌───────────┐ │ _varchar │ ├───────────┤ │ {100,200} │ └───────────┘ (1 row)
复制

我们使用pg_typeof可以看到它的类型本质上是数组类型。

postgres=# select pg_typeof('{100,200}'::_varchar); ┌─────────────────────┐ │ pg_typeof │ ├─────────────────────┤ │ character varying[] │ └─────────────────────┘ (1 row)
复制

int家族亦是如此:

postgres=# select pg_typeof('{100,200}'::_int2); ┌────────────┐ │ pg_typeof │ ├────────────┤ │ smallint[] │ └────────────┘ (1 row) postgres=# select pg_typeof('{100,200}'::_int4); ┌───────────┐ │ pg_typeof │ ├───────────┤ │ integer[] │ └───────────┘ (1 row) postgres=# select pg_typeof('{100,200}'::_int8); ┌───────────┐ │ pg_typeof │ ├───────────┤ │ bigint[] │ └───────────┘ (1 row)
复制

Q2.备份锁等待问题

问题描述

客户使用备份软件进行备份时监控到备份过程中产生了锁,模拟截图如下:

image.png

从截图中可以看到第一条alter语句对t1表进行结构修改,并且不提交,此时pg_dump备份到t1表时需要获取共享锁会等待。

另一种场景是pg_dump备份t1表数据过程中,有客户端对t1表进行结构修改,此时结构修改需要等待数据备份完成。

问题解答

针对第一种场景pg_dump备份可能阻塞在获取表的共享锁,可以设置锁等待超时,参考命令如下:

$ pg_dump -U postgres postgres -f t1.sql --lock-wait-timeout=5
复制

等待超时后pg_dump报错退出备份:

pg_dump: error: query failed: ERROR: canceling statement due to statement timeout pg_dump: error: query was: LOCK TABLE public.t1 IN ACCESS SHARE MODE
复制

第一种场景是先进行DDL结构修改,已经获取了锁资源。

第二种场景是后进行DDL结构修改,需要先从pg_stat_activity检测是否有其他客户端应用还在使用。

同时针对在线修改表约束,可以利用NOT VALID/VALID选项拆分多个步骤:先获取SHARE UPDATE EXCLUSIVE,再短暂获取ACCESS EXCLUSIVE LOCK,让整个过程更加可靠。

Q3.如何单独备份分区结构

问题描述

分区表结构如何一次性全部备份出来呢,比如需要在另外一个环境重建一样的表结构。

问题解答

使用pg_dump导出分区表的数据可以用load-via-partition-root选项使用父表作为目标表进行数据加载,可以通过父表规划数据。

使用pg_dump导出分区表的结构可以配置多个-t显式指定所有分区,另外一种方式是使用%通配符;

不过上面两种方案都不完美:要么表分区的数量并不是固化不发生改变,要么模糊匹配名可能与其他表名重叠。

即将发布版本16对pg_dump新增了"–table-and-children"选项,可以导出所有分区,包括声明式分区和继承表都适用。

$ /opt/pg16/bin/pg_dump -f xxx.sql -s --table-and-children=person
复制

Tips: 低版本的server也可以使用这个特性,只要pg_dump客户端是16即可。

Q4.xml标签属性值转义问题

问题描述

在数据库里使用xpath函数解析xml时,观察如下几种场景:

  • 场景一
postgres=# select (xpath('/正文','<?xml version="1.0" encoding="UTF-8"?><正文>这是正文</正文>'))[1]; ┌───────────────────────┐ │ xpath │ ├───────────────────────┤ │ <正文>这是正文</正文> │ └───────────────────────┘ (1 row)
复制

xml的标签名称及标签内容如果是中文可以正常显示。

  • 场景二
postgres=# select (xpath('/正文','<?xml version="1.0" encoding="UTF-8"?><正文 国家="china">这是正文</正文>'))[1]; ┌────────────────────────────────────┐ │ xpath │ ├────────────────────────────────────┤ │ <正文 国家="china">这是正文</正文> │ └────────────────────────────────────┘ (1 row)
复制

xml标签的属性如果是中文可以也正常显示。

  • 场景三
postgres=# select (xpath('/正文','<?xml version="1.0" encoding="UTF-8"?><正文 国家="中国">这是正文</正文>'))[1]; ┌───────────────────────────────────────────────┐ │ xpath │ ├───────────────────────────────────────────────┤ │ <正文 国家="&#x4E2D;&#x56FD;">这是正文</正文> │ └───────────────────────────────────────────────┘ (1 row)
复制

xml标签的属性值如果是中文,会被转义为十六进制Unicode码。

问题解答

在xml规范里,上面的转义字符标准叫法是Numeric Character Reference(NCR),直译为数字字符引用,简称为NCR。

NCR是为了方便浏览器网页方便解析xml或html,而不用考虑文件本身的编码,因为NCR字符只用到了ASCII字符集里的字符。

NCR使用的Unicode码支持以"&#“开头的十进制表示形式或”&#x"开头的十六进制形式,后面再跟一个分号组成。

NCR转义字符在浏览器或脚本语言javascript可以被正常解析:

<script type="text/javascript"> document.write(unescape("&#x4E2D;&#x56FD;")) </script>
复制

编程语接口例如Python,也可以使用HTMLParser进行解析

$ python2 Python 2.7.5 (default, Oct 14 2020, 14:56:59) [GCC 4.8.5 20150623 (Red Hat 4.8.5-44)] on linux2 Type "help", "copyright", "credits" or "license" for more information. >>> from HTMLParser import HTMLParser >>> print HTMLParser().unescape('&#x4E2D;&#x56FD;') 中国 >>>
复制

数据库也提供了unistr函数(新增于版本14)将unicode转义字符转化为可读的字符串:

postgres=# select unistr('\u4E2D\u56FD'); ┌────────┐ │ unistr │ ├────────┤ │ 中国 │ └────────┘ (1 row)
复制

Tips: “&#x"替换为”\u"

「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

文章被以下合辑收录

评论