本文主要介绍。NET PostgreSQL实践和陷阱避免指南。这篇文章给你做了非常详细的介绍，对你的学习或者工作有一定的参考价值。有需要的朋友可以参考一下。
简介
我一直在使用。NET PostgreSQL(简称PG)相当长时间了，感觉还挺好的。然而，当大多数人谈论。NET平台，他们还是想到了微软SQL Server(简称MSSQL)，跟它“原创”。其实没有MSSQL，甚至没有Windows Server都没有问题。谁说利用。NET一定要去微软家斗？这是什么年代了.
我就不详细展开与PG MSSQL的具体对比了。我自己去搜。关于这种对比分析的文章很多。应该说两种RDBMS各有特色。MSSQL工具集庞大(大部分不用或者不会用)，安装麻烦。PG虽小，但功能不弱。我们想要的都有了。我做过简单的添加、删除、修改性能的测试，两者没有明显区别。MSSQL好像最近才提供Linux版本，而PG自然是跨平台的，而且MSSQL的授权费好像也不低(没有进一步研究)。
希望看完这篇文章，你有所感悟。NET PostgreSQL，摇滚如我！没问题。
PG版本
PG应该选择什么版本？Linux还是Windows？当然首选Linux，但是开发环境无所谓。你在自己的工作电脑上安装Windows版本是没有问题的。有人说两者性能差距很大，Linux明显比Windows好，但是我做了一些测试，这个没有得到证实。不过我还是推荐Linux，因为它安装方便，配置简单(命令行界面用起来感觉一致)，方便写一些脚本实现数据库定时备份等等。其实你不需要担心安装PG后电脑会变慢。完全感觉不到。它是一只安静可爱的宠物。如果你不叫它，它就静静地坐在那里。我的Windows电脑也安装了一个PG，我经常用它来做一些脚本测试或者实验。另外，现在可以直接在Windows下安装PG的Linux版本。WSL，你知道什么？
PG有很多版本。现在最新版本是10.4，之前版本是9.6.x，嗯？有点奇怪，不是吗？0.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。
说到extras，PG10去年(2017年)正式上市，距离现在不到一年。它刚出来的时候，我想，这个“重大升级”(想想iPhone X，Mac OS X，10，这个数字很特别吧？)能否带来性能的大提升？我试了一下，结论是：没有，的确，它的升级文档并没有提到性能有什么明显的提升。它主要增加了对表分区的本机支持。表分区是指当你的表中有很多数据时，你可以通过表分区来提高读写速度。至于表多大才推荐分区？PG的官方文档说：如果表的大小赶上了你的主机的内存，你可以考虑表分区.那么，您确定要对那些只有数十或数百万行数据的表进行分区吗？
Npgsql
与一起使用PG。NET，你得用nuget引入Npgsql的包。这是它的官方网站：完全开源的http://www.npgsql.org/,它实际上是PG数据库(ADO.NET数据提供商)的ADO.NET引擎。这是它的帮助手册：http://www.npgsql.org/doc/index.html.
这里没有太多的困难。你需要做的就是安装好你的PG数据库(Windows版本/Linux版本都可以，没有影响)，然后创建一个. NET项目(我推荐使用。NET Core)，介绍Npgsql，然后按照使用说明书中的简单例子就能进门了。
当然，本文不会具体带你如何开始使用SELECT语句。下面主要讲讲我们克服的一些困难或者在使用过程中踩过的坑。
nvar在哪里？
MSSQL中最常用的文本类型是NVARCHAR，它是一种有长度限制的文本类型。相应的，PG里有VARCHAR，用起来也是可以的。但是，PG中的文本类型实际上与MSSQL中的文本类型略有不同。PG的文字基本可以认为长度不限。VARCHAR和text在PG内部是没有区别的，只是在写的时候，VARCHAR会检查长度，所以从性能上来说，VARCHAR并不比TEXT快，如果是真的话可能会慢一些，因为它要检查长度，所以你可以在设计数据库的时候无意识的把所有的文本类型都设置为TEXT(或者后面提到的CITEXT)，长度检查可以在业务系统中完成。
如果想不区分大小写呢？
大多数时候，我们希望不区分大小写。但是区分大小写会带来很多混乱，我们也查不出来，或者系统中有同名用户，一个叫john，一个叫John。MSSQL可以在创建库的时候指定不区分大小写，但是PG好像没有这样的功能。它需要一个叫做CITEXT的附加组件的帮助，CI的意思是不区分大小写。要使用CITEXT组件，您需要安装postgresql10-contrib包(假设您安装了PG10，如果没有，请转到相应的包)，然后使用以下命令创建CITEXT类型：
如果不存在，则使用公共架构创建扩展CITEXT
注意：数据库只需要执行这个命令一次。
如果是用psql客户端连接PG，此时就OK了。你会发现CITEXT的字段是不区分大小写的，但是如果你是用Npgsql通过代码访问PG的话，CITEXT好像并没有生效。其实原因就是这样。CITEXT不是PG的原生类型。当您使用查询语句时，您需要在参数后添加“:CITEXT ”,以明确地告诉PG您的
SELECT * FROM test_table其中test_name=@TextName:CITEXT和category=@Category:CITEXT
嗯，我承认有点麻烦，但是习惯就好了。我不知道现在还有什么更好的方法。
使用CITEXT时会发生NotSupportedException。
这个异常的演示内容大致如下：
系统。NotSupportedException:字段’ application_id ‘具有Npgsql (OID 41000)当前未知的类型。您可以通过将其标记为未知来将其作为字符串检索，请参见常见问题解答。
位于npg SQL . npg SQL datareader . getvalue(int 32 ordanal)
位于npg SQL . npg SQL datareader . get _ item(int 32 ordanal)
……
对于我们来说，这个错误曾经就像一个幽灵，时不时的出现。当它出现时，只需重启服务程序。不会再出现，过几周或者几个月又出现。有时候一天出现很多次也不是不可能。最后去github求助，才终于明白原因。链接：https://github.com/npgsql/npgsql/issues/1635
简单来说，PG对于各种数据类型都有一个内部ID值(称为oid)。当Npgsql第一次连接到数据库时，它将获取这些oid值并缓存它们。对于PG的内部类型，比如int，这些oid的值是固定的，但是对于CITEXT好像不是这样，因为CITEXT是我们公司用CREATE EXTENSION命令创建的(请参考本文前面的内容)。创建的时候，我们恢复数据库的时候，相当于重新创建了CITEXT类型，会导致CITEXT的oid发生变化，但是Npgsql并不知道，所以出现了这个异常。我们在开发过程中经常需要恢复数据库，导致了这个问题。
解决方案1:数据库恢复后，调用NpgsqlConnection。ReloadTypes()来刷新各种oid，但是这是非常困难的，因为恢复数据库是手工操作。完成后，打开网页，点击顶部的通知程序？
解决方案2，重启程序。实际上，这与解决方案1类似，只是不需要编写额外的代码。考虑到恢复数据库的动作并不太频繁，只在开发环境下进行，重启即可。我们现在就做，规定恢复数据库后自己重启服务程序。写一个脚本来做到这一点非常简单。
大量操作使用事务会导致程序崩溃。
这个问题我也去github求助过。链接：https://github.com/npgsql/npgsql/issues/1838
这个问题可能比上一个更严重，因为我很可能抓不到异常(就是有时候能抓到，有时候抓不到)，程序直接崩溃。对于一个. NET程序来说，这是一件非常糟糕的事情。就算我没有单独写try-catch，程序最外层的异常处理程序也应该能捕捉到相关的异常并记录下来吧？但是不行，没有日志，也捕捉不到。到目前为止，我怀疑这是一个. NET bug，可能和Npgsql没有关系。
如github上所述，找到了问题的原因，但无法从根本上纠正。这个问题其实就是一个简单的“事务超时”问题。
当我们的程序第一次启动时，它将初始化数据库的表，并插入大量的初始化数据。由于我公司特殊的开发环境，数据库延迟很高，导致插入速度很慢，每次插入最多需要几十毫秒。(生产环境不存在这个问题。)当超过10000条数据下来时，事务超时(事务超时默认时间为1分钟)。当然，解决方法很明显：初始化时，临时将TransactionScope的超时值增加到10分钟，这样总不会有问题。
类似的问题只能通过一些外部的变通方法来预防，很难从根本上解决。
5000:禁用准备好的事务
这又有点棘手了。首先，这个中文翻译很差。这是由数据库引发的错误消息。它的英文是“已准备好的事务被禁用”。我认为正确的中文翻译应该是：预处理事务已被禁用。唉，所以我才要英文版的。如果提示中文，网上找答案会有更多障碍。
事务的使用，这里有一个简单的例子：
using(npgsql connection conn=new npgsql connection(connectionStr)){
conn . Open()；
using(transaction scope ts=new transaction scope()){
conn.EnlistTransaction(事务。当前)；
//SQL.
}
ts。完成()；
}
}
什么是“预处理事务”？其实很简单，就是“交易包交易”，也就是可以分步提交的交易。比如我先启动了一个交易A，在这个交易中我启动了另一个交易B，由B提交，然后由A提交，对于PG预处理交易，默认关闭。当然可以打开，编辑配置文件postgresql.conf，将max_prepared_transactions改为100(默认为0，表示禁用)，重启PG服务。
但是你确定你真的需要预处理东西吗？我觉得不能用，但是为什么会有这个问题呢？3354还是我们程序写的问题，即使从单个方法看不到事务包事务。“预处理交易”可能发生在以下两种情况下：
1.我创建了一个方法A来访问数据库。这个方法可能被其他方法调用，所以它有一个DbConnection类型的参数，意思是调用者负责打开数据库连接并传递过去，而A在调用者不知情的情况下打开事务，它也打开事务形成预处理事务。
2.这种情况比较隐晦。数据库连接字符串，如：Host=192 . 168 . 1 . 101；Username=postgres密码=123456；Database=testdbEnlist=true，后跟一个名为Enlist的参数，这意味着当这个连接打开时，它将自动登记到当前执行上下文的事务中。如果在当前执行上下文中打开一个事务(从代码的角度来看包含在using(TransactionScope)中)，那么数据库连接会自动被登记，然后考虑这个场景：方法A会打开数据库连接自己查询某个东西，方法B也会访问数据库，方法B会使用事务，在事务中调用方法A。当方法A打开数据库连接时，发现当前执行上下文中有一个事务，于是自动Enlist上去，无意中形成了一个预处理事务，而且还是“分布式的”(A和B可能打开不同的数据库连接)。
那么我们该怎么办呢？我是这样做的：
1，max_prepared_transactions还是设为0，关掉吧，因为我们真的用不上。如果可以，那就是我们的代码写错了，所以一旦出现“禁用准备好的事务”这个异常，就回去检查代码。
2.从数据库连接字符串中删除Enlist=true。因此，每次使用事务时都需要显式调用conn . enlist transaction(transaction . current)。虽然一行代码是正确的，但是语义更清晰，不需要考虑是TransactionScope包DbConnection还是反过来。
3.将我们的数据库访问代码标准化，明确哪些事务是需要的，哪些是不需要的，并在每个方法的注释中注明。
40001:由于多个事务之间的读/写相关性，无法串行访问
其对应的英文是：由于读/写依赖关系事务，无法序列化访问。这个应该怎么理解？事实上，了解数据库事务隔离级别的人应该对此很熟悉。默认情况下，事务范围为。NET使用最高级别的事务隔离，——Serializable(可序列化)。这个级别最大程度上保证了数据的一致性，但成本也相当高。一方面速度慢，另一方面容易出现“事务间读/写依赖”。这是错误的。举个简单的例子：
两个事务A和B同时访问测试表中id为50的记录。a读取这个记录，然后B更新这个记录并提交。根据可序列化隔离级别的规则，A不知道B已经更新了记录。在B提交记录后，a试图修改它。这个时候数据库会让A的事务失败，抛出这个异常，因为如果A修改成功，B之前的修改会在不经意间丢失。可序列化隔离级别。
所以，这是一个“正常的错误”。按照常规的商业逻辑，应该很少发生。如果确实发生，而且经常发生，就要考虑业务逻辑设计是否不合理，看在设计上能不能避免这个问题。如果业务逻辑必须如此，可以用以下方法尝试一下：
1.用客户端代码把这个并行事务排队，得到一个线程安全的队列，一个一个执行，会慢一些，但是保证每一个事务的成功。
2.捕捉此异常并自动重试。其实这也是数据库推荐的正统做法。
3.降低事务隔离级别，它可能有问题，也可能没有问题。这完全取决于你的业务。至于事务隔离级别，这是一个相当大的话题。我会考虑在适当的时候再写一篇文章。
4.对于罕见的频率，你可以忽略它，只是捕捉这种异常类型，然后提示用户再试一次。很多网站好像都是这么做的。
摘要
如果有时间，我会再开一篇文章写一些PG的常用用法，比如热备用、冷备用、恢复维护等。但我不能保证什么时候写。
这就是这篇文章的内容。NET PostgreSQL实践和陷阱避免指南。有关的更多信息。NET PostgreSQL练习及避坑指南，请搜索脚本之家之前的文章或者继续浏览下面的相关文章。希望大家以后多多支持剧本之家！
来源：剧本之家
链接：https://www.jb51.net/article/205026.htm
