postgresql回滚的简单介绍

如何解决postgresql 错误：08p01：无效的信息格式

Appendix A. PostgreSQL错误代码

PostgreSQL服务器发出的所有消息都赋予 了五个字符的错误代码，这些代码遵循 SQL 的"SQLSTATE" 代码的习惯。需要知道发生了什么错误条件的应用通常应该测试错误代码， 而不是查看文本错误信息。这些错误代码轻易不会随着PostgreSQL 的版本更新而修改，并且一般也不会随着错误信息的本地化而发生修改。 请注意有些(但不是全部)PostgreSQL生成的错误代码是 由 SQL 标准定义的；有些标准没有定义的错误条件是发明的或者是从其它数据库借来的。

根据标准，错误代码的头两个字符表示错误类别， 而后三个字符表示在该类别内特定的条件。因此， 那些不能识别特定错误代码的应用仍然可以从错误类别中推断要做什么。

Table A-1里面列出了PostgreSQL 8.2.3 定义的所有错误代码(有些实际上目前并没有使用， 但是 SQL 标准定义了)。错误类别也列出在此。对于每个错误类别都有个 "标准"的错误代码，它的最后三个字符是000。 这个代码只用于那些落在该类别内，但是没有赋予任何更准确的代码的错误条件。

PL/pgSQL用于每个错误代码的条件名和表中显示的措辞相同， 只是用下划线代替了空白。比如，代码22012, DIVISION BY ZERO 的条件名是DIVISION_BY_ZERO。 条件名大小写无关。(请注意PL/pgSQL并不识别警告，这一点和错误、条件名正相反；那些是 00, 01, 02 类别。)

Table A-1. PostgreSQL 错误代码

错误代码

含义

常量名

Class 00 — 成功完成

00000 成功完成 successful_completion

Class 01 — 警告

01000 警告 warning

0100C 返回了动态结果 dynamic_result_sets_returned

01008 警告，隐含补齐了零比特位 implicit_zero_bit_padding

01003 在集合函数里消除null null_value_eliminated_in_set_function

01007 没有赋予权限 privilege_not_granted

01006 没有撤销权限 privilege_not_revoked

01004 字符串数据在右端截断 string_data_right_truncation

01P01 废弃的特性 deprecated_feature

Class 02 — 没有数据(按照 SQL 标准的要求，这也是警告类)

02000 没有数据 no_data

02001 返回了没有附加动态结果集 no_additional_dynamic_result_sets_returned

Class 03 — SQL语句尚未结束

03000 SQL语句尚未结束 sql_statement_not_yet_complete

Class 08 — 连接异常

08000 连接异常 connection_exception

08003 连接不存在 connection_does_not_exist

08006 连接失败 connection_failure

08001 SQL 客户端不能建立 SQL 连接 sqlclient_unable_to_establish_sqlconnection

08004 SQL 服务器拒绝建立 SQL 连接 sqlserver_rejected_establishment_of_sqlconnection

08007 未知的事务解析 transaction_resolution_unknown

08P01 违反协议 protocol_violation

Class 09 — Triggered Action Exception触发器动作异常

09000 触发器动作异常 triggered_action_exception

Class 0A — 不支持特性

0A000 不支持此特性 feature_not_supported

Class 0B — 非法事务初始化

0B000 非法事务初始化 invalid_transaction_initiation

Class 0F — 定位器异常

0F000 定位器异常 locator_exception

0F001 非法的定位器声明 invalid_locator_specification

Class 0L — 非法赋权者

0L000 非法赋权者 invalid_grantor

0LP01 非法赋权操作 invalid_grant_operation

Class 0P — 非法角色声明

0P000 非法角色声明 invalid_role_specification

Class 20 — 未发现情况

20000 未发现情况 case_not_found

Class 21 — 势违例

21000 势违例 cardinality_violation

Class 22 — 数据异常

22000 数据异常 data_exception

2202E 数组下标错误 array_subscript_error

22021 字符不在规定范围内 character_not_in_repertoire

22008 日期时间字段溢出 datetime_field_overflow

22012 被零除 division_by_zero

22005 赋值中出错 error_in_assignment

2200B 逃逸字符冲突 escape_character_conflict

22022 INDICATOR OVERFLOW指示器溢出 indicator_overflow

22015 内部字段溢出 interval_field_overflow

2201E 对数运算的非法参数 invalid_argument_for_logarithm

22014 NTILE函数的无效参数 invalid_argument_for_ntile_function

22016 N倍函数的无效参数 invalid_argument_for_nth_value_function

2201F 指数函数的无效参数 invalid_argument_for_power_function

2201G BUCKET函数的非法参数 invalid_argument_for_width_bucket_function

22018 类型转换时非法的字符值 invalid_character_value_for_cast

22007 非法日期时间格式 invalid_datetime_format

22019 非法的逃逸字符 invalid_escape_character

2200D 非法的逃逸字节 invalid_escape_octet

22025 非法逃逸序列 invalid_escape_sequence

22P06 非标准使用逃逸字符 nonstandard_use_of_escape_character

22010 非法指示器参数值 invalid_indicator_parameter_value

22023 非法参数值 invalid_parameter_value

2201B 非法正则表达式 invalid_regular_expression

2201W 无效的行数限制 invalid_row_count_in_limit_clause

2201X 在结果抵消子句中无效的行数 invalid_row_count_in_result_offset_clause

22009 非法时区显示值 invalid_time_zone_displacement_value

2200C 逃逸字符的非法使用 invalid_use_of_escape_character

2200G 最相关类型不匹配 most_specific_type_mismatch

22004 不允许 NULL 值 null_value_not_allowed

22002 NULL 值不能做指示器参数 null_value_no_indicator_parameter

22003 数字值超出范围 numeric_value_out_of_range

22026 字符串数据长度不匹配 string_data_length_mismatch

22001 字符串数据右边被截断 string_data_right_truncation

22011 抽取子字符串错误 substring_error

22027 截断错误 trim_error

22024 未结束的 C 字符串 unterminated_c_string

2200F 零长度的字符串 zero_length_character_string

22P01 浮点异常 floating_point_exception

22P02 非法文本表现形式 invalid_text_representation

22P03 非法二进制表现形式 invalid_binary_representation

22P04 错误的COPY文件格式 bad_copy_file_format

22P05 不可翻译字符 untranslatable_character

2200L 不是一个XML文档 not_an_xml_document

2200M 无效的XML文档 invalid_xml_document

2200N 无效的XML内容 invalid_xml_content

2200S 无效的XML评论 invalid_xml_comment

2200T 无效的XML处理指令 invalid_xml_processing_instruction

Class 23 — 违反完整性约束

23000 违反完整性约束 integrity_constraint_violation

23001 约束限制 restrict_violation

23502 NOT NULL VIOLATION违反非空 not_null_violation

23503 违反外键约束 foreign_key_violation

23505 违反唯一约束 unique_violation

23514 违反检查 check_violation

23P01 违反排除 exclusion_violation

Class 24 — 非法游标状态

24000 非法游标状态 invalid_cursor_state

Class 25 — 非法事务状态

25000 非法事务状态 invalid_transaction_state

25001 活跃的SQL状态 active_sql_transaction

25002 分支事务已经激活 branch_transaction_already_active

25008 持有的指针要求同样的隔离级别 held_cursor_requires_same_isolation_level

25003 对分支事务的不恰当的访问方式 inappropriate_access_mode_for_branch_transaction

25004 对分支事务的不恰当的隔离级别 inappropriate_isolation_level_for_branch_transaction

25005 分支事务没有活跃的SQL事务 no_active_sql_transaction_for_branch_transaction

25006 只读的SQL事务 read_only_sql_transaction

25007 不支持混和的模式和数据语句 schema_and_data_statement_mixing_not_supported

25P01 没有活跃的SQL事务 no_active_sql_transaction

25P02 在失败的SQL事务中 in_failed_sql_transaction

Class 26 — 非法SQL语句名

26000 非法SQL语句名 invalid_sql_statement_name

Class 27 — 触发数据更改违规

27000 触发数据更改违规 triggered_data_change_violation

Class 28 — 非法授权声明

28000 非法授权声明 invalid_authorization_specification

28P01 非法密码 invalid_password

Class 2B — 依然存在依赖的优先级描述符

2B000 依然存在依赖的优先级描述符 dependent_privilege_descriptors_still_exist

2BP01 依赖性对象仍然存在 dependent_objects_still_exist

Class 2D — 非法的事务终止

2D000 非法的事务终止 invalid_transaction_termination

Class 2F — SQL过程异常

2F000 SQL过程异常 sql_routine_exception

2F005 执行的函数没有返回语句 function_executed_no_return_statement

2F002 不允许修改SQL数据 modifying_sql_data_not_permitted

2F003 企图使用禁止的SQL语句 prohibited_sql_statement_attempted

2F004 不允许读取SQL数据 reading_sql_data_not_permitted

Class 34 — 非法指针名

34000 非法指针名 invalid_cursor_name

Class 38 — 外部过程异常

38000 外部过程异常 external_routine_exception

38001 不允许包含的SQL containing_sql_not_permitted

38002 不允许修改SQL数据 modifying_sql_data_not_permitted

38003 企图使用禁止的SQL语句 prohibited_sql_statement_attempted

38004 不允许读取SQL数据 reading_sql_data_not_permitted

Class 39 — 外部过程调用异常

39000 外部过程调用异常 external_routine_invocation_exception

39001 返回了非法的SQL状态 invalid_sqlstate_returned

39004 不允许使用NULL null_value_not_allowed

39P01 违反触发器协议 trigger_protocol_violated

39P02 违反 SRF 协议 srf_protocol_violated

Class 3B — 保存点异常

3B000 保存点异常 savepoint_exception

3B001 无效的保存点声明 invalid_savepoint_specification

Class 3D — 非法目录名

3D000 非法目录名 invalid_catalog_name

Class 3F — 非法模式名

3F000 非法模式名 invalid_schema_name

Class 40 — 事务回滚

40000 事务回滚 transaction_rollback

40002 违反事务完整性约束 transaction_integrity_constraint_violation

40001 串行化失败 serialization_failure

40003 不知道语句是否结束 statement_completion_unknown

40P01 侦测到死锁 deadlock_detected

Class 42 — 语法错误或者违反访问规则

42000 语法错误或者违反访问规则 syntax_error_or_access_rule_violation

42601 语法错误 syntax_error

42501 权限不够 insufficient_privilege

42846 无法进行类型转换 cannot_coerce

42803 分组错误 grouping_error

42P20 开窗口错误 windowing_error

42P19 非法递归 invalid_recursion

42830 非法的外键 invalid_foreign_key

42602 非法名称 invalid_name

42622 名称过长 name_too_long

42939 保留名称 reserved_name

42804 数据类型不匹配 datatype_mismatch

42P18 模糊数据类型 indeterminate_datatype

42809 错误的对象类型 wrong_object_type

42703 未定义的字段 undefined_column

42883 未定义的函数 undefined_function

42P01 未定义的表 undefined_table

42P02 未定义的参数 undefined_parameter

42704 未定义对象 undefined_object

42701 重复的字段 duplicate_column

42P03 重复的游标 duplicate_cursor

42P04 重复的数据库 duplicate_database

42723 重复的函数 duplicate_function

42P05 重复的预备语句 duplicate_prepared_statement

42P06 重复的模式 duplicate_schema

42P07 重复的表 duplicate_table

42712 重复的别名 duplicate_alias

42710 重复的对象 duplicate_object

42702 模糊的字段 ambiguous_column

42725 模糊的函数 ambiguous_function

42P08 模糊的参数 ambiguous_parameter

42P09 模糊的别名 ambiguous_alias

42P10 非法字段引用 invalid_column_reference

42611 非法字段定义 invalid_column_definition

42P11 非法游标定义 invalid_cursor_definition

42P12 非法数据库定义 invalid_database_definition

42P13 非法函数定义 invalid_function_definition

42P14 非法预备语句定义 invalid_prepared_statement_definition

42P15 非法模式定义 invalid_schema_definition

42P16 非法表定义 invalid_table_definition

42P17 非法对象定义 invalid_object_definition

Class 44 — 违反 WITH CHECK 选项

44000 违反 WITH CHECK 选项 with_check_option_violation

相关问答

Q1: MySQL与PostgreSQL比较 哪个数据库更好

特性 MySQL PostgreSQL

实例 通过执行 MySQL 命令（mysqld）启动实例。一个实例可以管理一个或多个数据库。一台服务器可以运行多个 mysqld 实例。一个实例管理器可以监视 mysqld 的各个实例。

通过执行 Postmaster 进程（pg_ctl）启动实例。一个实例可以管理一个或多个数据库，这些数据库组成一个集群。集群是磁盘上的一个区域，这个区域在安装时初始化并由一个目录组成，所有数据都存储在这个目录中。使用 initdb 创建第一个数据库。一台机器上可以启动多个实例。

数据库 数据库是命名的对象集合，是与实例中的其他数据库分离的实体。一个 MySQL 实例中的所有数据库共享同一个系统编目。 数据库是命名的对象集合，每个数据库是与其他数据库分离的实体。每个数据库有自己的系统编目，但是所有数据库共享 pg_databases。

数据缓冲区 通过 innodb_buffer_pool_size 配置参数设置数据缓冲区。这个参数是内存缓冲区的字节数，InnoDB 使用这个缓冲区来缓存表的数据和索引。在专用的数据库服务器上，这个参数最高可以设置为机器物理内存量的 80%。 Shared_buffers 缓存。在默认情况下分配 64 个缓冲区。默认的块大小是 8K。可以通过设置 postgresql.conf 文件中的 shared_buffers 参数来更新缓冲区缓存。

数据库连接 客户机使用 CONNECT 或 USE 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。使用角色管理数据库中的用户和用户组。 客户机使用 connect 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。使用角色管理数据库中的用户和用户组。

身份验证 MySQL 在数据库级管理身份验证。 基本只支持密码认证。 PostgreSQL 支持丰富的认证方法：信任认证、口令认证、Kerberos 认证、基于 Ident 的认证、LDAP 认证、PAM 认证

加密 可以在表级指定密码来对数据进行加密。还可以使用 AES_ENCRYPT 和 AES_DECRYPT 函数对列数据进行加密和解密。可以通过 SSL 连接实现网络加密。 可以使用 pgcrypto 库中的函数对列进行加密/解密。可以通过 SSL 连接实现网络加密。

审计 可以对 querylog 执行 grep。 可以在表上使用 PL/pgSQL 触发器来进行审计。

查询解释 使用 EXPLAIN 命令查看查询的解释计划。 使用 EXPLAIN 命令查看查询的解释计划。

备份、恢复和日志 InnoDB 使用写前（write-ahead）日志记录。支持在线和离线完全备份以及崩溃和事务恢复。需要第三方软件才能支持热备份。 在数据目录的一个子目录中维护写前日志。支持在线和离线完全备份以及崩溃、时间点和事务恢复。 可以支持热备份。

JDBC 驱动程序 可以从 参考资料 下载 JDBC 驱动程序。 可以从 参考资料 下载 JDBC 驱动程序。

表类型 取决于存储引擎。例如，NDB 存储引擎支持分区表，内存引擎支持内存表。 支持临时表、常规表以及范围和列表类型的分区表。不支持哈希分区表。 由于PostgreSQL的表分区是通过表继承和规则系统完成了，所以可以实现更复杂的分区方式。

索引类型 取决于存储引擎。MyISAM：BTREE，InnoDB：BTREE。 支持 B-树、哈希、R-树和 Gist 索引。

约束 支持主键、外键、惟一和非空约束。对检查约束进行解析，但是不强制实施。 支持主键、外键、惟一、非空和检查约束。

存储过程和用户定义函数 支持 CREATE PROCEDURE 和 CREATE FUNCTION 语句。存储过程可以用 SQL 和 C++ 编写。用户定义函数可以用 SQL、C 和 C++ 编写。 没有单独的存储过程，都是通过函数实现的。用户定义函数可以用 PL/pgSQL（专用的过程语言）、PL/Tcl、PL/Perl、PL/Python 、SQL 和 C 编写。

触发器 支持行前触发器、行后触发器和语句触发器，触发器语句用过程语言复合语句编写。 支持行前触发器、行后触发器和语句触发器，触发器过程用 C 编写。

系统配置文件 my.conf Postgresql.conf

数据库配置 my.conf Postgresql.conf

客户机连接文件 my.conf pg_hba.conf

XML 支持 有限的 XML 支持。 有限的 XML 支持。

数据访问和管理服务器 OPTIMIZE TABLE —— 回收未使用的空间并消除数据文件的碎片

myisamchk -analyze —— 更新查询优化器所使用的统计数据（MyISAM 存储引擎）

mysql —— 命令行工具

