Обсуждение: PostgreSQL query failed: COPY state must be terminated first
-----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-----
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/ > There are 10 kinds of people in the world, those that can do binary > arithmetic and those that can't.
-----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-----