簡介

.NET+PostgreSQL(簡稱PG)這個組合我已經(jīng)用了蠻長的一段時間，感覺還是挺不錯的。不過大多數(shù)人說起.NET平臺，還是會想起跟它“原汁原味”配套的Microsoft SQL Server(簡稱MSSQL)，其實沒有MSSQL也沒有任何問題，甚至沒有Windows Server都沒問題，誰說用.NET就一定要上微軟全家桶？這都什么年代了……

PG和MSSQL的具體比較我就不詳細(xì)展開了，自行搜一下，這種比較分析文章很多。應(yīng)該說兩個RDBMS各有特色，MSSQL工具集龐大(大多我們都用不到或不會用)，安裝較為麻煩，PG比較小巧，但功能也不弱，我們要的它都有，性能方面我做過簡單的增刪查改的測試，兩者看不出什么明顯差別，MSSQL貌似最近才提供了Linux版，而PG天生跨平臺，MSSQL的授權(quán)費似乎不低(沒深究)，PG開源免費，對比較摳的客戶來說，是不太愿意另外花錢買一套MSSQL的，PG就是非常不錯的選擇。

希望你看完本文之后，也同我一樣覺得.NET + PostgreSQL，Rocks！沒問題的了。

PG的版本

PG應(yīng)該選擇什么版本？Linux還是Windows？當(dāng)然是首選Linux，但開發(fā)環(huán)境無所謂，你在你自己的工作電腦上安裝一個Windows版也是沒問題的，有人說兩者性能差距較大，Linux明顯要好于Windows，但我有做過測試，這個并沒有被證實如此，然而，我還是推薦Linux，一來安裝簡便，二來配置簡單(命令行界面用起來感覺比較一致)，三來方便寫一些腳本來實現(xiàn)數(shù)據(jù)庫定時備份之類的。其實你并不需要擔(dān)心安裝了PG后電腦會變慢，我完全感覺不出來，它是個安靜的乖萌寵，你不叫它，它就靜靜坐在那里，我的Windows電腦上也安裝了一個PG，我經(jīng)常用它來做一些腳本測試或試驗。另外，現(xiàn)在也能在Windows下直接安裝Linux版本的PG了，WSL了解下？

PG有很多的版本，現(xiàn)在的最新版是10.4，它前面的版本是9.6.x，嗯？有點奇怪不是？10.4只有“兩段”，而9.6.x有三段，其實之前一直是三段，9表示大版本，6表示中版本，后面是小版本，小版本只有小的功能改進(jìn)，不會對數(shù)據(jù)格式造成任何影響，就是說，你的PG從9.6.1升級到9.6.9，你直接升了把舊程序替換掉就是，保證沒有任何問題。但如果你之前的版本是9.5.3，要升級到9.6.9，那就不行了，因為中間版本變了，你需要用一個遷移工具去把你的舊的數(shù)據(jù)格式轉(zhuǎn)為新的方可，那對10.4這個版本而言，哪個是大版本，哪個是中版本，哪個是小版本？這里我感覺有點不連貫，PG在從9升級到10的時候，似乎丟掉了“大版本”，10雖然是9的后繼，但它應(yīng)該算一個中版本，所以，10.1升級到10.4是不用轉(zhuǎn)換數(shù)據(jù)的，直接升級程序即可。那PG的下一個中版本是什么？沒錯，是11，再下一個應(yīng)該就是12了。軟件這個東西，如果你沒什么歷史包袱，我覺得直接選擇最新的，比如選擇10.4，將來升級10.5,10.6的時候也簡單。

說點額外的，PG10是去年(2017)正式推出的，距離現(xiàn)在都不到一年，剛出來的時候我就想，這個“重大升級”(想想看iPhone X，Mac OS X，10這個數(shù)字是很特別不是？)能不能帶來性能上的大提升呢？我試了一下，結(jié)論是：沒有。確實它的升級文檔上也沒提及到性能有什么明顯提升，它主要增加了對表分區(qū)的原生支持，表分區(qū)，就是你的表中的數(shù)據(jù)的數(shù)量很多很多的時候，通過表分區(qū)來提高讀寫速度，至于表要多大才推薦分區(qū)呢？PG的官方文檔說是：如果表的尺寸趕上了你主機(jī)的內(nèi)存的時候，可以考慮表分區(qū)……所以，對于那些只有區(qū)區(qū)幾千萬行或幾百萬行數(shù)據(jù)的表，你確定要分區(qū)嗎？

Npgsql

要用.NET使用PG，就得用nuget引入Npgsql這個包，這是它的官方網(wǎng)站：http://www.npgsql.org/，完全開源，它其實就是針對PG數(shù)據(jù)庫的ADO.NET引擎(ADO.NET Data Provider)。這里是它的幫助手冊：http://www.npgsql.org/doc/index.html

