Net+postgresql practice and the pit-shelter guide

Brief introduction

. Net+postgresql (PG) This combination I've been using for a long time, it feels pretty good. But most people talk about it. NET platform, or will think of its "original" matching Microsoft SQL Server (MSSQL), in fact, no MSSQL and no problems, and even without Windows Server is no problem, who said. NET must be on the Microsoft family barrel? What age is it?

PG and MSSQL specific comparison I do not elaborate, self-search, this comparative analysis of a lot of articles. Should say two RDBMS each has the characteristic, the MSSQL tool set is huge (most of us do not use or will not use), installs more troublesome, the PG is relatively small, but the function also is not weak, we want it to have, the performance aspect I have done the simple deletion and modification test, the two cannot see any obvious difference, MSSQL seems to have recently provided a Linux version, and PG Natural cross-platform, MSSQL license fee does not seem low (no drill down), pg Open source free, to the more key customers, is not willing to pay another to buy a set of MSSQL, PG is very good choice.

I hope that after you finish this article, I feel the same as I do. NET + postgresql,rocks! It's all fine.

Version of PG

What version should PG choose? Linux or Windows? Of course the preferred Linux, but the development environment does not matter, you install a Windows version on your own work computer is no problem, some people say that the performance gap between the two, Linux is significantly better than windows, but I have done tests, this has not been confirmed so, however, I still recommend Linux, one easy to install, and simple configuration (command line interface with a sense of consistency), Sunline easy to write some scripts to implement database scheduled backup and so on. In fact, you do not need to worry about the installation of PG after the computer will become slow, I do not feel it, it is a quiet good pet, you do not call it, it sits quietly there, my Windows computer also installed a PG, I often use it to do some script testing or testing. In addition, you can now install the Linux version of PG directly under Windows, WSL understand?

PG has a lot of versions, now the latest version is 10.4, it's in front of the version is 9.6.x, eh? It's kind of weird, isn't it? 10.4 only have "two paragraphs", While the 9.6.x has three paragraphs, in fact, has been three paragraphs, 9 for the large version, 6 for the medium version, followed by a small version, small version only small feature improvements, will not have any effect on the data format, that is, your PG from 9.6.1 upgrade to 9.6.9, you went straight up to replace the old program is to ensure that no problems. But if your previous version is 9.5.3, to upgrade to 9.6.9, that is not the case, because the intermediate version has changed, you need to use a migration tool to convert your old data format to a new Fangke, that for 10.4 this version, which is the big version, which is the middle version, which is the small version? Here I feel a bit incoherent, PG in the upgrade from 9 to 10, it seems to lose the "big version", 10 although the successor of 9, but it should be counted as a medium version, so, 10.1 upgrade to 10.4 is not the conversion of data, the direct upgrade program can be. What is the next version of PG? Yes, it's 11, and the next one should be 12. Software this thing, if you have no history baggage, I think directly choose the latest, such as Choose 10.4, in the future upgrade 10.5,10.6 is also simple.

Say something extra, PG10 was officially launched last year (2017), the distance is now less than a year, just out of time I think, this "major upgrade" (Think about the iphone X,mac OS x,10 This number is very special not? Can it bring about a big improvement in performance? I tried it, and the conclusion was: No. It does not mention in the upgrade document on the performance of any significant increase, it mainly increased the native support for table partitioning, table partitioning, is the number of data in your table a lot of times, through table partitioning to improve read and write speed, how much table to recommend partitioning it? The official PG document says: If the size of the table catches up with your host's memory, you can consider table partitioning ... So, for a table with only tens of millions of rows or millions of rows of data, are you sure you want to partition it?

Npgsql

To use. NET use of PG, you have to use NuGet to introduce npgsql this package, which is its official website: http://www.npgsql.org/, fully open source, it is actually for the PG database of the ADO (ADO Data Provider). Here is the help manual for it: http://www.npgsql.org/doc/index.html

There are not too many difficulties here, all you need to do is to install your PG database (Windows version/linux version is OK, no impact), and then create one. NET project (I recommend using. NET Core), introduce Npgsql, and then follow the simple example in the instruction manual to enter the door.

This article certainly does not specifically take you how to start using the SELECT statement, the following mainly on the use of the process, we overcome some difficulties or trampled pits.

Where's nvarchar?

The most used text type in MSSQL is nvarchar, which is a text type with a length limit, corresponding to a varchar in the PG, this is not a problem, but the text type in PG is actually somewhat different from the type of text in MSSQL, PG text can basically be considered as unlimited length , varchar and text for the internal PG, there is no difference, but in the writing, varchar will check the length, so in terms of performance, varchar is not faster than the text, the serious words may also be slower, because it to check the length of it, So when you are designing a database, you can set all text types to text (or the citext mentioned later) without a brain, and the length check work is done in the business system.

What if you want to be case insensitive?

Most of the time, we hope that the case is not sensitive, case sensitivity will bring a lot of confusion, query, or the system exists with the same name of the user, a call John another called John,mssql can create the library when the case is not sensitive, and PG does not seem to have such a function, It needs an extra component, called CITEXT,CI, which means case insensitive. To use the Citext component, you need to install the Postgresql10-contrib package (assuming that you are installing PG10, if not you go to the corresponding package), and then use the following command to create the Citext type:

CREATE IF  not EXISTS  with SCHEMA public;

Note: A database only needs to execute this command once

If you use the Psql client connected to use PG, this time is OK, you will find that the Citext field is case insensitive, but if you use Npgsql code to access the PG, Citext does not seem to be effective, in fact, the reason is that, Citext is not a native type of PG, you need to add ":: Citext" explicitly to the PG when you use the query statement, and your parameter is the Citext type, as in the following example:

SELECT *  from WHERE test_name=@TextName and category=@Category:: Citext

Well, I admit it's a bit of a hassle, but get used to it, I don't know what better way to do it now.

NotSupportedException occurs when using Citext

This exception renders roughly the following:

System.NotSupportedException:The field ' application_id ' have a type currently unknown to Npgsql (OID 41000). You can retrieve it as a string by marking it as unknown, please see the FAQ. In Npgsql.NpgsqlDataReader.GetValue (Int32 Ordi NAL) in Npgsql.NpgsqlDataReader.get_Item (Int32 ordinal) ...

This error for us, once like a ghost, occasionally appear, when it is time to restart the service program is good, no longer appear, and then in a few weeks or months, and then appear, sometimes a day to appear multiple times is not impossible. Finally, it was up to GitHub to ask for help before finally figuring out why. Links: https://github.com/npgsql/npgsql/issues/1635

To put it simply, PG has an internal ID value (called OID) for various data types, and Npgsql gets these OID values and caches them for the first time the database is connected, and the OID values are fixed for the internal type of PG, such as int, But for Citext does not seem to be so, because citext this type is our own door created with the Create Extension command (please refer to the earlier content of this article), when created to determine its OID. When we restore the database, it is also equivalent to re-create the Citext type, which will cause the OID of citext to change, but npgsql do not know, so there is this exception. We often need to do the action of restoring the database during the development process, so this problem has been caused.

Workaround 1, when the database is restored, call Npgsqlconnection.reloadtypes (), refresh the various types of OID, but this is difficult, because the restore database is a manual operation, after the end of the open Web page, on the above point notification program?

Workaround 2, restart the program. This is in fact the same as the solution 1, just do not need to write any extra code, considering the restore database this action is not too frequent, just in the development environment to do, so restart on it, we do now, the provisions of the restoration of the database after the service program itself restart. (It's easy to write a script to do this thing)

Program crashes due to a large number of operations with transactions

I also went to GitHub for help with this question, Link: https://github.com/npgsql/npgsql/issues/1838

This problem may be more serious than the previous one, because I might not catch the exception (that is , sometimes it can be caught, sometimes not), the program crashes directly, for a. NET program, this is a very inappropriate thing, even if I did not write Try-catch alone, the program's outermost exception handler should also be able to capture the relevant exception and log right? But no, no log, also can't catch. So far I suspect this is a. NET bug, may not be related to Npgsql.

The cause of the problem, as described on GitHub, is found, but not fundamentally fixed, and the problem is a simple "transaction timeout" issue.

Our program initializes the database table at the first boot, inserts a lot of initialization data, because our company's development environment is very special, the database latency is very high, so the insertion speed is very slow, each insert time can be up to dozens of milliseconds, (production environment does not have this problem) This results in a transaction timeout of more than 10,000 data (the default time for transaction timeouts is 1 minutes). The solution, of course, is obvious: when initializing, temporarily increase the time-out value of TransactionScope to 10 minutes, so it is no problem.

Similar to this problem we can only through some external workaround to prevent, it is difficult to fundamentally solve.

55000: Disable the prepared transaction

This is a little tricky thing, first of all, this Chinese translation is very bad, this is a database thrown out error message, its English is "Prepared transactions is disabled", its correct Chinese translation I think it should be: Preprocessing transaction has been disabled. Alas, so I said why the English version, if the hint of Chinese, want to find the answer on the Internet will be more obstacles.

For the use of transactions, here is a simple example:

    using New npgsqlconnection (CONNECTIONSTR)) {        Conn. Open ();         using New TransactionScope ()) {            Conn. EnlistTransaction (transaction.current);                 // sqls ...             }            ts.complete ();        }    }

What do you mean "preprocessing transactions"? In fact, it is very simple, that is, "Transaction package Transaction", is the transaction can be submitted in a step, such as I started a transaction a, in this transaction I opened a transaction b,b commit, A and then commit. PG is closed by default for preprocessing transactions, of course, you can open it, edit the configuration file postgresql.conf, change the max_prepared_transactions to 100 (by default, 0,0 means disable), restart the PG service.

But are you sure you can really use the preprocessing transaction? I don't think we can use it, but why does it come to this problem? -or we have a problem with the program, even if you don't see the transaction package transaction from a single method. Preprocessing transactions may occur in the following two scenarios:

1, I created a method A to access the database, this method may be called by other methods, so it has a dbconnection type parameter, indicating that the caller is responsible for opening the database connection passed over, and a inside open the transaction, and the caller does not know, also opened the transaction, to form a preprocessing transaction

2, this situation is more obscure, database connection string, such as: host=192.168.1.101; Username=postgres; password=123456; Database=testdb; Enlist=true, a parameter called Enlist in the back is true, which means that the connection is automatically Enlist to the transaction of the current execution context when it is opened, if the transaction is open in the current execution context (the code is included in the using ( TransactionScope), then this database connection automatically enlist up, and then consider such a scenario: A method will open the database connection itself to query what things, B method will also access the database, and the B method will use the transaction, the transaction called a method, A method open the database connection when found in the current execution context exists transaction, so automatically enlist up, inadvertently formed a preprocessing transaction, and is "distributed" (A and B open may be different database connection), this situation should not be what you need

So what should we do? Here is my approach:

1,max_prepared_transactions is still set to 0, turn off, because we really do not, if used to get, that is our code is wrong, so once the "disable the prepared transaction" This exception, go back to check the code

2, remove the enlist=true in the database connection string, so that each use of the transaction requires an explicit call to Conn. EnlistTransaction (Transaction.Current), although a line of code, but more explicit semantics, Also do not consider whether the TransactionScope package dbconnection or in turn DbConnection package TransactionScope

3, normalize our database access code, identify which ones are needed and which are not, and indicate on the comments of each method

40001: Serial access is not possible due to read/write dependencies between multiple transactions

It corresponds to the English is: Cound not serialize access due to read/write dependencies among transactions, how should this be understood? In fact, people who understand the database transaction isolation level should not be unfamiliar with this. NET TransactionScope uses the highest level of--serializable (serializable) in the transaction isolation level by default. This level ensures the consistency of the data to the greatest extent, but at a very high cost, one is slower, and the other is prone to "read/write dependencies between transactions", which is the error, for example:

A, b two transactions, while accessing the test table, each insert 100 data, they insert the data after the sum of the total number of the table and then write to another table, this will be a problem, because both for a or B, they opened the transaction when the other party's affairs are not commit, Their view of the test table is actually a "snapshot" of the beginning of a transaction, and before the transaction commits, the data they insert is not visible to the other, so if both A and B are successful, the sum of the data will be incorrect, which is called "Data inconsistency". So the database will let A, B succeed, while the other fails, and throws this exception.

So, this is a "normal error", according to the normal business logic, should rarely appear, if it does occur, and frequent, it is necessary to consider whether the business logic design is not reasonable, to see if the design can avoid this problem, if the business logic must be so, then you can use the following method to try:

1, the parallel transaction with the client code line up, get a thread-safe queue, executed one at a time, so the speed will be slow, but to ensure that every transaction is successful

2, catch this exception, and then automatically retry, in fact, this is the database recommended Orthodox practice

3, reduce the transaction isolation level, this may or may not appear, it is entirely up to your business, about the transaction isolation level, this is a pretty big topic, I consider to write an article in due course

4, for the rare occurrence of frequency, can not be processed, just need to catch this exception type, and then prompt the user to try again, many websites seem to do so

Summarize

If you have time, I will write another article to do some of the general usage of PG, such as hot standby cold standby restore maintenance, but not sure when to write it out.

For the moment to summarize so many, who if there is a problem in this area, welcome message.

Net+postgresql practice and the pit-shelter guide