Thread: selecting large result sets in psql using cursors
Hi there, attached is the new and fixed version of the patch for selecting large result sets from psql using cursors. It was previously discussed on hackers: http://archives.postgresql.org/pgsql-hackers/2006-07/msg00231.php Thanks again to Neil Conway for helping with this (the first sketch of the patch was his :) Wondering if this makes a chance to get accepted... Bye :-) Chris.
Attachment
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
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
Attachment
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. +
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
Attachment
> > > 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. IMHO with \pset I'd have different places where I'd need to figure out whether to do the cursor thing and I was a bit reluctant to add stuff to existing code paths. Also the other \pset options are somewhat orthogonal to this one. Just my two EUR cents, of course... :) Bye, Chris. -- Chris Mair http://www.1006.org
Chris Mair wrote: > > > > > 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. > > IMHO with \pset I'd have different places where I'd need to figure > out whether to do the cursor thing and I was a bit reluctant to add > stuff to existing code paths. Also the other \pset options are somewhat > orthogonal to this one. Just my two EUR cents, of course... :) Well, let's see what others say, but \pset seems _much_ more natural for this type of thing to me. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
Bruce Momjian <bruce@momjian.us> writes:
> Chris Mair wrote:
>> 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.
That seems like it would be very awkward to use: you'd have to type
quite a bit to go from one mode to the other.
Personally I think that insisting on a one-letter command name is not
such a good idea if you can't pick a reasonably memorable name.
I'd suggest "\gc" (\g with a Cursor) or "\gb" (\g for a Big query)
or some such.
regards, tom lane
Tom Lane wrote: > Bruce Momjian <bruce@momjian.us> writes: > > Chris Mair wrote: > >> 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. > > That seems like it would be very awkward to use: you'd have to type > quite a bit to go from one mode to the other. > > Personally I think that insisting on a one-letter command name is not > such a good idea if you can't pick a reasonably memorable name. > I'd suggest "\gc" (\g with a Cursor) or "\gb" (\g for a Big query) > or some such. So add it as a modifyer to \g? Yea, that works, but it doesn't work for ';' as a 'go' command, of course, which is perhaps OK. -- Bruce Momjian bruce@momjian.us EnterpriseDB http://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. +
> > >> 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. > > > > That seems like it would be very awkward to use: you'd have to type > > quite a bit to go from one mode to the other. > > > > Personally I think that insisting on a one-letter command name is not > > such a good idea if you can't pick a reasonably memorable name. > > I'd suggest "\gc" (\g with a Cursor) or "\gb" (\g for a Big query) > > or some such. \gc sounds like a good idea to me :) (I must admit gc reminds me about 'garbage collector', which in a weired way is related with what we're doing here... At least more related than 'Great Britain' ;) > So add it as a modifyer to \g? Yea, that works, but it doesn't work for > ';' as a 'go' command, of course, which is perhaps OK. Yes, it was intended to differentiate this command from ';'; Bye, Chris. -- Chris Mair http://www.1006.org
Am Donnerstag, 17. August 2006 20:05 schrieb Chris Mair: > \gc sounds like a good idea to me :) Strictly speaking, in the randomly defined grammer of psql, \gc is \g with an argument of 'c' (try it, it works). I'm not sure what use case you envision for this feature. Obviously, this is for queries with large result sets. I'd guess that people will not normally look at those result sets interactively. If the target audience is instead psql scripting, you don't really need the most convenient command possible. A \set variable would make sense to me. -- Peter Eisentraut http://developer.postgresql.org/~petere/