PostgreSQL即学即用(第3版)(txt+pdf+epub+mobi电子书下载)


发布时间:2020-06-03 05:01:11

点击下载

作者:(美) 瑞金娜·奥贝(Regina Obe) 利奥·徐(Leo Hsu)

出版社:人民邮电出版社

格式: AZW3, DOCX, EPUB, MOBI, PDF, TXT

PostgreSQL即学即用(第3版)

PostgreSQL即学即用(第3版)试读:

前言

PostgreSQL 宣称自己是世界上最先进的开源数据库。我们非常赞同这种说法。

我们希望本书能帮助读者在 PostgreSQL 的核心概念与功能特性等方面打下坚实的基础,正是这些先进的概念与功能特性使得这款数据库如此杰出和与众不同。同时我们将使读者相信,PostgreSQL“最先进的开源数据库”这一称号是实至名归的。这款数据库体系庞大、功能先进,因此如果一本书试图将其强大特性介绍清楚,其篇幅一定不会少于 3500 页。事实上,大多数用户并不需要摸透它所有复杂深奥的高级功能,因此在这本不到 300 页的书中,我们希望能够帮助读者提纲挈领抓住要点,从而做到像本书书名所宣称的那样——即学即用。

本书在介绍每个功能点的同时都会附带其适用的上下文场景,这样读者就可以了解每个功能点的适用范围以及功能表现。我们假设读者已经具备了其他数据库的使用经验,这样我们就可以直奔主题,介绍 PostgreSQL 的关键功能点,而不必在预先普及数据库基础知识上浪费时间。我们在一些必要的地方附上了相关资源,以便于读者深入钻研感兴趣的功能点。这些资源的内容多种多样,比如官方手册的特定章节、网上有帮助的文章以及 PostgreSQL 大牛们的博客文章等,当然也包括我们自己的官网 Postgre Online Journal 上的一些文章,这个网站上有我们自己编写的很多 PostgreSQL 相关文章,也有一些研究 PostgreSQL 与其他应用之间互操作性的文章。

本书主要介绍 PostgreSQL 9.5、PostgreSQL 9.6 以及 PostgreSQL 10,但也会涉及之前版本中一些独特的高级特性。本书读者

对于从其他数据库引擎迁移到 PostgreSQL 的读者来说,我们将在本书中列出其他数据库的高级特性在 PostgreSQL 中的实现方法。更重要的是,我们会重点介绍一些在 PostgreSQL 中可以实现而在其他数据库中很难或者不可能实现的高级功能。

本书不会教读者怎么写 SQL,相关学习资料有很多,因此这不是本书的重点。学习 SQL 就像学习下棋——几个小时就能了解基本规则,但要熟练掌握却需要终身持续学习。你会发现选择 PostgreSQL 是一个明智的决定,并将因此而受益终身。

如果你是对 PostgreSQL 非常熟悉的老用户或者是经验丰富的 DBA,那么本书中的大量内容你都会觉得很熟悉,但即便如此,你也一定可以学到新版 PostgreSQL 中引入的一些新特性,也很可能会了解到一些在老版本中已经提供但此前被你遗漏的功能点。好吧,如果你对于书中内容均已了解,那么本书对你来说依然有价值,因为它比官方的 PostgreSQL 手册要轻得多,最起码是便于携带了。

如果你还没接触过 PostgreSQL,那么本书将扮演你身边的 PostgreSQL“布道师”的角色。这位“布道师”将向你证明:多用那些很弱的数据库一天,你的系统就不得不多做一天功能上的妥协;多绑在商业数据库上一天,你就会被那些厂商掏走更多的钱。

最后,如果你的工作与数据库领域甚至是 IT 界毫无关系,又或者你刚刚幼儿园毕业,那么能否购买本书呢?答案依然是“可以”!因为封面上可爱的象鼩鼠图片就已经让本书物有所值了。关于PostgreSQL的更多信息

PostgreSQL 有一套制作精良的在线文档,建议读者收藏它。这套文档有 HTML 和 PDF 两种格式,还有纸质印刷版。

其他可用的 PostgreSQL 资源如下。● Planet PostgreSQL 是 PostgreSQL 技术博客文章的汇聚站点,

其中包含从 PostgreSQL 核心开发人员到普通用户编写的各类文

章,包括新特性用法介绍、原有特性的巧妙用法以及已发现但尚

未正式修复的 bug 报告。● PostgreSQL Wiki 提供对 PostgreSQL 各个方面的使用技巧说

明,以及从其他数据库移植到 PostgreSQL 的方法。● PostgreSQL Books 提供有关 PostgreSQL 的图书列表信息。● PostGIS in Action Books 是我们已出版的关于 PostGIS 和

pgRouting 插件的图书的官方站点。PostGIS 是 PostgreSQL 上

的空间数据管理插件,pgRouting 是基于 PostGIS 的一款网络路

径规划插件,在导航出行类应用中经常要用到。代码与输出格式

对于括号中的内容,我们一般会将左括号与之前的内容放置于同一行,右括号单独放置一行。这是经典的 C 语言排版风格,我们比较喜欢,因为它可以减少空行数。格式如下:function ( Welcome to PostgreSQL);

为节省版面,我们还移除了命令行执行输出结果中无意义的空格,因此如果发现实际输出结果的格式与书中提供的不一致,请不要担心,这是正常的。

当一行中存在多个逗号时,如果每个元素的长度都比较短,我们会把逗号之后的空格去掉。比如:('a', 'b', 'c')。

