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:

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.
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.

One option to consider may be to declare and instantiate a separate connection for each command. — mjwills
– mjwills, Commented Apr 5, 2019 at 7:29
Separate connections good idea. But in this case I get not optimized code. Many functions which making the same operations. I want to have more elegant decision. Anyway, thank you! — Alex
– Alex, Commented Apr 5, 2019 at 7:31
Application is real-time app, that write data to database few times every second. And does it with few processes. Open and close connection every second for every process... I thinking, better if I open connection once, at start app and close it when app closing. Unfortunately, I'm new in PostgreSQL. So, I don't know nothing about performance for open/close connection often. — Alex
– Alex, Commented Apr 5, 2019 at 9:41

Laurenz Albe · Accepted Answer · 2019-04-05 08:07:47Z

9

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.

answered Apr 5, 2019 at 8:07

Laurenz Albe

257k22 gold badges312 silver badges388 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

schlebe · Accepted Answer · 2022-02-17 08:25:56Z

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 !

Collectives™ on Stack Overflow

PostgreSQL: command is already in progress

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related