Thread: Bug(?) with cursors using aggregate functions.

    I've been developing a web-based selection browser using cursors and discovered a
very frustrating little feature as I try to MOVE FORWARD/BACKWARD through my selection. If, for
a normal select statement (select this, that from table) I have no worries trying to tell my cursor to
move forwards or backwards any number of positions, even beyond the beginning or end - in which
case, it just will stay put as one might expect. However, if I pass in a select statement that uses an
aggregate function (select sum(this), that from table) then as soon as I try to scroll off the end or
prior to the beginning my cursor is busted and no longer fetchable. I have not tried to find out what
other functions or options (like grouping) have similar effects yet so the list isn't exhaustive. This is
all under Postgres 7.3 and 7.3.2 under linux and cygwin respectively.

    Is this a known bug? Any ability for me to ask my cursor what its present absolute offset is
(so I don't ask it to scroll past the end accidently)? Any other workarounds? This is making it quite
difficult for me to implement my browser cleanly.

    thanx & later,

        Ben Scherrey

postgres has cursors?

Benjamin Scherrey wrote:
>     I've been developing a web-based selection browser using cursors and discovered a
> very frustrating little feature as I try to MOVE FORWARD/BACKWARD through my selection. If, for
> a normal select statement (select this, that from table) I have no worries trying to tell my cursor to
> move forwards or backwards any number of positions, even beyond the beginning or end - in which
> case, it just will stay put as one might expect. However, if I pass in a select statement that uses an
> aggregate function (select sum(this), that from table) then as soon as I try to scroll off the end or
> prior to the beginning my cursor is busted and no longer fetchable. I have not tried to find out what
> other functions or options (like grouping) have similar effects yet so the list isn't exhaustive. This is
> all under Postgres 7.3 and 7.3.2 under linux and cygwin respectively.
>
>     Is this a known bug? Any ability for me to ask my cursor what its present absolute offset is
> (so I don't ask it to scroll past the end accidently)? Any other workarounds? This is making it quite
> difficult for me to implement my browser cleanly.
>
>     thanx & later,
>
>         Ben Scherrey
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
>

Benjamin Scherrey <scherrey@proteus-tech.com> writes:
> I've been developing a web-based selection browser using cursors and
> discovered a very frustrating little feature as I try to MOVE
> FORWARD/BACKWARD through my selection.

You can't run a nontrivial query plan (anything more than a seqscan or
indexscan) backwards with any reliability.  There are fixes for this in
CVS tip, but not in any released version :-(.  It should also be noted
that the fix consists of saving-aside copies of all rows emitted by the
underlying query, so if you are talking about a large result set you
might not like the performance...

            regards, tom lane

    Thanx for the response, Tom, which was, unfortunately, pretty close to what I feared. I'm
glad to hear that a fix is pending but I am concerned about the performance issue. Some of the
queries that my form will be browsing are in the tens of thousands of results. This is actually the
reason why I use cursors so I can just fetch one screen full of results but bounce back and forth in
the result set to get where I want. Saving copies of what actually gets fetched will be fine, but
saving copies of anything that I actually scroll by would quickly be prohibitive Presently I guess I
could do a fetch all and get the same result. A much preferable solution would be the ability to
determine absolute position of the query and even pay the performance cost of re-querying at some
points. I imagine that this it outside the SQL standard but I'm willing to take that penalty to get
around a complex query limitation. I haven't tried it yet but I presume a view built from a complex
query will give me the same problem?

    thanx & later,

        Ben Scherrey

4/28/2003 11:51:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

>Benjamin Scherrey <scherrey@proteus-tech.com> writes:
>> I've been developing a web-based selection browser using cursors and
>> discovered a very frustrating little feature as I try to MOVE
>> FORWARD/BACKWARD through my selection.
>
>You can't run a nontrivial query plan (anything more than a seqscan or
>indexscan) backwards with any reliability.  There are fixes for this in
>CVS tip, but not in any released version :-(.  It should also be noted
>that the fix consists of saving-aside copies of all rows emitted by the
>underlying query, so if you are talking about a large result set you
>might not like the performance...
>
>            regards, tom lane

Benjamin Scherrey <scherrey@proteus-tech.com> writes:
>     Thanx for the response, Tom, which was, unfortunately, pretty
> close to what I feared. I'm glad to hear that a fix is pending but I
> am concerned about the performance issue. Some of the queries that my
> form will be browsing are in the tens of thousands of results.

Tens-of-k rows doesn't sound like a big problem to me.  Tens-of-millions
would be a problem ...

            regards, tom lane

If you are using PHP, the ADODB library will do caching of results on the server file system and allow you to 'cursor'
throughit. Of course, if you need requerying, you'd have to trigger that and pay the time penalty. 

Benjamin Scherrey wrote:
>     Thanx for the response, Tom, which was, unfortunately, pretty close to what I feared. I'm
> glad to hear that a fix is pending but I am concerned about the performance issue. Some of the
> queries that my form will be browsing are in the tens of thousands of results. This is actually the
> reason why I use cursors so I can just fetch one screen full of results but bounce back and forth in
> the result set to get where I want. Saving copies of what actually gets fetched will be fine, but
> saving copies of anything that I actually scroll by would quickly be prohibitive Presently I guess I
> could do a fetch all and get the same result. A much preferable solution would be the ability to
> determine absolute position of the query and even pay the performance cost of re-querying at some
> points. I imagine that this it outside the SQL standard but I'm willing to take that penalty to get
> around a complex query limitation. I haven't tried it yet but I presume a view built from a complex
> query will give me the same problem?
>
>     thanx & later,
>
>         Ben Scherrey
>
> 4/28/2003 11:51:51 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
>
>
>>Benjamin Scherrey <scherrey@proteus-tech.com> writes:
>>
>>>I've been developing a web-based selection browser using cursors and
>>>discovered a very frustrating little feature as I try to MOVE
>>>FORWARD/BACKWARD through my selection.
>>
>>You can't run a nontrivial query plan (anything more than a seqscan or
>>indexscan) backwards with any reliability.  There are fixes for this in
>>CVS tip, but not in any released version :-(.  It should also be noted
>>that the fix consists of saving-aside copies of all rows emitted by the
>>underlying query, so if you are talking about a large result set you
>>might not like the performance...
>>
>>            regards, tom lane
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 6: Have you searched our list archives?
>
> http://archives.postgresql.org
>

Should I expect to be dump/restore from 7.2 to 7.4devel to work
cleanly?  Of course it's perfectly OK if the answer is "not surprising
on a devel build" :-) -- just letting people know.


To dump I used:

  pg_dump -O -v -h 192.168.50.90 -F c -U logs logs2 -f /mnt/data1/tmp/logs2.dmp

from the new machine with a couple-day-old CVS-tip.  The old machine (192.168.50.90)
is running 7.2.

To restore I tried both

  pg_restore --no-acl -O -v -U logs -d logs2 < /mnt/data1/tmp/logs2.dmp

which gave me

   pg_restore: restoring data for table d_ref
   pg_restore: [archiver (db)] error returned by PQputline
   pg_restore: *** aborded because of error

and

  pg_restore --no-acl -O -v -U logs < /mnt/data1/tmp/logs.dmp | psql logs2 logs

which gave me

  ERROR: CopyReadAttribute: Literal carriage return data value
  found in input that has newline termination; use \r
  CONTEXT: COPY FROM, line 1605109

       Ron

"Ron Mayer" <ron@intervideo.com> writes:
> Should I expect to be dump/restore from 7.2 to 7.4devel to work
> cleanly?  Of course it's perfectly OK if the answer is "not surprising
> on a devel build" :-) -- just letting people know.

Hmm, I'd expect it to work --- as you say, it's not hugely surprising if
there are bugs in there, but I wasn't aware of any.  Can you look more
closely and find out what's going on --- for example, what shows up in
the postmaster log with this case:

>    pg_restore: restoring data for table d_ref
>    pg_restore: [archiver (db)] error returned by PQputline
>    pg_restore: *** aborded because of error

One thing to watch out for is that cvs-tip pg_dump won't talk to a
pre-7.4 server at all (if it does, you've got some shared-library
misalignment).  That will have to be fixed, but it's not on
the critical path at the moment.

            regards, tom lane

Tom wrote:
> Hmm, I'd expect it to work --- as you say, it's not hugely surprising if
> there are bugs in there, but I wasn't aware of any.  Can you look more
> closely and find out what's going on --- for example, what shows up in
> the postmaster log with this case:
>
> >    pg_restore: restoring data for table d_ref
> >    pg_restore: [archiver (db)] error returned by PQputline
> >    pg_restore: *** aborted because of error
>

The last dozen lines or so are... (sorry if there are any typos,
copy/paste not working).

  COPY d_ref (ref_id, ref_host, ref_path, ref_query) from STDIN;
  IN:  pg_parse_query (postgres.c:393)
  LOG:  Checkpoint segments are being created too frequently (3 secs)
         Consider increasing CHECKPOINT SEGMENTS
  IN:  sigusr1_handler (postmaster.c:2504)
  ERROR: CopyReadAttribute: Literal carriage return data value
  found in input that has newline termination; use \r
  CONTEXT: COPY FROM, line 146178
  IN:  CopyReadAttribute (copy.c:1650)
  FATAL:  Socket command type
   unknown
  IN:  SocketBackend (postgres.c:294)


> One thing to watch out for is that cvs-tip pg_dump won't talk to a
> pre-7.4 server at all (if it does, you've got some shared-library
> misalignment).  That will have to be fixed, but it's not on
> the critical path at the moment.

That could explain everything.  I think (but can't promise) that
I had the cvs-tip pg_dump.  However it's complaining about '\r's
and I see that dumputils.c in cvs-tip looks like it escapes '\r's.
OTOH, I'm happy to run it again if you need to be sure (takes
about 10 hours, so I'd have results next day).

    Ron

Actually I'm using pyPgSQL. How does ASODB do this? Does it know some lower-level calls to
determine absolute position or just it just cache ALL results from the query up front? If the former,
then I'd like to know how they do it and perhaps I can implement this for pyPgSQL.

    thanx & later,

        Ben Scherrey

4/29/2003 11:04:47 AM, Dennis Gearon <gearond@cvc.net> wrote:

>If you are using PHP, the ADODB library will do caching of results on the server file system and
allow you to 'cursor' through it. Of course, if you need requerying, you'd have to trigger that and pay
the time penalty.

"Ron Mayer" <ron@intervideo.com> writes:
> The last dozen lines or so are... (sorry if there are any typos,
> copy/paste not working).

>   ERROR: CopyReadAttribute: Literal carriage return data value
>   found in input that has newline termination; use \r
>   CONTEXT: COPY FROM, line 146178
>   IN:  CopyReadAttribute (copy.c:1650)
>   FATAL:  Socket command type
>    unknown
>   IN:  SocketBackend (postgres.c:294)

This is odd and disturbing.  7.2 and later servers should never generate
an unescaped \r in COPY output, so the first error shouldn't appear;
and even if it did, the new FE/BE protocol is supposed to prevent loss
of message-boundary sync, which the second error suggests is happening
anyway.  It's really not clear what's being sent or who's at fault.

Unfortunately, I can't think of any painless method of tracing down an
error that is happening 146178 lines into a bulk COPY :-(.  If you are
running this across a TCP socket, maybe you could capture the traffic
with tcpdump --- if so, looking at the last few dozen packets in each
direction would be mighty useful ...

            regards, tom lane

Tom wrote:

> This is odd and disturbing.  7.2 and later servers should never generate
> an unescaped \r in COPY output, so the first error shouldn't appear;
> and even if it did, the new FE/BE protocol is supposed to prevent loss
> of message-boundary sync, which the second error suggests is happening
> anyway.  It's really not clear what's being sent or who's at fault.
>
> Unfortunately, I can't think of any painless method of tracing down an
> error that is happening 146178 lines into a bulk COPY :-(.  If you are
> running this across a TCP socket, maybe you could capture the traffic
> with tcpdump --- if so, looking at the last few dozen packets in each
> direction would be mighty useful ...

Gladly, but that's new to me so it might take a bit.


Is it useful to see what pg_restore does without the "-d" flag?

Switching back to tools I know, I see that trying
   pg_restore --no-acl -O -v -U logs < /mnt/data1/tmp/logs2.dmp > bad.sql
with no "-d" flag, followed by
   cat -n bad.sql | LANG=C grep d_ref
tells me that that copy command started 26,869,936 lines into the dump...
followed by
   tail +26869938 bad.sql | head 146188 | tail -20
and do a hex dump of the result, I do indeed see a "0x0d" character
at the end of one of the fields there.


I can create a one-row-table
   create table tmp_bad as select * from d_ref where ref_id=145278;
and dump it
   copy tmp_bad to '/tmp/zzz';
and I see the "0x0d" in a hex dump of that file as well.


Given your first statement, I think this means my old 7.2 database is
somehow broken.


On this database
   select version();
gives me
   Postgresql 7.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7)


Later, (though probably not til the weekend) I'd be happy to try
the tcpdump if that's a better tool, though; since it sounds like
the new protocol should have recovered more gracefully than it did.

   Thanks for your help!
   Ron

"Ron Mayer" <ron@intervideo.com> writes:
> I can create a one-row-table
>    create table tmp_bad as select * from d_ref where ref_id=145278;
> and dump it
>    copy tmp_bad to '/tmp/zzz';
> and I see the "0x0d" in a hex dump of that file as well.

> Given your first statement, I think this means my old 7.2 database is
> somehow broken.

Hm [sounds of digging in archives] ... it looks like the fix to emit
"\r" rather than an actual 0x0d byte was applied between 7.2 and 7.2.1.
If that's an actual 7.2 installation, I can offer you many excellent
reasons why you should update it to 7.2.4 forthwith ...

> Later, (though probably not til the weekend) I'd be happy to try
> the tcpdump if that's a better tool, though; since it sounds like
> the new protocol should have recovered more gracefully than it did.

Yes, there's still the question of why it lost sync after the first
error.  Please investigate when you get a chance.

            regards, tom lane

They cache all the results in the file system. I assume that there's a different command for moving the curosor, (a
ADODBobject method), and that it goes to the filesystem to fetch the results. Not really that much different than what
adatabase does with curors except maybe the db keeps it in memory. ADODB transparently uses native cursors if they are
available.So it's meant to take you to Oracle, Sybase, DB2, and others as your project grous. 

Benjamin Scherrey wrote:
> Actually I'm using pyPgSQL. How does ASODB do this? Does it know some lower-level calls to
> determine absolute position or just it just cache ALL results from the query up front? If the former,
> then I'd like to know how they do it and perhaps I can implement this for pyPgSQL.
>
>     thanx & later,
>
>         Ben Scherrey
>
> 4/29/2003 11:04:47 AM, Dennis Gearon <gearond@cvc.net> wrote:
>
>
>>If you are using PHP, the ADODB library will do caching of results on the server file system and
>
> allow you to 'cursor' through it. Of course, if you need requerying, you'd have to trigger that and pay
> the time penalty.
>
>
>
>

Tom wrote...
> > > [from older msg] If you are running this across a TCP socket,
> > > maybe you could capture the traffic with tcpdump --- if so,
> > > looking at the last few dozen packets in each direction would
> > > be mighty useful ...]
> >
> > Later, (though probably not til the weekend) I'd be happy to try
> > the tcpdump if that's a better tool, though; since it sounds like
> > the new protocol should have recovered more gracefully than it did.
>
> Yes, there's still the question of why it lost sync after the first
> error.  Please investigate when you get a chance.

I've never used tcpdump, so any pointers to what's valuable to
you would be appreciated.

Is
  tcpdump -X -x -s 8192 -i any port 5432 | tail -[a_few_thousand]
the most useful to you?  Or are other flags better?




> [quoted out of order]
> [...]the fix to emit "\r" [...] was applied between 7.2 and 7.2.1[...]
>
> If that's an actual 7.2 installation, I can offer you many excellent
> reasons why you should update it to 7.2.4 forthwith ...

Well, the system it's running has been working flawlessly over a year,
and due to changing job priorities and conservatism about touching
working systems I'm reluctant. :-)  The hard disk space (160G) will
probably fill up in Q4 this year, so I pointed the new guys looking
at 7.3.X or 7.4.X  for the next generation system, hence this discovery.

   Ron

"Ron Mayer" <ron@intervideo.com> writes:
> I've never used tcpdump, so any pointers to what's valuable to
> you would be appreciated.

> Is
>   tcpdump -X -x -s 8192 -i any port 5432 | tail -[a_few_thousand]
> the most useful to you?  Or are other flags better?

Sounds good for starters.

            regards, tom lane