selecting large result sets in psql using cursors

Chris Mair <list@1006.org> writes:

attached is the new and fixed version of the patch for selecting
large result sets from psql using cursors.

The is_select_command bit is wrong because it doesn't allow for left
parentheses in front of the SELECT keyword (something entirely
reasonable when considering big union/intersect/except trees).
Also you'd need to allow for VALUES as the first keyword.
But isn't the whole thing unnecessary? ISTM you could just ship the
query with the DECLARE CURSOR prepended, and see whether you get a
syntax error or not.

At some point we ought to extend libpq enough to expose the V3-protocol
feature that allows partial fetches from portals; that would be a
cleaner way to implement this feature. However since nobody has yet
proposed a good API for this in libpq, I don't object to implementing
\u with DECLARE CURSOR for now.

BTW, \u seems not to have any mnemonic value whatsoever ... isn't there
some other name we could use?

regards, tom lane

Tom Lane wrote:

BTW, \u seems not to have any mnemonic value whatsoever ... isn't
there some other name we could use?

Ever since pgsql-patches replies started going to -hackers, threading
doesn't work anymore, so I for one can't tell what this refers to at
all.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:

Tom Lane wrote:

BTW, \u seems not to have any mnemonic value whatsoever ... isn't
there some other name we could use?

Ever since pgsql-patches replies started going to -hackers, threading
doesn't work anymore, so I for one can't tell what this refers to at
all.

I see the original posting here:

http://archives.postgresql.org/pgsql-patches/2006-07/msg00287.php

but I don't remember seeing this posting at all, and it isn't saved in
my mailbox either. Strange.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

On Thu, 2006-08-17 at 03:14 -0400, Bruce Momjian wrote:

Peter Eisentraut wrote:

Tom Lane wrote:

BTW, \u seems not to have any mnemonic value whatsoever ... isn't
there some other name we could use?

Ever since pgsql-patches replies started going to -hackers, threading
doesn't work anymore, so I for one can't tell what this refers to at
all.

I see the original posting here:

http://archives.postgresql.org/pgsql-patches/2006-07/msg00287.php

but I don't remember seeing this posting at all, and it isn't saved in
my mailbox either. Strange.

FWIW I saw it.

--
Simon Riggs
EnterpriseDB http://www.enterprisedb.com

Peter Eisentraut <peter_e@gmx.net> writes:

Ever since pgsql-patches replies started going to -hackers, threading
doesn't work anymore, so I for one can't tell what this refers to at
all.

Yeah, that experiment hasn't seemed to work all that well for me
either. Do you have another idea to try, or do you just want to revert
to the old way?

regards, tom lane

Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

Ever since pgsql-patches replies started going to -hackers, threading
doesn't work anymore, so I for one can't tell what this refers to at
all.

Yeah, that experiment hasn't seemed to work all that well for me
either. Do you have another idea to try, or do you just want to revert
to the old way?

I'd vote for reverting to the old way. Anyone serious about hacking
should be on both lists.

Joe

Joe Conway wrote:

Tom Lane wrote:

Peter Eisentraut <peter_e@gmx.net> writes:

Ever since pgsql-patches replies started going to -hackers, threading
doesn't work anymore, so I for one can't tell what this refers to at
all.

Yeah, that experiment hasn't seemed to work all that well for me
either. Do you have another idea to try, or do you just want to revert
to the old way?

I'd vote for reverting to the old way. Anyone serious about hacking
should be on both lists.

+1

--
Alvaro Herrera http://www.CommandPrompt.com/
PostgreSQL Replication, Consulting, Custom Development, 24x7 support

Tom, all:

I thought the strategy was to provide a way to subscribe to
pgsql-patches, get the text of the messages, and not get the
attachments. Was that techincally infeasable?

--Josh

Hi,

thanks for reviewing this :)

attached is the new and fixed version of the patch for selecting
large result sets from psql using cursors.