PostgreSQL 的 SQL 解释器会将语句中的制表符、换行符和回车符当作空白处理。在我们提供的示例代码中,一般会使用空白而不是制表符作为缩进符。请确保使用的代码编辑器不会自动将制表符、换行符和回车符删除,或者把它们转换为空格以外的字符,否则会导致问题。

如果在执行示例代码时遇到了问题,请检查你复制过来的代码与我们提供的原始代码是否一致。

注意,有些示例适用于 Linux,而有些适用于 Windows。传统上,二者的路径分隔符是不同的,Linux 下是斜杠(/),而 Windows 下是反斜杠(\)。但请注意:在 PostgreSQL 中,即使是 Windows 环境下,也一定要使用 Linux 的 / 作为路径分隔符,而不是 Windows 传统的 \。你会看到示例代码中有类似于 /postgresql_book/somefile.csv 这样的路径,这指的是 Linux 服务器根目录下的路径。如果使用的是 Windows 环境,那么需要加上驱动器符,因此路径要改为:C:/postgresql_book/somefile.csv。排版约定

本书将使用如下排版约定。● 黑体表示新术语。● 等宽字体(constant width)表示程序片段,以及正文中出现的变量、函数名、数据库、

数据类型、环境变量、语句和关键字等。● 加粗等宽粗体(constant width bold)表示应该由用户输入的命令或其他文本。● 斜体等宽斜体(constant width italic)表示应替换成用户提供的值或由上下文决定的值。 该图标表示提示或建议。 该图标表示警告或警示。使用代码示例

代码和数据示例可以从 http://www.postgresonline.com/downloads/postgresql_book_3e.zip 下载。

本书是要帮你完成工作的。一般来说,如果本书提供了示例代码,你可以把它用在你的程序或文档中。除非你使用了很大一部分代码,否则无须联系我们获得许可。比如,用本书的几个代码片段写一个程序就无须获得许可,销售或分发 O'Reilly 图书的示例光盘则需要获得许可;引用本书中的示例代码回答问题无须获得许可,将书中大量的代码放到你的产品文档中则需要获得许可。

