[sqlalchemy] Re: Does limit() work with update()?
On Jan 11, 2009, at 10:44 AM, Darren Govoni wrote: Hi, I have 2 records in the database. I made an expression to update only 1 record, but all are getting updated. works=session.query(Work).filter(tnow- Work.takentimedelta(minutes=15)) .filter (Work .completed==None).limit(1).with_lockmode(mode='update').update(values) Shouldn't the above query only perform the update on the limited results of the query? query.update() just issues an UPDATE statement. UPDATE doesn't support any kind of LIMITing keywords (maybe MySQL does, but thats not SQL). The Query should really be raising an error here without emitting any SQL. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Does limit() work with update()?
Thank you,
So I changed my query to a select/for update. then re-added the updated
rows in the transaction, then committed.
works=session.query(Work).filter(tnow-Work.takentimedelta(minutes=60)).
filter(Work.completed==None).limit(1).with_lockmode(mode='update').all()
When I run two instances of the program, the second one will block on
the query while the first is inside the transaction ('update'). BUT. the
second one should return 1 row when it unblocks because the first
instance only modified 1 row, leaving the other to satisfy the blockers
query. It doesn't return anything when the transaction is released to
the second instance. Peculiar.
I re-run the second instance after that and it then is able to find the
qualifying row. Is that correct behavior? Both program instances are the
same code.
On Sun, 2009-01-11 at 11:04 -0500, Michael Bayer wrote:
On Jan 11, 2009, at 10:44 AM, Darren Govoni wrote:
Hi,
I have 2 records in the database. I made an expression to update only
1 record, but all are getting updated.
works=session.query(Work).filter(tnow-
Work.takentimedelta(minutes=15))
.filter
(Work
.completed==None).limit(1).with_lockmode(mode='update').update(values)
Shouldn't the above query only perform the update on the limited
results
of the query?
query.update() just issues an UPDATE statement. UPDATE doesn't
support any kind of LIMITing keywords (maybe MySQL does, but thats not
SQL). The Query should really be raising an error here without
emitting any SQL.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Does limit() work with update()?
On Jan 11, 2009, at 11:18 AM, Darren Govoni wrote:
Thank you,
So I changed my query to a select/for update. then re-added the
updated
rows in the transaction, then committed.
works=session.query(Work).filter(tnow-
Work.takentimedelta(minutes=60)).
filter
(Work.completed==None).limit(1).with_lockmode(mode='update').all()
When I run two instances of the program, the second one will block on
the query while the first is inside the transaction ('update'). BUT.
the
second one should return 1 row when it unblocks because the first
instance only modified 1 row, leaving the other to satisfy the
blockers
query. It doesn't return anything when the transaction is released to
the second instance. Peculiar.
I re-run the second instance after that and it then is able to find
the
qualifying row. Is that correct behavior? Both program instances are
the
same code.
what I'm not sure about here is if you are expecting the UPDATE to
return the number of rows actually modified, which again is a MySQL
only thing, or the number of rows actually matched. I'm also not
sure if you are updating the rows in such a way that they won't match
after they're updated. So I only have a hazy view of the actual
operation. But from what I'm reading the behavior doesn't sound
correct. Check the SQL log output of both applications which should
illustrate the full conversation.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Does limit() work with update()?
Sorry for the haze. I'm using PostgreSQL and am checking their docs on
isolation to see that it is consistent with SA.
but here is a simplified example.
I have 2 rows in the database with 1 column FOO. Both rows have a NULL
value for column FOO.
Two programs, A and B. They are the same program.
I have a query that: selects for update, all rows with FOO=NULL, limit
1. This will block all other processes attempting the same query.
A goes first and selects for update. B thus blocks. Both are looking for
1 row, FOO=NULL.
A query returns 1 row. Inside the transaction. A updates that row to
FOO=A. B is still waiting. Only 1 row is updated, because 1 is returned
from the select. I use session.add(row) to simply re-add the mapped
object after changing the value.
A commits its change to 1 row. The second row is still FOO=NULL.
B unblocks. B returns 0 results.
A, B exit.
Re-run B.
B finds 1 row where FOO=NULL and sets FOO=B.
B exits.
Does that help clarify?
thank you.
On Sun, 2009-01-11 at 11:33 -0500, Michael Bayer wrote:
On Jan 11, 2009, at 11:18 AM, Darren Govoni wrote:
Thank you,
So I changed my query to a select/for update. then re-added the
updated
rows in the transaction, then committed.
works=session.query(Work).filter(tnow-
Work.takentimedelta(minutes=60)).
filter
(Work.completed==None).limit(1).with_lockmode(mode='update').all()
When I run two instances of the program, the second one will block on
the query while the first is inside the transaction ('update'). BUT.
the
second one should return 1 row when it unblocks because the first
instance only modified 1 row, leaving the other to satisfy the
blockers
query. It doesn't return anything when the transaction is released to
the second instance. Peculiar.
I re-run the second instance after that and it then is able to find
the
qualifying row. Is that correct behavior? Both program instances are
the
same code.
what I'm not sure about here is if you are expecting the UPDATE to
return the number of rows actually modified, which again is a MySQL
only thing, or the number of rows actually matched. I'm also not
sure if you are updating the rows in such a way that they won't match
after they're updated. So I only have a hazy view of the actual
operation. But from what I'm reading the behavior doesn't sound
correct. Check the SQL log output of both applications which should
illustrate the full conversation.
--~--~-~--~~~---~--~~
You received this message because you are subscribed to the Google Groups
sqlalchemy group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to
sqlalchemy+unsubscr...@googlegroups.com
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~--~~~~--~~--~--~---
[sqlalchemy] Re: Does limit() work with update()?
On Jan 11, 2009, at 11:40 AM, Darren Govoni wrote: Sorry for the haze. I'm using PostgreSQL and am checking their docs on isolation to see that it is consistent with SA. but here is a simplified example. I have 2 rows in the database with 1 column FOO. Both rows have a NULL value for column FOO. Two programs, A and B. They are the same program. I have a query that: selects for update, all rows with FOO=NULL, limit 1. This will block all other processes attempting the same query. A goes first and selects for update. B thus blocks. Both are looking for 1 row, FOO=NULL. A query returns 1 row. Inside the transaction. A updates that row to FOO=A. B is still waiting. Only 1 row is updated, because 1 is returned from the select. I use session.add(row) to simply re-add the mapped object after changing the value. A commits its change to 1 row. The second row is still FOO=NULL. B unblocks. B returns 0 results. A, B exit. Re-run B. B finds 1 row where FOO=NULL and sets FOO=B. B exits. Does that help clarify? yeah I understood all that (except for the session.add(row) part, which doesn't seem related). And no, it does not sound correct, in that when B is unblocked into the transaction, it should see the same thing as if it had just been run completely after the A transaction were committed. in any case you should view the SQL logs (echo=True) to see what the conversation is saying. If that all looks as expected, open up two postgres consoles and try the same conversation manually. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: Does limit() work with update()?
Hehe. I just did it what you suggested and it doesn't work there either. If I leave off the limit 1, it produces expected results. So at least SA is consistent with PG at the moment, even if PG is broken. Going to post on their list now, about this. Thanks. On Sun, 2009-01-11 at 11:51 -0500, Michael Bayer wrote: On Jan 11, 2009, at 11:40 AM, Darren Govoni wrote: Sorry for the haze. I'm using PostgreSQL and am checking their docs on isolation to see that it is consistent with SA. but here is a simplified example. I have 2 rows in the database with 1 column FOO. Both rows have a NULL value for column FOO. Two programs, A and B. They are the same program. I have a query that: selects for update, all rows with FOO=NULL, limit 1. This will block all other processes attempting the same query. A goes first and selects for update. B thus blocks. Both are looking for 1 row, FOO=NULL. A query returns 1 row. Inside the transaction. A updates that row to FOO=A. B is still waiting. Only 1 row is updated, because 1 is returned from the select. I use session.add(row) to simply re-add the mapped object after changing the value. A commits its change to 1 row. The second row is still FOO=NULL. B unblocks. B returns 0 results. A, B exit. Re-run B. B finds 1 row where FOO=NULL and sets FOO=B. B exits. Does that help clarify? yeah I understood all that (except for the session.add(row) part, which doesn't seem related). And no, it does not sound correct, in that when B is unblocked into the transaction, it should see the same thing as if it had just been run completely after the A transaction were committed. in any case you should view the SQL logs (echo=True) to see what the conversation is saying. If that all looks as expected, open up two postgres consoles and try the same conversation manually. --~--~-~--~~~---~--~~ You received this message because you are subscribed to the Google Groups sqlalchemy group. To post to this group, send email to sqlalchemy@googlegroups.com To unsubscribe from this group, send email to sqlalchemy+unsubscr...@googlegroups.com For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
