Crate and Npgsql in the same app?

[hlngo]

When creating a new app, Create requires to do the type mapping either at global level:
CrateDbDatabaseInfo.AddCrateDbSpecificTypeMappings(NpgsqlConnection.GlobalTypeMapper);

or do the same thing for connection:
CrateDbDatabaseInfo.AddCrateDbSpecificTypeMappings(con.TypeMapper);

If I use the global approach, is it safe to connect to an PostgreSQL server after doing that? The second approach has a significant performance hit, why is that?

[mikethebeer]

You should be fine if you go with the global approach. It should be possible to create multiple connections to different databases in prallel because when it connects it detects the database automatically and choses the correct type mappings.

However, it sounds a bit strange that you investigated a huge performance decrease if you apply the type mappings for each connection separately. Probably @apircher has more insights why this could be the case.

[apircher]

@hlngo, @mikethebeer The performance hit is caused by thousands of ArgumentExceptions that npgsql throws - 9.467 when just running a single test. Every one with a message like A PostgreSQL type with the name ... was not found in the database.

This is because the global type mapper per default contains mappings for every type supported by postgresql. When opening a connection, npgsql adds all mappings from the global type mapper to the connector´s type mapper and then tries to bind each type. While doing that, the mapper tries to get the corresponding PostgresType-instance from the current DatabaseInfo-instance and if that instance does not know a type with the given name, a ArgumentException is thrown.

To somewhat deal with this problem, we added the CrateDbDatabaseInfo.RemoveUnsupportedDataTypes-method. It removes the mappings not supported by CrateDB from the given INpgsqlTypeMapper. This method is also called in the CrateDbDatabaseInfo.AddCrateDbSpecificTypeMappings-method.

If you call that method at the global level, all mappings for types not supported by CrateDB are removed from the global type-mapper. And that will probably cause problems when connecting to PostgreSQL.

If you call it for the connection, the mappings for types not supported by CrateDB are only removed from the connector´s type mapper. But the method can only be called after opening the connection and while calling open, the type-mapper tries to bind all global mappings - what causes the performance hit.

I think we need some more npgsql changes to fix this. Will look into it today.

[hlngo]

@apircher Thanks for the detailed explanation. All makes sense.

[apircher]

I made some changes in the following branch: https://github.com/crate/npgsql-cratedb/tree/a/adapttypemappings

In that branch, the CrateDbDatabaseInfo.AddCrateDbSpecificTypeMappings-method is not needed anymore and the performance is also improved.

But it depends on some changes to npgsql which are contained in this PR: npgsql/npgsql#1938

[hlngo]

@apircher Is there any reason that you don't merge this one to dev branch?

[apircher]

@hlngo I guess we can merge that pretty soon. But since this commit 2d1027d the dev branch depends on the official npgsql and because PR#1938 is not merged at this time, things would not work.

[hlngo]

@apircher things move really slow with the npgsql. Is there anything that can be done to speed up the process?

[monanllc]

This issue may help: npgsql/npgsql#2186. The key is to make the ResetFactories public? https://github.com/npgsql/npgsql/blob/c362e1c996d432f1df7030af49966897c4e26f5a/src/Npgsql/NpgsqlDatabaseInfo.cs#L263

[monanllc]

@apircher @mikethebeer Is there a way to revert NpgsqlDatabaseInfo.RegisterFactory(new CrateDbDatabaseInfoFactory()); after I am done with the Crate so I can use Postgres without restarting the app?

[roji]

Please see this note on the design of database info factories, and how to have multiple ones co-exist side-by-side in the same application. In a nutshell, the CrateDB factory is expected to detect CrateDB, and return null otherwise, allowing the regular PostgreSQL type loading to occur.

[monanllc]

A simple app connects to CrateDB first then fails to open a connection to postgresql. I have looked at the CrateDbDatabaseInfoFactory and see it follows what @roji suggested. Do you guys have any idea why it fails?

[apircher]

@monanllc Surely you already use separate connection objects to access the two databases - right? Or do you use a connection object to connect to CrateDB first, then close it again and then use the same object to open a connection to PostgreSQL? I guess that could cause some problems...

[monanllc]

@apircher I used 2 different connections with Pooling=False.

[apircher]

@monanllc Ok, I thought so. We have to reproduce it to say more about it. That could take a bit.
Maybe we could help more if you can provide more Information about how it fails to open. Are any exceptions thrown?

[monanllc]

Please see the code below. The exception is "Object reference not set to an instance of an object".

// Crate
NpgsqlDatabaseInfo.RegisterFactory(new CrateDbDatabaseInfoFactory());
string crateConStr = "Server=localhost;Port=54321;User ID=test;Password=test;Timeout=0;Command Timeout=0;Pooling=False;";
using (var crateCon = new NpgsqlConnection(crateConStr))
{
    crateCon.Open();
    crateCon.TypeMapper.UseCrateDbObjectHandler();
    DataTable resultTable = null;
    using (var cmd = new NpgsqlCommand("SELECT * FROM doc.tweets LIMIT 100;", crateCon))
    {
        using (var adapter = new NpgsqlDataAdapter(cmd))
        {
            resultTable = new DataTable();
            resultTable.BeginLoadData();
            adapter.Fill(resultTable);
            resultTable.EndLoadData();
        }
    }
    //dataGrid.ItemsSource = resultTable.DefaultView;
}

// Connect to Posgresql
string pgConStr = "Database=; Host=localhost; Port=5432; Username=postgres; Password=test; Pooling=False;";
using (var pgCon = new NpgsqlConnection(pgConStr))
{
    try
    {
        pgCon.Open();  // throws "Object reference not set to an instance of an object"
    }
    catch (Exception ex)
    {
        MessageBox.Show(ex.Message);
    }
}