The is_select_command bit is wrong because it doesn't allow for left
parentheses in front of the SELECT keyword (something entirely
reasonable when considering big union/intersect/except trees).
Also you'd need to allow for VALUES as the first keyword.

You're right, I improved is_select_command to take these into account.
(Btw, I didn't even know a command VALUES existed..)

But isn't the whole thing unnecessary? ISTM you could just ship the
query with the DECLARE CURSOR prepended, and see whether you get a
syntax error or not.

I find it neat that \u gives a good error message if someone
executes a non-select query. If I leave that out there is no way to tell
a real syntax error from one cause by executing non-selects...

Anyway, if we don't want the extra check, I can skip the
is_select_command call, of course.

Patch with fix against current CVS is attached.

At some point we ought to extend libpq enough to expose the V3-protocol
feature that allows partial fetches from portals; that would be a
cleaner way to implement this feature. However since nobody has yet
proposed a good API for this in libpq, I don't object to implementing
\u with DECLARE CURSOR for now.

BTW, \u seems not to have any mnemonic value whatsoever ... isn't there
some other name we could use?

True :)
Since buffer commands all have a single char I wanted a single char one
too. The "c" for "cursor" was taken already, so i choose the "u" (second
char in "cursor"). If somebody has a better suggestion, let us know ;)

Bye, Chris.

PS: I'm traveling Fri 18th - Fri 25th and won't check mail often.

--

Chris Mair
http://www.1006.org

Tom Lane wrote:

Yeah, that experiment hasn't seemed to work all that well for me
either. Do you have another idea to try, or do you just want to
revert to the old way?

Since almost the first day I hacked on PostgreSQL I have been filtering
both lists into the same folder, so they pretty much appear to be one
and the same to me anyway. The only step that would optimize that
situation further would be doing away with pgsql-patches and telling
people to send patches to pgsql-hackers. I understand that some people
may not care for the extra volume that the patches bring in. But with
250+ kB of hackers mail a day, the few patches don't seem all that
significant. And to be serious about hacking (and tracking the
hacking) you need to get both lists anyway, so it would make sense to
me to just have one.

--
Peter Eisentraut
http://developer.postgresql.org/~petere/

Peter Eisentraut wrote:

Tom Lane wrote:

Yeah, that experiment hasn't seemed to work all that well for me
either. Do you have another idea to try, or do you just want to
revert to the old way?

Since almost the first day I hacked on PostgreSQL I have been filtering
both lists into the same folder, so they pretty much appear to be one
and the same to me anyway. The only step that would optimize that
situation further would be doing away with pgsql-patches and telling
people to send patches to pgsql-hackers. I understand that some people
may not care for the extra volume that the patches bring in. But with
250+ kB of hackers mail a day, the few patches don't seem all that
significant. And to be serious about hacking (and tracking the
hacking) you need to get both lists anyway, so it would make sense to
me to just have one.

how many very large patches are sent? Not too many. We could in fact put
a limit on the attachment size and make people publish very large
patches some other way (on the web, say?)

cheers

andrew

Ever since pgsql-patches replies started going to -hackers,
threading doesn't work anymore, so I for one can't tell what this
refers to at all.

Yeah, that experiment hasn't seemed to work all that well for me
either. Do you have another idea to try, or do you just want to
revert to the old way?

I'd vote for reverting to the old way. Anyone serious about hacking
should be on both lists.

Then why bother with two different lists?

If developers need to be on both list (which I beleive they do), and the
focus of both lists is developers, then why not just remove one of them
and get rid of the problem?

//Magnus

Chris Mair wrote:

At some point we ought to extend libpq enough to expose the V3-protocol
feature that allows partial fetches from portals; that would be a
cleaner way to implement this feature. However since nobody has yet
proposed a good API for this in libpq, I don't object to implementing
\u with DECLARE CURSOR for now.

BTW, \u seems not to have any mnemonic value whatsoever ... isn't there
some other name we could use?

