Обсуждение: How can I configure the postgresql odbc to return 1 row at a time?
Hi, Is it possible to ask postgresql odbc driver to fetch the resulsets from the table one at a time instead of fetching allmillions rows into memory? Thank you, Jerry
--- Jerry Lam <jlam@sandvine.com> wrote: > Hi, > > Is it possible to ask postgresql odbc driver to > fetch the resulsets from the table one at a time > instead of fetching all millions rows into memory? Perhaps setting the "Use declare/fetch" driver option to true will help you. This will cause a server side cursor to be used, which will return the rows in blocks. What controls the size of the blocks, I have no idea; I've never messed with that. > > Thank you, > > Jerry > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > __________________________________ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/
You can use in the odbc.ini configuration the following definitions: Fetch = 2048 UseDeclareFetch = 1 "Fetch" defines the number of rows to fetch at a time. UseDeclareFetch activates the feature. Regards, Marko Ristola Jeff Eckermann wrote: >--- Jerry Lam <jlam@sandvine.com> wrote: > > >>Hi, >> >>Is it possible to ask postgresql odbc driver to >>fetch the resulsets from the table one at a time >>instead of fetching all millions rows into memory? >> >> > >Perhaps setting the "Use declare/fetch" driver option >to true will help you. This will cause a server side >cursor to be used, which will return the rows in >blocks. What controls the size of the blocks, I have >no idea; I've never messed with that. > > > >>Thank you, >> >>Jerry >> >>---------------------------(end of >>broadcast)--------------------------- >>TIP 5: Have you checked our extensive FAQ? >> >> http://www.postgresql.org/docs/faq >> >> >> > > > >__________________________________ >Do you Yahoo!? >Yahoo! Small Business - Try our new resources site! >http://smallbusiness.yahoo.com/resources/ > >---------------------------(end of broadcast)--------------------------- >TIP 3: if posting/reading through Usenet, please send an appropriate > subscribe-nomail command to majordomo@postgresql.org so that your > message can get through to the mailing list cleanly > >
--- Jerry Lam <jlam@sandvine.com> wrote: > Thanks for the reply. I wonder if there is any > documentation for all the possible configurations in > posgresql odbc? I assume that you have seen this (incomplete) coverage: http://gborg.postgresql.org/project/psqlodbc/genpage.php?doc-config I am not aware of any other documentation, apart from the source code itself. Most of the configuration options are obscure, and I would say little used. I generally find good results running with default settings, or with minimal changes. Nearly all of the list discussion on these configuration options relates to a very small number of them. I would suggest that you post any questions you may have about specific options, and see if someone can help you. > Here is the list: > > #define INI_DSN DBMS_NAME > > #define INI_KDESC "Description" /* Data source > description */ > #define INI_SERVER "Servername" /* Name of Server > running PostgreSQL */ > #define INI_PORT "Port" /* Port on which the > Postmaster is listening */ > #define INI_DATABASE "Database" /* Database Name > */ > #define INI_USER "Username" /* Default User Name > */ > #define INI_PASSWORD "Password" /* Default > Password */ > #define INI_DEBUG "Debug" /* Debug flag */ > #define INI_FETCH "Fetch" /* Fetch Max Count */ > #define INI_SOCKET "Socket" /* Socket buffer size > */ > #define INI_READONLY "ReadOnly" /* Database is > read only */ > #define INI_COMMLOG "CommLog" /* Communication to > backend logging */ > #define INI_PROTOCOL "Protocol" /* What protocol > (6.2) */ > #define INI_OPTIMIZER "Optimizer" /* Use backend > genetic optimizer */ > #define INI_KSQO "Ksqo" /* Keyset query > optimization */ > #define INI_CONNSETTINGS "ConnSettings" /* Anything > to send to backend on successful connection */ > #define INI_UNIQUEINDEX "UniqueIndex" /* Recognize > unique indexes */ > #define INI_UNKNOWNSIZES "UnknownSizes" /* How to > handle unknown result set sizes */ > #define INI_CANCELASFREESTMT "CancelAsFreeStmt" > #define INI_USEDECLAREFETCH "UseDeclareFetch" /* > Use Declare/Fetch cursors */ > > /* More ini stuff */ > #define INI_TEXTASLONGVARCHAR "TextAsLongVarchar" > #define INI_UNKNOWNSASLONGVARCHAR > "UnknownsAsLongVarchar" > #define INI_BOOLSASCHAR "BoolsAsChar" > #define INI_MAXVARCHARSIZE "MaxVarcharSize" > #define INI_MAXLONGVARCHARSIZE "MaxLongVarcharSize" > > #define INI_FAKEOIDINDEX "FakeOidIndex" > #define INI_SHOWOIDCOLUMN "ShowOidColumn" > #define INI_ROWVERSIONING "RowVersioning" > #define INI_SHOWSYSTEMTABLES "ShowSystemTables" > #define INI_LIE "Lie" > #define INI_PARSE "Parse" > #define INI_EXTRASYSTABLEPREFIXES > "ExtraSysTablePrefixes" > > #define INI_TRANSLATIONNAME "TranslationName" > #define INI_TRANSLATIONDLL "TranslationDLL" > #define INI_TRANSLATIONOPTION "TranslationOption" > #define INI_DISALLOWPREMATURE "DisallowPremature" > #define INI_UPDATABLECURSORS "UpdatableCursors" > #define INI_LFCONVERSION "LFConversion" > #define INI_TRUEISMINUS1 "TrueIsMinus1" > #define INI_INT8AS "BI" > #define INI_BYTEAASLONGVARBINARY > "ByteaAsLongVarBinary" > #define INI_USESERVERSIDEPREPARE > "UseServerSidePrepare" > #define INI_LOWERCASEIDENTIFIER > "LowerCaseIdentifier" > > Some of them have description (but too short to > understand clearly what it will do) and some of them > have no description at all... > > Please help, > > Jerry > > -----Original Message----- > From: Jeff Eckermann > [mailto:jeff_eckermann@yahoo.com] > Sent: Monday, April 11, 2005 11:12 AM > To: Jerry Lam; pgsql-odbc@postgresql.org > Subject: Re: [ODBC] How can I configure the > postgresql odbc to return 1 > row at a time? > > > --- Jerry Lam <jlam@sandvine.com> wrote: > > Hi, > > > > Is it possible to ask postgresql odbc driver to > > fetch the resulsets from the table one at a time > > instead of fetching all millions rows into memory? > > Perhaps setting the "Use declare/fetch" driver > option > to true will help you. This will cause a server > side > cursor to be used, which will return the rows in > blocks. What controls the size of the blocks, I > have > no idea; I've never messed with that. > > > > > Thank you, > > > > Jerry > > > > ---------------------------(end of > > broadcast)--------------------------- > > TIP 5: Have you checked our extensive FAQ? > > > > http://www.postgresql.org/docs/faq > > > > > > __________________________________ > Do you Yahoo!? > Yahoo! Small Business - Try our new resources site! > http://smallbusiness.yahoo.com/resources/ > __________________________________ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/
Thanks for the reply. I wonder if there is any documentation for all the possible configurations in posgresql odbc? Here is the list: #define INI_DSN DBMS_NAME #define INI_KDESC "Description" /* Data source description */ #define INI_SERVER "Servername" /* Name of Server running PostgreSQL */ #define INI_PORT "Port" /* Port on which the Postmaster is listening */ #define INI_DATABASE "Database" /* Database Name */ #define INI_USER "Username" /* Default User Name */ #define INI_PASSWORD "Password" /* Default Password */ #define INI_DEBUG "Debug" /* Debug flag */ #define INI_FETCH "Fetch" /* Fetch Max Count */ #define INI_SOCKET "Socket" /* Socket buffer size */ #define INI_READONLY "ReadOnly" /* Database is read only */ #define INI_COMMLOG "CommLog" /* Communication to backend logging */ #define INI_PROTOCOL "Protocol" /* What protocol (6.2) */ #define INI_OPTIMIZER "Optimizer" /* Use backend genetic optimizer */ #define INI_KSQO "Ksqo" /* Keyset query optimization */ #define INI_CONNSETTINGS "ConnSettings" /* Anything to send to backend on successful connection */ #define INI_UNIQUEINDEX "UniqueIndex" /* Recognize unique indexes */ #define INI_UNKNOWNSIZES "UnknownSizes" /* How to handle unknown result set sizes */ #define INI_CANCELASFREESTMT "CancelAsFreeStmt" #define INI_USEDECLAREFETCH "UseDeclareFetch" /* Use Declare/Fetch cursors */ /* More ini stuff */ #define INI_TEXTASLONGVARCHAR "TextAsLongVarchar" #define INI_UNKNOWNSASLONGVARCHAR "UnknownsAsLongVarchar" #define INI_BOOLSASCHAR "BoolsAsChar" #define INI_MAXVARCHARSIZE "MaxVarcharSize" #define INI_MAXLONGVARCHARSIZE "MaxLongVarcharSize" #define INI_FAKEOIDINDEX "FakeOidIndex" #define INI_SHOWOIDCOLUMN "ShowOidColumn" #define INI_ROWVERSIONING "RowVersioning" #define INI_SHOWSYSTEMTABLES "ShowSystemTables" #define INI_LIE "Lie" #define INI_PARSE "Parse" #define INI_EXTRASYSTABLEPREFIXES "ExtraSysTablePrefixes" #define INI_TRANSLATIONNAME "TranslationName" #define INI_TRANSLATIONDLL "TranslationDLL" #define INI_TRANSLATIONOPTION "TranslationOption" #define INI_DISALLOWPREMATURE "DisallowPremature" #define INI_UPDATABLECURSORS "UpdatableCursors" #define INI_LFCONVERSION "LFConversion" #define INI_TRUEISMINUS1 "TrueIsMinus1" #define INI_INT8AS "BI" #define INI_BYTEAASLONGVARBINARY "ByteaAsLongVarBinary" #define INI_USESERVERSIDEPREPARE "UseServerSidePrepare" #define INI_LOWERCASEIDENTIFIER "LowerCaseIdentifier" Some of them have description (but too short to understand clearly what it will do) and some of them have no descriptionat all... Please help, Jerry -----Original Message----- From: Jeff Eckermann [mailto:jeff_eckermann@yahoo.com] Sent: Monday, April 11, 2005 11:12 AM To: Jerry Lam; pgsql-odbc@postgresql.org Subject: Re: [ODBC] How can I configure the postgresql odbc to return 1 row at a time? --- Jerry Lam <jlam@sandvine.com> wrote: > Hi, > > Is it possible to ask postgresql odbc driver to > fetch the resulsets from the table one at a time > instead of fetching all millions rows into memory? Perhaps setting the "Use declare/fetch" driver option to true will help you. This will cause a server side cursor to be used, which will return the rows in blocks. What controls the size of the blocks, I have no idea; I've never messed with that. > > Thank you, > > Jerry > > ---------------------------(end of > broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faq > __________________________________ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/