Shulou(Shulou.com)06/01 Report--

Today, I'll show you how .NET + PostgreSQL practices and pit avoidance techniques are. The content of the article is good. Now I would like to share it with you. Friends who feel in need can understand it. I hope it will be helpful to you. Let's read it along with the editor's ideas.

Brief introduction

Net + PostgreSQL (PG for short) I've been using this combination for a long time, and it feels pretty good. However, when most people talk about the .NET platform, they still think of the "authentic" matching Microsoft SQL Server (MSSQL). In fact, there is no problem without MSSQL, or even without Windows Server. Who says that if you use .NET, you must go to the Microsoft family bucket? What's the age?

I will not expand the specific comparison between PG and MSSQL in detail. I will search by myself. There are many articles on this kind of comparative analysis. It should be said that the two RDBMS have their own characteristics, MSSQL tool set is huge (most of us can not use or can not use), installation is more troublesome, PG is relatively small, but the function is not weak, we all have, in terms of performance, I have done a simple test of additions, deletions, searches and changes, and there is no obvious difference between the two. MSSQL seems to have only recently provided the Linux version, while PG is naturally cross-platform, and the license fee for MSSQL seems to be not low (no further research). PG is open source free. For stingy customers, they are reluctant to spend extra money on a set of MSSQL, and PG is a very good choice.

The version of PG

Which version of PG should be chosen? Linux or Windows? Of course, Linux is the first choice, but the development environment does not matter. It is no problem for you to install a Windows version on your own work computer. Some people say that there is a big performance gap between the two. Linux is obviously better than Windows, but I have tested it, and this has not been proved. However, I still recommend Linux, which is easy to install and easy to configure (the command line interface feels consistent). Third, it is convenient to write some scripts to realize the scheduled backup of the database and so on. In fact, you don't need to worry that the computer will slow down after installing PG. I can't feel it at all. It's a quiet pet. If you don't call it, it just sits there quietly. My Windows computer also has a PG installed. I often use it to do some script tests or experiments. In addition, now you can install the Linux version of PG directly under Windows, WSL?

There are many versions of PG, the latest version is 10.4, and the previous version is 9.6.x, huh? It's a little weird, isn't it? 10.4 only "two paragraphs", while 9.6.x has three paragraphs, in fact, there have been three paragraphs before, 9 means a large version, 6 means a medium version, followed by a small version, the small version has only small functional improvements, will not have any impact on the data format, that is to say, your PG upgrade from 9.6.1 to 9.6.9, you directly upgrade to replace the old program, to ensure that there are no problems. But if your previous version is 9.5.3 and you want to upgrade to 9.6.9, you can't, because the intermediate version has changed, and you need to use a migration tool to convert your old data format to a new one. For version 10.4, which is the big version, which is the medium version, and which is the small version? I feel a little incoherent here. PG seems to have lost its "big version" when upgrading from 9 to 10. Although 10 is the successor of 9, it should be considered a medium version, so there is no need to convert data from 10.1 to 10.4, just upgrade the program directly. So what's the next Chinese version of PG? Yes, it's 11. The next one should be 12. For software, if you don't have any historical baggage, I think it's easy to choose the latest one, such as 10.4, and upgrade 10.5 and 10.6 in the future.

By the way, PG10 was officially launched last year (2017), less than a year ago, and when I first came out, I thought, this "major upgrade" (think about the number iPhone X OS X 10 is very special, isn't it?) Can it bring a big improvement in performance? I tried and came to the conclusion: no. Indeed, its upgrade document does not mention any significant improvement in performance, it mainly increases the native support for table partitioning, table partitioning, that is, when there is a lot of data in your table, use table partitioning to improve read and write speed, as for the size of the table to recommend partitioning? PG's official document says: if the size of the table catches up with your host's memory, you can consider table partitioning. So, for tables with only tens of millions or millions of rows of data, are you sure you want to partition?

Npgsql

To use PG with. Net, you have to use nuget to introduce the Npgsql package, which is its official website: http://www.npgsql.org/, completely open source, which is actually the ADO.NET engine (ADO.NET Data Provider) for PG databases. Here is its help manual: http://www.npgsql.org/doc/index.html

There are not many difficulties here, all you need to do is install your PG database (either Windows or Linux, with no impact), then create a .NET project (I recommend using .NET Core), introduce Npgsql, and follow the simple examples in the instruction manual.

Of course, this article will not show you how to start using the select statement, the following is mainly about some of the difficulties or pitfalls we have overcome in the process of use.

Where's NVARCHAR?

The most commonly used text type in MSSQL is NVARCHAR, which is a text type with length restrictions. Correspondingly, there is VARCHAR in PG, so it's okay to use it, but the text type in PG is actually a little different from the text type in MSSQL. PG text can basically be considered unlimited in length. VARCHAR and TEXT are no different to PG internally, but when writing, VARCHAR will check the length, so in terms of performance. VARCHAR is not faster than TEXT, and it may be slower if it is serious, because it checks the length, so when you design the database, you can set all the text types to TEXT (or CITEXT mentioned later), and the length check can be done in the business system.

What if you want to be case-insensitive?

Most of the time, we want to be case-insensitive, but case-sensitive will bring a lot of confusion, cannot be queried, or there are users with the same name in the system, one named John and the other john,MSSQL can specify case-insensitivity when creating a library, while 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 you are installing PG10, if not, find the corresponding package), and then use the following command to create the CITEXT type:

CREATE EXTENSION IF NOT EXISTS CITEXT WITH SCHEMA public

Note: a database only needs to execute this command once.

If you are using a psql client to connect and use PG, it is already OK, and you will find that the fields of CITEXT are already case-insensitive, but if you are using Npgsql to access PG with code, CITEXT does not seem to work. The reason is that CITEXT is not a native type of PG. When you use the query statement, you need to explicitly tell PG with ":: CITEXT" after the parameter. Your parameter is of type CITEXT. The example is as follows:

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

Well, I admit it's a little troublesome, but just get used to it. I don't know what better way to do it yet.

NotSupportedException appears when using CITEXT

The presentation of this exception looks something like this:

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. In Npgsql.NpgsqlDataReader.GetValue (Int32 ordinal) and in Npgsql.NpgsqlDataReader.get_Item (Int32 ordinal).

To us, this mistake used to be like a ghost, appearing from time to time, just restart the service program when it appeared, no longer appear, and then occur again in a few weeks or months, sometimes it is not impossible to happen many times a day. It was only when I went to github for help that I finally figured out why. Link: https://github.com/npgsql/npgsql/issues/1635

To put it simply, PG has an internal ID value (called oid) for various data types. When Npgsql connects to the database for the first time, it gets these oid values and caches them. For the internal types of PG, such as INT, these oid values are fixed, but this does not seem to be the case for CITEXT, because the type CITEXT is created by ourselves with the CREATE EXTENSION command (see earlier in this article). Determine its oid when it is created. When we restore the database, we also recreate the CITEXT type, which causes the oid of CITEXT to change, but Npgsql doesn't know about it, so this exception occurs. We often need to restore the database in the development process, which leads to this problem.

Solution 1, when the database is restored, call NpgsqlConnection.ReloadTypes () to refresh various types of oid, but this is very difficult, because restoring the database is a manual operation, open the web page after it is done, and click on the notification program above?

Solution 2, restart the program. This is actually similar to solution 1, except that you don't need to write any additional code, considering that restoring the database is not very frequent and is just done in the development environment, so restart it. Let's do it now. It is stipulated that you restart the service program after restoring the database. (it's easy to write a script to do this.)

Causes the program to crash when using transactions to perform a large number of operations

I also went to github for help on this question, link: https://github.com/npgsql/npgsql/issues/1838

This problem may be more serious than the previous one, because I probably can't catch the exception (that is, sometimes I can catch it, sometimes I can't), and the program crashes directly, which is a very wrong thing for a .NET program. Even if I didn't write try-catch alone, the outermost exception handler of the program should be able to catch the relevant Exception and log, right? But no, no log, no capture. So so far I suspect that this is a. Net bug, maybe it has nothing to do with Npgsql.

The cause of the problem, as described on github, is that it has been found, but cannot be fundamentally corrected. This problem is actually a simple "transaction timeout" problem.

When our program starts for the first time, it initializes the tables in the database and inserts a large amount of initialization data. Due to the special development environment of our company, the database latency is very high, so the insertion speed is very slow. Each insert can take up to dozens of milliseconds, (there is no problem in the production environment), so that more than 10,000 pieces of data lead to transaction timeout (the default transaction timeout is 1 minute). Of course, the solution is obvious: during initialization, temporarily increase the timeout value of TransactionScope to 10 minutes, which is no problem after all.

