简介
.NET+PostgreSQL(简称 PG)这个组合我已经用了蛮长的一段时间, 感觉还是挺不错的. 不过大多数人说起. NET 平台, 还是会想起跟它 "原汁原味" 配套的 Microsoft SQL Server(简称 MSSQL), 其实没有 MSSQL 也没有任何问题, 甚至没有 Windows Server 都没问题, 谁说用. NET 就一定要上微软全家桶? 这都什么年代了......
PG 和 MSSQL 的具体比较我就不详细展开了, 自行搜一下, 这种比较分析文章很多. 应该说两个 RDBMS 各有特色, MSSQL 工具集庞大(大多我们都用不到或不会用), 安装较为麻烦, PG 比较小巧, 但功能也不弱, 我们要的它都有, 性能方面我做过简单的增删查改的测试, 两者看不出什么明显差别, MSSQL 貌似最近才提供了 Linux 版, 而 PG 天生跨平台, MSSQL 的授权费似乎不低(没深究),PG 开源免费, 对比较抠的客户来说, 是不太愿意另外花钱买一套 MSSQL 的, PG 就是非常不错的选择.
希望你看完本文之后, 也同我一样觉得. NET + PostgreSQL,Rocks! 没问题的了.
PG 的版本
PG 应该选择什么版本? Linux 还是 Windows? 当然是首选 Linux, 但开发环境无所谓, 你在你自己的工作电脑上安装一个 Windows 版也是没问题的, 有人说两者性能差距较大, Linux 明显要好于 Windows, 但我有做过测试, 这个并没有被证实如此, 然而, 我还是推荐 Linux, 一来安装简便, 二来配置简单(命令行界面用起来感觉比较一致), 三来方便写一些脚本来实现数据库定时备份之类的. 其实你并不需要担心安装了 PG 后电脑会变慢, 我完全感觉不出来, 它是个安静的乖萌宠, 你不叫它, 它就静静坐在那里, 我的 Windows 电脑上也安装了一个 PG, 我经常用它来做一些脚本测试或试验. 另外, 现在也能在 Windows 下直接安装 Linux 版本的 PG 了, WSL 了解下?
PG 有很多的版本, 现在的最新版是 10.4, 它前面的版本是 9.6.x, 嗯? 有点奇怪不是? 10.4 只有 "两段", 而 9.6.x 有三段, 其实之前一直是三段, 9 表示大版本, 6 表示中版本, 后面是小版本, 小版本只有小的功能改进, 不会对数据格式造成任何影响, 就是说, 你的 PG 从 9.6.1 升级到 9.6.9, 你直接升了把旧程序替换掉就是, 保证没有任何问题. 但如果你之前的版本是 9.5.3, 要升级到 9.6.9, 那就不行了, 因为中间版本变了, 你需要用一个迁移工具去把你的旧的数据格式转为新的方可, 那对 10.4 这个版本而言, 哪个是大版本, 哪个是中版本, 哪个是小版本? 这里我感觉有点不连贯, PG 在从 9 升级到 10 的时候, 似乎丢掉了 "大版本",10 虽然是 9 的后继, 但它应该算一个中版本, 所以, 10.1 升级到 10.4 是不用转换数据的, 直接升级程序即可. 那 PG 的下一个中版本是什么? 没错, 是 11, 再下一个应该就是 12 了. 软件这个东西, 如果你没什么历史包袱, 我觉得直接选择最新的, 比如选择 10.4, 将来升级 10.5,10.6 的时候也简单.
说点额外的, PG10 是去年 (2017) 正式推出的, 距离现在都不到一年, 刚出来的时候我就想, 这个 "重大升级"(想想看 iPhone X,Mac OS X,10 这个数字是很特别不是?)能不能带来性能上的大提升呢? 我试了一下, 结论是: 没有. 确实它的升级文档上也没提及到性能有什么明显提升, 它主要增加了对表分区的原生支持, 表分区, 就是你的表中的数据的数量很多很多的时候, 通过表分区来提高读写速度, 至于表要多大才推荐分区呢? PG 的官方文档说是: 如果表的尺寸赶上了你主机的内存的时候, 可以考虑表分区...... 所以, 对于那些只有区区几千万行或几百万行数据的表, 你确定要分区吗?
Npgsql
要用. NET 使用 PG, 就得用 nuget 引入 Npgsql 这个包, 这是它的官方网站: http://www.npgsql.org/, 完全开源, 它其实就是针对 PG 数据库的 ADO.NET 引擎(ADO.NET Data Provider). 这里是它的帮助手册: http://www.npgsql.org/doc/index.html
这里边并没有太多难点, 你所需要做的, 就是安装好你的 PG 数据库(Windows 版 / Linux 版都行, 没有什么影响), 然后创建一个. NET 项目(我推荐使用. NET Core), 引入 Npgsql, 然后照着说明手册上的简单例子入一下门即可.
本文当然不会具体带你如何开始使用 SELECT 语句, 下面主要讲述在使用过程中, 我们所克服的一些困难或踩过的坑.
NVARCHAR 呢?
MSSQL 中用得最多的的文本类型是 NVARCHAR, 这是一个带长度限制的文本类型, 对应地, PG 中有 VARCHAR, 这样用没问题, 但 PG 中的文本类型其实跟 MSSQL 中的文本类型是有点区别的, PG 的文本基本上可以认为不限长度, VARCHAR 及 TEXT 对 PG 内部来说, 并没有什么差别, 只是在写入的时候, VARCHAR 会检查一下长度, 所以性能上来看, VARCHAR 并不比 TEXT 要快, 较真的话可能还会慢点, 因为它要检查长度嘛, 所以你在设计数据库的时候可以无脑地将所有文本类型设置为 TEXT(或后面提到的 CITEXT), 长度检查工作放在业务系统中去做即可.
想要大小写不敏感怎么办?
绝大多数时候, 我们都是希望大小写不敏感的, 大小写敏感反倒会带来很多困惑, 查询不出, 或者系统中存在同名的用户, 一个叫 John 另一个叫 john,MSSQL 可以在创建库的时候指定大小写不敏感, 而 PG 似乎没有这样的功能, 它需要借助一个额外的组件, 叫 CITEXT,CI 的意思就是 Case Insensitive. 要使用 CITEXT 组件, 你需要安装 postgresql10-contrib 包(假设你安装的是 PG10, 如果不是的话你去找对应的包), 再使用以下命令创建 CITEXT 类型:
CREATE EXTENSION IF NOT EXISTS CITEXT WITH SCHEMA public;
注: 一个 database 只需要执行一次这个命令即可
如果你使用的是 psql 客户端连上去使用 PG 的话, 这时候已经 OK 了, 你会发现 CITEXT 的字段已经是大小写不敏感了, 但如果你用的是 Npgsql 用代码去访问 PG 的话, CITEXT 似乎没生效, 其实原因是这样的, CITEXT 并不是 PG 的原生类型, 你在用查询语句的时候, 需要在参数后面加上 "::CITEXT" 显式地告诉 PG, 你的参数是 CITEXT 类型, 例子如下:
SELECT * FROM test_table WHERE test_name=@TextName::CITEXT AND category=@Category::CITEXT
嗯, 我承认是有点麻烦, 但习惯就好, 我现在还不知道有什么更佳方法.
使用 CITEXT 时候出现 NotSupportedException
这个异常的呈现内容大致如此:
System.NotSupportedException: The field 'application_id' has a type currently unknown to Npgsql (OID 41000). You can retrieve it as a string by marking it as unknown, please see the FAQ.
在 Npgsql.NpgsqlDataReader.GetValue(Int32 ordinal)
在 Npgsql.NpgsqlDataReader.get_Item(Int32 ordinal)
......
这个错误对我们而言, 曾经像个幽灵似的, 时不时出现, 出现的时候重启一下服务程序就好了, 不再出现, 然后过几个星期或者几个月又出现, 有时候一天出现多次也不是没有可能. 最后是到 github 上面求助才最终搞懂了原因. 链接: https://github.com/npgsql/npgsql/issues/1635
简单地说, PG 对各种数据类型, 是有一个内部的 ID 值的(叫 oid),Npgsql 在第一次连接数据库的时候, 会获取到这些 oid 值并缓存起来, 对于 PG 的内部类型, 如 INT 什么的, 这些 oid 值是固定的, 但对于 CITEXT 似乎不是这样, 因为 CITEXT 这个类型是我门自己用 CREATE EXTENSION 命令创建的(请参考本文前面内容), 创建的时候确定其 oid. 我们在还原数据库的时候, 也相当于重新创建了 CITEXT 类型, 这样会导致 CITEXT 的 oid 发生变化, 但 Npgsql 并不知道, 所以就出现了这个异常. 我们在开发过程中常常需要做还原数据库的动作, 所以导致了这个问题的发生.
解决方法 1, 当数据库还原了之后, 调用 NpgsqlConnection.ReloadTypes(), 刷新各类型 oid, 但这个很难, 因为还原数据库都是手动操作, 做完之后打开网页, 在上面点一下通知程序吗?
解决方法 2, 重启一下程序. 这个其实跟解决方法 1 差不多, 只不过不需要写什么额外代码, 考虑到还原数据库这个动作其实也不是太频繁, 只是在开发环境中做, 所以重启就重启吧, 我们现在就干, 规定还原数据库后自己重启下服务程序.(写个脚本干这个事情很简单)
使用事务进行大量操作时候导致程序崩溃
这个问题我同样到 github 上求助了, 链接: https://github.com/npgsql/npgsql/issues/1838
这个问题比前面的问题可能更严重, 因为我很可能捕捉不到异常(就是说有时候可以捕捉到, 有时候不行), 程序直接崩溃了, 对于一个. NET 程序来说, 这是很不应该的事情, 即便我没单独写 try-catch, 程序的最外层异常处理器应该也能捕捉到相关的 Exception 并 log 对不? 但偏不, 没有 log, 也捕捉不到. 所以至今我怀疑这是一个. NET 的 bug, 可能跟 Npgsql 并没有关系.
问题的原因如 github 上所描述, 是找到了, 但却无法从根本上修正, 这个问题其实是个简单的 "事务超时" 问题.
我们的程序在第一次启动的时候会初始化数据库的表, 插入大量的初始化数据, 由于我们公司的开发环境比较特殊, 数据库延迟十分高, 所以导致插入速度很慢, 每条插入耗时可高达几十毫秒,(生产环境并没有这个问题)这样一万多条数据下来就导致了事务超时(事务超时默认时间是 1 分钟). 解决方法当然很明显了: 初始化的时候, 临时增加 TransactionScope 的超时值, 增加到 10 分钟, 这样总归没问题了.
类似这种问题我们只能通过一些外部的 workaround 来预防, 很难从根本上解决.
55000: 禁用已准备好的事务
这又是一个有点棘手的事情, 首先是这个中文翻译得很不好, 这是一条数据库抛出来的出错信息, 它的英文是 "Prepared transactions are disabled", 其正确的中文翻译我觉得应该是: 预处理事务已被禁用. 唉, 所以我说为什么要英文版, 如果提示中文, 想在网上找答案都会多些障碍.
对事务的使用, 这里有个简单的例子:
- using (NpgsqlConnection conn = new NpgsqlConnection(connectionStr)) {
- conn.Open();
- using (TransactionScope ts = new TransactionScope()) {
- conn.EnlistTransaction(Transaction.Current);
- //SQLs...
- }
- ts.Complete();
- }
- }
什么叫 "预处理事务"? 其实很简单, 就是 "事务包事务", 就是可以分步提交的事务, 比如我先开启了一个事务 A, 在这个事务中我又开启了一个事务 B,B 提交, A 再提交. PG 对于预处理事务是默认关闭的, 当然了, 你可以打开它, 编辑配置文件 postgresql.conf, 把 max_prepared_transactions 改为 100(默认是 0,0 表示禁用), 重启 PG 服务即可.
但你确定你真的用得到预处理事务吗? 我看下来我们是用不到的, 但为什么出现这个问题?-- 还是我们程序写得有问题, 即便你从单个方法上看不出来事务包事务. 以下两种场景可能会出现 "预处理事务":
1, 我创建了一个方法 A 访问数据库, 这个方法可能会被其它方法调用, 所以它有个 DbConnection 类型的参数, 表示调用者负责打开数据库连接传递过来, 而 A 里面开启了事务, 而调用者并不知情, 也开启了事务, 形成预处理事务
2, 这种情况更隐晦些, 数据库连接字符串, 如: Host=192.168.1.101; Username=postgres; Password=123456; Database=testdb; Enlist=true, 在后面有个叫 Enlist 的参数为 true, 这表示这个连接在打开的时候, 会自动 Enlist 到当前执行上下文的 Transaction 中去, 如果当前执行上下文中打开了事务 (从代码上看包含在了 using(TransactionScope) 中), 那这个数据库连接就自动 Enlist 上去了, 再考虑这样的场景: A 方法会自己打开数据库连接去查询点什么东西, B 方法也会访问数据库, 且 B 方法会使用事务, 事务中调用了 A 方法, A 方法打开数据库连接的时候发现当前执行上下文中存在 Transaction, 于是自动 Enlist 上去了, 不经意间形成了预处理事务, 且还是 "分布式" 的(A 和 B 打开的可能是不同的数据库连接), 这种情况应该并不是你所需要的
那我们应该怎么做? 下面是我的做法:
1,max_prepared_transactions 还是设置为 0, 关掉, 因为我们真用不到, 如果用得到, 那就是我们代码写错了, 所以一旦出现 "禁用已准备好的事务" 这个异常, 就回去检查代码
2, 把 Enlist=true 在数据库连接字符串中去掉, 这么一来, 每次使用事务都需要显式地调用 conn.EnlistTransaction(Transaction.Current), 虽然对了一行代码, 但语义更明确, 也不用考虑到底是 TransactionScope 包 DbConnection 或反过来 DbConnection 包 TransactionScope
3, 规范化我们的数据库访问代码, 明确哪些是需要事务哪些是不需要的, 在各个方法的注释上注明
40001: 由于多个事务间的读 / 写依赖而无法串行访问
它对应的英文是: Cound not serialize access due to read/write dependencies among transactions, 这个应该怎么理解呢? 其实了解数据库事务隔离级别的人对这个应该不会陌生..NET 的 TransactionScope 默认使用的是事务隔离级别中的最高级别 --Serializable(可序列化). 这个级别最大程度上确保了数据的一致性, 但代价也挺高, 一来速度较慢, 二来很容易出现 "事务间读 / 写依赖", 就是这个错误了, 举个简单的例子:
A,B 两个事务, 同时访问 test 表, 各自插入 100 条数据, 它们插入数据完毕后都需要 sum 一下这个表的总条数然后写入另一张表中去, 这就会出问题了, 因为不管对 A 或者对 B, 他们在事务开启的时候对方的事务都没有 commit, 他们对 test 表的视图其实是一个事务开始时刻的 "快照", 而在事务提交前, 它们 insert 的数据对对方其实是不可见的, 所以如果让 A 和 B 这两个事务都成功的话, sum 出来的数据肯定会有不正确的, 这就所谓 "数据不一致". 所以数据库会让 A,B 中的一个成功, 而另一个失败, 并抛出这个异常.
所以, 这是个 "正常的错误", 按常规的业务逻辑来说, 应该很少会出现, 如果真的出现, 且频繁出现, 那需要考虑下是不是业务逻辑设计得不太合理, 看看能不能从设计上避免这个问题, 如果业务逻辑一定如此, 那可以用下面的方法尝试一下:
1, 将这种并行事务用客户端代码排个队, 弄个线程安全队列, 逐个执行, 这样速度会慢点, 但确保了每个事务都能成功
2, 捕捉这个异常, 然后自动重试, 其实这也是数据库推荐的正统的做法
3, 降低事务隔离级别, 这个可能会出现问题, 也可能不出现, 这完全取决于你的业务, 关于事务隔离级别, 这是个蛮大的话题, 我考虑适当时候再写一篇文章
4, 对于极少出现的频次来说, 可以不处理, 仅仅需要捕捉这个异常类型, 然后提示用户重试即可, 很多网站貌似都这么干的
总结
有时间的话我会另外开一篇文章来写写 PG 的一些常规用法, 如热备冷备还原维护等, 但不太能保证什么时候能写出来.
暂时先总结那么多, 谁如果有这方面的问题的话, 欢迎留言.
来源: https://www.cnblogs.com/guogangj/p/9217390.html