说米网

这篇文章首要介绍了.NET+PostgreSQL实践与避坑攻略,本文给咱们介绍的非常详细，对咱们的学习或作业具有必定的参阅借鉴价值，需求的朋友能够参阅下

简介

.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();
}
}

什么叫“预处理业务”？godaddy域名解析其实很简略，便是“业务包业务”，便是能够分步提交的业务，比方我先敞开了一个业务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表中id为50的一条记载，A读出这条记载，接着B更新了这条记载并提交，依据可序列化的阻隔等级的规则，A并不知道B更新了记载，A在B提交后尝试修正这条记载，这时分数据库就会让A业务失利，并抛出这个反常，由于让A修正成功的话，就会导致B之前的修正不经意间丢失了，可序列化阻隔等级并不允许这种状况的产生。

所以，这是个“正常的过错”，按常规的业务逻辑来说，应该很少会呈现，假如真的呈现，且频频呈现，那需求考虑下是不是业务逻辑规划得不太合理，看看能不能从规划上避免这个问题，假如业务逻辑必定如此，那能够用下面的办法尝试一下：

1，将这种并行业务用客户端代码排个队，弄个线程安全队列，逐个履行，这样速度会慢点，但确保了每个业务都能成功

2，捕捉这个反常，然后主动重试，其实这也是数据库引荐的正统的做法

3，降低业务阻隔等级，这个或许会呈现问题，也或许不呈现，这彻底取决于你的业务，关于业务阻隔等级，这是个蛮大的话题，我考虑恰当时分再写一篇文章

4，关于极少呈现的频次来说，能够不处理，仅仅需求捕捉这个反常类型，然后提示用户重试即可，许多网站貌似都这么干的

总结

有时刻的话我会别的开一篇文章来写写PG的一些常规用法，如热备冷备复原维护等，但不太能确保什么时分能写出来。

到此这篇关于.NET+PostgreSQL实践与避坑攻略的文章就介绍到这了.