這里邊并沒有太多難點，你所需要做的，就是安裝好你的PG數(shù)據(jù)庫(Windows版/Linux版都行，沒有什么影響)，然后創(chuàng)建一個.NET項目(我推薦使用.NET Core)，引入Npgsql，然后照著說明手冊上的簡單例子入一下門即可。

本文當(dāng)然不會具體帶你如何開始使用SELECT語句，下面主要講述在使用過程中，我們所克服的一些困難或踩過的坑。

NVARCHAR呢？

MSSQL中用得最多的的文本類型是NVARCHAR，這是一個帶長度限制的文本類型，對應(yīng)地，PG中有VARCHAR，這樣用沒問題，但PG中的文本類型其實跟MSSQL中的文本類型是有點區(qū)別的，PG的文本基本上可以認(rèn)為不限長度，VARCHAR及TEXT對PG內(nèi)部來說，并沒有什么差別，只是在寫入的時候，VARCHAR會檢查一下長度，所以性能上來看，VARCHAR并不比TEXT要快，較真的話可能還會慢點，因為它要檢查長度嘛，所以你在設(shè)計數(shù)據(jù)庫的時候可以無腦地將所有文本類型設(shè)置為TEXT(或后面提到的CITEXT)，長度檢查工作放在業(yè)務(wù)系統(tǒng)中去做即可。

想要大小寫不敏感怎么辦？

絕大多數(shù)時候，我們都是希望大小寫不敏感的，大小寫敏感反倒會帶來很多困惑，查詢不出，或者系統(tǒng)中存在同名的用戶，一個叫John另一個叫john，MSSQL可以在創(chuàng)建庫的時候指定大小寫不敏感，而PG似乎沒有這樣的功能，它需要借助一個額外的組件，叫CITEXT，CI的意思就是Case Insensitive。要使用CITEXT組件，你需要安裝postgresql10-contrib包(假設(shè)你安裝的是PG10，如果不是的話你去找對應(yīng)的包)，再使用以下命令創(chuàng)建CITEXT類型：

CREATE EXTENSION IF NOT EXISTS CITEXT WITH SCHEMA public;

注：一個database只需要執(zhí)行一次這個命令即可

如果你使用的是psql客戶端連上去使用PG的話，這時候已經(jīng)OK了，你會發(fā)現(xiàn)CITEXT的字段已經(jīng)是大小寫不敏感了，但如果你用的是Npgsql用代碼去訪問PG的話，CITEXT似乎沒生效，其實原因是這樣的，CITEXT并不是PG的原生類型，你在用查詢語句的時候，需要在參數(shù)后面加上“::CITEXT”顯式地告訴PG，你的參數(shù)是CITEXT類型，例子如下：

SELECT * FROM test_table WHERE test_name=@TextName::CITEXT AND category=@Category::CITEXT

嗯，我承認(rèn)是有點麻煩，但習(xí)慣就好，我現(xiàn)在還不知道有什么更佳方法。

使用CITEXT時候出現(xiàn)NotSupportedException

這個異常的呈現(xiàn)內(nèi)容大致如此：

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)
……

這個錯誤對我們而言，曾經(jīng)像個幽靈似的，時不時出現(xiàn)，出現(xiàn)的時候重啟一下服務(wù)程序就好了，不再出現(xiàn)，然后過幾個星期或者幾個月又出現(xiàn)，有時候一天出現(xiàn)多次也不是沒有可能。最后是到github上面求助才最終搞懂了原因。鏈接：https://github.com/npgsql/npgsql/issues/1635

簡單地說，PG對各種數(shù)據(jù)類型，是有一個內(nèi)部的ID值的(叫oid)，Npgsql在第一次連接數(shù)據(jù)庫的時候，會獲取到這些oid值并緩存起來，對于PG的內(nèi)部類型，如INT什么的，這些oid值是固定的，但對于CITEXT似乎不是這樣，因為CITEXT這個類型是我門自己用CREATE EXTENSION命令創(chuàng)建的(請參考本文前面內(nèi)容)，創(chuàng)建的時候確定其oid。我們在還原數(shù)據(jù)庫的時候，也相當(dāng)于重新創(chuàng)建了CITEXT類型，這樣會導(dǎo)致CITEXT的oid發(fā)生變化，但Npgsql并不知道，所以就出現(xiàn)了這個異常。我們在開發(fā)過程中常常需要做還原數(shù)據(jù)庫的動作，所以導(dǎo)致了這個問題的發(fā)生。