MySQL Administrator —— 客户机 GUI 工具 Vacuum —— 回收未使用的空间

Analyze —— 更新查询优化器所使用的统计数据

psql —— 命令行工具

pgAdmin —— 客户机 GUI 工具

并发控制 支持表级和行级锁。InnoDB 存储引擎支持 READ_COMMITTED、READ_UNCOMMITTED、REPEATABLE_READ 和 SERIALIZABLE。使用 SET TRANSACTION ISOLATION LEVEL 语句在事务级设置隔离级别。 支持表级和行级锁。支持的 ANSI 隔离级别是 Read Committed（默认 —— 能看到查询启动时数据库的快照）和 Serialization（与 Repeatable Read 相似 —— 只能看到在事务启动之前提交的结果）。使用 SET TRANSACTION 语句在事务级设置隔离级别。使用 SET SESSION 在会话级进行设置。

MySQL相对于PostgreSQL的劣势：

MySQL

PostgreSQL

最重要的引擎InnoDB很早就由Oracle公司控制。目前整个MySQL数据库都由Oracle控制。

BSD协议，没有被大公司垄断。

对复杂查询的处理较弱，查询优化器不够成熟

很强大的查询优化器，支持很复杂的查询处理。

只有一种表连接类型:嵌套循环连接(nested-loop),不支持排序-合并连接(sort-merge join)与散列连接(hash join)。

都支持

性能优化工具与度量信息不足

提供了一些性能视图，可以方便的看到发生在一个表和索引上的select、delete、update、insert统计信息，也可以看到cache命中率。网上有一个开源的pgstatspack工具。

InnoDB的表和索引都是按相同的方式存储。也就是说表都是索引组织表。这一般要求主键不能太长而且插入时的主键最好是按顺序递增，否则对性能有很大影响。

不存在这个问题。

大部分查询只能使用表上的单一索引;在某些情况下，会存在使用多个索引的查询,但是查询优化器通常会低估其成本,它们常常比表扫描还要慢。

不存在这个问题

表增加列，基本上是重建表和索引，会花很长时间。

表增加列，只是在数据字典中增加表定义，不会重建表

存储过程与触发器的功能有限。可用来编写存储过程、触发器、计划事件以及存储函数的语言功能较弱

除支持pl/pgsql写存储过程，还支持perl、python、Tcl类型的存储过程：pl/perl，pl/python，pl/tcl。

也支持用C语言写存储过程。

不支持Sequence。

支持

不支持函数索引，只能在创建基于具体列的索引。

不支持物化视图。

支持函数索引，同时还支持部分数据索引，通过规则系统可以实现物化视图的功能。

执行计划并不是全局共享的, 仅仅在连接内部是共享的。

执行计划共享

MySQL支持的SQL语法(ANSI SQL标准)的很小一部分。不支持递归查询、通用表表达式（Oracle的with 语句）或者窗口函数（分析函数）。

都 支持

不支持用户自定义类型或域(domain)

支持。

对于时间、日期、间隔等时间类型没有秒以下级别的存储类型

可以精确到秒以下。

身份验证功能是完全内置的，不支持操作系统认证、PAM认证，不支持LDAP以及其它类似的外部身份验证功能。

支持OS认证、Kerberos 认证 、Ident 的认证、LDAP 认证、PAM 认证

不支持database link。有一种叫做Federated的存储引擎可以作为一个中转将查询语句传递到远程服务器的一个表上,不过,它功能很粗糙并且漏洞很多

有dblink，同时还有一个dbi-link的东西，可以连接到oracle和mysql上。

Mysql Cluster可能与你的想象有较大差异。开源的cluster软件较少。

复制(Replication)功能是异步的,并且有很大的局限性.例如,它是单线程的(single-threaded),因此一个处理能力更强的Slave的恢复速度也很难跟上处理能力相对较慢的Master.

有丰富的开源cluster软件支持。

explain看执行计划的结果简单。

explain返回丰富的信息。

类似于ALTER TABLE或CREATE TABLE一类的操作都是非事务性的.它们会提交未提交的事务，并且不能回滚也不能做灾难恢复

DDL也是有事务的。

PostgreSQL主要优势：

1. PostgreSQL完全免费，而且是BSD协议，如果你把PostgreSQL改一改，然后再拿去卖钱，也没有人管你，这一点很重要，这表明了PostgreSQL数据库不会被其它公司控制。oracle数据库不用说了，是商业数据库，不开放。而MySQL数据库虽然是开源的，但现在随着SUN被oracle公司收购，现在基本上被oracle公司控制，其实在SUN被收购之前，MySQL中最重要的InnoDB引擎也是被oracle公司控制的，而在MySQL中很多重要的数据都是放在InnoDB引擎中的，反正我们公司都是这样的。所以如果MySQL的市场范围与oracle数据库的市场范围冲突时，oracle公司必定会牺牲MySQL，这是毫无疑问的。

2. 与PostgreSQl配合的开源软件很多，有很多分布式集群软件，如pgpool、pgcluster、slony、plploxy等等，很容易做读写分离、负载均衡、数据水平拆分等方案，而这在MySQL下则比较困难。

3. PostgreSQL源代码写的很清晰，易读性比MySQL强太多了，怀疑MySQL的源代码被混淆过。所以很多公司都是基本PostgreSQL做二次开发的。

4. PostgreSQL在很多方面都比MySQL强，如复杂SQL的执行、存储过程、触发器、索引。同时PostgreSQL是多进程的，而MySQL是线程的，虽然并发不高时，MySQL处理速度快，但当并发高的时候，对于现在多核的单台机器上，MySQL的总体处理性能不如PostgreSQL，原因是MySQL的线程无法充分利用CPU的能力。

目前只想到这些，以后想到再添加，欢迎大家拍砖。

PostgreSQL与oracle或InnoDB的多版本实现的差别

PostgreSQL与oracle或InnoDB的多版本实现最大的区别在于最新版本和历史版本是否分离存储，PostgreSQL不分，而oracle和InnoDB分，而innodb也只是分离了数据,索引本身没有分开。

PostgreSQL的主要优势在于：

1. PostgreSQL没有回滚段，而oracle与innodb有回滚段，oracle与Innodb都有回滚段。对于oracle与Innodb来说，回滚段是非常重要的，回滚段损坏，会导致数据丢失，甚至数据库无法启动的严重问题。另由于PostgreSQL没有回滚段，旧数据都是记录在原先的文件中，所以当数据库异常crash后，恢复时，不会象oracle与Innodb数据库那样进行那么复杂的恢复，因为oracle与Innodb恢复时同步需要redo和undo。所以PostgreSQL数据库在出现异常crash后，数据库起不来的几率要比oracle和mysql小一些。

2. 由于旧的数据是直接记录在数据文件中，而不是回滚段中，所以不会象oracle那样经常报ora-01555错误。

3. 回滚可以很快完成，因为回滚并不删除数据，而oracle与Innodb，回滚时很复杂，在事务回滚时必须清理该事务所进行的修改，插入的记录要删除，更新的记录要更新回来(见row_undo函数)，同时回滚的过程也会再次产生大量的redo日志。

4. WAL日志要比oracle和Innodb简单，对于oracle不仅需要记录数据文件的变化，还要记录回滚段的变化。

PostgreSQL的多版本的主要劣势在于：

1、最新版本和历史版本不分离存储，导致清理老旧版本需要作更多的扫描，代价比较大，但一般的数据库都有高峰期，如果我们合理安排VACUUM，这也不是很大的问题，而且在PostgreSQL9.0中VACUUM进一步被加强了。

2、由于索引中完全没有版本信息，不能实现Coverage index scan，即查询只扫描索引，直接从索引中返回所需的属性，还需要访问表。而oracle与Innodb则可以;

进程模式与线程模式的对比

PostgreSQL和oracle是进程模式，MySQL是线程模式。

进程模式对多CPU利用率比较高。

进程模式共享数据需要用到共享内存，而线程模式数据本身就是在进程空间内都是共享的，不同线程访问只需要控制好线程之间的同步。

线程模式对资源消耗比较少。

所以MySQL能支持远比oracle多的更多的连接。

对于PostgreSQL的来说，如果不使用连接池软件，也存在这个问题，但PostgreSQL中有优秀的连接池软件软件，如pgbouncer和pgpool，所以通过连接池也可以支持很多的连接。

Q2: postgresql 触发器 SQLserver 触发器

SQL server 触发器 没有 BEFORE 啊， 只有 AFTER.

对于 postgresql， 如果 befroe 异常了， 那就回滚结束掉了， after 没有执行的意义了。

after 触发器执行异常了， 对于 postgresql 和 SQL server ， 那都是要回滚的。

Q3: 有用PostgreSQL的吗，哪个package好用一些

特性 MySQL PostgreSQL

实例 通过执行 MySQL 命令（mysqld）启动实例。一个实例可以管理一个或多个数据库。一台服务器可以运行多个 mysqld 实例。一个实例管理器可以监视 mysqld 的各个实例。

通过执行 Postmaster 进程（pg_ctl）启动实例。一个实例可以管理一个或多个数据库，这些数据库组成一个集群。集群是磁盘上的一个区域，这个区域在安装时初始化并由一个目录组成，所有数据都存储在这个目录中。使用 initdb 创建第一个数据库。一台机器上可以启动多个实例。

数据库 数据库是命名的对象集合，是与实例中的其他数据库分离的实体。一个 MySQL 实例中的所有数据库共享同一个系统编目。 数据库是命名的对象集合，每个数据库是与其他数据库分离的实体。每个数据库有自己的系统编目，但是所有数据库共享 pg_databases。

数据缓冲区 通过 innodb_buffer_pool_size 配置参数设置数据缓冲区。这个参数是内存缓冲区的字节数，InnoDB 使用这个缓冲区来缓存表的数据和索引。在专用的数据库服务器上，这个参数最高可以设置为机器物理内存量的 80%。 Shared_buffers 缓存。在默认情况下分配 64 个缓冲区。默认的块大小是 8K。可以通过设置 postgresql.conf 文件中的 shared_buffers 参数来更新缓冲区缓存。

数据库连接 客户机使用 CONNECT 或 USE 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。使用角色管理数据库中的用户和用户组。 客户机使用 connect 语句连接数据库，这时要指定数据库名，还可以指定用户 id 和密码。使用角色管理数据库中的用户和用户组。

身份验证 MySQL 在数据库级管理身份验证。 基本只支持密码认证。 PostgreSQL 支持丰富的认证方法：信任认证、口令认证、Kerberos 认证、基于 Ident 的认证、LDAP 认证、PAM 认证

加密 可以在表级指定密码来对数据进行加密。还可以使用 AES_ENCRYPT 和 AES_DECRYPT 函数对列数据进行加密和解密。可以通过 SSL 连接实现网络加密。 可以使用 pgcrypto 库中的函数对列进行加密/解密。可以通过 SSL 连接实现网络加密。

审计 可以对 querylog 执行 grep。 可以在表上使用 PL/pgSQL 触发器来进行审计。

查询解释 使用 EXPLAIN 命令查看查询的解释计划。 使用 EXPLAIN 命令查看查询的解释计划。

