Обсуждение: Default "maximum rows to retrieve" setting
Hi, In the Preferences options, there's an option on the Query tab that says "Maximum rows to retrieve." This is currently set to 100, but I can't see how/where it takes effect. I have a table that's growing an average of 250 rows a day (currently at 1098 rows.) If I select the table, and use the toolbar button "view data in selected object", it retrieves all rows, with the limit drop-down set to "No Limit." If I open the query editor, and type "SELECT * FROM large_table;" that retrieves all rows as well. I have to manually key "LIMIT 1000" in the query, or change the drop-down in the view data dialogue (after all data has been downloaded.) Ideally what I'd like is to set the default rows to retrieve to 1000, so it downloads the first 1000 rows when I open the table, and then I can choose to retrieve others if I need to. Thanks Andy.
Andy Shellam wrote: > Hi, > > In the Preferences options, there's an option on the Query tab that says > "Maximum rows to retrieve." This is currently set to 100, but I can't > see how/where it takes effect. > > I have a table that's growing an average of 250 rows a day (currently at > 1098 rows.) If I select the table, and use the toolbar button "view > data in selected object", it retrieves all rows, with the limit > drop-down set to "No Limit." > > If I open the query editor, and type "SELECT * FROM large_table;" that > retrieves all rows as well. I have to manually key "LIMIT 1000" in the > query, or change the drop-down in the view data dialogue (after all data > has been downloaded.) What it used to do was display a warning before retrieving all the rows, and give you the option of aborting the select before attempting to download 10 million rows or whatever. Looking at the code, that seems to have been lost in this commit: http://svn.pgadmin.org/cgi-bin/viewcvs.cgi?rev=5098&view=rev, which rendered the option at least partially useless. I'll update the docs to reflect that. > Ideally what I'd like is to set the default rows to retrieve to 1000, so > it downloads the first 1000 rows when I open the table, and then I can > choose to retrieve others if I need to. That would be a whole different patch. Feel free to submit one :-) Regards, Dave.
Dave Page wrote: > >> Ideally what I'd like is to set the default rows to retrieve to 1000, so >> it downloads the first 1000 rows when I open the table, and then I can >> choose to retrieve others if I need to. >> I'd love to! But I'm afraid I don't know C/C++ programming, and have never done anything with WxWidgets. If someone else wants to have a go, I could see perhaps having a text box within the "View data (filtered)" dialogue that would take the default setting of "max. rows to retrieve" but give you the option to override it to 0 (unlimited) or some other figure, and that would apply the "LIMIT" clause to the query, just as it does the "WHERE" clause currently. So it would end up that "View all" does the same as it does now, but "View data (filtered)" gives you both the WHERE filter and the LIMIT clause. Hope this helps someone! Andy.
One of the useful things about the frontend/backend protocol (http:// www.postgresql.org/docs/8.2/interactive/protocol.html) is that it provides a way to set a value for the maximum number of rows to return. This avoids the major hassle of trying to analyze the query and figure out how to add a limit clause. Unfortunately, I don't recall seeing a way set this using libpq or EXECUTE. John On Mar 19, 2007, at 5:03 AM, Dave Page wrote: >> Ideally what I'd like is to set the default rows to retrieve to >> 1000, so >> it downloads the first 1000 rows when I open the table, and then I >> can >> choose to retrieve others if I need to. > > That would be a whole different patch. Feel free to submit one :-) John DeSoi, Ph.D. http://pgedit.com/ Power Tools for PostgreSQL
John DeSoi wrote: > One of the useful things about the frontend/backend protocol > (http://www.postgresql.org/docs/8.2/interactive/protocol.html) is that > it provides a way to set a value for the maximum number of rows to > return. This avoids the major hassle of trying to analyze the query and > figure out how to add a limit clause. Unfortunately, I don't recall > seeing a way set this using libpq or EXECUTE. No, I don't believe there is a way - and I'm not sure you can prepare a statement with more than one query in it either; certainly thats the case if using PQexecParams. Regards, Dave.