Thread: Problem with query length

Hello all

I sent this to the GENERAL list but have not received a reply in 2 days.
So, I decided to send this to the HACKERS list.

--------------------------------------------------------------------------------

Hello

We have two different machines both running Red Hat 5.2, but one machine

has PostgreSQL version 6.3 while the other machine has PostgreSQL
version 6.4.2. Our problem is that we have a table which we want to
store political boundaries as polygons. Taking into account the 8K limit

per tuple and the storage space for a polygon (4 + 32n) we determined
that we can only store 255 points for each polygon. Therefore, we
ensured that all our polygons have less than 250 points.

Our problem is with the insert command when we insert a large polygon
(~200 points). With version 6.3 we have no problem inserting the
polygon. In fact all the polygons were inserted, verifying that they
were less than the 8K limit. However with version 6.4.2, the backend
closes the connection during the insert.

The first time we noticed this, we had a lot of trailing zeros on the
values of the points and eliminating the zeros allowed a smaller polygon

(that previously failed with ~ 170 points) to be inserted in version
6.4.2. This seems to indicate that there is some kind of limit on the
length of the query string. Isolating the query string that failed
indicates that the string is about 4500 bytes.

Does anyone have any idea what happened between 6.3 and 6.4.2 and what
we can do to solve this problem? We checked the archives but only found
references to the 8K limit. Any help would be greatly appreciated.

Thank you for your time and effort in this matter.

Best regards

Taravudh

Taravudh Tipdecho <taravudh@hpcc.nectec.or.th> writes:
> Our problem is with the insert command when we insert a large polygon
> (~200 points). With version 6.3 we have no problem inserting the
> polygon. In fact all the polygons were inserted, verifying that they
> were less than the 8K limit. However with version 6.4.2, the backend
> closes the connection during the insert.
> The first time we noticed this, we had a lot of trailing zeros on the
> values of the points and eliminating the zeros allowed a smaller polygon
> (that previously failed with ~ 170 points) to be inserted in version
> 6.4.2. This seems to indicate that there is some kind of limit on the
> length of the query string. Isolating the query string that failed
> indicates that the string is about 4500 bytes.

That's really odd; it's hard to believe that the maximum query length
got shorter.

I presume the backend dropped a corefile when it crashed; can you use
gdb on the corefile to provide a backtrace?  Alternatively, can you
provide a fairly short psql script that demonstrates the problem?
(If you're right about the problem, just a CREATE TABLE and INSERT
oughta do it...)  I will look into it if I can reproduce it here.
        regards, tom lane