备份、恢复和日志 InnoDB 使用写前（write-ahead）日志记录。支持在线和离线完全备份以及崩溃和事务恢复。需要第三方软件才能支持热备份。 在数据目录的一个子目录中维护写前日志。支持在线和离线完全备份以及崩溃、时间点和事务恢复。 可以支持热备份。

JDBC 驱动程序 可以从 参考资料 下载 JDBC 驱动程序。 可以从 参考资料 下载 JDBC 驱动程序。

表类型 取决于存储引擎。例如，NDB 存储引擎支持分区表，内存引擎支持内存表。 支持临时表、常规表以及范围和列表类型的分区表。不支持哈希分区表。 由于PostgreSQL的表分区是通过表继承和规则系统完成了，所以可以实现更复杂的分区方式。

索引类型 取决于存储引擎。MyISAM：BTREE，InnoDB：BTREE。 支持 B-树、哈希、R-树和 Gist 索引。

约束 支持主键、外键、惟一和非空约束。对检查约束进行解析，但是不强制实施。 支持主键、外键、惟一、非空和检查约束。

存储过程和用户定义函数 支持 CREATE PROCEDURE 和 CREATE FUNCTION 语句。存储过程可以用 SQL 和 C++ 编写。用户定义函数可以用 SQL、C 和 C++ 编写。 没有单独的存储过程，都是通过函数实现的。用户定义函数可以用 PL/pgSQL（专用的过程语言）、PL/Tcl、PL/Perl、PL/Python 、SQL 和 C 编写。

触发器 支持行前触发器、行后触发器和语句触发器，触发器语句用过程语言复合语句编写。 支持行前触发器、行后触发器和语句触发器，触发器过程用 C 编写。

系统配置文件 my.conf Postgresql.conf

数据库配置 my.conf Postgresql.conf

客户机连接文件 my.conf pg_hba.conf

XML 支持 有限的 XML 支持。 有限的 XML 支持。

数据访问和管理服务器 OPTIMIZE TABLE —— 回收未使用的空间并消除数据文件的碎片

myisamchk -analyze —— 更新查询优化器所使用的统计数据（MyISAM 存储引擎）

mysql —— 命令行工具

MySQL Administrator —— 客户机 GUI 工具 Vacuum —— 回收未使用的空间

Analyze —— 更新查询优化器所使用的统计数据

psql —— 命令行工具

pgAdmin —— 客户机 GUI 工具

并发控制 支持表级和行级锁。InnoDB 存储引擎支持 READ_COMMITTED、READ_UNCOMMITTED、REPEATABLE_READ 和 SERIALIZABLE。使用 SET TRANSACTION ISOLATION LEVEL 语句在事务级设置隔离级别。 支持表级和行级锁。支持的 ANSI 隔离级别是 Read Committed（默认 —— 能看到查询启动时数据库的快照）和 Serialization（与 Repeatable Read 相似 —— 只能看到在事务启动之前提交的结果）。使用 SET TRANSACTION 语句在事务级设置隔离级别。使用 SET SESSION 在会话级进行设置。

MySQL相对于PostgreSQL的劣势：

MySQL

PostgreSQL

最重要的引擎InnoDB很早就由Oracle公司控制。目前整个MySQL数据库都由Oracle控制。

BSD协议，没有被大公司垄断。

对复杂查询的处理较弱，查询优化器不够成熟

很强大的查询优化器，支持很复杂的查询处理。

只有一种表连接类型:嵌套循环连接(nested-loop),不支持排序-合并连接(sort-merge join)与散列连接(hash join)。

都支持

性能优化工具与度量信息不足

提供了一些性能视图，可以方便的看到发生在一个表和索引上的select、delete、update、insert统计信息，也可以看到cache命中率。网上有一个开源的pgstatspack工具。

InnoDB的表和索引都是按相同的方式存储。也就是说表都是索引组织表。这一般要求主键不能太长而且插入时的主键最好是按顺序递增，否则对性能有很大影响。

不存在这个问题。

大部分查询只能使用表上的单一索引;在某些情况下，会存在使用多个索引的查询,但是查询优化器通常会低估其成本,它们常常比表扫描还要慢。

不存在这个问题

表增加列，基本上是重建表和索引，会花很长时间。

表增加列，只是在数据字典中增加表定义，不会重建表

存储过程与触发器的功能有限。可用来编写存储过程、触发器、计划事件以及存储函数的语言功能较弱

除支持pl/pgsql写存储过程，还支持perl、python、Tcl类型的存储过程：pl/perl，pl/python，pl/tcl。

也支持用C语言写存储过程。

不支持Sequence。

支持

不支持函数索引，只能在创建基于具体列的索引。

不支持物化视图。

支持函数索引，同时还支持部分数据索引，通过规则系统可以实现物化视图的功能。

执行计划并不是全局共享的, 仅仅在连接内部是共享的。

执行计划共享

MySQL支持的SQL语法(ANSI SQL标准)的很小一部分。不支持递归查询、通用表表达式（Oracle的with 语句）或者窗口函数（分析函数）。

都 支持

不支持用户自定义类型或域(domain)

支持。

对于时间、日期、间隔等时间类型没有秒以下级别的存储类型

可以精确到秒以下。

身份验证功能是完全内置的，不支持操作系统认证、PAM认证，不支持LDAP以及其它类似的外部身份验证功能。

支持OS认证、Kerberos 认证 、Ident 的认证、LDAP 认证、PAM 认证

不支持database link。有一种叫做Federated的存储引擎可以作为一个中转将查询语句传递到远程服务器的一个表上,不过,它功能很粗糙并且漏洞很多

有dblink，同时还有一个dbi-link的东西，可以连接到oracle和mysql上。

Mysql Cluster可能与你的想象有较大差异。开源的cluster软件较少。

复制(Replication)功能是异步的,并且有很大的局限性.例如,它是单线程的(single-threaded),因此一个处理能力更强的Slave的恢复速度也很难跟上处理能力相对较慢的Master.

有丰富的开源cluster软件支持。

explain看执行计划的结果简单。

explain返回丰富的信息。

类似于ALTER TABLE或CREATE TABLE一类的操作都是非事务性的.它们会提交未提交的事务，并且不能回滚也不能做灾难恢复

DDL也是有事务的。

PostgreSQL主要优势：

1. PostgreSQL完全免费，而且是BSD协议，如果你把PostgreSQL改一改，然后再拿去卖钱，也没有人管你，这一点很重要，这表明了PostgreSQL数据库不会被其它公司控制。oracle数据库不用说了，是商业数据库，不开放。而MySQL数据库虽然是开源的，但现在随着SUN被oracle公司收购，现在基本上被oracle公司控制，其实在SUN被收购之前，MySQL中最重要的InnoDB引擎也是被oracle公司控制的，而在MySQL中很多重要的数据都是放在InnoDB引擎中的，反正我们公司都是这样的。所以如果MySQL的市场范围与oracle数据库的市场范围冲突时，oracle公司必定会牺牲MySQL，这是毫无疑问的。

2. 与PostgreSQl配合的开源软件很多，有很多分布式集群软件，如pgpool、pgcluster、slony、plploxy等等，很容易做读写分离、负载均衡、数据水平拆分等方案，而这在MySQL下则比较困难。

3. PostgreSQL源代码写的很清晰，易读性比MySQL强太多了，怀疑MySQL的源代码被混淆过。所以很多公司都是基本PostgreSQL做二次开发的。

4. PostgreSQL在很多方面都比MySQL强，如复杂SQL的执行、存储过程、触发器、索引。同时PostgreSQL是多进程的，而MySQL是线程的，虽然并发不高时，MySQL处理速度快，但当并发高的时候，对于现在多核的单台机器上，MySQL的总体处理性能不如PostgreSQL，原因是MySQL的线程无法充分利用CPU的能力。

目前只想到这些，以后想到再添加，欢迎大家拍砖。

PostgreSQL与oracle或InnoDB的多版本实现的差别

PostgreSQL与oracle或InnoDB的多版本实现最大的区别在于最新版本和历史版本是否分离存储，PostgreSQL不分，而oracle和InnoDB分，而innodb也只是分离了数据,索引本身没有分开。

PostgreSQL的主要优势在于：

1. PostgreSQL没有回滚段，而oracle与innodb有回滚段，oracle与Innodb都有回滚段。对于oracle与Innodb来说，回滚段是非常重要的，回滚段损坏，会导致数据丢失，甚至数据库无法启动的严重问题。另由于PostgreSQL没有回滚段，旧数据都是记录在原先的文件中，所以当数据库异常crash后，恢复时，不会象oracle与Innodb数据库那样进行那么复杂的恢复，因为oracle与Innodb恢复时同步需要redo和undo。所以PostgreSQL数据库在出现异常crash后，数据库起不来的几率要比oracle和mysql小一些。

2. 由于旧的数据是直接记录在数据文件中，而不是回滚段中，所以不会象oracle那样经常报ora-01555错误。

3. 回滚可以很快完成，因为回滚并不删除数据，而oracle与Innodb，回滚时很复杂，在事务回滚时必须清理该事务所进行的修改，插入的记录要删除，更新的记录要更新回来(见row_undo函数)，同时回滚的过程也会再次产生大量的redo日志。

4. WAL日志要比oracle和Innodb简单，对于oracle不仅需要记录数据文件的变化，还要记录回滚段的变化。

PostgreSQL的多版本的主要劣势在于：

1、最新版本和历史版本不分离存储，导致清理老旧版本需要作更多的扫描，代价比较大，但一般的数据库都有高峰期，如果我们合理安排VACUUM，这也不是很大的问题，而且在PostgreSQL9.0中VACUUM进一步被加强了。

2、由于索引中完全没有版本信息，不能实现Coverage index scan，即查询只扫描索引，直接从索引中返回所需的属性，还需要访问表。而oracle与Innodb则可以;

进程模式与线程模式的对比

PostgreSQL和oracle是进程模式，MySQL是线程模式。

进程模式对多CPU利用率比较高。

进程模式共享数据需要用到共享内存，而线程模式数据本身就是在进程空间内都是共享的，不同线程访问只需要控制好线程之间的同步。

线程模式对资源消耗比较少。

所以MySQL能支持远比oracle多的更多的连接。

对于PostgreSQL的来说，如果不使用连接池软件，也存在这个问题，但PostgreSQL中有优秀的连接池软件软件，如pgbouncer和pgpool，所以通过连接池也可以支持很多的连接。

堆表与索引组织表的的对比

Oracle支持堆表，也支持索引组织表

PostgreSQL只支持堆表，不支持索引组织表

Innodb只支持索引组织表

索引组织表的优势：

表内的数据就是按索引的方式组织，数据是有序的，如果数据都是按主键来访问，那么访问数据比较快。而堆表，按主键访问数据时，是需要先按主键索引找到数据的物理位置。

索引组织表的劣势：

索引组织表中上再加其它的索引时，其它的索引记录的数据位置不再是物理位置，而是主键值，所以对于索引组织表来说，主键的值不能太大，否则占用的空间比较大。

对于索引组织表来说，如果每次在中间插入数据，可能会导致索引分裂，索引分裂会大大降低插入的性能。所以对于使用innodb来说，我们一般最好让主键是一个无意义的序列，这样插入每次都发生在最后，以避免这个问题。

由于索引组织表是按一个索引树，一般它访问数据块必须按数据块之间的关系进行访问，而不是按物理块的访问数据的，所以当做全表扫描时要比堆表慢很多，这可能在OLTP中不明显，但在数据仓库的应用中可能是一个问题。

　 PostgreSQL9.0中的特色功能：

PostgreSQL中的Hot Standby功能

也就是standby在应用日志同步时，还可以提供只读服务，这对做读写分离很有用。这个功能是oracle11g才有的功能。

PostgreSQL异步提交（Asynchronous Commit）的功能：

　 这个功能oracle中也是到oracle11g R2才有的功能。因为在很多应用场景中，当宕机时是允许丢失少量数据的，这个功能在这样的场景中就特别合适。在PostgreSQL9.0中把synchronous_commit设置为false就打开了这个功能。需要注意的是，虽然设置为了异步提交，当主机宕机时，PostgreSQL只会丢失少量数据，异步提交并不会导致数据损坏而数据库起不来的情况。MySQL中没有听说过有这个功能。

PostgreSQL中索引的特色功能：

PostgreSQL中可以有部分索引，也就是只能表中的部分数据做索引，create index 可以带where 条件。同时PostgreSQL中的索引可以反向扫描，所以在PostgreSQL中可以不必建专门的降序索引了。

Q4: 为什么postgrelsql的性能没有mysql好

一、 PostgreSQL 的稳定性极强， Innodb 等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足进步，然而很多 MySQL 用户都遇到过Server级的数据库丢失的场景——mysql系统库是MyISAM的，相比之下，PG数据库这方面要好一些。

二、任何系统都有它的性能极限，在高并发读写，负载逼近极限下，PG的性能指标仍可以维持双曲线甚至对数曲线，到顶峰之后不再下降，而 MySQL 明显出现一个波峰后下滑（5.5版本之后，在企业级版本中有个插件可以改善很多，不过需要付费）。

三、PG 多年来在 GIS 领域处于优势地位，因为它有丰富的几何类型，实际上不止几何类型，PG有大量字典、数组、bitmap 等数据类型，相比之下mysql就差很多，instagram就是因为PG的空间数据库扩展POSTGIS远远强于MYSQL的my spatial而采用PGSQL的。

四、PG 的“无锁定”特性非常突出，甚至包括 vacuum 这样的整理数据空间的操作，这个和PGSQL的MVCC实现有关系。

五、PG 的可以使用函数和条件索引，这使得PG数据库的调优非常灵活，mysql就没有这个功能，条件索引在web应用中很重要。

六、PG有极其强悍的 SQL 编程能力（9.x 图灵完备，支持递归！），有非常丰富的统计函数和统计语法支持，比如分析函数（ORACLE的叫法，PG里叫window函数），还可以用多种语言来写存储过程，对于R的支持也很好。这一点上MYSQL就差的很远，很多分析功能都不支持，腾讯内部数据存储主要是MYSQL，但是数据分析主要是HADOOP+PGSQL。

七、PG 的有多种集群架构可以选择，plproxy 可以支持语句级的镜像或分片，slony 可以进行字段级的同步设置，standby 可以构建WAL文件级或流式的读写分离集群，同步频率和集群策略调整方便，操作非常简单。

八、一般关系型数据库的字符串有限定长度8k左右，无限长 TEXT 类型的功能受限，只能作为外部大数据访问。而 PG 的 TEXT 类型可以直接访问，SQL语法内置正则表达式，可以索引，还可以全文检索，或使用xml xpath。用PG的话，文档数据库都可以省了。

九，对于WEB应用来说，复制的特性很重要，mysql到现在也是异步复制，pgsql可以做到同步，异步，半同步复制。还有mysql的同步是基于binlog复制，类似oracle golden gate,是基于stream的复制，做到同步很困难，这种方式更加适合异地复制，pgsql的复制基于wal，可以做到同步复制。同时，pgsql还提供stream复制。

十，pgsql对于numa架构的支持比mysql强一些，比MYSQL对于读的性能更好一些，pgsql提交可以完全异步，而mysql的内存表不够实用（因为表锁的原因）

最后说一下我感觉 PG 不如 MySQL 的地方。

第一，MySQL有一些实用的运维支持，如 slow-query.log ，这个pg肯定可以定制出来，但是如果可以配置使用就更好了。

第二是mysql的innodb引擎，可以充分优化利用系统所有内存，超大内存下PG对内存使用的不那么充分，

第三点，MySQL的复制可以用多级从库，但是在9.2之前，PGSQL不能用从库带从库。

第四点，从测试结果上看，mysql 5.5的性能提升很大，单机性能强于pgsql，5.6应该会强更多.

第五点，对于web应用来说,mysql 5.6 的内置MC API功能很好用，PGSQL差一些。

另外一些：

pgsql和mysql都是背后有商业公司，而且都不是一个公司。大部分开发者，都是拿工资的。

说mysql的执行速度比pgsql快很多是不对的，速度接近，而且很多时候取决于你的配置。

对于存储过程，函数，视图之类的功能，现在两个数据库都可以支持了。

另外多线程架构和多进程架构之间没有绝对的好坏，oracle在unix上是多进程架构，在windows上是多线程架构。

很多pg应用也是24/7的应用，比如skype. 最近几个版本VACUUM基本不影响PGSQL 运行，8.0之后的PGSQL不需要cygwin就可以在windows上运行。

至于说对于事务的支持，mysql和pgsql都没有问题。

Q5: postgresql 为什么没有undo回滚

查看回滚段名称及大小 select segment_name, tablespace_name, r.status, (initial_extent/1024) InitialExtent,(next_extent/1024) NextExtent, max_extents, v.curext CurExtent From dba_rollback_segs r, v$rollstat v Where r.segment_id =