PostgreSQL: command is already in progress

Question

My asynchronous function tries to select a single record from a table. This function accepts a few arguments passed from another function.

So, some processes (6 at least) can use it simultaneously. Often I get an error with the message "command is already in progress".

I know that the problem hides in the reader, because the reader is busy when another process tries to access it.

Let me publish the full code below:

async private void InsertToLog(List<Printer> APrinter, List<PrinterToGridBinds> AGridBind, int index)
        {
            if (!String.IsNullOrEmpty(APrinter[index].Type.Trim()) && !String.IsNullOrEmpty(AGridBind[index].extBatchNumber.Trim()) && !String.IsNullOrEmpty(AGridBind[index].extBatchCounter.Trim()) && !String.IsNullOrEmpty(AGridBind[index].extDIOCounter.Trim()))
            {
                string dio_count = "0";
                string GetDIOCounter = string.Format(@"SELECT dio_counter FROM {0} WHERE device_type = '{1}' AND batch_number = '{2}' ORDER BY id DESC
  LIMIT 1;", log_table_name, lst_PrinterStruct[index].Type, AGridBind[index].extBatchNumber);

                try
                {
                    NpgsqlCommand db_getCounter = new NpgsqlCommand(GetDIOCounter, conn);
                    if (conn.State != ConnectionState.Open)
                        conn.Open();
                    using (DbDataReader reader = await db_getCounter.ExecuteReaderAsync())
                    {
                        while (await reader.ReadAsync())
                            dio_count = reader[0].ToString().Trim();
                    }

                    AGridBind[index].extDIOCounter = (Int32.Parse(dio_count) + Int32.Parse(AGridBind[index].extDIOCounter.Trim())).ToString();
                    string Insert_SQL = String.Format(@"INSERT INTO {0} (device_type, batch_number, start_date, batch_counter, dio_counter) VALUES ('{1}', '{2}', '{3}', '{4}', '{5}') ON CONFLICT ON CONSTRAINT unique_log_key DO UPDATE SET batch_counter='{4}', dio_counter='{5}';", log_table_name, APrinter[index].Type.Trim(),
               AGridBind[index].extBatchNumber.Trim(), DateTime.Now.ToString(), AGridBind[index].extBatchCounter.Trim(), AGridBind[index].extDIOCounter.Trim());

                    var db_cmd = new NpgsqlCommand(Insert_SQL, conn);
                    int res = await db_cmd.ExecuteNonQueryAsync();
                }
                catch (Exception e)
                {
                    string FMessage = String.Format("Printer {0} \r\n Can not write to table\r\n Error: {1}",
                        lst_PrinterStruct[index].Name, e.Message);
                    MessageBox.Show(FMessage);
                }

                finally
                {
                    conn.Close();
                }
            }
        }

As you can see, the reader is wrapped by using here.

Anyway, I have what I have (an error). So, my question is how to avoid this error message (I'm about "command is already in progress")?

I have a few ideas about possible decisions. Maybe:

1. Set the DbDataReader as parameter. And the name of the reader can be generated by the Random function. But I don't know how to do that.

2. Wait until the current process has finished and closes the reader, then run the second etc. But I don't know how to tell the new process to wait until the previous has finished.

So, I need your help, guys.

Answer 1

I have encountered same problem in VB.Net when migrating OleDB program to PNGSQL.

My program executed simply a loop to read some records returned by a simple SQL SELECT.

'*******************************************************************
'* SelectMouvements()
'*******************************************************************

Private Function SelectMouvements() As Integer

    SQL = _
<sql-select>

SELECT *
  FROM mouvements
  INNER JOIN comptes
    ON comptes.no_compte = mouvements.personnal_account
  LEFT OUTER JOIN cartes
    ON cartes.no_carte = mouvements.no_carte
        
</sql-select>

    Dim cmd As New NpgsqlCommand(SQL, cn)
    Dim dr As NpgsqlDataReader

    dr = cmd.ExecuteReader()
    While dr.Read()
        grid.Rows.Add()
        With grid.Rows(iRow)
            .Cells(0).Value = dr("communication")
        ... 
        End With
        call NormalizeSomeFields(dr("name"))
    End While
End Sub

The problem with my code that doesn't exist when using OleDb is that NormalizeSomeFields() function was opening a second DataReader.

Private Sub NormalizeSomeFields(ByRef sNom as String)

    SQL = 
<sql-select>

SELECT NewNom
  FROM aliasnom
  WHERE Nom = <%= AddQuote(sNom) %>

<sql-select>

    Dim cmdNom As New NpgSqlCOmmand(SQL, cn)
    Dim drNom As NpgsqlDataReader

    drNom = cmdNom.ExecuteReader()
    If drNom.Read() Then
        sNom = drNom(0)
    End If
    drNom.Close()
End Sub

This program is returning NpgsqlOperationInProgresException on cmdNom.ExecuteReader() line.

---

SOLUTION

---

To solve this problem I have defined another NpgsqlConnection named cn2 just after code that is defining cn connection as in following lines

    cn = New NpgsqlConnection(cs)
    Try
        cn.Open()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

    cn2 = New NpgsqlConnection(cs)
    Try
        cn2.Open()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try

and I use it now when second DataReader is defined

    Dim cmdNom As New NpgSqlCOmmand(SQL, cn2)

NPGSQL seems to refuse multiple sets ... but accepts multiple connections !

Answer 2

If several threads share one database connection, you have to synchronize them so that only one of them uses the connection at the same time. You can only run one statement at a time in a PostgreSQL database session.

Since synchronization is cumbersome and may hurt your concurrency, you'd be better off using a connection pool, and each thread requests a connection from the pool if it needs one.