Many update queries in a row but only a small portion get saved to the database

[betawarz]

I'm using dotConnect Postgres client version 5.10.141.0. I wrote a simple C# application that queries for a list of rows in which column foo is null. This results in about 30,000 matching rows. The program then loops over each of those rows and sends an update query to put some data into that column. During this loop, the ExecuteNonQuery function reports back a value greater than zero, indicating that it made a successful query, at about a pace of 1,000 or so rows a second. This is a local Postgres server on my development machine.

After the program finishes, I expected to look into the database and see all of those matching rows with data in that previously null column. To my surprise, almost all of the rows still had null columns.

So, I ran the program again and sure enough, it too continued to find rows with null values that it had found and attempted to update the last time I ran it. Each time I run the program, only about 2 or 3 thousand rows are actually updated, in Postgres. The rest of the rows return success from ExecuteNonQuery but don't actually get updated.

Does this sound like something in dotConnect or perhaps a setting in Postgres? Would you have any clue why this might be happening? I previously did this with your Oracle client library just fine, on our Oracle server.

Here is the function in question:

Code: Select all

public static void UpdateMethod2()
        {
            List<EmployeeRecord> employee_records = new List<EmployeeRecord>();

            using (PgSqlConnection c = new PgSqlConnection(Properties.Settings.Default.PostgresConnectionString))
            {
                // open the database connection
                c.Open();

                using (PgSqlCommand queryObject = c.CreateCommand())
                {
                    queryObject.CommandText = "SELECT COUNT(1) FROM evdb_employee_history WHERE history_id IN (SELECT MAX(history_id) FROM evdb_employee_history WHERE alias IS null AND email IS NOT null GROUP BY emplid)";

                    // get a count of the reords to be updated
                    int count = Convert.ToInt32(queryObject.ExecuteScalar());

                    Console.WriteLine("Receiving {0} query results ...", count);

                    // get all of the records that we may need to update
                    queryObject.CommandText = "SELECT emplid, email, alias, history_id FROM evdb_employee_history WHERE history_id IN (SELECT MAX(history_id) FROM evdb_employee_history WHERE alias IS NULL AND email IS NOT null GROUP BY emplid)";

                    using (PgSqlDataReader reader = queryObject.ExecuteReader())
                    {
                        while (reader.Read())
                        {
                            EmployeeRecord employee_record = new EmployeeRecord();
                            employee_record.Id = PostgresHelpers.ReadInt(reader, "emplid");
                            employee_record.Email = PostgresHelpers.ReadString(reader, "email");
                            employee_record.Alias = PostgresHelpers.ReadString(reader, "alias");
                            employee_record.HistoryId = PostgresHelpers.ReadInt(reader, "history_id");

                            // do not add anyone with an alias already
                            if (employee_record.Alias.Length > 0)
                                continue;

                            // do not add anyone with no email
                            if (employee_record.Email.Trim().Length == 0)
                                continue;

                            // format this persons alias
                            employee_record.Alias = employee_record.Email.Trim();

                            if (employee_record.Alias.Length > 0)
                                employee_records.Add(employee_record);
                        }
                    }
                }

                int current_record = 0;

                // loop over the ones that do not have an alias
                foreach (EmployeeRecord employee_record in employee_records)
                {
                    using (PgSqlCommand cmd = c.CreateCommand())
                    {
                        cmd.CommandText = "UPDATE evdb_employee_history SET alias = :alias WHERE history_id = :history_id";
                        cmd.Parameters.Add("alias", PgSqlType.VarChar).Value = employee_record.Alias;
                        cmd.Parameters.Add("history_id", PgSqlType.Int).Value = employee_record.HistoryId;

                        if (cmd.ExecuteNonQuery() > 0)
                            Console.WriteLine("[{0} of {1}] New alias for {2} is {3}", ++current_record, employee_records.Count, employee_record.Id, employee_record.Alias);
                    }
                }
            }
        }

[Pinturiccio]

We couldn't reproduce the issue. Your example is incomplete, so we created the EmployeeRecord class and the PostgresHelpers method. Besides, you haven't provided the script for your table, so we just created a table with 4 columns: emplid, email, alias, history_id. Then we inserted 30000 records into the table. All 30000 records have the NULL value in the 'alias' column.
Under these conditions all 30000 were updated. This was the result of your test sample, and the result in the database was the same.

To reproduce the issue, please send us the complete project with all the necessary classes and methods defined. Please also provide the script for your 'evdb_employee_history' table.

[betawarz]

It'd be too difficult to tear this apart enough to send you a complete working example. It sounds like your test was close enough, anyway. It must be some setting with my Postgres install, then. I wonder why Postgres would report that it's updating a column but then when I go look it didn't actually update it.

I'll take this to the Postgres IRC channel or forums. Thanks.