New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Doc resource on MSSQL->PG migrations #2180

Open
roji opened this Issue Oct 4, 2018 · 4 comments

Comments

Projects
None yet
3 participants
@roji
Member

roji commented Oct 4, 2018

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)

@roji roji added the doc label Oct 4, 2018

@roji roji added this to the 4.1 milestone Oct 4, 2018

@YohDeadfall

This comment has been minimized.

Member

YohDeadfall commented Oct 4, 2018

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

@roji

This comment has been minimized.

Member

roji commented Oct 4, 2018

@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

This comment has been minimized.

Member

YohDeadfall commented Oct 6, 2018

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

This comment has been minimized.

Member

austindrenski commented Oct 6, 2018

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.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment