Bulk update does not fire as the example shows with Postgres #178

Closed
coordinate opened this Issue Dec 23, 2012 · 6 comments

Projects

None yet

3 participants

@coordinate

I used massive in my asp.net mvc project with postgreSQL.
This is my model.

public class Users : DynamicModel
{
    public Users() : base("pg", "\"users\"", "username") { }
}

I want to update all user with status 1 to status 0.

        var table = new Users();
        var users = table.All("WHERE status=1");
        foreach (var item in users.ToArray())
        {
            item.status = 0;
        }
        table.Save(users.ToArray());

But it dose not work. I debug and find that when massive save users, user's status is 1 but not 0.
So I have to change code like this:

        var table = new Users();
        var users = table.All("WHERE status=1");
        foreach (var item in users.ToArray())
        {
            var user = new { status = 0 };
            table.Update(user, item.username);
        }

Now it worked.

@robconery
Collaborator

Do you have any other information here? SQL strings? Why aren't you doing an update with a WHERE condition? I'm a bit confused.

Also - no need for exclamations - I read these things and they don't shout to me from afar.

@coordinate

The connect string is "Server=127.0.0.1;Port=5432;Database=postgres;User Id=postgres;Password=1;CommandTimeout=20;"

I do not have SQL strings.
I imitate the example “Inserts and Updates”, use "save" function to change user's status from 1 to 0.
but user's status was not change. So I use “update” function to do this and it worked.

@CreepyGnome
Contributor

@coordinate

The two examples you have are different by the way. The first one is setting the status to zero on each item in a foreach and then calling save for all the items. The second one is creating a NEW User with a status of zero, and then in the same scope calling Update with the NEW user and the current ITEM's username.

To be the same your second example would have to loop this way:

foreach (var item in users.ToArray())
{
    item.status = 0;
    table.Update(item, item.username);
}

Also there is no need to call ToArray() in the foreach loop you already have an IEnumerable converting it to an Array is unneeded overhead here.

Regardless, digging deeper into the table.All call it uses the Query(string, params object[]) method which I have an open bug pull request for that has a missing using statement on the Command, and this could have unexpected results that may or may not be related to your issue. Without the actual code and running through a debugger, etc this is just a shot in the dark, but you may want to try replacing in Massive.PostgresSQL.cs (line 237 in current master) which looks like below:

public virtual IEnumerable<dynamic> Query(string sql, params object[] args)
{
    using (var conn = OpenConnection())
    {
        var rdr = CreateCommand(sql, conn, args).ExecuteReader();
        while (rdr.Read())
        {
            yield return rdr.RecordToExpando(); ;
        }
    }
}

With this:

public virtual IEnumerable<dynamic> Query(string sql, params object[] args)
{
    using (var conn = OpenConnection())
    using (var rdr = CreateCommand(sql, conn, args).ExecuteReader())
    {
        while (rdr.Read())
        {
            yield return rdr.RecordToExpando();
        }
    }
}

And see if this works with your first example using the Save outside the foreach loop. If it does fix it report it.

If it doesn't work then you may want to debug and step into the Save call from your code and see what exactly is going on in your environment. Maybe one or more of your Updates in the transaction are failing and therefor all are rolling back and nothing is changed, where as when you do one update as a time in the loop if one fails it will not prevent the rest of them from being updated, also your update sample wasn't a valid match to the one used with save from what I can tell.

@coordinate

I change code at Massive.PostgresSQL.cs (line 237 in current master) . It do not work.
I debug with visual studio and found that when I step into "table.Save(users.ToArray());"
it first go into "public virtual IEnumerable Query(string sql, params object[] args)" founction.
Then all status is become 1(I have change them to 0). So status were not save as 0.

@CreepyGnome
Contributor

The code change was a long shot at best, thanks for trying.

Can you provide a reproducible unit test or a project that is self contained?

If the value you claim was changed to 0 but when you look at it in the debugger and it was still the orignal value of 1 then it was not actually ever changed on the object you inspected so maybe you are not changing the same objects you are attempting to save.

@robconery
Collaborator

Think I found the problem - in the example the loop is sending the users to an array, then iterating on that array. That's not the same as altering the list then sending to an array.

So - loop the users list (foreach var user in users), set the value, then call save on ToArray() - that will retain the changes.

@robconery robconery closed this Jan 8, 2013
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment