How to use Npgsql functions with .NET parallel methods?

[Christian LeMoussel]

Hello,

I'm a .NET develepor and I'm trying to use NPGSQL with parallel methods.

Here is my example,but I got exception "Backend sent unrecognized response type: \0"

Is it possible to use Npgsql functions with Parallel.For block?

    NpgsqlConnection PGconnexion = new NpgsqlConnection(...);
    string SqlParallel = "";
    int OID = -1;
    Parallel.For(0, 100, i =>
    {
        SqlParallel = string.Format(@"INSERT INTO ParallelTest(Id) VALUES({0}) RETURNING OID;", i);
        using (NpgsqlCommand PGcommandParallel = new NpgsqlCommand(SqlParallel, PGconnexion)) 
        {
            try { OID = (int)PGcommandParallel.ExecuteScalar(); }
            catch (Exception ex) { Console.WriteLine(ex.Message); }
        }
        Console.WriteLine("Insert: {0} OID: {1}", i, OID);
    });

-- Table: paralleltest

-- DROP TABLE paralleltest;

CREATE TABLE paralleltest
(
  id integer
)
WITH (
  OIDS=TRUE
);
GRANT ALL ON TABLE paralleltest TO public;

[Francisco Figueiredo Jr.]

Hi, Christian!

Npgsql, like other providers, aren't thread safe. As you are using multiple threads with the same connection you end up with this problem.

In order to make it work, you have to put the line which creates the connection inside the Parallel.For loop:

    string SqlParallel = "";
    int OID = -1;
    Parallel.For(0, 100, i =>
    {

       using (NpgsqlConnection PGconnexion = new NpgsqlConnection(...))

       {

             SqlParallel = string.Format(@"INSERT INTO ParallelTest(Id) VALUES({0}) RETURNING OID;", i);
             using (NpgsqlCommand PGcommandParallel = new NpgsqlCommand(SqlParallel, PGconnexion)) 
             {
                  try { OID = (int)PGcommandParallel.ExecuteScalar(); }
                  catch (Exception ex) { Console.WriteLine(ex.Message); }
             }
             Console.WriteLine("Insert: {0} OID: {1}", i, OID);
        } 

     });

I tested it here and it worked ok. Please, give it a try and let me know if you still have an issue with this change. 

Note that I used the "using" clause so the connection is closed as soon as the block finishes.

I hope it helps.

--
You received this message because you are subscribed to the Google Groups "Npgsql Help" group.
To unsubscribe from this group and stop receiving emails from it, send an email to npgsql-help...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/npgsql-help/55cbf325-a1cc-4e4c-90d8-7f407b817de4%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

--
Regards,

Francisco Figueiredo Jr.
Npgsql Lead Developer
http://www.npgsql.org
http://gplus.to/franciscojunior
http://fxjr.blogspot.com
http://twitter.com/franciscojunior

[Christian LeMoussel]

Hi Franscisco,

Strange .... Very strange .....

With this code

            
            string SqlParallel = "";
            int OID = -1;

            Parallel.For(0, 5, i =>

            {
                SqlParallel = string.Format(@"INSERT INTO ParallelTest(Id) VALUES({0}) RETURNING OID;", i);

                using (NpgsqlConnection PGconnexionParallel = new NpgsqlConnection(string.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", PGHost, PGPort, PGUser, PGPassword, PGDatabase)))
                {

                    using (NpgsqlCommand PGcommandParallel = new NpgsqlCommand(SqlParallel, PGconnexion))
                    {
                        try 
                        { 
                            OID = (int)PGcommandParallel.ExecuteScalar();

                            Console.WriteLine("Insert: {0} OID: {1}", i, OID);
                        }

                        catch (Exception ex) { Console.WriteLine(ex.Message); }
                    }
                }
            });

I get errors and incorrect values ​​in the table

Console Output

Insert: 0 OID: 16511
Insert: 0 OID: 16512
Insert: 0 OID: 16514
Insert: 0 OID: 16515
Backend sent unrecognized response type: i


Values in ParallelTest table

oid   id
16511 4
16512 4
16513 4
16514 1
16515 3

BR,

Christian


[Francisco Figueiredo Jr.]

Hi, Christian!

The problem is that you define a connection called pgconnexionparallel but later still use the pgconnexion :-)

I hope it helps.

--

You received this message because you are subscribed to the Google Groups "Npgsql Help" group.
To unsubscribe from this group and stop receiving emails from it, send an email to npgsql-help...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/npgsql-help/117aa0ea-165b-4433-80ce-bc74e6cba7c5%40googlegroups.com.

[Christian LeMoussel]

Franscisco, I'm confused

I changed the line with using (NpgsqlCommand PGcommandParallel = new NpgsqlCommand(SqlParallel, PGconnexionParallel))

On Win 7 Pro, I use
   Npgsql V 2.1.0-rc1
   PostgreSql V 9.3

I got

Console Output

The connection is not open.
The connection is not open.
The connection is not open.
The connection is not open.
The connection is not open.





[Francisco Figueiredo Jr.]

You have to call pgconnexionparallel.open inside the using pgconnexionparallel  block. That will make it work without any issues.

I hope it helps.

--
You received this message because you are subscribed to the Google Groups "Npgsql Help" group.
To unsubscribe from this group and stop receiving emails from it, send an email to npgsql-help...@googlegroups.com.

To view this discussion on the web visit https://groups.google.com/d/msgid/npgsql-help/d43ff1f1-2c3b-4164-a217-9004e3f2a043%40googlegroups.com.

[Christian LeMoussel]

So good !

Here thread safe solution.

        Parallel.For(0, 100, i =>
        {
            string SqlParallel = string.Format(@"INSERT INTO ParallelTest(Id) VALUES({0}) RETURNING OID;", i);

            using (NpgsqlConnection PGconnexionParallel = new NpgsqlConnection(string.Format("Server={0};Port={1};User Id={2};Password={3};Database={4};", PGHost, PGPort, PGUser, PGPassword, PGDatabase)))
            {

                PGconnexionParallel.Open();

                using (NpgsqlCommand PGcommandParallel = new NpgsqlCommand(SqlParallel, PGconnexionParallel))

                {
                    try 
                    { 
                        int OID = (int)PGcommandParallel.ExecuteScalar();
                        Console.WriteLine("SqlParallel: {0} OID: {1}", SqlParallel, OID);