Bug(?) with cursors using aggregate functions.

postgres has cursors?

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

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:

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' through it. Of course, if you need requerying, you'd have to trigger that and pay the time penalty.

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.

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

"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 ADODB object method), and that it goes to the filesystem to fetch the results. Not really that much different than what a database 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:

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