We can only prevent this kind of problem through some external workaround, which is difficult to solve fundamentally.

55000: disable prepared transactions

This is another tricky thing. First of all, the Chinese translation is not very good. This is an error message thrown by the database. Its English is "Prepared transactions are disabled". I think the correct Chinese translation should be: preprocessing transaction has been disabled. Alas, so I said why the English version, if prompted in Chinese, want to find answers on the Internet will be more obstacles.

Here is a simple example of the use of transactions:

What is meant by "preprocessing transaction"? In fact, it is very simple, that is, "transaction package transaction", which is a transaction that can be committed step by step. For example, I first opened a transaction A, and in this transaction I opened another transaction, BMagazine B, to commit, and then A to commit. PG is off by default for preprocessing transactions, of course, you can open it, edit the configuration file postgresql.conf, change max_prepared_transactions to 100 (the default is 0, 0 means disabled), and restart the PG service.

But are you sure you can really preprocess the transaction? From my point of view, we don't need it, but why is this a problem? There is still something wrong with our program, even if you can'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, which may be called by other methods, so it has a parameter of type DbConnection, indicating that the caller is responsible for opening the database connection and passing it, and the transaction is opened in A, but the caller does not know it, and the transaction is opened to form a preprocessing transaction.

2, this situation is more obscure, the database connection string, such as: Host=192.168.1.101; Username=postgres; Password=123456; Database=testdb Enlist=true, followed by a parameter called Enlist, is true, which means that when the connection is opened, it will automatically Enlist to the Transaction of the current execution context. If a transaction is opened in the current execution context (which is included in using (TransactionScope) in code), then the database connection will be automatically Enlist. Consider this scenario: method A will open the database connection to query something. Method B will also access the database, and method B will use the transaction, method An is called in the transaction, method A finds that there is Transaction in the current execution context when method An opens the database connection, so it automatically Enlist up, inadvertently forming a preprocessing transaction, and is still "distributed" (An and B may open different database connections), this situation should not be what you need.

So what should we do? Here's what I do:

1 the maxim preparedtransactions is still set to 0, turn it off, because we really don't need it, if we can use it, our code is written wrong, so if there is an exception of "disable prepared transactions", go back and check the code.

2. Remove Enlist=true from the database connection string, so that every time you use a transaction, you need to explicitly call conn.EnlistTransaction (Transaction.Current). Although you have a line of code, the semantics are more clear, and you don't have to consider whether it is the TransactionScope package DbConnection or the reverse DbConnection package TransactionScope.

3. Standardize our database access code to make clear which transactions 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

Its corresponding English is: Cound not serialize access due to read/write dependencies among transactions, how should this be understood? In fact, people who know the isolation level of database transactions should be familiar with this. .net 's TransactionScope defaults to the highest level of transaction isolation, Serializable (serializable). This level ensures the consistency of the data to the greatest extent, but it is also very expensive, for one thing, it is slow, and on the other hand, it is easy to have "inter-transaction read / write dependency". This is the mistake. Let me give you a simple example:

An and B simultaneously access a record in the test table with an id of 50. A reads the record, and then B updates the record and commits it. According to the rules of serializable isolation level, A does not know that B has updated the record, and A tries to modify the record after B commits. At this time, the database will let A transaction fail and throw this exception, because if A modifies successfully. It will cause the previous modification of B to be inadvertently lost, but the serialization isolation level does not allow this to happen.

Therefore, this is a "normal error". According to conventional business logic, it should be rare. If it does and occurs frequently, you need to consider whether the design of business logic is unreasonable. See if you can avoid this problem in terms of design. If this is the case with business logic, you can try it in the following ways:

1. Queue this parallel transaction with client code, create a thread-safe queue, and execute it one by one, which slows down, but ensures the success of each transaction.

Catch this exception and try again automatically. In fact, this is the orthodox practice recommended by the database.

3, lower the transaction isolation level, this may or may not occur, it all depends on your business, about the transaction isolation level, this is a big topic, I would like to write another article at an appropriate time

4. For the rare frequency, you don't have to deal with it, you just need to catch the exception type, and then prompt the user to try again. Many websites seem to do this.

This is the whole content of .NET + PostgreSQL practice and pit avoidance techniques, and more content related to .NET + PostgreSQL practices and pit avoidance techniques can be searched for previous articles or browse the following articles to learn! I believe the editor will add more knowledge to you. I hope you can support it!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.