True :)
Since buffer commands all have a single char I wanted a single char one
too. The "c" for "cursor" was taken already, so i choose the "u" (second
char in "cursor"). If somebody has a better suggestion, let us know ;)

I think a new backslash variable isn't the way to go. I would use a
\pset variable to control what is happening.

--
Bruce Momjian bruce@momjian.us
EnterpriseDB http://www.enterprisedb.com

+ If your life is a hard drive, Christ can be your backup. +

Magnus Hagander wrote:

Then why bother with two different lists?

If developers need to be on both list (which I beleive they do), and the
focus of both lists is developers, then why not just remove one of them
and get rid of the problem?

I wouldn't argue with that. It would be at least equally good from my
perspective, and maybe slightly better.

Joe

On Aug 17, 2006, at 9:30 AM, Magnus Hagander wrote:

Ever since pgsql-patches replies started going to -hackers,
threading doesn't work anymore, so I for one can't tell what this
refers to at all.

Yeah, that experiment hasn't seemed to work all that well for me
either. Do you have another idea to try, or do you just want to
revert to the old way?

I'd vote for reverting to the old way. Anyone serious about hacking
should be on both lists.

Then why bother with two different lists?

If developers need to be on both list (which I beleive they do),
and the
focus of both lists is developers, then why not just remove one of
them
and get rid of the problem?

One reason might be that a lot of application developers who develop
applications or modules associated with PG, but not the core PG code
itself also lurk on -hackers, as it's by far the best way to keep up
with
the status of various PG enhancements (and also an excellent place
to pick up a lot of undocumented good practices).

Cheers,
Steve

Replying to myself...

Patch with fix against current CVS is attached.

Alvaro Herrera sent two fixes off-list: a typo and
at the end of SendQueryUsingCursor I sould COMMIT, not ROLLBACK.

So, one more version (6) that fixes these too is attached.

Bye, Chris.

PS: I'm keeping this on both lists now, hope it's ok.

--
Chris Mair
http://www.1006.org

Patch with fix against current CVS is attached.

Forgot the attachment... soory.

--

Chris Mair
http://www.1006.org

I'd vote for reverting to the old way. Anyone serious

about hacking

should be on both lists.

Then why bother with two different lists?

If developers need to be on both list (which I beleive they

do), and

the focus of both lists is developers, then why not just

remove one of

them and get rid of the problem?

One reason might be that a lot of application developers who
develop applications or modules associated with PG, but not
the core PG code itself also lurk on -hackers, as it's by far
the best way to keep up with the status of various PG
enhancements (and also an excellent place to pick up a lot of
undocumented good practices).

Won't you learn even more good practices if you actually see the patches
as well? :-P

The bottom line is, I think, does the volume of mail on -patches
actually make a big difference given the much higher volume on -hackers?
(If you just want to skip the patches, just set up attachment filtering
on the list..)

//Magnus

On Aug 17, 2006, at 9:30 AM, Magnus Hagander wrote:

Then why bother with two different lists?

If developers need to be on both list (which I beleive they do), and the
focus of both lists is developers, then why not just remove one of them
and get rid of the problem?

Didn't I say something about not being able to convince people by arguing but
being sure people would come around eventually? :)

Steve Atkins <steve@blighty.com> writes:

One reason might be that a lot of application developers who develop
applications or modules associated with PG, but not the core PG code
itself also lurk on -hackers, as it's by far the best way to keep up with
the status of various PG enhancements (and also an excellent place
to pick up a lot of undocumented good practices).

Well if they want to keep up with the status of various PG enhancements they
had better be seeing the patches too since that's where that information is!
They don't have to read the actual patches but at least see the messages
describing them and their status. As the work progresses that's the only way
to clearly understand the status of it.

I originally suggested having the list manager strip out attachments, save
them on a web accessible place and insert a url in the message. I think we're
blocking on having that implemented in majordomo. If people are coming around
to my suggestion then I'll talk to Marc and see if I can help implement that.
I'm not sure what the majordomo code looks like so I don't know how easy it is
to hack in filters like that.

--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com