Thread: Out of memory with ODBC
I tried posting this to the OBC list but didn't get any responses, so
I'm trying my luck here.
> I'm receiving an out of memory error when trying to execute a long,
> complex query involving many temp tables. I'm using psqlODBC ver
> 8.02.0500 and PG 8.2.4 on WinXP. I'm trying to execute this query via
> a pass-through query in MS Access 2003. If I execute this query in
> PGAdmin III it runs without any errors and returns no records.
>
> My connection string in Access is:
> ODBC; DRIVER={PostgreSQL Unicode}; SERVER=myServer; DATABASE=iqdb;
> PORT=5432; UID=myUserName; PWD=xxxxxxx; B9=0; B7=1; B3=1; C8=1
>
> I've attached the log file which contains the SQL I'm trying to
> execute and the error details. I've tried setting UseDeclareFetch = 1
> but it didn't resolve the error. Any suggestions?
Mike
Attachment
Relyea, Mike wrote:
> I tried posting this to the OBC list but didn't get any responses, so
> I'm trying my luck here.
>
>> I'm receiving an out of memory error when trying to execute a long,
>> complex query involving many temp tables. I'm using psqlODBC ver
>> 8.02.0500 and PG 8.2.4 on WinXP. I'm trying to execute this query via
>> a pass-through query in MS Access 2003. If I execute this query in
>> PGAdmin III it runs without any errors and returns no records.
Hmm. So what's different about the queries?
>> My connection string in Access is:
>> ODBC; DRIVER={PostgreSQL Unicode}; SERVER=myServer; DATABASE=iqdb;
>> PORT=5432; UID=myUserName; PWD=xxxxxxx; B9=0; B7=1; B3=1; C8=1
>>
>> I've attached the log file which contains the SQL I'm trying to
>> execute and the error details. I've tried setting UseDeclareFetch = 1
>
>> but it didn't resolve the error. Any suggestions?
[145.188]ERROR from backend during send_query: 'SERROR'
[145.188]ERROR from backend during send_query: 'C53200'
[145.188]ERROR from backend during send_query: 'Mout of memory'
[145.188]ERROR from backend during send_query: 'DFailed on request of
size 16.'
[145.188]ERROR from backend during send_query: 'Faset.c'
[145.188]ERROR from backend during send_query: 'L712'
[145.188]ERROR from backend during send_query: 'RAllocSetAlloc'
OK, so this seems to be a server-side error, which means something
should be in the server logs. Is there anything?
Oh, and I'd probably split that query into about a dozen smaller ones -
one per statement.
--
Richard Huxton
Archonet Ltd
> From: Richard Huxton [mailto:dev@archonet.com] > > Relyea, Mike wrote: >> If I execute this query in PGAdmin III it runs without any errors and returns no records. > > Hmm. So what's different about the queries? > Nothing. The SQL is identical. I copied out of the log file and pasted into PGAdmin. > [145.188]ERROR from backend during send_query: 'SERROR' > [145.188]ERROR from backend during send_query: 'C53200' > [145.188]ERROR from backend during send_query: 'Mout of memory' > [145.188]ERROR from backend during send_query: 'DFailed on > request of size 16.' > [145.188]ERROR from backend during send_query: 'Faset.c' > [145.188]ERROR from backend during send_query: 'L712' > [145.188]ERROR from backend during send_query: 'RAllocSetAlloc' > > OK, so this seems to be a server-side error, which means > something should be in the server logs. Is there anything? I've pasted below what I found immediately before the error. > > Oh, and I'd probably split that query into about a dozen > smaller ones - one per statement. What do you mean one per statement? One per transaction? TopMemoryContext: 475184 total in 11 blocks; 12016 free (27 chunks); 463168 used Local Buffer Lookup Table: 8192 total in 1 blocks; 1776 free (0 chunks); 6416 used TopTransactionContext: 122880 total in 4 blocks; 14064 free (5 chunks); 108816 used Type information cache: 8192 total in 1 blocks; 1800 free (0 chunks); 6392 used MessageContext: 1946198040 total in 258 blocks; 26624 free (43 chunks); 1946171416 used JoinRelHashTable: 516096 total in 6 blocks; 169496 free (9 chunks); 346600 used JoinRelHashTable: 8192 total in 1 blocks; 4864 free (0 chunks); 3328 used JoinRelHashTable: 8192 total in 1 blocks; 4864 free (0 chunks); 3328 used JoinRelHashTable: 8192 total in 1 blocks; 4864 free (0 chunks); 3328 used JoinRelHashTable: 8192 total in 1 blocks; 4864 free (0 chunks); 3328 used JoinRelHashTable: 8192 total in 1 blocks; 4864 free (0 chunks); 3328 used JoinRelHashTable: 8192 total in 1 blocks; 4864 free (0 chunks); 3328 used JoinRelHashTable: 8192 total in 1 blocks; 4864 free (0 chunks); 3328 used JoinRelHashTable: 8192 total in 1 blocks; 4864 free (0 chunks); 3328 used JoinRelHashTable: 8192 total in 1 blocks; 4864 free (0 chunks); 3328 used JoinRelHashTable: 8192 total in 1 blocks; 4864 free (0 chunks); 3328 used JoinRelHashTable: 8192 total in 1 blocks; 4864 free (0 chunks); 3328 used JoinRelHashTable: 8192 total in 1 blocks; 4864 free (0 chunks); 3328 used JoinRelHashTable: 8192 total in 1 blocks; 4864 free (0 chunks); 3328 used JoinRelHashTable: 8192 total in 1 blocks; 4864 free (0 chunks); 3328 used Operator class cache: 8192 total in 1 blocks; 4872 free (0 chunks); 3320 used smgr relation table: 24576 total in 2 blocks; 16080 free (4 chunks); 8496 used TransactionAbortContext: 32768 total in 1 blocks; 32752 free (0 chunks); 16 used Portal hash: 8192 total in 1 blocks; 3912 free (0 chunks); 4280 used PortalMemory: 8192 total in 1 blocks; 8176 free (1 chunks); 16 used Relcache by OID: 8192 total in 1 blocks; 1816 free (0 chunks); 6376 used CacheMemoryContext: 1183288 total in 20 blocks; 378352 free (1 chunks); 804936 used MMCommonMeasurementsID_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used MMColorID_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used tblManualMeasurements_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used tblTestResultsComments_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used tblTestTypes_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used tblZones_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used PrintersModelID: 1024 total in 1 blocks; 392 free (0 chunks); 632 used tblPrinters_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used tblSuppliers_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used CIMachineID_idx: 1024 total in 1 blocks; 352 free (0 chunks); 672 used CIMachineIDColorID_idx: 1024 total in 1 blocks; 328 free (0 chunks); 696 used CIColorID_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used tblCartridgeInfo_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used CMMachineID_idx: 1024 total in 1 blocks; 352 free (0 chunks); 672 used CMMachineIDPrintCopyID_idx: 1024 total in 1 blocks; 328 free (0 chunks); 696 used tblCommonMeasurements_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_toast_101745681_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_toast_101745676_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_toast_101745671_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_toast_101745666_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_toast_101745661_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_toast_101745654_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_toast_101745649_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_toast_101745644_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_toast_101745639_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_toast_101745634_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_toast_101745629_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_toast_101745624_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_toast_101745619_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_toast_101745614_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_toast_2619_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used TargetsTargetID: 1024 total in 1 blocks; 352 free (0 chunks); 672 used Targets_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used AMAnalysisModuleName_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used AnalysisModules_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used ParameterNames_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used PVParameterID_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used PVMeasurementID_idx: 1024 total in 1 blocks; 352 free (0 chunks); 672 used MpsaID_idx: 1024 total in 1 blocks; 352 free (0 chunks); 672 used MmsmTag_idx: 1024 total in 1 blocks; 352 free (0 chunks); 672 used MTargetID_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used MMetricID_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used MMeasurementUuid_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used MMeasurementID_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used Measurements_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used Measurements_MeasurementUuid_key: 1024 total in 1 blocks; 392 free (0 chunks); 632 used PSApsaUuid_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used PSApsaID_idx: 1024 total in 1 blocks; 352 free (0 chunks); 672 used PSAImageID_idx: 1024 total in 1 blocks; 352 free (0 chunks); 672 used PrintSampleAnalyses_psaUuid_key: 1024 total in 1 blocks; 392 free (0 chunks); 632 used PrintSampleAnalyses_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used DIPrintSampleID_idx: 1024 total in 1 blocks; 352 free (0 chunks); 672 used DIImageID_idx: 1024 total in 1 blocks; 352 free (0 chunks); 672 used DIDigitalImageUuidID_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used DigitalImages_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used DigitalImages_DigitalImageUuid_key: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_shdepend_depender_index: 1024 total in 1 blocks; 216 free (0 chunks); 808 used pg_depend_depender_index: 1024 total in 1 blocks; 216 free (0 chunks); 808 used pg_depend_reference_index: 1024 total in 1 blocks; 216 free (0 chunks); 808 used pg_shdepend_reference_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used TIPrinterID: 1024 total in 1 blocks; 392 free (0 chunks); 632 used TIMachineID_idx: 1024 total in 1 blocks; 352 free (0 chunks); 672 used tblTestInformation_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used PSTestPatternName_idx: 1024 total in 1 blocks; 392 free (0 chunks); 632 used PSPrintSampleID_idx: 1024 total in 1 blocks; 352 free (0 chunks); 672 used PSMachineID_idx: 1024 total in 1 blocks; 352 free (0 chunks); 672 used PSMachineIDTestPatternName_idx: 1024 total in 1 blocks; 288 free (0 chunks); 736 used PSMachineIDPrintCopyID_idx: 1024 total in 1 blocks; 328 free (0 chunks); 696 used PrintSamples_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used FamiliesFamilyID: 1024 total in 1 blocks; 392 free (0 chunks); 632 used tblFamilies_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used tblCartridgeTypes_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used tblSQLStatements_pkey: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_am_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used pg_am_name_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_attrdef_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_attrdef_adrelid_adnum_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used pg_index_indrelid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used pg_type_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used pg_type_typname_nsp_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used pg_statistic_relid_att_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used pg_class_relname_nsp_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used pg_proc_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used pg_proc_proname_args_nsp_index: 1024 total in 1 blocks; 216 free (0 chunks); 808 used pg_operator_oprname_l_r_n_index: 1024 total in 1 blocks; 152 free (0 chunks); 872 used pg_namespace_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used pg_namespace_nspname_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used pg_language_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_language_name_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_inherits_relid_seqno_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_database_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used pg_conversion_oid_index: 1024 total in 1 blocks; 392 free (0 chunks); 632 used pg_conversion_name_nsp_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_conversion_default_index: 1024 total in 1 blocks; 192 free (0 chunks); 832 used pg_opclass_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used pg_opclass_am_name_nsp_index: 1024 total in 1 blocks; 216 free (0 chunks); 808 used pg_cast_source_target_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used pg_authid_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used pg_authid_rolname_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used pg_auth_members_role_member_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_auth_members_member_role_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_attribute_relid_attnam_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used pg_amop_opr_opc_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used pg_aggregate_fnoid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used pg_trigger_tgrelid_tgname_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used pg_rewrite_rel_rulename_index: 1024 total in 1 blocks; 328 free (0 chunks); 696 used pg_operator_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used pg_amproc_opc_proc_index: 1024 total in 1 blocks; 216 free (0 chunks); 808 used pg_amop_opc_strat_index: 1024 total in 1 blocks; 216 free (0 chunks); 808 used pg_index_indexrelid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used pg_attribute_relid_attnum_index: 1024 total in 1 blocks; 288 free (0 chunks); 736 used pg_class_oid_index: 1024 total in 1 blocks; 352 free (0 chunks); 672 used MdSmgr: 8192 total in 1 blocks; 5320 free (0 chunks); 2872 used LOCALLOCK hash: 24576 total in 2 blocks; 14112 free (4 chunks); 10464 used Timezones: 49432 total in 2 blocks; 5968 free (0 chunks); 43464 used ErrorContext: 8192 total in 1 blocks; 8176 free (0 chunks); 16 used 2007-10-12 10:55:00 ERROR: out of memory 2007-10-12 10:55:00 DETAIL: Failed on request of size 16. 2007-10-12 10:55:00 STATEMENT: [SNIP STATEMENT]
Relyea, Mike wrote: >> From: Richard Huxton [mailto:dev@archonet.com] >> >> Relyea, Mike wrote: >>> If I execute this query in PGAdmin III it runs without any errors and > returns no records. >> Hmm. So what's different about the queries? > > Nothing. The SQL is identical. I copied out of the log file and pasted > into PGAdmin. Must be context then. >> [145.188]ERROR from backend during send_query: 'SERROR' >> [145.188]ERROR from backend during send_query: 'C53200' >> [145.188]ERROR from backend during send_query: 'Mout of memory' >> [145.188]ERROR from backend during send_query: 'DFailed on >> request of size 16.' >> [145.188]ERROR from backend during send_query: 'Faset.c' >> [145.188]ERROR from backend during send_query: 'L712' >> [145.188]ERROR from backend during send_query: 'RAllocSetAlloc' >> >> OK, so this seems to be a server-side error, which means >> something should be in the server logs. Is there anything? > > I've pasted below what I found immediately before the error. Thanks >> Oh, and I'd probably split that query into about a dozen >> smaller ones - one per statement. > > What do you mean one per statement? One per transaction? Send one query for each sql statement. That way if you get an error you know which failed without having to work through the SQL. > TopMemoryContext: 475184 total in 11 blocks; 12016 free (27 chunks); > 463168 used > Local Buffer Lookup Table: 8192 total in 1 blocks; 1776 free (0 chunks); > 6416 used > TopTransactionContext: 122880 total in 4 blocks; 14064 free (5 chunks); > 108816 used > Type information cache: 8192 total in 1 blocks; 1800 free (0 chunks); > 6392 used > MessageContext: 1946198040 total in 258 blocks; 26624 free (43 chunks); > 1946171416 used Well, I don't have to be a developer to know that if there's a memory problem it's that big number starting 1946... that's the problem. If that's bytes, it's ~ 1.9GB Do you see a backend process growing to 2GB+ before failure? A quick rummage through the source and I find this file, backend/utils/mmgr/README containing: MessageContext --- this context holds the current command message from the frontend, as well as any derived storage that need only live as long as the current message (for example, in simple-Query mode the parse and plan trees can live here). This context will be reset, and any children deleted, at the top of each cycle of the outer loop of PostgresMain. This is kept separate from per-transaction and per-portal contexts because a query string might need to live either a longer or shorter time than any single transaction or portal. Hmm - I can't think how that could reach 1.9GB in size, especially since it has to be something different between a "raw" connection and how ODBC is doing things. Can you reproduce this immediately (connect, query, crash), or does the system have to run for a while first? -- Richard Huxton Archonet Ltd
Richard Huxton <dev@archonet.com> writes:
> Hmm - I can't think how that could reach 1.9GB in size, especially since
> it has to be something different between a "raw" connection and how ODBC
> is doing things.
Try turning on log_statement to see what's really being executed.
I seem to recall reading that Access thinks it can mangle your SQL
as it sees fit.
regards, tom lane
Tom Lane wrote: > Richard Huxton <dev@archonet.com> writes: >> Hmm - I can't think how that could reach 1.9GB in size, especially since >> it has to be something different between a "raw" connection and how ODBC >> is doing things. > > Try turning on log_statement to see what's really being executed. > I seem to recall reading that Access thinks it can mangle your SQL > as it sees fit. Hmm - Mike said it was a pass-through query which should be safe. Also, the odbc log should have accurate SQL in it. -- Richard Huxton Archonet Ltd
> From: Richard Huxton [mailto:dev@archonet.com] > > MessageContext: 1946198040 total in 258 blocks; 26624 free (43 > > chunks); > > 1946171416 used > > Well, I don't have to be a developer to know that if there's > a memory problem it's that big number starting 1946... that's > the problem. If that's bytes, it's ~ 1.9GB > > Do you see a backend process growing to 2GB+ before failure? I'm running PG 8.2.4 on WinXP. I used the task manager (as a quick and dirty utility) to watch the memory usage of the backend and it seemed to peak around 1.2GB. My server only has 1.5GB installed memory, so that would seem to verify that the process does indeed run out of memory. > > A quick rummage through the source and I find this file, > backend/utils/mmgr/README containing: > > MessageContext --- this context holds the current command > message from the frontend, as well as any derived storage > that need only live as long as the current message (for > example, in simple-Query mode the parse and plan trees can > live here). This context will be reset, and any children > deleted, at the top of each cycle of the outer loop of PostgresMain. > This is kept separate from per-transaction and per-portal > contexts because a query string might need to live either a > longer or shorter time than any single transaction or portal. > > Hmm - I can't think how that could reach 1.9GB in size, > especially since it has to be something different between a > "raw" connection and how ODBC is doing things. > > Can you reproduce this immediately (connect, query, crash), > or does the system have to run for a while first? I rebooted my server (thankfully I don't have very many clients at all, so that helps) and before anybody else connected to it, ran the query and observed the same result. This seems to be a problem with the ODBC driver? How can I narrow that down further? Mike
Relyea, Mike wrote: >> From: Richard Huxton [mailto:dev@archonet.com] >> >> Do you see a backend process growing to 2GB+ before failure? > > I'm running PG 8.2.4 on WinXP. I used the task manager (as a quick and > dirty utility) to watch the memory usage of the backend and it seemed to > peak around 1.2GB. My server only has 1.5GB installed memory, so that > would seem to verify that the process does indeed run out of memory. Yep >> Can you reproduce this immediately (connect, query, crash), >> or does the system have to run for a while first? > > I rebooted my server (thankfully I don't have very many clients at all, > so that helps) and before anybody else connected to it, ran the query > and observed the same result. > > This seems to be a problem with the ODBC driver? How can I narrow that > down further? Well, first make 100% certain the query being executed is the one you see being sent from Access. If so, the next obvious thing would be to set up an empty database with the same structure as your live one and try the query on that. If *that* crashes too then you can rule out any data processing problems. It will also let you turn statement logging on in that database (ALTER DATABASE SET ...) and capture everything the ODBC driver sends. There might be something that leaps out at you. Take all of those statements and put them into a text-file and run them using psql -f <file>. That should cause the same crash. If it does, it means you have a self-contained test-case that someone else can look at for you. We can also test it on Linux/BSD etc. Then, if you still can't see where the problem is, replace the ODBC driver with a different (in your case older) version and see if you still get the problem. Might be worth going back a few versions too, to see if this is something introduced recently. http://www.postgresql.org/ftp/odbc/versions/msi/ Another option would be to try the odbc-ng project from Command Prompt and see if that does the same thing. I believe that's a completely separate code-base. https://projects.commandprompt.com/public/odbcng/wiki/Downloads Remember, *something* in the sequence of commands that get executed from Access must be different than when you execute them through pgAdmin. -- Richard Huxton Archonet Ltd
> From: Richard Huxton [mailto:dev@archonet.com] > Relyea, Mike wrote: > > This seems to be a problem with the ODBC driver? How can I narrow > > that down further? > > Well, first make 100% certain the query being executed is the > one you see being sent from Access. > > If so, the next obvious thing would be to set up an empty > database with the same structure as your live one and try the > query on that. If *that* crashes too then you can rule out > any data processing problems. > > It will also let you turn statement logging on in that > database (ALTER DATABASE SET ...) and capture everything the > ODBC driver sends. There might be something that leaps out at > you. Take all of those statements and put them into a > text-file and run them using psql -f <file>. That should > cause the same crash. > > If it does, it means you have a self-contained test-case that > someone else can look at for you. We can also test it on > Linux/BSD etc. > > Then, if you still can't see where the problem is, replace > the ODBC driver with a different (in your case older) version > and see if you still get the problem. Might be worth going > back a few versions too, to see if this is something > introduced recently. > > http://www.postgresql.org/ftp/odbc/versions/msi/ > > Another option would be to try the odbc-ng project from > Command Prompt and see if that does the same thing. I believe > that's a completely separate code-base. > > https://projects.commandprompt.com/public/odbcng/wiki/Downloads > > > Remember, *something* in the sequence of commands that get > executed from Access must be different than when you execute > them through pgAdmin. Thanks. I'll try that and see what happens.
> From: Richard Huxton [mailto:dev@archonet.com] > Relyea, Mike wrote: > > This seems to be a problem with the ODBC driver? How can I narrow > > that down further? > > Well, first make 100% certain the query being executed is the > one you see being sent from Access. I did by setting log_statement = 'all' and they were. > It will also let you turn statement logging on in that > database (ALTER DATABASE SET ...) and capture everything the > ODBC driver sends. There might be something that leaps out at > you. Take all of those statements and put them into a > text-file and run them using psql -f <file>. That should > cause the same crash. After setting log_statement = 'all', I ran my query using pgAdmin, and then ran the query using Access. I now had all of the commands sent to the DB by each application. > Remember, *something* in the sequence of commands that get > executed from Access must be different than when you execute > them through pgAdmin. That's exactly what it turned out to be. I looked at only the SET statements issued by each application and the differences popped right out at me. Turns out that Access was sending "set geqo to 'OFF'" and pgAdmin wasn't. As soon as I adjusted that setting in my connection string the problem went away. I'm not sure why it was off to begin with anyway. Thanks for your help! Mike
Relyea, Mike wrote: > > After setting log_statement = 'all', I ran my query using pgAdmin, and > then ran the query using Access. I now had all of the commands sent to > the DB by each application. > >> Remember, *something* in the sequence of commands that get >> executed from Access must be different than when you execute >> them through pgAdmin. > > That's exactly what it turned out to be. I looked at only the SET > statements issued by each application and the differences popped right > out at me. Turns out that Access was sending "set geqo to 'OFF'" and > pgAdmin wasn't. As soon as I adjusted that setting in my connection > string the problem went away. I'm not sure why it was off to begin with > anyway. Good detective work. I'm a bit puzzled why this would cause out-of-memory problems though. If you get a chance, could you run an EXPLAIN <query> with geqo=off and we'll see what plan it was comint up with. -- Richard Huxton Archonet Ltd