PostgreSQL Dig 1 (译)

1. What is PostgreSQL?

认识PostgreSQL

PostgreSQL is an object-relational database management system (ORDBMS) based on  POSTGRES,Version 4.2, developed at the University of California at Berkeley Computer Science Department.POSTGRES pioneered many concepts that only became available in some commercial database systems much later.

PostgreSQL是基于POSTGRES 4.2版的对象-关系型数据管理系统，诞生于加利福尼亚大学伯克利计算机科学系。POSTGRES首倡了许多后来只有在一些商业性的数据库系统中才会实现的概念。

PostgreSQL is an open-source descendant of this original Berkeley code. It supports a large part ofthe SQL standard and offers many modern features:

PostgreSQL是最初伯克利代码的一个开源后继版，它支持大部分的SQL标准而且提供很多当下数据库的特性：

• 复杂查询
• 外键
• 触发器
• 视图
• 事物完整性
• 多版本并发控制
  而且，用户也可以通过多种对PostgreSQL进行扩展，比如添加新的：
  
  • 数据类型
  • 函数
  • 操作数
  • 聚合函数
  • 索引方法
  • 程序语言

因为自由许可，PostgreSQL可以被任何人以任何用途免费使用、修改、发布 ，或者私用、或者商用、或者学术研究 。

2. A Brief History of PostgreSQL

PostgreSQL简史

The object-relational database management system now known as PostgreSQL is derived from the POSTGRES package written at the University of California at Berkeley. With over two decades of development behind it, PostgreSQL is now the most advanced open-source database available any-where

对象关系型数据库PostgreSQL起源于加利福尼亚大学伯克利系的POSTGRES软件包，在这之后发展了20多年，PostgreSQL现在是可以在任何地方使用的最先进的开源数据库。

2.1. 伯克利POSTGRES项目

The POSTGRES project, led by Professor Michael Stonebraker, was sponsored by the Defense Ad-vanced Research Projects Agency (DARPA), the Army Research Office (ARO), the National Science Foundation (NSF), and ESL, Inc. The implementation of POSTGRES began in 1986. The initial concepts for the system were presented in The design of POSTGRES, and the definition of the initialdata model appeared in The POSTGRES data model.The design of the rule system at that time was described in The design of the POSTGRES rules system. The rationale and architecture of the storage manager were detailed in The design of the POSTGRES storage system

POSTGRES是由Michael Stonebraker教授领导，由DARPA、军方研究机构、美国国家科学基金会、ESL公司资助的始于1986年的POSTGRES实现的一个项目。最初对于这个系统概念的提出是在POSTGRES的设计中，对于原始数据模型的定义是在POSTGRES数据模型之后出现的。对于此规则系统的设计在那个时候在POSTGRES规则系统中已经有描述。 POSTGRES的基本原理和存储管理在POSTGRES存储系统中被详细阐明。

POSTGRES has undergone several major releases since then. The first “demoware” system became operational in 1987 and was shown at the 1988 ACM-SIGMOD Conference. Version 1, described in The implementation of POSTGRES, was released to a few external users in June 1989. In response to a critique of the first rule system ( A commentary on the POSTGRES rules system), the rule system was redesigned (On Rules, Procedures, Caching and Views in Database Systems), and Version 2 was released in June 1990 with the new rule system. Version 3 appeared in 1991 and added support for multiple storage managers, an improved query executor, and a rewritten rule system. For the most part, subsequent releases until Postgres95 (see below) focused on portability and reliability.

自那以后POSTGRES经历了几次重大的版本发行。第一个演示软件系统在1987年开始运作，在1988ACM-SIGMOD大会上得到展示。POSTGRES实现中描述的第一版在1989发行，得到了几个外界用户的试用。为了回应用户对于第一版的批评，规则系统被重新设计，并在1990 6月发行了带有新的规则系统的第二个版本。第三个版本发行1991年，并且添加了对于多存储管理的支持、优化了查询执行器、重写了规则系统。最重要的是，后来直到Postgres95的发行版本都主要在专注于可移植性与可靠性。

POSTGRES has been used to implement many different research and production applications. These include:  a  financial  data  analysis  system,  a  jet  engine  performance  monitoring  package,  an  asteroid tracking database, a medical information database, and several geographic information systems. POSTGRES has also been used as an educational tool at several universities. Finally, Illustra Information Technologies (later merged into Informix2, which is now owned by IBM3) picked up the code and commercialized it. In late 1992, POSTGRES became the primary data manager for the  Sequoia2000 scientific computing project 4.

POSTGRES已经被用来实现多种研究性和生产性应用，这些应用包含以下：金融数据分析系统、喷气引擎表现监控、星状追踪数据库、医疗信息数据库、以及好几个地理信息系统。POSTGRES也被好几所大学用作教学性工具。最后，说明信息技术（后来被合并到Informix2，现属IBM3）整合了代码并将其商业化。在1992年后期，POSTGRES成为了Sequoia2000科学计算项目4的主要数据管理软件。

The size of the external user community nearly doubled during 1993. It became increasingly obvious that maintenance of the prototype code and support was taking up large amounts of time that should have  been  devoted  to  database  research.  In  an  effort  to  reduce  this  support  burden,  the  Berkeley POSTGRES project officially ended with Version 4.2

在1993年POSTGRES的外部用户社团几乎翻番，对于原代码的支持花费大量的时间变得日益明显，而那些时间本应该被投入到数据库的研究中去。为了努力削减这种支持负担，伯克利POSTGRES项目在4.2版本做了官方终结。

2.2. Postgres95

In 1994, Andrew Yu and Jolly Chen added an SQL language interpreter to POSTGRES. Under a new name, Postgres95 was subsequently released to the web to find its own way in the world as an open-source descendant of the original POSTGRES Berkeley code. Postgres95 code was completely ANSI C and trimmed in size by 25%. Many internal changes improved  performance  and  maintainability.  Postgres95  release  1.0.x  ran  about  30-50%  faster  on  the Wisconsin Benchmark compared to POSTGRES, Version 4.2. Apart from bug fixes, the following were the major enhancements:

1994 Andrew Yu和Jolly Chen为POSTGRES添加了一个SQL语言解释器，有了新名字之后，Postgres95后来被发布到了网上去寻找自己作为伯克利源码的后继的发展之路。Postgres95代码是完全的ANSI C并且被减去了25%的代码量。许多内部的改变提高了其应用表现和维护性。Postgres95 1.0.x的速度相对于Wisconsin Benchmark的POSTGRES 4.2要超速30-50%。除了bug修复之外，还有很多的强化内容：

The query language PostQUEL was replaced with SQL (implemented in the server). Subqueries were not supported until PostgreSQL (see below), but they could be imitated in Postgres95 with user-defined SQL functions. Aggregate functions were re-implemented. Support for the GROUP BY query clause was also added.

查询语言PostQUEL被替换为SQL (在服务器中实现)。直到PostgreSQL子查询才被支持，但是这些功能都能在Postgres95中通过用户定义的SQL函数模仿。所有的函数都被重新实现了。对于组排序查询从句的支持也被加了进去。

A new program (psql) was provided for interactive SQL queries, which used GNU Readline. This largely superseded the old monitor program.

一个使用GUN Readline的新程序（psql）被提供给交互SQL查询，这很大程度上替代了旧的监控程序。

A new front-end library,libpgtcl, supported Tcl-based clients. A sample shell,pgtclsh, provided new Tcl commands to interface Tcl programs with the Postgres95 server.

一个新的前端库libpgtcl，支持基于Tcl-based的客户端。一个示例脚本pgtclsh，提供新的Tcl命令使用Postgres95服务器去连接Tcl程序

The large-object interface was overhauled. The inversion large objects were the only mechanism for storing large objects. (The inversion file system was removed.)

大对像接口被革新，翻转大对象是唯一的大对象存储机制。（翻转文件新系统被移除）

The instance-level rule system was removed. Rules were still available as rewrite rules.

实例级的规则系统被移除。规则作为重写规则仍然可用。

A short tutorial introducing regular SQL features as well as those of Postgres95 was distributed with the source code
GNU make (instead of BSD make) was used for the build. Also, Postgres95 could be compiled with an unpatched GCC (data alignment of doubles was fixed).

一个介绍通用的SQL特性和Postgres95的教程被以源码的形式发布。GUN 的make命令被用来进行编译，而且，Postgres95能够被未打包的GCC打包。

2.3. PostgreSQL

By 1996, it became clear that the name “Postgres95” would not stand the test of time. We chose a new name, PostgreSQL, to reflect the relationship between the original POSTGRES and the more recent versions with SQL capability. At the same time, we set the version numbering to start at 6.0, putting the numbers back into the sequence originally begun by the Berkeley POSTGRES project.

到1996年，很清楚Postgres95这个名字不会一直代表着测试时间，于是便有了一个新名字PostgreSQL来反应原始POSTGRES和最新带有SQL兼容版本的关系，与此同时，版本号也以6.0作为开始，此号码被放在伯克利POSTGRES项目的里。

Many people continue to refer to PostgreSQL as “Postgres” (now rarely in all capital letters) becauseof tradition or because it is easier to pronounce. This usage is widely accepted as a nickname or alias.

许多人继续将PostgreSQL认为是Postgres，因为传统或者因为容易发音。这种用法被广泛认为是一种绰号或者是别名。

The emphasis during development of Postgres95 was on identifying and understanding existing problems in the server code. With PostgreSQL, the emphasis has shifted to augmenting features and capabilities, although work continues in all areas.Details about what has happened in PostgreSQL since then can be found in Appendix E.

在开发Postgres95时的重点是区别和理解存在于服务器中的代码问题；在PostgreSQL中，重点转换成了增加特性和功能，尽管各部分的工作都在持续进行。关于PostgreSQL发生了什么，自从那时起可以在Appendix E中被找到。

3. Conventions

以下协定在命令摘要中被使用：括号代表着选项部分。（在Tcl命令的摘要中，问号代表着同样的意思）。括号()和竖线|意味着你一定要做一个选择。点(.)意味着前一个元素可以被重复。为了增强清晰性，SQL语句都在提示符=>之后，脚本命令在$之后，虽然一般情况下提示没有被显示出来。
管理员通常是负责安装和运行服务器的人，用户可以是任何使用，或者想要使用部分PostgreSQL系统的人。这些名词不应该被翻译的太过狭隘，本书中没有关于管理员程序修正的推定。