Doc resource on MSSQL->PG migrations

[roji]

From time to time users port an existing codebase from MSSQL to PostgreSQL, and run into issues - it would be useful to have a doc page that lists common pitfalls and issues. The following is a probably partial list that needs to be completed::

• Lack of MARS support (#462)

var myQueryable1 = myContext.MyDbset1.OrderByDescending(m => m.Timestamp);
var myQueryable2 = myContext.MyDbset2.Where(m => m.IsActive);

foreach (var q1 in myQueryable1) {
  foreach (var q2 in myQueryable2) { //Exception occurs here
    ...
  }
}

• No DateTimeOffset database type (timestamptz is not a good representation)
• Stored functions and procedures differences
  • Cannot return multiple resultsets from functions, need to manually use cursors (#1785)
  • No out params separate from the resultset
  • If you use the new PG 11 stored procedures, you have to call them manually (no CommandType.StoredProcedure support)

[YohDeadfall]

There is a connection limit on the server side. Therefore, batches should be used where possible. Arrays of composites perfectly fit this case.

[roji]

@YohDeadfall I think there's a connection limit in MSSQL as well, no? What's the exact difference? Also, I think batches should be used when possible also on MSSQL, regardless of any connection limits - they reduce roundtrips and therefore significantly improve perf...

[YohDeadfall]

There is a big difference between SQL Server and PostgreSQL.

The first has only one process per instance and uses SQLOS which manages connections, workers, etc. This allows to have move effective caching and processing than PostgreSQL allows. In addition it allows up to 32,717 user connections. You'll probably hit the resource limit.

The second one uses a single process per connection and allows up to 100 connections by default. At the same time it has reach type system with less restrictions than SQL Server. Therefore, many people doesn't use SqlDataRecord due to its complexity (you need to create a table valued type even if you want to insert array of primitives). In large companies it's a blocking issue since a database administrator only has the full access to the database and they need a strong reason why you want such a thing.

[austindrenski]

The differences in connection limit/batching sound like purely backened concerns. Whereas differences in MARS support, type mapping, and function/procedure handling are directly related to the Npgsql driver.

It seems to me like we would want the docs to explain the driver-level changes (e.g. changes in consuming code) needed when switching drivers, not a full guide for system-level differences when migrating databases.