Thread: Problem with very big queries.
Hello everybody. I want to send to postgresql-8.0 a very big query, select * from table where lala IN (....) Inside IN there are almost 60000 values. Unfortunately, the server terminates with a singal 11 and I get to my console the following: An I/O error occured while sending to the backend. An I/O error occured while sending to the backend. jdbc version is 8.0-322 JDBC 3. Could anybody help? Regards.
On Friday 11 April 2008 02:04:42 Dave Cramer wrote: > First you should upgrade your server to 8.0.15 > > The server is segfaulting. The driver is just reporting that the > server crashed. Hi! I am using 8.0.15 postgres server and unfortunately can't upgrade to 8.3 since I am using sp-gist tries. So, there is a limit in the allowable query length? Thanks. > > Dave > > On 10-Apr-08, at 6:52 PM, Panagiotis Papadakos wrote: > > Hello everybody. > > > > I want to send to postgresql-8.0 a very big query, > > select * from table where lala IN (....) > > Inside IN there are almost 60000 values. > > > > Unfortunately, the server terminates with a singal 11 and I get > > to my console the following: > > > > An I/O error occured while sending to the backend. > > An I/O error occured while sending to the backend. > > > > jdbc version is 8.0-322 JDBC 3. > > > > Could anybody help? > > > > Regards. > > > > -- > > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > > To make changes to your subscription: > > http://www.postgresql.org/mailpref/pgsql-jdbc
Panagiotis Papadakos <papadako@csd.uoc.gr> writes:
> I want to send to postgresql-8.0 a very big query,
> select * from table where lala IN (....)
> Inside IN there are almost 60000 values.
> Unfortunately, the server terminates with a singal 11 and I get
> to my console the following:
It shouldn't crash. I suspect you tried this, got
ERROR: stack depth limit exceeded
HINT: Increase the configuration parameter "max_stack_depth".
and blindly followed the HINT without any regard for whether the value
you picked was actually safe on your platform. If you make
max_stack_depth bigger than what the kernel allows, you will indeed
get a crash; but that's not a bug it's pilot error.
More recent PG versions try to limit max_stack_depth to a safe value,
but 8.0 just believes what you tell it.
regards, tom lane
Panagiotis Papadakos <papadako@csd.uoc.gr> writes:
> I am using 8.0.15 postgres server and unfortunately can't upgrade to 8.3
> since I am using sp-gist tries.
How about 8.2?
> So, there is a limit in the allowable query length?
Not per se. Sufficiently complex queries will run out of memory,
of course. 8.2 and up handle long IN lists a bit more efficiently
than prior versions, so you can use longer lists in the same amount
of memory.
regards, tom lane
On Friday 11 April 2008 02:22:30 Tom Lane wrote: > Panagiotis Papadakos <papadako@csd.uoc.gr> writes: > > I am using 8.0.15 postgres server and unfortunately can't upgrade to 8.3 > > since I am using sp-gist tries. > > How about 8.2? Unfortunately not. I tried to compile even with 8.1 but many headers were missing.... > > > So, there is a limit in the allowable query length? > > Not per se. Sufficiently complex queries will run out of memory, > of course. 8.2 and up handle long IN lists a bit more efficiently > than prior versions, so you can use longer lists in the same amount > of memory. > > regards, tom lane
On Friday 11 April 2008 02:20:51 Tom Lane wrote: > Panagiotis Papadakos <papadako@csd.uoc.gr> writes: > > I want to send to postgresql-8.0 a very big query, > > select * from table where lala IN (....) > > Inside IN there are almost 60000 values. > > > > Unfortunately, the server terminates with a singal 11 and I get > > to my console the following: > > It shouldn't crash. I suspect you tried this, got > > ERROR: stack depth limit exceeded > HINT: Increase the configuration parameter "max_stack_depth". > > and blindly followed the HINT without any regard for whether the value > you picked was actually safe on your platform. If you make > max_stack_depth bigger than what the kernel allows, you will indeed > get a crash; but that's not a bug it's pilot error. > > More recent PG versions try to limit max_stack_depth to a safe value, > but 8.0 just believes what you tell it. Yep you are correct. But I have set the value to 8192, based on ulimit -s but it still crashes. > > regards, tom lane
First you should upgrade your server to 8.0.15 The server is segfaulting. The driver is just reporting that the server crashed. Dave On 10-Apr-08, at 6:52 PM, Panagiotis Papadakos wrote: > Hello everybody. > > I want to send to postgresql-8.0 a very big query, > select * from table where lala IN (....) > Inside IN there are almost 60000 values. > > Unfortunately, the server terminates with a singal 11 and I get > to my console the following: > > An I/O error occured while sending to the backend. > An I/O error occured while sending to the backend. > > jdbc version is 8.0-322 JDBC 3. > > Could anybody help? > > Regards. > > -- > Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-jdbc
Panagiotis Papadakos wrote:
> Hello everybody.
>
> I want to send to postgresql-8.0 a very big query,
> select * from table where lala IN (....)
> Inside IN there are almost 60000 values.
>
I think I'd put those 60000 values in their own table, and use some sort
of join.
select t.f1,t.f2 from table as t, table2 as b WHERE t.lala = b.lala;
or something like that. (i'm lousy at figuring out joins)