解決方法1，當(dāng)數(shù)據(jù)庫還原了之后，調(diào)用NpgsqlConnection.ReloadTypes()，刷新各類型oid，但這個很難，因為還原數(shù)據(jù)庫都是手動操作，做完之后打開網(wǎng)頁，在上面點一下通知程序嗎？

解決方法2，重啟一下程序。這個其實跟解決方法1差不多，只不過不需要寫什么額外代碼，考慮到還原數(shù)據(jù)庫這個動作其實也不是太頻繁，只是在開發(fā)環(huán)境中做，所以重啟就重啟吧，我們現(xiàn)在就干，規(guī)定還原數(shù)據(jù)庫后自己重啟下服務(wù)程序。(寫個腳本干這個事情很簡單)

使用事務(wù)進(jìn)行大量操作時候?qū)е鲁绦虮罎?/h2>

這個問題我同樣到github上求助了，鏈接：https://github.com/npgsql/npgsql/issues/1838

這個問題比前面的問題可能更嚴(yán)重，因為我很可能捕捉不到異常(就是說有時候可以捕捉到，有時候不行)，程序直接崩潰了，對于一個.NET程序來說，這是很不應(yīng)該的事情，即便我沒單獨寫try-catch，程序的最外層異常處理器應(yīng)該也能捕捉到相關(guān)的Exception并log對不？但偏不，沒有l(wèi)og，也捕捉不到。所以至今我懷疑這是一個.NET的bug，可能跟Npgsql并沒有關(guān)系。

問題的原因如github上所描述，是找到了，但卻無法從根本上修正，這個問題其實是個簡單的“事務(wù)超時”問題。

我們的程序在第一次啟動的時候會初始化數(shù)據(jù)庫的表，插入大量的初始化數(shù)據(jù)，由于我們公司的開發(fā)環(huán)境比較特殊，數(shù)據(jù)庫延遲十分高，所以導(dǎo)致插入速度很慢，每條插入耗時可高達(dá)幾十毫秒，(生產(chǎn)環(huán)境并沒有這個問題)這樣一萬多條數(shù)據(jù)下來就導(dǎo)致了事務(wù)超時(事務(wù)超時默認(rèn)時間是1分鐘)。解決方法當(dāng)然很明顯了：初始化的時候，臨時增加 TransactionScope的超時值，增加到10分鐘，這樣總歸沒問題了。

類似這種問題我們只能通過一些外部的workaround來預(yù)防，很難從根本上解決。

55000: 禁用已準(zhǔn)備好的事務(wù)

這又是一個有點棘手的事情，首先是這個中文翻譯得很不好，這是一條數(shù)據(jù)庫拋出來的出錯信息，它的英文是“Prepared transactions are disabled”，其正確的中文翻譯我覺得應(yīng)該是：預(yù)處理事務(wù)已被禁用。唉，所以我說為什么要英文版，如果提示中文，想在網(wǎng)上找答案都會多些障礙。

對事務(wù)的使用，這里有個簡單的例子：

using (NpgsqlConnection conn = new NpgsqlConnection(connectionStr)) {
  conn.Open();
  using (TransactionScope ts = new TransactionScope()) {
   conn.EnlistTransaction(Transaction.Current);
    //SQLs...
   }
   ts.Complete();
  }
 }

什么叫“預(yù)處理事務(wù)”？其實很簡單，就是“事務(wù)包事務(wù)”，就是可以分步提交的事務(wù)，比如我先開啟了一個事務(wù)A，在這個事務(wù)中我又開啟了一個事務(wù)B，B提交，A再提交。PG對于預(yù)處理事務(wù)是默認(rèn)關(guān)閉的，當(dāng)然了，你可以打開它，編輯配置文件postgresql.conf，把max_prepared_transactions改為100(默認(rèn)是0，0表示禁用)，重啟PG服務(wù)即可。

但你確定你真的用得到預(yù)處理事務(wù)嗎？我看下來我們是用不到的，但為什么出現(xiàn)這個問題？——還是我們程序?qū)懙糜袉栴}，即便你從單個方法上看不出來事務(wù)包事務(wù)。以下兩種場景可能會出現(xiàn)“預(yù)處理事務(wù)”：

1，我創(chuàng)建了一個方法A訪問數(shù)據(jù)庫，這個方法可能會被其它方法調(diào)用，所以它有個DbConnection類型的參數(shù)，表示調(diào)用者負(fù)責(zé)打開數(shù)據(jù)庫連接傳遞過來，而A里面開啟了事務(wù)，而調(diào)用者并不知情，也開啟了事務(wù)，形成預(yù)處理事務(wù)

