I have a .NET application that queries a DB and should return all records that was updated. But for some odd reason it only returns half of the records that it updated. For example if there is 100 records in the DB that is updated, NpgsqlCommand only returns 50 records.

Has anyone else come across this issue?

I've tested this in the DB and the query executes perfectly. Just when querying with NpgsqlCommand does it return consistently half the records that it has updated.

Here is the query that is being run:

string sql = UPDATE {TableA} set is_processed = 'Y'\n"  
"where id IN(\n"  
"SELECT id\n"  
$"FROM {TableA} b\n"  
"WHERE is_processed = 'N'\n"  
"order  by id"  
"FOR UPDATE SKIP LOCKED\n"  
"LIMIT 10\n"  
")\n"  
"returning *;";

And the method that is performing the call:

public static DataSet PerformGetUpdateLoad()
        {
            DataSet _ds = new DataSet();
            NpgsqlConnection conn = new NpgsqlConnection(dbConnectionString);
            NpgsqlCommand command = null;
            NpgsqlTransaction txn = null;
            try
            {
                conn.Open();
                txn = conn.BeginTransaction();
                command = new NpgsqlCommand(connection: conn, cmdText: sql);
                NpgsqlDataAdapter da = new NpgsqlDataAdapter(command);

                command.ExecuteNonQuery();

                _ds = new DataSet();
                da.Fill(_ds);
                txn.Commit();
            }

            catch (Exception ex)
            {
                txn.Rollback();
            }
            finally
            {
                command = null;
                conn.Close();
            }
            return _ds;
        }

P.S. I have it running in a loop that as long as _ds is filled it will continuously run until there is no more records. So if there is 100 records it should run 10 times but when executed it runs 5 times but updates 100 records? Not exactly sure what is wrong.

CodePudding user response：