PGSQLMAN CHAPTER3
From PostgreSQL 中文维基, PostgreSQL 中文站, PostgreSQL 中国社区, PostgreSQL Chinese community
目录 |
[编辑] 高级特性
[编辑] 介绍
在前面几章里，我们介绍了使用 SQL 存储和访问在 PostgreSQL 里的数据的基本方法。 我们现在将讨论一些 SQL 更高级的特性，这些特性可以简化管理和避免你的数据的丢失或损坏。 最后，我们将看看一些 PostgreSQL 的扩展。
本章将不时引用在第二章里的例子， 并且对它们进行修改和提高，因此如果你已经看过那章会更好。 本章的一些例子也可以在教程目录里的 advanced.sql 文件里找到。 这个文件还包括一些要装载的例子数据，这些数据没有在这里介绍。 （请参考第2.1节获取如何使用该文件的方法。）
[编辑] 视图
回头看看在第2.6节里的查询。 假设你的应用对天气记录和城市位置的组合列表特别感兴趣， 而你又不想每次键入这些查询。那么你可以在这个查询上创建一个视图， 它给这个查询一个名字，你可以像普通表那样引用它。
CREATE VIEW myview AS
SELECT city, temp_lo, temp_hi, prcp, date, location
FROM weather, cities
WHERE city = name;
SELECT * FROM myview;
自由地运用视图是好的 SQL 数据库设计的一个关键要素。 视图允许我们把表结构的细节封装起来，这些表可能因你的应用的进化而变化， 而这些变化却可以躲在一个一致的接口后面。
视图几乎可以在一个真正的表可以使用的任何地方使用。 在其它视图上面再建造视图也并非罕见。
[编辑] 外键
回忆一下第二章里的 weather 和 cities 表。考虑一下下面的问题：你想确保没有人可以在 weather 表里插入一条在 cities 表里没有匹配记录的数据行。 这就叫维护你的表的参考完整性。 在简单的数据库系统里，实现（如果也叫实现）这个特性的方法 通常是先看看 cities 表里是否有匹配的记录， 然后插入或者拒绝新的 weather 记录。 这个方法有许多问题，而且非常不便，因此 PostgreSQL 可以为你做这些。
新的表声明看起来会象下面这样：
CREATE TABLE cities (
city varchar(80) primary key,
location point
);
CREATE TABLE weather (
city varchar(80) references cities(city),
temp_lo int,
temp_hi int,
prcp real,
date date
);
然后我们试图插入一条非法的记录：
INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
ERROR: insert or update on table "weather" violates foreign key constraint "weather_city_fkey" DETAIL: Key (city)=(Berkeley) is not present in table "cities".
外键的行为可以为你的应用仔细调节。在这份教程里我们就不再多说了，而是请你参考第五章获取更多的信息。 正确使用外键无疑将改进你的数据库应用，所以我们强烈建议你学习它们。
[编辑] 事务
事务是所有数据库系统的一个基本概念。 一次事务的要点就是它把多个步骤捆绑成了一个单一的，不成功则成仁的操作。 其它并发的事务是看不到在这些步骤之间的中间状态的，并且如果发生了一些问题， 导致该事务无法完成，那么所有这些步骤都完全不会影响数据库。
比如，假设一个银行的数据库包含各种客户帐户的余额，以及每个分行的总余额。 假设我们要记录一次从 Alice 的帐户到 Bob 的帐户的金额为 $100.00 的支付动作。那么，完成这个任务的简单到极点的 SQL 命令象下面这样
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
UPDATE branches SET balance = balance - 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Alice');
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
UPDATE branches SET balance = balance + 100.00
WHERE name = (SELECT branch_name FROM accounts WHERE name = 'Bob');
这些命令的细节在这儿并不重要；重要的是这里牵涉到了好几个独立的更新来完成这个相当简单的操作。 我们的银行官员会希望要么所有这些更新都生效，要么全部不起作用。 我们当然不希望一次系统崩溃就导致 Bob 收到 100 块不是 Alice 支付的钱， 也不希望 Alice 老是不花钱从 Bob 那里拿到物品。我们需要保证：如果在操作的过程中出了差错， 那么所有这些步骤都不会发生效果。把这些更新组合成一个事务就给予我们这样的保证。 事务被认为是原子的：从其它事务的角度来看，它要么是全部发生，要么完全不发生。
我们还需要保证：一旦一个事务完成并且得到数据库系统的认可， 那么它必须被真正永久地存储，并且不会在随后的崩溃中消失。 比如，如果我们记录到了一个 Bob 撤单的动作， 那么我们不希望仅仅在他走出银行大门之后的一次崩溃就会导致对他的帐户的扣减动作消失。 一个事务型数据库保证一个事务所做的所有更新在事务发出完成响应之前都记录到永久的存储中（也就是磁盘）。
事务型数据库的另外一个重要的性质和原子更新的概念关系密切： 当多个事务并发地运行的时候，那么每个事务都不应看到其它事务所做的未完成的变化。 比如，如果一个事务正忙着计算所有分行的余额总和， 那么它不应该包括来自 Alice 的分行的扣帐和来自 Bob 分行的入帐，反之亦然。 所以事务必须是黑白分明的，不仅仅体现在它们在数据库上产生的永久影响出发，而且体现在它们运转时的自身的可视性上。 一个打开的事务做的更新在它完成之前是其它事务无法看到的，而到提交的时候所有更新同时可见。
在 PostgreSQL 里，一个事务是通过把 SQL 命令用 BEGIN 和 COMMIT 命令包围实现的。 因此我们的银行事务实际上看起来象下面这样
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
-- 等等
COMMIT;
如果在该事务的过程中，我们决定不做提交（可能是我们刚发现 Alice 的余额是负数）， 那么我们可以发出 ROLLBACK 命令而不是 COMMIT 命令，那么到目前为止我们的所有更新都会被取消。
PostgreSQL 实际上把每个 SQL 语句当做在一个事务中执行的来看待。 如果你没有发出 BEGIN 命令，那么每个独立的语句都有一个隐含的 BEGIN 和（如果成功的话） COMMIT 语句包围在周围。 一组包围在 BEGIN 和 COMMIT 语句中间的语句有时候被称做事务块。
- 注意：一些客户库自动发出 BEGIN 和 COMMIT， 因此你可能不需要特意请求就可以获取事务块的效果。查看你使用的接口的文档。
我们可以通过使用 savepoints 的方法，在一个事务里更加精细地控制其中的语句。 保存点允许你有选择性地抛弃事务中的某些部分，而提交其它剩下的。 在用 SAVEPOINT 定义了一个保存点后，如果需要，你可以使用 ROLLBACK TO 回滚到该保存点。 则该事务在定义保存点到回滚到它之间的所有数据库更改都被抛弃，但是在保存点之前的修改将被保留。
在回滚到一个保存点之后，这个保存点仍然保存着其定义，所以你可以回滚到这个位置好几次。 当然，如果你确信你不需要再次回滚到一个保存点，那么你可以释放它，这样系统可以释放一些资源。 要记住：释放或者回滚到一个保存点都会自动释放在其后定义的所有保存点。
所有这些都发生在一个事务块内部，所以所有这些都不可能被其它事务会话看到。 当且仅当你提交了这个事务块，这些提交了的动作才能以一个单元的方式被其它会话看到， 而回滚的动作完全不会再被看到。
还记得我们的银行数据库吗？假设我们从 Alice 的帐户上消费 $100.00， 然后给 Bob 的帐户进行贷记加款，稍后我们发现我们应该给 Wally 的账号贷记加款。 那么我们可以像下面这样的保存点来做：
BEGIN;
UPDATE accounts SET balance = balance - 100.00
WHERE name = 'Alice';
SAVEPOINT my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Bob';
-- 呀！加错钱了，应该用 Wally 的账号
ROLLBACK TO my_savepoint;
UPDATE accounts SET balance = balance + 100.00
WHERE name = 'Wally';
COMMIT;
这个例子当然是实在太简单了，但是通过使用保存点，我们可以对事务块有大量的控制。 并且，ROLLBACK TO 是除了事务全部回滚，重新来过之外的唯一可用的， 用于重新控制一个因错误而被系统置于退出状态下的事务的方法。
[编辑] 继承
继承是面向对象的数据库的概念。它开启了数据库设计新的有趣的可能性大门。
让我们创建两个表：一个表 cities 和一个表 capitals。自然，首府（capital）也是城市（cities）， 因此在列出所有城市时你想要某种方法隐含地显示首府。 如果你已经很高明了，那么你可能会创造类似下面这样的模式：
CREATE TABLE capitals (
name text,
population real,
altitude int, -- (单位是英尺)
state char(2)
);
CREATE TABLE non_capitals (
name text,
population real,
altitude int -- (单位是英尺)
);
CREATE VIEW cities AS
SELECT name, population, altitude FROM capitals
UNION
SELECT name, population, altitude FROM non_capitals;
如果只是查询，那么这个方法运转得很好，比如如果你需要更新某几行， 那这个方法就很难看了。
一种更好的方法是：
CREATE TABLE cities (
name text,
population real,
altitude int -- (单位是英尺)
);
CREATE TABLE capitals (
state char(2)
) INHERITS (cities);
在这个例子里，capitals 的一行继承所有来自它的父表， cities 的所有字段（name， population，和 altitude）。 字段 name 的类型是 text， 是 PostgreSQL 用于变长字符串的固有类型。 州首府有一个额外的字段，州，显示所处的州。在 PostgreSQL 里，一个表可以从零个或者更多其它表中继承过来。
比如，下面的查询找出所有海拔超过 500 英尺的城市的名字， 包括州首府：
SELECT name, altitude
FROM cities
WHERE altitude > 500;
它返回：
name | altitude -----------+---------- Las Vegas | 2174 Mariposa | 1953 Madison | 845 (3 rows)
另外一方面，下面的查询找出所有不是州首府并且位于海拔大于或等于 500 英尺的城市：
SELECT name, altitude
FROM ONLY cities
WHERE altitude > 500;
name | altitude -----------+---------- Las Vegas | 2174 Mariposa | 1953 (2 rows)
这里的 cities 前面的 ONLY 指示系统只对 cities 表运行查询，而不包括继承级别中低于 cities 的表。 许多我们已经讨论过的命令 — SELECT, UPDATE 和 DELETE — 支持这个 ONLY 表示法。
- 注意：尽管继承经常是有用的，但是它还没有集成唯一约束或者外键，因此制约了其实用性。 参阅第5.8节获取更多细节。
[编辑] 结论
PostgreSQL 有许多这份教程里没有谈到的特性， 因为这份教程主要是面向新 SQL 用户的。这些特性在本书剩余部分将有更详细的介绍。
如果你觉得自己需要更多介绍性材料，请访问[1]获取更多资源的联接。
