PostgreSQL query failed: COPY state must be terminated first

This kind of error usually indicates that your result
set has exceeded the available memory. I think the
"COPY" message is just noise.
The workaround would be to declare your query as a
cursor, and fetch the results in manageable chunks.

--- Shane Wright <me@shanewright.co.uk> wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi

I have a (PHP) script that pulls a whole bunch of
data from the database, does
a bit to it and spits it out, but with a large set
of data, I'm getting some
really weird things happen!

After doing a chunk of it (same place every time),
it dies with this error:

PostgreSQL query failed: COPY state must be
terminated first

And I get a whole bunch of random gubbins outputted
too...

oD������
server sent data ("D" message) without prior row
description ("T" message)

server sent data ("D" message) without prior row
description ("T" message)

server sent data ("D" message) without prior row
description ("T" message)

server sent data ("D" message) without prior row
description ("T" message)

server sent data ("D" message) without prior row
description ("T" message)

server sent data ("D" message) without prior row
description ("T" message)

unexpected character Y following empty query
response ("I" message)

server sent data ("D" message) without prior row
description ("T" message)

oD������
oD������
server sent data ("D" message) without prior row
description ("T" message)

server sent data ("D" message) without prior row
description ("T" message)

server sent data ("D" message) without prior row
description ("T" message)

oD������
server sent binary data ("B" message) without prior
row description ("T"
message)

server sent binary data ("B" message) without prior
row description ("T"
message)

server sent data ("D" message) without prior row
description ("T" message)

server sent data ("D" message) without prior row
description ("T" message)

server sent data ("D" message) without prior row
description ("T" message)

or this:

oD������
oD������
CE
unexpected character C following empty query
response ("I" message)

...which all seems very strange - I'm not using a
COPY (only SELECTs), and
I've no idea what the rest means.

Anyone seen this before? Any idea what or why or
how to fix it? I'm using
PostgreSQL 7.1.3 and PHP 4.1.1 (I know, I know,
upgrade.. but I can't as
its on my ISPs server..)

Any help or any ideas appreciated...

Cheers

- --
Shane
http://www.shanewright.co.uk/
Public key:
http://www.shanewright.co.uk/files/public_key.asc
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQE9rpvE5DXg6dCMBrQRAtstAKCtxynOlPuOqKGV1csinhD+93cQDQCghwUN

WjftHUqLKjo4jEy4HAXGmtw=
=Il/B
-----END PGP SIGNATURE-----

---------------------------(end of
broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please
send an appropriate
subscribe-nomail command to majordomo@postgresql.org
so that your
message can get through to the mailing list cleanly

__________________________________________________
Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos & More
http://faith.yahoo.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi

Thanks, that sounds plausible. However IIRC 7.1.x doesn't have cursors :(

It would be prohibitively complex to try to rework the queries - is there a
particular memory chunk its likely to be (and how to increase whats
available?) The server isn't short of RAM so should be a reasonable
workaround for now.

Thanks

Shane

On Thursday 17 October 2002 2:58 pm, Jeff Eckermann wrote:

This kind of error usually indicates that your result
set has exceeded the available memory. I think the
"COPY" message is just noise.
The workaround would be to declare your query as a
cursor, and fetch the results in manageable chunks.

- --
Shane
http://www.shanewright.co.uk/
Public key: http://www.shanewright.co.uk/files/public_key.asc
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQE9rt505DXg6dCMBrQRAgyQAKDC/FPOms8mwd+PgLSqkl5u5SoJcwCeKXeh
T/7JHmvzRk4Me687TJgq6BU=
=WeS6
-----END PGP SIGNATURE-----

--- Shane Wright <me@shanewright.co.uk> wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi

Thanks, that sounds plausible. However IIRC 7.1.x
doesn't have cursors :(

Sure it does:
http://www.us.postgresql.org/users-lounge/docs/7.1/reference/sql-declare.html

It would be prohibitively complex to try to rework
the queries - is there a
particular memory chunk its likely to be (and how to
increase whats
available?) The server isn't short of RAM so should
be a reasonable
workaround for now.

Others more knowledgable will have to answer that. I
had assumed that you are hitting some hard limit.

Thanks

Shane

On Thursday 17 October 2002 2:58 pm, Jeff Eckermann
wrote:

This kind of error usually indicates that your

result

set has exceeded the available memory. I think

the

"COPY" message is just noise.
The workaround would be to declare your query as a
cursor, and fetch the results in manageable

chunks.

- --
Shane
http://www.shanewright.co.uk/
Public key:
http://www.shanewright.co.uk/files/public_key.asc
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)

iD8DBQE9rt505DXg6dCMBrQRAgyQAKDC/FPOms8mwd+PgLSqkl5u5SoJcwCeKXeh

T/7JHmvzRk4Me687TJgq6BU=
=WeS6
-----END PGP SIGNATURE-----

---------------------------(end of
broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/users-lounge/docs/faq.html

__________________________________________________
Do you Yahoo!?
Faith Hill - Exclusive Performances, Videos & More
http://faith.yahoo.com

On Thu, Oct 17, 2002 at 04:59:48PM +0100, Shane Wright wrote:

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi

Thanks, that sounds plausible. However IIRC 7.1.x doesn't have cursors :(

Even 7.0.x had cursors, dunno before that.

It would be prohibitively complex to try to rework the queries - is there a
particular memory chunk its likely to be (and how to increase whats
available?) The server isn't short of RAM so should be a reasonable
workaround for now.

The result is stored on the *client* side, not the server. The result
reaches the client and will simply keep growing. I've accedently made
results sets several gig in size and no amount of memory will fix that.

--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Hi

Sure it does:
http://www.us.postgresql.org/users-lounge/docs/7.1/reference/sql-declare.ht
ml

Thats excellent - worked a treat :))

I could have sworn 7.1.x didn't have cursors.. maybe it was that stored
procedures couldn't return cursors... or maybe I'm just going mad.. oh
well..

Thanks

- --
Shane
http://www.shanewright.co.uk/
Public key: http://www.shanewright.co.uk/files/public_key.asc
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.0.7 (GNU/Linux)

iD4DBQE9r+Yh5DXg6dCMBrQRAqM+AKDKXe0fq73E3xq42NQKt4QSg7443QCXQb/g
NpCuLVpK9V126eFkHbE2jg==
=GurB
-----END PGP SIGNATURE-----