2，這種情況更隱晦些，數(shù)據(jù)庫連接字符串，如：Host=192.168.1.101; Username=postgres; Password=123456; Database=testdb; Enlist=true，在后面有個叫Enlist的參數(shù)為true，這表示這個連接在打開的時候，會自動Enlist到當(dāng)前執(zhí)行上下文的Transaction中去，如果當(dāng)前執(zhí)行上下文中打開了事務(wù)(從代碼上看包含在了using(TransactionScope)中)，那這個數(shù)據(jù)庫連接就自動Enlist上去了，再考慮這樣的場景：A方法會自己打開數(shù)據(jù)庫連接去查詢點什么東西，B方法也會訪問數(shù)據(jù)庫，且B方法會使用事務(wù)，事務(wù)中調(diào)用了A方法，A方法打開數(shù)據(jù)庫連接的時候發(fā)現(xiàn)當(dāng)前執(zhí)行上下文中存在Transaction，于是自動Enlist上去了，不經(jīng)意間形成了預(yù)處理事務(wù)，且還是“分布式”的(A和B打開的可能是不同的數(shù)據(jù)庫連接)，這種情況應(yīng)該并不是你所需要的

那我們應(yīng)該怎么做？下面是我的做法：

1，max_prepared_transactions還是設(shè)置為0，關(guān)掉，因為我們真用不到，如果用得到，那就是我們代碼寫錯了，所以一旦出現(xiàn)“禁用已準(zhǔn)備好的事務(wù)”這個異常，就回去檢查代碼

2，把Enlist=true在數(shù)據(jù)庫連接字符串中去掉，這么一來，每次使用事務(wù)都需要顯式地調(diào)用 conn.EnlistTransaction(Transaction.Current)，雖然對了一行代碼，但語義更明確，也不用考慮到底是TransactionScope包DbConnection或反過來DbConnection包TransactionScope

3，規(guī)范化我們的數(shù)據(jù)庫訪問代碼，明確哪些是需要事務(wù)哪些是不需要的，在各個方法的注釋上注明

40001：由于多個事務(wù)間的讀/寫依賴而無法串行訪問

它對應(yīng)的英文是：Cound not serialize access due to read/write dependencies among transactions，這個應(yīng)該怎么理解呢？其實了解數(shù)據(jù)庫事務(wù)隔離級別的人對這個應(yīng)該不會陌生。.NET的TransactionScope默認(rèn)使用的是事務(wù)隔離級別中的最高級別——Serializable(可序列化)。這個級別最大程度上確保了數(shù)據(jù)的一致性，但代價也挺高，一來速度較慢，二來很容易出現(xiàn)“事務(wù)間讀/寫依賴”，就是這個錯誤了，舉個簡單的例子：

A、B兩個事務(wù)，同時訪問test表中id為50的一條記錄，A讀出這條記錄，接著B更新了這條記錄并提交，根據(jù)可序列化的隔離級別的規(guī)則，A并不知道B更新了記錄，A在B提交后嘗試修改這條記錄，這時候數(shù)據(jù)庫就會讓A事務(wù)失敗，并拋出這個異常，因為讓A修改成功的話，就會導(dǎo)致B之前的修改不經(jīng)意間丟失了，可序列化隔離級別并不允許這種情況的發(fā)生。

所以，這是個“正常的錯誤”，按常規(guī)的業(yè)務(wù)邏輯來說，應(yīng)該很少會出現(xiàn)，如果真的出現(xiàn)，且頻繁出現(xiàn)，那需要考慮下是不是業(yè)務(wù)邏輯設(shè)計得不太合理，看看能不能從設(shè)計上避免這個問題，如果業(yè)務(wù)邏輯一定如此，那可以用下面的方法嘗試一下：

1，將這種并行事務(wù)用客戶端代碼排個隊，弄個線程安全隊列，逐個執(zhí)行，這樣速度會慢點，但確保了每個事務(wù)都能成功

2，捕捉這個異常，然后自動重試，其實這也是數(shù)據(jù)庫推薦的正統(tǒng)的做法

3，降低事務(wù)隔離級別，這個可能會出現(xiàn)問題，也可能不出現(xiàn)，這完全取決于你的業(yè)務(wù)，關(guān)于事務(wù)隔離級別，這是個蠻大的話題，我考慮適當(dāng)時候再寫一篇文章

4，對于極少出現(xiàn)的頻次來說，可以不處理，僅僅需要捕捉這個異常類型，然后提示用戶重試即可，很多網(wǎng)站貌似都這么干的

總結(jié)

有時間的話我會另外開一篇文章來寫寫PG的一些常規(guī)用法，如熱備冷備還原維護(hù)等，但不太能保證什么時候能寫出來。

到此這篇關(guān)于.NET+PostgreSQL實踐與避坑指南的文章就介紹到這了,更多相關(guān).NET+PostgreSQL實踐與避坑指南內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！