Thread: Re: "IN" statement causing execution cancel?

Re: "IN" statement causing execution cancel?

From
Andrew Ayers
Date:
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. 


Re: "IN" statement causing execution cancel?

From
"Mark Wilson"
Date:
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
>
>



Re: "IN" statement causing execution cancel?

From
Richard Huxton
Date:
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