[sqlalchemy] Re: postgres and server_side_cursors
Upon reviewing my code this morning it appears that I forgot to fix the scalar method. Updated patch attached. ~ Daniel --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~--- server_side_cursors.patch Description: Binary data On Jan 23, 2007, at 10:06 PM, Daniel Miller wrote: I think I was the original person who had this problem. I found a bit of time to troubleshoot it and came up with a patch. The problem seems to be in ResultProxy when it does the metadata = cursor.description bit. cursor.description is returning None because the cursor has not had any rows fetched yet. If I do a cursor.fetchone () then cursor.description returns the expected result. So it looks like the solution here is to defer the metadata translation (construction of ResultProxy.props) until after some data has been fetched from the cursor. Patch attached (works for me, but not heavily tested). ~ Daniel --~--~-~--~~~---~--~~ 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- [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/ group/sqlalchemy?hl=en -~--~~~~--~~--~--~--- server_side_cursors.patch On Jan 23, 2007, at 11:22 AM, Michael Bayer wrote: ive heard of this one already, which is why the option is turned off. unfortunately I cant reproduce that here, so until someone wants to figure out whats going on with that, not sure what I can do. we do have a ticket where someone commented that the server side cursor will cause things like rowcount to not function until the first row is fetched. I also do not observe that behavior on my system...asked the guy to post what cases cause that, big surprise, no response. its like our trac system is an oracle of shadowy myths and rumors. so if anyone wants to wrestle with the psycopg2 guys on this, and/or figure out what the issue is, they seem to be generally not excited by server side cursors in the first place, in favor of just using LIMIT on your SQL so that there is little advantage to the server side approach. from my point of view its not a critical issue since its true, you can just use LIMIT as appropriate. --~--~-~--~~~---~--~~ 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- [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/ group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: postgres and server_side_cursors
On Jan 24, 2007, at 8:17 AM, Daniel Miller wrote: Yeah, I know that's ugly. However, its definitely more visual overhead than actual overhead. Not to mention (as was said on the psycopg list) fetchone() should not be used many times in succession. but ResultProxy is used for every database, not just postgres. Instead, a batch of rows should be fetched from the database (fetchmany)--it's a much more efficient usage of the database round- trip. It would be cool if ResultProxy.__iter__ did this automatically (i.e. grabbed a batch of rows) rather than using fetchone(). i dont think ResultProxy should make that assumption. its not always appropriate to prefetch rows. for example, I just committed a fix to Oracle so that it can use binary types. and as it turns out, Oracle result sets that contain LOBs have a restriction that I am accustomed to in other languages (and also i think Pygresql, another PG driver we dont support right now, has this restriction for some of its datatypes) - that the row you fetch contains active data, which is invalidated if you fetch the next row without fully reading all columns. typically BLOBs. we might work around this in a future release by prefetching the column data as well. however, im not comfortable with SA result sets totally fetching entire grids of results, in all cases, by default. i can see a lot of people having a problem with that, particularly if and when there is someday a DBAPI that actually implements result- fetching correctly and users would like SA to allow it to work to its full potential. so i would favor some flags on ResultProxy that get passed from the dialect for prefetching of rows, prefetching of columns. flags on the PG and Oracle dialects can affect these options but i like them being off by default. it would also be nice if ResultProxy didnt have any conditionals stuck into fetchone() if the flags are turned off, so im thinking maybe a __new__()/metaclass method of providing a prefetching subclass of ResultProxy (PrefetchResultProxy) might be a way to compartmentalize that complexity only when needed. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: postgres and server_side_cursors
Michael Bayer wrote:
ive heard of this one already, which is why the option is turned off.
unfortunately I cant reproduce that here, so until someone wants to
figure out whats going on with that, not sure what I can do.
Tell me what I have to do. Here I have debian etch and pg 8.1.(the last
deb package)
I don't know if it can help, but a:
print key, self.props
at line 622 (so before the error), show me: 0 {}
we do have a ticket where someone commented that the server side
cursor will cause things like rowcount to not function until the
first row is fetched. I also do not observe that behavior on my
system...asked the guy to post what cases cause that, big surprise,
no response. its like our trac system is an oracle of shadowy myths
and rumors.
Just tried with a named cursor (conn.cursor(test)) with a raw
connection (without sa) and I see that the cur.rowcount have the number
of fetched records, so:
cur.execute(SELECT id FROM a_table_with_100k_rec)
cur.fetchone()
print cur.rowcount # print 1
cur.execute(SELECT id FROM a_table_with_100k_rec)
cur.fetchmany(1000)
print cur.rowcount # print 1000
Bye,
Michele
smime.p7s
Description: S/MIME Cryptographic Signature
[sqlalchemy] Re: postgres and server_side_cursors
Michael Bayer wrote:
ive heard of this one already, which is why the option is turned off.
unfortunately I cant reproduce that here, so until someone wants to
figure out whats going on with that, not sure what I can do.
After some tries, I see that is I wrote (very bad code line):
cursor.cursor.fetchone()
at line 553 at engine/base.py, all work.
with a:
print cur descr bef, cursor.description
cursor.cursor.fetchone()
print after, cursor.descriptio
cur descr bef
None after (('col1', 25, 10, -1, None, None, None), ...
end so on
Michele
smime.p7s
Description: S/MIME Cryptographic Signature
[sqlalchemy] Re: postgres and server_side_cursors
I think I was the original person who had this problem. I found a bit of time to troubleshoot it and came up with a patch. The problem seems to be in ResultProxy when it does the metadata = cursor.description bit. cursor.description is returning None because the cursor has not had any rows fetched yet. If I do a cursor.fetchone () then cursor.description returns the expected result. So it looks like the solution here is to defer the metadata translation (construction of ResultProxy.props) until after some data has been fetched from the cursor. Patch attached (works for me, but not heavily tested). ~ Daniel --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~--- server_side_cursors.patch Description: Binary data On Jan 23, 2007, at 11:22 AM, Michael Bayer wrote: ive heard of this one already, which is why the option is turned off. unfortunately I cant reproduce that here, so until someone wants to figure out whats going on with that, not sure what I can do. we do have a ticket where someone commented that the server side cursor will cause things like rowcount to not function until the first row is fetched. I also do not observe that behavior on my system...asked the guy to post what cases cause that, big surprise, no response. its like our trac system is an oracle of shadowy myths and rumors. so if anyone wants to wrestle with the psycopg2 guys on this, and/or figure out what the issue is, they seem to be generally not excited by server side cursors in the first place, in favor of just using LIMIT on your SQL so that there is little advantage to the server side approach. from my point of view its not a critical issue since its true, you can just use LIMIT as appropriate. --~--~-~--~~~---~--~~ 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- [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/ group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
[sqlalchemy] Re: postgres and server_side_cursors
ugh, adds an extra conditional into every fetchone() :) On Jan 23, 2007, at 10:06 PM, Daniel Miller wrote: I think I was the original person who had this problem. I found a bit of time to troubleshoot it and came up with a patch. The problem seems to be in ResultProxy when it does the metadata = cursor.description bit. cursor.description is returning None because the cursor has not had any rows fetched yet. If I do a cursor.fetchone () then cursor.description returns the expected result. So it looks like the solution here is to defer the metadata translation (construction of ResultProxy.props) until after some data has been fetched from the cursor. Patch attached (works for me, but not heavily tested). ~ Daniel server_side_cursors.patch On Jan 23, 2007, at 11:22 AM, Michael Bayer wrote: ive heard of this one already, which is why the option is turned off. unfortunately I cant reproduce that here, so until someone wants to figure out whats going on with that, not sure what I can do. we do have a ticket where someone commented that the server side cursor will cause things like rowcount to not function until the first row is fetched. I also do not observe that behavior on my system...asked the guy to post what cases cause that, big surprise, no response. its like our trac system is an oracle of shadowy myths and rumors. so if anyone wants to wrestle with the psycopg2 guys on this, and/or figure out what the issue is, they seem to be generally not excited by server side cursors in the first place, in favor of just using LIMIT on your SQL so that there is little advantage to the server side approach. from my point of view its not a critical issue since its true, you can just use LIMIT as appropriate. --~--~-~--~~~---~--~~ 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 [EMAIL PROTECTED] For more options, visit this group at http://groups.google.com/group/sqlalchemy?hl=en -~--~~~~--~~--~--~---
