Обсуждение: Re: "IN" statement causing execution cancel?
All, I am having (yet another) problem. I am attempting to execute, via ODBC (thus, the PostgreSQL ODBC driver under Windows), a statement of the form: SELECT field1 FROM table WHERE field2 IN (list); Where "list" is a *very* long comma-delimited list of values (on the order of several hundred values). When I attempt to execute this query via the ODBC driver, a very long wait occurs (one minute or so), then I get back an error of "Execution Cancelled" - this error is reported both to my application, as well as in the error logs for the server PostgreSQL (7.3.2) is running on. I have also noticed the same error in the commlog you can set up to be created in the ODBC driver setup. The log also gives a strange error of an invalid character or something found at position 15 of my SQL statement - which makes no sense since that character is part of the field name, plus if I limit my search to only a few (say 10-20) values in the "IN" list, I don't get any error. What is strange, is that if I execute this same statement under psql (that is, the large list), it executes it fine, returning the expected results. I need to use ODBC for this application (it is a VB app). I have given thought to re-writing my SQL statement generation code to split it up into several SQL statements to be executed, and collate the results when they come back. However, this is a bandaid to the problem. BTW - running the same large query against my Access 97 DB gives back the results expected (ie, via JET - not ODBC). Is there some ODBC driver config option, or something on the server, that needs to be properly set for this to work? Is there a limit on the size of the IN value list? Thank you, Andrew L. Ayers Phoenix, Arizona -- CONFIDENTIALITY NOTICE -- This message is intended for the sole use of the individual and entity to who it is addressed, and may contain informationthat is privileged, confidential and exempt from disclosure under applicable law. If you are not the intendedaddressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy,disclose or distribute to anyone the message or any information contained in the message. If you have received thismessage in error, please immediately advise the sender by reply email, and delete the message. Thank you.
I don't know why that problem is occurring in postgresql. However, these two statements will return the same resultset: SELECT field1 FROM table WHERE field2 IN (1,2,3,4,5,6,7); SELECT field1 FROM table WHERE ',1,2,3,4,5,6,7,' like '%,'||field2||',%'; You may need to cast field2 to something to convert it to a string. Cheers ----- Original Message ----- From: "Andrew Ayers" <aayers@eldocomp.com> To: <pgsql-general@postgresql.org> Sent: Thursday, May 22, 2003 9:16 AM Subject: Re: [GENERAL] "IN" statement causing execution cancel? > All, > > I am having (yet another) problem. > > I am attempting to execute, via ODBC (thus, the PostgreSQL ODBC driver > under Windows), a statement of the form: > > SELECT field1 FROM table WHERE field2 IN (list); > > Where "list" is a *very* long comma-delimited list of values (on the > order of several hundred values). > > When I attempt to execute this query via the ODBC driver, a very long > wait occurs (one minute or so), then I get back an error of "Execution > Cancelled" - this error is reported both to my application, as well as > in the error logs for the server PostgreSQL (7.3.2) is running on. I > have also noticed the same error in the commlog you can set up to be > created in the ODBC driver setup. > > The log also gives a strange error of an invalid character or something > found at position 15 of my SQL statement - which makes no sense since > that character is part of the field name, plus if I limit my search to > only a few (say 10-20) values in the "IN" list, I don't get any error. > > What is strange, is that if I execute this same statement under psql > (that is, the large list), it executes it fine, returning the expected > results. > > I need to use ODBC for this application (it is a VB app). I have given > thought to re-writing my SQL statement generation code to split it up > into several SQL statements to be executed, and collate the results when > they come back. However, this is a bandaid to the problem. BTW - running > the same large query against my Access 97 DB gives back the results > expected (ie, via JET - not ODBC). > > Is there some ODBC driver config option, or something on the server, > that needs to be properly set for this to work? Is there a limit on the > size of the IN value list? > > Thank you, > > Andrew L. Ayers > Phoenix, Arizona > > > -- CONFIDENTIALITY NOTICE -- > > This message is intended for the sole use of the individual and entity to who it is addressed, and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are not the intended addressee, nor authorized to receive for the intended addressee, you are hereby notified that you may not use, copy, disclose or distribute to anyone the message or any information contained in the message. If you have received this message in error, please immediately advise the sender by reply email, and delete the message. Thank you. > > > ---------------------------(end of broadcast)--------------------------- > TIP 5: Have you checked our extensive FAQ? > > http://www.postgresql.org/docs/faqs/FAQ.html > >
On Wednesday 21 May 2003 10:16 pm, Andrew Ayers wrote: > All, > > I am having (yet another) problem. > > I am attempting to execute, via ODBC (thus, the PostgreSQL ODBC driver > under Windows), a statement of the form: > > SELECT field1 FROM table WHERE field2 IN (list); > > Where "list" is a *very* long comma-delimited list of values (on the > order of several hundred values). Good to see you've solved it by extending a timeout, but you might find it executes much faster if you: 1. CREATE TEMP TABLE search_list 2. Insert thousands of values 3. Join against search_list Especially if your thousands don't change much, then you could make the search_list table permanent. Do a search on the mailing list archives for "IN","slow","EXISTS" - PG's current handling of IN isn't as smart as it might be. -- Richard Huxton