Re: [HACKERS] [PATCHES] selecting large result sets in psql using
> If this will be used interactively, it would be nice to have both. That > way if you're running a bunch of cursor fetches, you can just do one > \set, but if you only want to run one or a few you can use \gc and not > mess around with \set. But I don't know how common interactive usage > will be. Presumably code can easily be taught to do either, though \set > would probably be less invasive to older code that someone wants to > change. I don't know if having both is really that desirable. In particular, as Peter pointed out, \gc is not possible because it means \g outputting to file 'c' in the current version of psql. > Another thought (which probably applies more to \set than \gc): if you > could set a threshold of how many rows the planner is estimating before > automatically switching to a cursor, that would simplify things. > Interactively, you could just let psql/PostgreSQL decide which was best > for each query. Same is true in code, though it probably matters more > for existing code than new code. Right now, this would be very hard, because the existing output code cannot readily be adapted to using cursors. My patch does fetching and output in a new code path that is very simple, but doesn't do all the nice formatting for human readability. So moving seamlessly between the two behind the scenes is not possible, least refactoring the whole output code of psql. Tom Lane mentioned the solution at the root of all this eventually might be a new version of libpq that does large fetches in chunks on its own. But, we're talking > 8.2.0 then... Bye :) Chris. -- Chris Mair http://www.1006.org ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
On Fri, Aug 18, 2006 at 10:16:12AM -0400, Tom Lane wrote: > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > A \set variable would make sense to me. > > So Peter and Bruce like a \set variable, Chris and I like a different > command. Seems like a tie ... more votes out there anywhere? If this will be used interactively, it would be nice to have both. That way if you're running a bunch of cursor fetches, you can just do one \set, but if you only want to run one or a few you can use \gc and not mess around with \set. But I don't know how common interactive usage will be. Presumably code can easily be taught to do either, though \set would probably be less invasive to older code that someone wants to change. Another thought (which probably applies more to \set than \gc): if you could set a threshold of how many rows the planner is estimating before automatically switching to a cursor, that would simplify things. Interactively, you could just let psql/PostgreSQL decide which was best for each query. Same is true in code, though it probably matters more for existing code than new code. -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
[EMAIL PROTECTED] wrote: To cut the Gordon knot I'm going to suggest we use: ITYM "Gordian" - see http://en.wikipedia.org/wiki/Gordian_Knot cheers andrew ;-) ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
>> To cut the Gordon knot I'm going to suggest we use: > >> \set CURSOR_FETCH fetch_count > >> and \g and ; are modified such that when they see >> this variable set to fetch_count > 0 and the buffer >> is a select they would use the modified fetch/output code. > >> Does this sound reasonable to everyone? > > OK with me, but maybe call the variable FETCH_COUNT, to avoid the > presupposition that the implementation uses a cursor. As I mentioned > before, I expect we'll someday rework it to not use that. > > regards, tom lane Ok, sounds good. I'm travelling this week, but can send an updated patch during the weekend. Bye, Chris. -- Chris Mair http://www.1006.org ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: pgsql-patches reply-to (was Re: [HACKERS] [PATCHES] selecting large result sets in psql using cursors)
> -Original Message- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] On Behalf Of Robert Treat > Sent: 23 August 2006 04:16 > To: pgsql-hackers@postgresql.org > Cc: Peter Eisentraut; Tom Lane > Subject: Re: pgsql-patches reply-to (was Re: [HACKERS] > [PATCHES] selecting large result sets in psql using cursors) > > I've always been > quite amazed how much email some of the folks here manage to > process... I > suppose I could just chalk it up to a pine vs. gui thing, but > I suspect there > are some other tricks people have to make emails more > manageable (anyone > combine all pg mail to one folder?) More or less - one for -www, webmaster and slaves stuff, and another for -odbc, -hackers, -patches, -committers, -perform, -general and so on. I do keep additional ones for FG and -core though. Everything is auto-filtered at our Exchange server so it's organised as I like whether I pick it up on PDA, webmail, PC or Mac. Regards, Dave. ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: pgsql-patches reply-to (was Re: [HACKERS] [PATCHES] selecting large result sets in psql using cursors)
On Aug 23, 2006, at 12:15 , Robert Treat wrote: On Thursday 17 August 2006 11:55, 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. I'm curious, do you combine any other lists like that? I've played around with that idea (for example, I used to combine webmaster emails, pgsql-www, and -slaves emails but the slaves traffic was too high so I had to split it back out). As someone subscribed to a good dozen pg lists, I've always been quite amazed how much email some of the folks here manage to process... I suppose I could just chalk it up to a pine vs. gui thing, but I suspect there are some other tricks people have to make emails more manageable (anyone combine all pg mail to one folder?) Reading pg ml mail is relatively high on my list of things I want to do, so I have it all come into my inbox. However, with other mailing lists (e.g., ruby-talk and the RoR lists which have the highest volume of any mailing list I'm subscribed to) I generally have them routed into their own folder. I usually let lower-volume mailing lists just end up in my inbox as well Mail.app on Mac OS X 10.4. I make heavy use of the Mail Act-on[1] plugin to make further processing of mail easier (such as archiving to appropriate folders). Michael Glaesemann grzm seespotcode net [1](http://www.indev.ca/MailActOn.html) ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: pgsql-patches reply-to (was Re: [HACKERS] [PATCHES] selecting large result sets in psql using cursors)
Bruce Momjian <[EMAIL PROTECTED]> writes: > Robert Treat wrote: >> ... some other tricks people have to make emails more manageable (anyone >> combine all pg mail to one folder?) > Yes, all mine are in one folder, and I use elm ME. It is faster than a > GUI email client. All my PG list mail goes into one folder too. The list bot is pretty good (not perfect :-() about sending only one copy of crossposted messages. Personally I use exmh, but I don't expect people who don't remember the Mesozoic era to know what that is. regards, tom lane ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: pgsql-patches reply-to (was Re: [HACKERS] [PATCHES] selecting large result sets in psql using cursors)
On Thursday 17 August 2006 11:55, 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.
I'm curious, do you combine any other lists like that? I've played around
with that idea (for example, I used to combine webmaster emails, pgsql-www,
and -slaves emails but the slaves traffic was too high so I had to split it
back out). As someone subscribed to a good dozen pg lists, I've always been
quite amazed how much email some of the folks here manage to process... I
suppose I could just chalk it up to a pine vs. gui thing, but I suspect there
are some other tricks people have to make emails more manageable (anyone
combine all pg mail to one folder?)
--
Robert Treat
Build A Brighter LAMP :: Linux Apache {middleware} PostgreSQL
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
<[EMAIL PROTECTED]> writes: > To cut the Gordon knot I'm going to suggest we use: > \set CURSOR_FETCH fetch_count > and \g and ; are modified such that when they see > this variable set to fetch_count > 0 and the buffer > is a select they would use the modified fetch/output code. > Does this sound reasonable to everyone? OK with me, but maybe call the variable FETCH_COUNT, to avoid the presupposition that the implementation uses a cursor. As I mentioned before, I expect we'll someday rework it to not use that. regards, tom lane ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
>> True. They could even put it in .psqlrc if they want. Basically need >> a way to modify \g. Seems a \set is the way we have always done such >> modifications in the past. The big question is whether this is >> somehow different. Personally, I don't think so. > > If you want a \set variable, then at least make it do something useful: > make it an integer var that sets the fetch count, rather than > hard-wiring the count as is done in Chris' existing patch. Zero (or > perhaps unset) disables. > > regards, tom lane Hello, first I must admit that I misunderstood Bruce post. I thought he meant to tweak \pset (psql command to set formatting). This didn't make sense to me. Only now I realize everyone is talking about \set (psql internal variable). That being said, I'm a bit unsure now what we should do. As Peter said, it is true that mostly this feature would be used for scripting where \set and \unset are not as cumbersome to use as in an interactive session. Tom's idea to factor in the fetch count as an option is also very tempting. To cut the Gordon knot I'm going to suggest we use: \set CURSOR_FETCH fetch_count and \g and ; are modified such that when they see this variable set to fetch_count > 0 and the buffer is a select they would use the modified fetch/output code. Does this sound reasonable to everyone? Bye :) Chris. -- Chris Mair http://www.1006.org ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
Bruce Momjian <[EMAIL PROTECTED]> writes: > True. They could even put it in .psqlrc if they want. Basically need a > way to modify \g. Seems a \set is the way we have always done such > modifications in the past. The big question is whether this is somehow > different. Personally, I don't think so. If you want a \set variable, then at least make it do something useful: make it an integer var that sets the fetch count, rather than hard-wiring the count as is done in Chris' existing patch. Zero (or perhaps unset) disables. regards, tom lane ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
David Fetter wrote: > On Fri, Aug 18, 2006 at 10:16:12AM -0400, Tom Lane wrote: > > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > > A \set variable would make sense to me. > > > > So Peter and Bruce like a \set variable, Chris and I like a > > different command. Seems like a tie ... more votes out there > > anywhere? > > It seems to me that a \set variable lets people use minimal > intrusiveness on scripts, etc., as they'll just set it when they start > needing cursor-ized result sets and unset it when finished. True. They could even put it in .psqlrc if they want. Basically need a way to modify \g. Seems a \set is the way we have always done such modifications in the past. The big question is whether this is somehow different. Personally, I don't think so. -- Bruce Momjian [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
On Fri, Aug 18, 2006 at 10:16:12AM -0400, Tom Lane wrote: > Peter Eisentraut <[EMAIL PROTECTED]> writes: > > A \set variable would make sense to me. > > So Peter and Bruce like a \set variable, Chris and I like a > different command. Seems like a tie ... more votes out there > anywhere? It seems to me that a \set variable lets people use minimal intrusiveness on scripts, etc., as they'll just set it when they start needing cursor-ized result sets and unset it when finished. Just my $.02 :) Cheers, D -- David Fetter <[EMAIL PROTECTED]> http://fetter.org/ phone: +1 415 235 3778AIM: dfetter666 Skype: davidfetter Remember to vote! ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
Peter Eisentraut <[EMAIL PROTECTED]> writes: > A \set variable would make sense to me. So Peter and Bruce like a \set variable, Chris and I like a different command. Seems like a tie ... more votes out there anywhere? regards, tom lane ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
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/ ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
> > >> 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
Tom Lane wrote: > Bruce Momjian <[EMAIL PROTECTED]> 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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] selecting large result sets in psql using cursors
Bruce Momjian <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: pgsql-patches reply-to (was Re: [HACKERS] [PATCHES] selecting large result sets in psql using cursors)
On Thu, Aug 17, 2006 at 09:20:43AM -0400, Tom Lane wrote: > Peter Eisentraut <[EMAIL PROTECTED]> 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? Has that actually been working? I seem to still get replies in both places... -- Jim C. Nasby, Sr. Engineering Consultant [EMAIL PROTECTED] Pervasive Software http://pervasive.comwork: 512-231-6117 vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
> > > 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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
> > Patch with fix against current CVS is attached.
Forgot the attachment... soory.
--
Chris Mair
http://www.1006.org
diff -rc pgsql.original/doc/src/sgml/ref/psql-ref.sgml pgsql/doc/src/sgml/ref/psql-ref.sgml
*** pgsql.original/doc/src/sgml/ref/psql-ref.sgml 2006-08-17 16:50:58.0 +0200
--- pgsql/doc/src/sgml/ref/psql-ref.sgml 2006-08-17 18:02:29.0 +0200
***
*** 1201,1206
--- 1201,1231
+
+
+ \u [ { filename | |command } ]
+
+
+
+ Sends the current query input buffer to the server and
+ optionally stores the query's output in filename or pipes the output
+ into a separate Unix shell executing command.
+ Unlike \g, \u works only
+ for select statements and uses a cursor to retrieve the result set.
+ Therefore \u uses only a limited amount of memory,
+ regardless the size of the result set. It can be used whenever a result
+ set needs to be retrieved that exceeds the client's memory resources.
+ \u always gives unaligned output. It does, however
+ use the current field separator (see \pset).
+ \u gives an error when trying to execute something
+ that is not a SELECT statement.
+
+
+
+
+
\help (or \h) [ command ]
diff -rc pgsql.original/src/bin/psql/command.c pgsql/src/bin/psql/command.c
*** pgsql.original/src/bin/psql/command.c 2006-08-17 16:51:04.0 +0200
--- pgsql/src/bin/psql/command.c 2006-08-17 18:02:49.0 +0200
***
*** 830,835
--- 830,866
}
}
+ /*
+ * \u executes the current query buffer using a cursor
+ */
+ else if (strcmp(cmd, "u") == 0)
+ {
+ char *fname = psql_scan_slash_option(scan_state,
+ OT_FILEPIPE, NULL, false);
+
+ if (!fname)
+ pset.gfname = NULL;
+ else
+ {
+ expand_tilde(&fname);
+ pset.gfname = pg_strdup(fname);
+ }
+ free(fname);
+
+ if (query_buf && query_buf->len == 0)
+ {
+ if (!quiet)
+ {
+ puts(_("Query buffer is empty."));
+ fflush(stdout);
+ }
+ }
+ else
+ {
+ status = PSQL_CMD_SEND_USING_CURSOR;
+ }
+ }
+
/* \unset */
else if (strcmp(cmd, "unset") == 0)
{
diff -rc pgsql.original/src/bin/psql/command.h pgsql/src/bin/psql/command.h
*** pgsql.original/src/bin/psql/command.h 2006-08-17 16:51:04.0 +0200
--- pgsql/src/bin/psql/command.h 2006-08-17 16:55:25.0 +0200
***
*** 16,21
--- 16,22
{
PSQL_CMD_UNKNOWN = 0, /* not done parsing yet (internal only) */
PSQL_CMD_SEND, /* query complete; send off */
+ PSQL_CMD_SEND_USING_CURSOR, /* query complete; send off using cursor */
PSQL_CMD_SKIP_LINE,/* keep building query */
PSQL_CMD_TERMINATE,/* quit program */
PSQL_CMD_NEWEDIT,/* query buffer was changed (e.g., via \e) */
diff -rc pgsql.original/src/bin/psql/common.c pgsql/src/bin/psql/common.c
*** pgsql.original/src/bin/psql/common.c 2006-08-17 16:51:04.0 +0200
--- pgsql/src/bin/psql/common.c 2006-08-17 18:40:51.0 +0200
***
*** 28,33
--- 28,34
#include "command.h"
#include "copy.h"
#include "mb/pg_wchar.h"
+ #include "mbprint.h"
/* Workarounds for Windows */
***
*** 52,58
((T)->millitm - (U)->millitm))
#endif
!
static bool command_no_begin(const char *query);
/*
--- 53,59
((T)->millitm - (U)->millitm))
#endif
! static bool is_select_command(const char *query);
static bool command_no_begin(const char *query);
/*
***
*** 952,957
--- 953,1146
/*
+ * SendQueryUsingCursor: send the (SELECT) query string to the backend
+ * using a cursor and print out results.
+ *
+ * Unlike with SendQuery(), single step mode, ON_ERROR_ROLLBACK mode,
+ * timing and format settings (except delimiters) are NOT honoured.
+ *
+ * Returns true if the query executed successfully, false otherwise.
+ */
+ bool
+ SendQueryUsingCursor(const char *query)
+ {
+ PGresult *results;
+ bool started_txn = false;
+ PQExpBufferData buf;
+ FILE *queryFout_copy = NULL;
+ bool queryFoutPipe_copy = false;
+ intntuples, nfields = -1;
+ inti, j;
+
+ if (!pset.db)
+ {
+ psql_error("You are currently not connected to a database.\n");
+ return false;
+ }
+
+ if (!is_select_command(query))
+ {
+ psql_error("Need a SELECT command to perform cursor fetch.\n");
+ return false;
+ }
+
+ if (VariableEquals(pset.vars, "ECHO", "queries"))
+ {
+ puts(query);
+ fflush(stdout);
+ }
+
+ if (pset.logfile)
+ {
+ fprintf(pset.logfile,
+ _("* QUERY **\n"
+ "%s\n"
+ "**\n\n"), query);
+ fflush(pset.logfile);
+ }
+
+ SetCancelConn();
+
+ /* prepare to write output to \u argument, if any */
+ if (pset.gfname)
+ {
+ queryFout_copy = pset.queryFout
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
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 ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: pgsql-patches reply-to (was Re: [HACKERS] [PATCHES] selecting large result sets in psql using cursors)
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/ ---(end of broadcast)--- TIP 5: don't forget to increase your free space map settings
Re: [HACKERS] [PATCHES] selecting large result sets in psql using cursors
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
diff -rc pgsql.original/doc/src/sgml/ref/psql-ref.sgml pgsql/doc/src/sgml/ref/psql-ref.sgml
*** pgsql.original/doc/src/sgml/ref/psql-ref.sgml 2006-08-17 16:50:58.0 +0200
--- pgsql/doc/src/sgml/ref/psql-ref.sgml 2006-08-17 16:54:41.0 +0200
***
*** 1201,1206
--- 1201,1231
+
+
+ \u [ { filename | |command } ]
+
+
+
+ Sends the current query input buffer to the server and
+ optionally stores the query's output in filename or pipes the output
+ into a separate Unix shell executing command.
+ Unlike \g, \u works only
+ for select statements and uses a cursor to retrieve the result set.
+ Therefore \u uses only a limited amount of memory,
+ regardless the size of the result set. It can be used whenever a result
+ set needs to be retrieved that exeeds the client's memory resources.
+ \u always gives unaligned output. It does, however
+ use the current field separator (see \pset).
+ \u gives an error when trying to execute something
+ that is not a SELECT statement.
+
+
+
+
+
\help (or \h) [ command ]
diff -rc pgsql.original/src/bin/psql/command.c pgsql/src/bin/psql/command.c
*** pgsql.original/src/bin/psql/command.c 2006-08-17 16:51:04.0 +0200
--- pgsql/src/bin/psql/command.c 2006-08-17 16:55:25.0 +0200
***
*** 830,835
--- 830,866
}
}
+ /*
+ * \u executes the current query buffer using a cursor
+ */
+ else if (strcmp(cmd, "u") == 0)
+ {
+ char *fname = psql_scan_slash_option(scan_state,
+ OT_FILEPIPE, NULL, false);
+
+ if (!fname)
+ pset.gfname = NULL;
+ else
+ {
+ expand_tilde(&fname);
+ pset.gfname = pg_strdup(fname);
+ }
+ free(fname);
+
+ if (query_buf && query_buf->len == 0)
+ {
+ if (!quiet)
+ {
+ puts(_("Query buffer is empty."));
+ fflush(stdout);
+ }
+ }
+ else
+ {
+ status = PSQL_CMD_SEND_USING_CURSOR;
+ }
+ }
+
/* \unset */
else if (strcmp(cmd, "unset") == 0)
{
diff -rc pgsql.original/src/bin/psql/command.h pgsql/src/bin/psql/command.h
*** pgsql.original/src/bin/psql/command.h 2006-08-17 16:51:04.0 +0200
--- pgsql/src/bin/psql/command.h 2006-08-17 16:55:25.0 +0200
***
*** 16,21
--- 16,22
{
PSQL_CMD_UNKNOWN = 0, /* not done parsing yet (internal only) */
PSQL_CMD_SEND, /* query complete; send off */
+ PSQL_CMD_SEND_USING_CURSOR, /* query complete; send off using cursor */
PSQL_CMD_SKIP_LINE,/* keep building query */
PSQL_CMD_TERMINATE,/* quit program */
PSQL_CMD_NEWEDIT,/* query buffer was changed (e.g., via \e) */
diff -rc pgsql.original/src/bin/psql/common.c pgsql/src/bin/psql/common.c
*** pgsql.original/src/bin/psql/common.c 2006-08-17 16:51:04.0 +0200
--- pgsql/src/bin/psql/common.c 2006-08-17 16:59:05.0 +0200
***
*** 28,33
--- 28,34
#include "command.h"
#include "copy.h"
#include "mb/pg_wchar.h"
+ #include "mbprint.h"
pgsql-patches reply-to (was Re: [HACKERS] [PATCHES] selecting large result sets in psql using cursors)
Peter Eisentraut <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 4: Have you searched our list archives? http://archives.postgresql.org
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
Re: [HACKERS] [PATCHES] selecting large result sets in psql using
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 [EMAIL PROTECTED] EnterpriseDBhttp://www.enterprisedb.com + If your life is a hard drive, Christ can be your backup. + ---(end of broadcast)--- TIP 9: In versions below 8.0, the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
Re: [HACKERS] [PATCHES] selecting large result sets in psql using cursors
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/ ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [HACKERS] [PATCHES] selecting large result sets in psql using cursors
Chris Mair <[EMAIL PROTECTED]> 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 ---(end of broadcast)--- TIP 3: Have you checked our extensive FAQ? http://www.postgresql.org/docs/faq