我们很希望但并不强制要求你在引用本书内容时加上引用说明。引用说明一般包括书名、作者、出版社和 ISBN。比如:“PostgreSQL: Up and Running, Third Edition by Regina Obe and Leo Hsu (O'Reilly). Copyright 2018 Regina Obe and Leo Hsu, 978-1-491-96341-8.”

如果你觉得自己对示例代码的用法超出了上述许可的范围,欢迎你通过 permissions@ oreilly.com 与我们联系。O'Reilly Safari

Safari(原来叫 Safari Books Online)是一个会员制的培训和参考平台,面向企业、政府、教育机构和个人。

会员可以访问几千种图书、培训视频、学习路径、互动式教程和精选播放列表,提供这些资源的出版商超过 250 家,包括 O'Reilly Media、Harvard Business Review、Prentice Hall Professional、Addison-Wesley Professional、Microsoft Press、Sams、Que、Peachpit Press、Adobe、Focal Press、Cisco Press、John Wiley & Sons、Syngress、Morgan Kaufmann、IBM Redbooks、Packt、Adobe Press、FT Press、Apress、Manning、New Riders、McGraw-Hill、Jones & Bartlett、Course Technology,等等。

要获得更多信息,请访问 http://oreilly.com/safari。联系我们

请把对本书的评价和问题发给出版社。美国:

  O'Reilly Media, Inc.

  1005 Gravenstein Highway North

  Sebastopol, CA 95472

中国:

  北京市西城区西直门南大街 2 号成铭大厦 C 座 807 室(100035)

  奥莱利技术咨询(北京)有限公司

对于本书的评论和技术性问题,请发送电子邮件到:bookquestions@oreilly.com

要提交勘误,请访问本书的勘误页面(https://www.oreilly.com/1catalog/errata.csp?isbn=0636920052715)。1本书中文版勘误请到 http://www.ituring.com.cn/book/2460 查看和提交。——编者注

本书的配套网站地址是:https://www.oreilly.com/catalog/errata.csp?isbn=0636920052715

要联系本书作者,请发送邮件到 lr@pcorp.us。

对于本书的评论和技术性问题,请发送电子邮件到:bookquestions@oreilly.com

要了解更多有关 O'Reilly 图书、培训课程、会议和新闻的信息,请访问以下网站:http://www.oreilly.com

我们在 Facebook 的地址如下:http://facebook.com/oreilly

请关注我们的 Twitter 动态:http://twitter.com/oreillymedia

我们的 YouTube 视频地址如下:http://www.youtube.com/oreillymedia  第1章 基础知识

PostgreSQL 是一款极其强大的数据库,它的很多特性可能是你前所未见的。它的部分特性在其他知名数据库中也有,但名称可能不同。在深入钻研官方手册之前,你需要了解一些核心概念,本章将为你介绍这些概念,期间也会涉及其他数据库中的相关概念和术语。

本章将首先介绍如何下载和安装 PostgreSQL,然后会介绍一些必备的管理工具和 PostgreSQL 术语。本书写作之时,PostgreSQL 10 已发布,我们将重点介绍该版本的一些新特性。本章末尾会提供一些帮助资源,当你需要额外的帮助或者报告 bug 时会用得到。1.1 为什么应该选择PostgreSQL

PostgreSQL 是一款企业级关系型数据库管理系统,即使与 Oracle、Microsoft SQL Server、IBM DB2 等业界最好的商用数据库相比也毫不逊色。PostgreSQL 之所以如此特别,是因为它不仅仅是一个数据库,还是一个功能强大的应用开发平台。

PostgreSQL 的速度很快。大量的评测数据已经表明:与其商用以及开源竞争对手相比,PostgreSQL 的速度要么远远胜出,要么旗鼓相当。

PostgreSQL 支持用多种编程语言编写存储过程和函数。除了系统自带的 C、SQL 和 PL/ pgSQL 编程语言外,还可以通过安装扩展包来支持 PL/Perl、PL/Python、PL/V8(又称为 PL/JavaScript)、PL/Ruby 以及 PL/R 等。这种支持多语言的能力可以让开发人员根据待解决问题的特点来选择最合适的语言。比如可以使用 R 语言来解决统计和图形领域的问题,通过 Python 来调用 Web 服务,通过使用 SciPy 库来进行科学计算,通过 PL/V8 来进行数据验证、字符串处理和 JSON 数据处理,等等。PostgreSQL 不但支持种类繁多的开发语言,使用过程也很简单:先找到你需要的函数,看下它是用什么语言编写的,在 PostgreSQL 中安装好支持该语言的扩展包,然后把代码复制过来就可以执行了。真的不能更简单。

大多数数据库会限制用户只能使用预定义的数据类型,比如整型、字符串、文本型、布尔型等。PostgreSQL 在数据类型的支持方面有两个优势:不但支持比绝大多数数据库更丰富的原生数据类型,而且还允许用户按需自定义数据类型。如果用户需要更复杂的数字类型,那么可以定义包含两个浮点类型(float)的复合类型;如果需要定义一个三角形,那么可以先定义一种“坐标”类型,然后再定义一种包含三个坐标的“三角形”类型即可;如果你对十二进制感兴趣,那么可以定义你自己的十二进制类型。值得注意的是,要想自定义类型完全发挥出其威力,需要有相应的运算符和函数来识别并配合它。因此,如果你自定义了一种特殊的数值类型,千万不要忘了为它重定义配套的数学运算符。是的,你没看错,PostgreSQL 允许用户重定义基础运算符(+、-、/、*)的实现逻辑。另外,用户自定义一种数据类型后,PostgreSQL 会自动定义出一种基于该类型的数组类型。因此,如果你定义了一种复合数据类型,那么该复合数据类型的数组类型自动就有了,你无须做额外的定义工作。

PostgreSQL 会为每一张用户表自动创建一个数据类型定义。比如我们创建了一张名为 dogs 的表,包含 breed(品种)、cuteness(可爱程度)、barkiness(爱叫的程度)等字段,那么 PostgreSQL 会自动在后台创建一个同名的 dogs 数据类型。这一特性将关系型数据库领域的“表”概念与面向对象领域的“对象”概念紧密地联系到了一起,用户可以像处理对象实例一样去处理记录。比如你可以创建一个函数来每次处理一个或者一批对象实例。PostgreSQL 的很多第三方扩展包就利用该自定义数据类型能力来优化性能,或者通过添加支持某个领域专用的特殊 SQL 语法来让业务代码更简洁和易于维护,或者实现一些在别的数据库中完全不可能实现的功能。

我们建议用户不要把数据库仅仅当成一个数据容器,像 PostgreSQL 这样的数据库其实是一个成熟而完整的应用开发平台。你会发现:强大的数据库在手,其他一切都是过眼云烟。一旦你成了 SQL 高手,别人用其他工具需要几小时才能完成的工作,你在数据库里只需要几秒钟。不管是对比编码时间还是对比实际数据处理时间,效果都是如此。

近年来,我们看到很多 NoSQL 数据库异军突起,一时风头无两(我们认为这里面有很大的炒作成分)。尽管 PostgreSQL 总体上看是一个关系型数据库,但它其实也具备强大的处理非关系型数据的能力。比如 ltree 这个插件可以处理图数据,但我们几乎已经想不起它到底是何时被加入到 PostgreSQL 中的;又比如 hstore 插件可以实现键值存储;还有 JSON 和 JSONB 类型可以提供类似 MongoDB 的文档操作能力。从很多方面来看,PostgreSQL 甚至在“NoSQL”这个词出现之前就已经提供了那些所谓的 NoSQL 特性。

如果从 Postgres95 正式改名为 PostgreSQL 开始算起,PostgreSQL 已经诞生二十年了,但事实上它的历史可向前一直追溯1到 1986 年。PostgreSQL 支持当前所有主流的操作系统,包括 Linux、Unix、Windows 以及 Mac。目前每年会发布一个大版本,包含性能提升以及那些不断超越关系型数据库功能极限的新功能。1从 1986 年开始,Stonebraker 教授发表了一系列论文,引入对象关系理念,探讨了新的数据库的结构设计和扩展设计。1988 年,他发表了 Postgres 的第一个原型设计,1989 年 6 月发布了版本 1。因此 1986 年可视为 PostgreSQL 发展史的元年。——译者注

最后值得一提的是,PostgreSQL 不但是开源的,而且开源得非常彻底,它的许可策略非常宽松。现在 PostgreSQL 社区由一群无私奉献的的开发者和用户构成,他们并不把赚钱视为人生终极目标。如果需要新特性,你可以自行贡献代码或者提出诉求。如果你试图修改 PostgreSQL 代码以为己所用,也不会有人起诉你。是成千上万用户的参与和贡献使得 PostgreSQL 变成了它今日的模样。

到最后你会想:我为什么还要使用别家的数据库?PostgreSQL 已经提供了我所需要的一切功能,而且还是免费的!你不再需要去阅读那些商业数据库附带的密密麻麻的授权条款,来了解在一个八核虚拟机上支持 X 个并发连接所需要的费用是多少,也不需要了解每次升级后要再为许可证加多少钱。1.2 不适用PostgreSQL的场景

在为 PostgreSQL 做了这么多“鼓吹”之后,我们也应介绍一下它不适用于哪些场景。

在不安装任何扩展包的情况下,PostgreSQL 需占用 100MB 以上的磁盘空间,可以看出它的个头还是比较大的。因此,在一些存储空间极为有限的小型设备上使用 PostgreSQL 是不合适的,把 PostgreSQL 当成简单的缓存区来用也是不合适的,此时应选用一些更轻量级的数据库。

作为一款企业级数据库产品,PostgreSQL 对于安全是极其重视的。因此,如果你在开发一个把安全管理放到应用层去做的轻量级应用,那么 PostgreSQL 完善的安全机制反倒会成为负担,因为它的角色和权限管理非常复杂,会带来不必要的管理复杂度和性能损耗。此时可以考虑选用 SQLite 这样的单用户数据库,或者选用 FireBird 这样既能以客户端 / 服务器模式运行也能以嵌入式单用户模式运行的数据库。

通过上述介绍可以看到,一般来说需要将 PostgreSQL 与别的数据库搭配使用,使它们各展所长。一种常见的组合是把 Redis 或者 Memcache 当成 PostgreSQL 的查询缓存来用;另一种组合是用 PostgreSQL 做主数据库,用 SQLite 存储离线数据来做离线查询。

令人遗憾的一个事实是,很多共享主机服务(多个用户共享同一个操作系统实例)供应商并不支持预安装 PostgreSQL,或者只支持安装一个很陈旧的版本。它们更喜欢预装较弱的 MySQL。对于一个 Web 设计人员来说,用什么数据库并不是首要问题,此时 MySQL 可能能够满足要求。但当用户的 SQL 技能不断提升,不再满足于写一写单表 select 或者简单的 join 查询时,MySQL 的缺点就会暴露无遗。自本书第一版出版以来,虚拟化技术的进步使得商业化的云主机服务得到了长足的发展,因此拥有自己的独立云主机不再是一件很奢侈的事情。当用户拥有自己的独立云主机时,就可以自由选择在上面安装什么软件。随着 PaaS(平台即服务)、DBaaS(数据库即服务)等云计算模式的流行,PostgreSQL 的发展前景向好。绝大多数的云服务厂商都提供 PostgreSQL 服务,其中比较著名的有 Heroku、Engine Yard、RedHat OpenShift、Amazon RDS for PostgreSQL、Google Cloud SQL for PostgreSQL、Amazon Aurora for PostgreSQL 以及 Microsoft Azure for PostgreSQL。1.3 如何获得PostgreSQL

若干年前,你只能通过手动编译源码的方式来安装 PostgreSQL。还好那种痛苦的时代已经一去不复返了。当然,现在依然可以通过编译源码来安装,但大多数用户会使用制作好的安装包来安装,只需敲击几下键盘和鼠标就可以了。

如果你是首次安装 PostgreSQL,那么应该选择适用于你的操作系统平台的最新稳定版发行包。PostgreSQL 官方站点的核心发布页面上维护了一个列表,记录了适用于各操作系统的二进制包的下载地址。在附录 A 中,你会看到安装指导和一些定制版本的下载链接地址。1.4 管理工具

PostgreSQL 常用的管理工具有四种:psql、pgAdmin、phpPgAdmin 和 Adminer。PostgreSQL 的核心开发团队维护着前三种,因此它们一般会随着 PostgreSQL 的版本发布而同步更新。Adminer 并非 PostgreSQL 的专用管理工具,它支持管理多种类型的关系型数据库,包括 SQLite、MySQL、SQL Server 和 Oracle。除了刚刚提到的这四种以外,还有大量优秀的管理工具,开源的和商业的都有。1.4.1 psql

psql 是一种用于执行查询的命令行工具,每个 PostgreSQL 发行版中都自带 psql(参见附录 B.4 节)。它有一些独特的功能,比如导入和导出基于分隔符(逗号或者制表符等)格式的平面数据文件,以及生成简易的 HTML 格式报表等。psql 是 PostgreSQL 从诞生之初就一直附带的命令行工具,它是很多高级用户日常操作工具的不二之选,非常适用于只有控制台字符界面而无图形用户界面的使用场景。另外,在通过 shell 脚本执行数据库操作时,psql 也是必备工具。不过新用户一般更喜欢使用图形界面工具,而且也无法理解为什么“老”一代人会对命令行方式那么执着。1.4.2 pgAdmin

pgAdmin 是一款流行的免费的 PostgreSQL 图形界面管理工具。如果你的 PostgreSQL 安装包里没有附带此工具,请从其官网单独下载安装。pgAdmin 可在 PostgreSQL 支持的任意一种操作系统平台上运行。

即使你的数据库安装在只有控制台字符界面的 Linux 服务器上,只要你在本地工作站上安装了 pgAdmin,也可以用这种强大的图形化工具对其进行管理。

pgAdmin 近期已经发布了它的第四个大版本,称为 pgAdmin4。该版本对之前的 pgAdmin3 进行了彻底的重写,使用 Python 实现了“一套代码两种模式运行”的效果,一种模式是作为桌面应用运行,另一种是在浏览器中运行。pgAdmin4 当前的版本是 1.5。pgAdmin4 的首个版本是与 PostgreSQL 9.6 同时发布的,并被若干 PostgreSQL 发行版作为自带软件一起打包发布。如前所述,pgAdmin4 既可以作为桌面应用运行,也可以在浏览器中运行。

图 1-1 是 pgAdmin4 的界面示意图。图 1-1:pgAdmin4 的树状视图

如果你对 PostgreSQL 还不太熟悉,那么 pgAdmin 毫无疑问是你开始 PostgreSQL 学习之旅的最佳入口。只需在主界面上摸索一下,你就可以对 PostgreSQL 的丰富功能一览无遗。如果你正打算逃离 Microsoft SQL Server 阵营,并且习惯于 SQL Server 的 Management Studio,那么很快就能适应 pgAdmin。

相比 pgAdmin3,pgAdmin4 还有一些短板,但它正在快速补齐并在很多方面都超过了 pgAdmin3。即便如此,如果你是 PgAdmin3 的长期用户并且短期内无法切换到 pgAdmin4,那么你可以继续使用 BigSQL 公司提供的 pgAdmin3 LTS(长期支持)版,在对 pgAdmin4 进行完善测试后再切换过去。请务必牢记,pgAdmin4 才是 pgAdmin 未来的主力版本,pgAdmin3 只会维持现状,不会再有什么发展。1.4.3 phpPgAdmin

phpPgAdmin 是一种免费的基于 Web 页面的管理工具,其界面如图 1-2 所示。它是从流行的 MySQL 管理工具 phpMyAdmin 移植而来的,二者的差别主要在于 phpPgAdmin 新增了对 PostgreSQL 的 schema、过程式语言、类型转换器、运算符等对象的管理功能。如果你对 phpMyAdmin 很熟悉,会发现 phpPgAdmin 的界面风格与其完全类似。图 1-2:phpPgAdmin1.4.4 Adminer

如果你正在寻找一款除了能够管理 PostgreSQL,还能管理别的数据库的整合型工具,那么 Adminer 将是你合适的选择。Adminer 是一款轻量级的开源 PHP 应用程序,可以在同一套图形界面上管理 PostgreSQL、MySQL、SQLite、SQL Server 以及 Oracle 等多种数据库。

Adminer 有一种独特的功能让我们印象深刻:它能够以图形化方式展示数据库中的对象,并将外键约束关系以连接线的方式展示出来。另外,整个 Adminer 程序的本体仅包含一个 PHP 文件,非常简洁,这可以大大减少你安装部署时的麻烦。

图 1-3 中,左侧是登录屏幕的截图,右侧是表间关系图形化后呈现的效果。很多用户会因为登录屏幕上没有填写端口号的地方而感到困惑。如果 PostgreSQL 使用标准的 5432 侦听端口,那么登录时不填也没问题;但如果不是,就需要在服务器名称后面加上端口号,注意用冒号分隔主机名和端口号,如图 1-3 所示。图 1-3:Adminer

对于简单的查询和修改操作来说,Adminer 的功能是足够的。但为了支持多种数据库,Adminer 的功能体系已经被裁剪成了各数据库均支持的最小公共集合,因此你无法实现 PostgreSQL 所特有的一些操作,比如创建新用户、授予权限、查询当前权限列表等。Adminer 为了与它所支持的各家数据库在概念上保持兼容和通用而将每个 schema 当作一个 database,这使得以图形化展示表与表之间外键关系这一功能受到了极大影响,如果两个不同 schema 的表之间存在外键关联关系,那么在 Adminer 的界面上是无法展示出来的。如果你是 DBA,那么建议使用 pgAdmin,当然也可以安装一套 Adminer 以备不时之需。1.5 PostgreSQL数据库对象

假设你现在已经安装好了 PostgreSQL,请启动并连接好 pgAdmin,然后点开左侧的目录树,此时展现在你面前的是一堆令人眼花缭乱的数据库对象,有些你可能很熟悉,有些则可能闻所未闻。PostgreSQL 对象类型的数量超过了绝大多数关系型数据库(这还是在未安装任何扩展包的情况下)。这些对象中,有许多你可能永远都不会用到,但如果你发现业务上需要实现一种新的对象类型,那么一般来说你要实现的东西在那一堆眼花缭乱的对象中已经有前人实现过了,所以只需要正确选用即可。本书不会介绍 PostgreSQL 以标准方式安装完毕后所提供的所有对象类型,因为 PostgreSQL 引入新特性的速度惊人,任何一本书都不可能全面覆盖所有对象类型。因此我们仅讨论你有必要了解的那些对象类型。2

database2database 一词含义宽泛,既可表示广义的数据库系统,又可以表示某些特定数据库系统中的某一级数据存储单位,如表述不当极易给读者造成混淆。因此本书中会区别使用,表示广义的数据库系统时,用中文“数据库”;表示狭义的数据存储单位时,用英文“database”。——译者注

每个 PostgreSQL 服务可以包含多个独立的 database。3

schema3数据库业界对于 schema 有多种译法:纲要、模式、方案,等等。但各种译法都不能准确直观地表达出其原本的含义,即位于一个独立命名空间内的一组相关数据库对象的集合,因此前述译法从来没有一种成为主流。一般业界人员都直接使用英文 schema。考虑到这个情况,为防止初级用户理解困难,我们也按照业界习惯直接使用英文原名。——译者注

ANSI SQL 标准中对 schema 有着明确的定义,database 的下一层逻辑结构就是 schema。

如果把 database 比作一个国家,那么 schema 就是一些独立的州(或者是省、府、辖区等,具体取决于各国的实际情况)。大多数对象是隶属于某个 schema 的,然后 schema 又隶属于某个 database。在创建一个新的 database 时,PostgreSQL 会自动为其创建一个名为 public 的 schema。如果未设置 search_path 变量(后续会介绍该变量的含义),那么 PostgreSQL 会将你创建的所有对象默认放入 public schema 中。如果表的数量较少,这是没问题的,但如果你有几千张表,那么我们还是建议你将它们分门别类放入不同的 schema 中。

任何一个数据库中,表都是最核心的对象类型。在 PostgreSQL 中,表首先属于某个 schema,而 schema 又属于某个 database,这样就构成了一种三级存储结构。

PostgreSQL 的表支持两种很强大的功能。第一种是表继承,即一张表可以有父表和子表。这种层次化的结构可以极大地简化数据库设计,还可以为你省掉大量的重复查询代码。第二种是创建一张表的同时,系统会自动为此表创建一种对应的自定义数据类型。

视图

大多数关系型数据库都支持视图。视图是基于表的一种抽象,通过它可以实现一次性查询多张表,也可以实现通过复杂运算来构造出虚拟字段。视图一般是只读的,但 PostgreSQL 支持对视图数据进行修改,前提是该视图基于单张实体表构建。如果需要修改基于多张表关联而来的视图,可以针对视图编写触发器。9.3 版还引入了对物化视图的支持,该机制通过对视图数据进行缓存来实现对常用查询的加速,缺点是查到的数据可能不是最新的。更多细节请参见 7.1.3 节。

扩展包

开发人员可以通过该机制将一组相关的函数、数据类型、数据类型转换器、用户自定义索引、表以及属性变量等对象打包成一个功能扩展包,该扩展包可以整体安装和删除。扩展包在概念上与 Oracle 的 package 类似,从 PostgreSQL 9.1 版本之后一般推荐使用该机制来为数据库提供功能扩展。扩展包的具体安装步骤,请参考开发手册。一般来说,需要先将扩展包的二进制安装包和脚本复制到 PostgreSQL 安装目录下,然后运行一系列脚本,再在需要其功能的 database 中单独安装该扩展包。注意:仅需在需要该扩展包功能的 database 中安装,不必为当前数据库系统中的每个 database 都安装。比如需要对某个 database 中的数据进行高级文本搜索,那么单独在该 database 中安装 fuzzystrmatch 扩展包即可。

安装扩展包时可以指定该包中所含有的成员对象安装到哪个 schema,若不指定则默认会安装到 public schema 中。我们不建议采用默认设置,因为这会导致 public schema 变得庞大复杂且难以管理,尤其是如果你将自己的数据库对象也都存入 public schema 中,那么情况会变得更糟糕。我们建议你创建一个独立的 schema 用于存放所有扩展包的对象,甚至为规模较大的扩展包单独创建一个 schema。为避免出现找不到新增扩展包对象的问题,请将这些新增的 schema 名称加入 search_path 变量中,这样就可以直接使用扩展包的功能而无须关注它到底安装到了哪个 schema 中。也有一些扩展包明确要求必须安装到某个 schema 下(特别是过程式语言扩展包),这种情况下你就不能自行指定了。有很多语言扩展包,比如 plv8,就要求必须安装到 pg_catalog schema 中。

多个扩展包之间可能存在依赖关系。在 PostgreSQL 9.6 之前,你需要了解这个依赖关系并把被依赖包先装好,但从 9.6 版开始,只需在安装时加上 cascade 关键字,PostgreSQL 就会自动安装当前扩展包所依赖的扩展包。例如:CREATE EXTENSION postgis_tiger_geocoder CASCADE;

这条命令会先寻找并安装被依赖的 postgis 和 fuzzystrmatch 这两个扩展包,当然,如果原本就有了,就不需要了。

函数

用户可以编写自定义函数来对数据进行新增、修改、删除和复杂计算等操作,可以使用 PostgreSQL 所支持的各种过程式语言来编码。PostgreSQL 装好以后自身就包含了数以千计的系统函数,都在默认的 postgres 库中。函数支持返回以下数据类型:标量值(也就是单个值)、数组、单条记录以及记录集。其他数据库将对数据进行增删改操作的函数称为“存储过程”,把不进行增删改的函数叫作“函数”,但 PostgreSQL 中并不区分,统一把二者称为“函数”。

内置编程语言

函数是以过程式语言编写的。PostgreSQL 默认支持三种内置编程语言:SQL、PL/pgSQL 以及 C 语言。可以通过 CREATE EXTENSION 或者 CREATE PRODCEDURAL LANGUAGE 命令来添加其他语言。目前较常用的语言是 PL/Python、PL/V8(即 JavaScript)以及 PL/R。我们将在第 8 章中展示大量的相关示例。

运算符

运算符本质上是以简单符号形式呈现的函数别名,例如 =、&& 等。PostgreSQL 支持自定义运算符。如果用户定义了自己的数据类型,那么一般来说需要再自定义一些运算符来与之配合工作。比如你定义了一个复数类型,那么你很有可能需要自定义 +、-、*、/ 这几个运算符来对复数进行运算。

外部表和外部数据封装器

外部表是一些虚拟表,通过它们可以直接在本地数据库中访问来自外部数据源的数据。只要数据映射关系配置正确,外部表的用法就与普通表没有任何区别。外部表支持映射到以下类型的数据源:CSV 文件、另一个服务器上的 PostgreSQL 表、SQL Server 或 Oracle 这些异构数据库中的表、Redis 这样的 NoSQL 数据库,甚至像 Twitter 或 Salesforce 这样的 Web 服务。

外部表映射关系的建立是通过配置外部数据封装器(foreign data wrapper,FDW)实现的。FDW 是 PostgreSQL 和外部数据源之间的一架“魔法桥”,可实现两边数据的互联互通。其内部实现机制遵循 SQL 标准中的 MED(Management of External Data)规范,更多细节请参考维基百科上关于 MED 的描述。

许多无私的开发者已经为当下大部分流行的数据源开发了 FDW 并已免费共享出来。你也可以通过创建自己的 FDW 来练习。(我们建议你一旦成功了也公布出来,这样整个社区都可以分享你的劳动成果。)FDW 是通过扩展包机制实现的,安装好以后在 pgAdmin 界面上名为 Foreign Data Wrapper 的目录节点下能看到它。

触发器和触发器函数

绝大多数企业级数据库都支持触发器机制,该机制可以侦测到数据修改事件的发生。在 PostgreSQL 中,当一个触发器被触发后,系统会自动调用用户定义好的触发器函数。触发器的触发时机是可设置的,可以是语句级触发或者记录级触发,也可以是修改前触发或修改后触发。

在 pgAdmin 中,如果希望了解哪些表上挂载了触发器,只需在对象树上层层点击,一直打开到表这一级,然后可以看到下面有个 trigger 子栏目,里面就是该表的所有触发器。

定义触发器时需要定义对应的触发器函数,这类函数与前面介绍过的普通函数有所不同,主要差异在于触发器函数可以通过系统内置变量来同时访问到修改前和修改后的数据,这样就可以实现对于非法的数据修改行为的识别和拦截。因此触发器函数一般会用于编写复杂校验逻辑,这类复杂逻辑通过 check 约束是无法实现的。

PostgreSQL 的触发器技术正在快速的演进之中。9.0 版引入了对 WITH 子句的支持,通过它可以实现带条件的记录级触发,即只有当某条记录符合指定的 WHEN 条件时,触发器才会被调用。9.0 版还引入了 UPDATE OF 子句,通过它可以实现精确到字段级的触发条件设置。仅当指定的字段内容被更改时才会激活触发器。9.1 版支持了针对视图的触发器。9.3 版支持了针对 DDL 的触发器。目前支持触发器的 DDL 命令列表请参见官方手册中“触发器触发时机一览表”。pgAdmin 中会把 DDL 触发器列在 event trigger 这一栏下。最后值得一提的是,从 9.4 版开始,针对外部表的触发器也获得了支持。4

catalog4catalog 的译法与 schema 存在相同的问题,翻译为“目录”后并不能让读者准确地理解其原意,反而容易造成混淆,因此还是沿用英文原名。——译者注

catalog 是系统级的 schema,用于存储系统函数和系统元数据。每个 database 创建好以后默认都会含有两个 catalog:一个名为 pg_catalog,用于存储 PostgreSQL 系统自带的函数、表、系统视图、数据类型转换器以及数据类型定义等元数据;另一个是 information_schema,用于存储 ANSI 标准中所要求提供的元数据查询视图,这些视图遵从 ANSI SQL 标准的要求,以指定的格式向外界提供 PostgreSQL 元数据信息。

一直以来,PostgreSQL 数据库的发展都严格地遵循着其“自由与开放”的核心理念。如果你足够了解这款数据库,会发现它几乎是一种可以“自我生长”的数据库。比如,它所有的核心设置都保存在系统表中,用户可以不受限地查看和修改这些数据,这为 PostgreSQL 提供了远超任何一种商业数据库的巨大灵活性(不过从另一个角度看,将这种灵活性称为“可破坏性”也未尝不可)。只要仔细地研究一下 pg_catalog,你就可以了解到 PostgreSQL 这样一个庞大的系统是如何基于各种部件构建起来的。如果你有超级用户权限,那么可以直接修改 pg_catalog 的内容(当然,如果改得不对,那你的行为就跟搞破坏没什么两样了)。

Information_schema catalog 在 MySQL 和 SQL Server 中也有。PostgreSQL 的 Information_schema 中最常用的视图一般有以下几个:columns 视图,列出了数据库中的所有字段; tables 视图,列出了数据库中的所有表(包括视图);views 视图,列出了所有视图以及用于创建该视图的原始 SQL。

类型

类型是数据类型的简称。每种数据库产品和每种编程语言都会支持一系列的数据类型,比如整型、字符型、数组、二进制大对象(blob)等。除前述常见类型外,PostgreSQL 还支持复合数据类型,这种类型可以是多种数据类型的一个组合,比如复数、极坐标、向量、张量等都是复合数据类型。

PostgreSQL 会自动为用户自己创建的表定义一个同名的复合数据类型。这样就可以把表记录当作对象实例来处理。当用户需要在函数中遍历表记录时,该特性特别有用。注意:在 pgAdmin 的界面上你看不到这些在创建表时自动生成的自定义类型,但请放心,这并不代表它们不存在。

全文检索

全文检索(full text search,FTS)是一种基于自然语言的搜索机制。这种搜索机制有一些“智能”成分。与正则表达式搜索不同,全文检索能够基于语义来进行匹配查找,而不仅仅是纯粹的语法匹配。例如,用户需要在一段长文本中搜索 running 这个词,那么命中的结果可能包含 run、running、jog、sprint、dash 等词。全文检索功能依赖于 FTS 配置库、FTS 词典、FTS 解析器这三个部件。有了它们,PostgreSQL 原生的 FTS 功能即可正常使用。一般场景下的全文检索靠这三个原生部件已经足够,但在涉及药理学、有组织犯罪学等专业场景下,搜索目标文本中会包括该领域专有词汇和特殊语法规则,此时需要用专门的 FTS 部件来替换原生 FTS 部件。我们会在 5.8 节探讨 FTS 功能。

数据类型转换器

数据类型转换器可以将一种数据类型转换为另一种,其底层通过调用转换函数来实现真正的转换逻辑。PostgreSQL 支持用户自定义转换器或者重载、加强默认的转换器。例如,如果你需要把邮政编码(美国的邮政编码是一个 5 位的整数)从 integer 转换为 character,那么可以自定义一个支持“数字不足 5 位则前面自动补 0”规则的转换器。

转换器可以被隐式调用也可以被显式调用。隐式转换是系统自动执行的,一般来说,将一种特定数据类型转为更通用的数据类型(比如数字转换为字符串)时就会发生隐式类型转换。如果进行隐式转换时系统找不到合适的转换器,你就必须显式执行转换动作。

序列号生成器

序列号生成器用于实现 serial 数据类型值的自动递增分配。在创建 serial 字段时,PostgreSQL 会自动为其创建一个相应的序列号生成器,但用户也可以很方便地更改其初始值、步长和下一个值。因为序列号生成器是独立对象,所以多个表可以共享同一个序列号生成器。基于该机制,用户可以实现跨越多个表的唯一键。SQL Server 和 Oracle 也都支持序列号生成器,但必须手动创建。

规则

规则的功能是在一个 SQL 执行前对其进行改写。本书中不会讨论有关规则的内容,因为这一技术已经过时,通过触发器能实现相同的功能。

PostgreSQL 允许用户对前述每一种对象进行参数配置。这些参数可以在服务级、库级、函数级等不同层级生效。你将来很可能会看到一个很炫的词叫 GUC,意思是“大一统配置”(grand unified configuration),它实际上指的就是 PostgreSQL 中的那些配置参数。1.6 最新版本的PostgreSQL中引入的新特性

PostgreSQL 在每年的 9 月份会发布一个大版本。每个新版本都会带来稳定性、安全性、性能等方面的提升,以及一些前沿的新特性。而且版本升级过程也变得越来越简单。那么显而易见,请尽量把你的数据库及时升级到最新的稳定版。关于每个版本引入的关键特性列表,请参见官方提供的“PostgreSQL 各版本功能特性一览表”。1.6.1 为什么要升级

如果你正在使用 PostgreSQL 9.1 或者更早的版本,请立即升级!因为 9.1 版在 2016 年 9 月已进入生命周期终结(end of life,EOL)状态。请参考 PostgreSQL 官方的发行版支持策略以获取更多关于 PostgreSQL EOL 政策的细节。请务必不要使用已过了 EOL 期限的版本,因为开发组不会再为其提供新的安全更新和功能补丁。一旦这种老版本出了问题,你只能花钱去请 PostgreSQL 专家级顾问来解决故障或寻找临时解决方案,这种服务一般都是很昂贵的,而且你不一定能找得到这种专家。

不管当前使用的是哪个大版本,你都应该尽快跟进小版本号的更新。比如从 9.1.17 升级到 9.1.21,只需要替换二进制文件并重启一下即可。小版本仅修改 bug 而不会涉及功能变化,因此这种升级是很安全的,也会为你降低出问题的概率。1.6.2 PostgreSQL 10中引入的新特性

PostgreSQL 10 是目前最新的稳定版,于 2017 年 10 月发布。从 PostgreSQL 10 开始,PostgreSQL 会以一种新的方式升级其版本号。在之前的版本中,发布大版本时变化的是第二位小版本号,比如从 PostgreSQL 9.5 到 PostgreSQL 9.6,即使增加了一些比较大的新功能,也只有小版本号发生变化。但从 PostgreSQL 10 开始,每个变化较大的版本都会在主版本号上加一。因此 PostgreSQL 10 的下一个大版本是 PostgreSQL 11。这样就与 SQLite、SQL Server、Oracle 等数据库的版本号策略保持了一致。

以下是 PostgreSQL 10 中引入的关键新特性。

提升了查询的并行度

对于并行查询启用了新的优化策略,包括并行位图堆扫描、并行索引扫描等。这些增强将使得更多查询语句能被并行执行。请参考 9.4 节以了解更多信息。

逻辑复制

此前版本的 PostgreSQL 中已经支持流复制特性。通过流复制可以实现整个 PostgreSQL 服务实例的复制,但该机制有一些固有的缺点:从节点是只读的,只能用于数据查询,不能对其数据进行修改;从节点上也不能创建自己独有的表。逻辑复制解决了这两个问题。通过逻辑复制可以实现仅复制单张表或者单个 database(不用复制整个服务实例的所有数据)。既然不需要复制整个数据库服务,那么自然从库上就可以有自己的表和数据,这部分数据是不包含在复制体系中的,因此主从库上允许不一样。

针对 JSON 和 JSONB 类型的全文检索

此前的版本中,to_tsvector 函数仅能为文本类型的字段生成全文检索向量。现在它已支持处理 JSON 和 JSONB 类型,处理过程中会忽略其中 key 的部分,而仅包含 value 的部分。同时 ts_headline 函数也专为 JSON 和 JSONB 类型做了适配,它可以对 JSON 内容中的关键字进行加亮标记。详情请参考 5.8.7 节。

支持 ANSI 标准中的 XMLTABLE 特性

XMLTABLE 特性可以将 XML 文本内容以一种更为简单的方式映射为普通二维表记录。该特性在 Oracle 和 IBM DB2 中已支持。详情请参见示例 5-41。

FDW 聚合下推

FDW API 可以将 COUNT(*) 或者 SUM(*) 这种聚合操作推送到远端节点执行。postgres_fdw 插件从该特性中受益最大。此前的版本中,postgres_fdw 插件在执行聚合操作时,需要把所有相关数据从远端 PostgreSQL 取到本地然后再进行聚合运算,这极大地影响了整体运算效率。

试读结束[说明:试读内容隐藏了图片]

下载完整电子书


相关推荐

最新文章


© 2020 txtepub下载