SEO

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

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



--
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

--
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 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

--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

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



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

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



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc

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


--
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)



--
Sent via pgsql-jdbc mailing list (pgsql-jdbc@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-jdbc